Lenovo Z500 Windows 10 Backlight Problem

Seems that lots of people have had problems with not being able to adjust the screen backlight…

The solution I (or rather my daughter Smile) found was – while booting your PC – to hold down the Fn-key and then adjust the lighting with the adjustment keys (F11 & F12).

Advertisements

Importing SAS data into PowerBI

Data analysis without visualization is almost useless and to many (end-)users Microsoft Power BI is just the easiest to use, freely available tool around. While the built-in PowerQuery data access feature provides access to a lot of different types of data sources, SAS datasets is, however, not one of them.

But while there is no built in native adapter for SAS data sets in Power BI (which a good many people seem  to be asking for) the “R script” adapter can come in quite handy as a workaround!

For R there are a couple of packages which will read SAS data:  “sas7bdat” and “haven”. It seems that “sas7bdat” is quite slow and as “haven” is the choice in RStudio for importing SAS data, I took that as a seal of approval and went with “haven”.

When “R script” is selected as the data source in PowerBI a script window pops up like so:

image

Here are the few lines of script for my sample:

if (!require(“haven”)) {
   install.packages(“haven”)
}

require(haven)
data_file <- “c:/Users/peh/Documents/SAS/aregn03.sas7bdat”;
sasdata <- read_sas(data_file, catalog_file = NULL, encoding = NULL)

Simply edit the SAS file name (“data_file” – remember to fix the slashes in the path name to R-standard) in the script to suit your own file-path and press OK. Now the Power BI navigator will ask which of the R dataframes in your script to import. Here there is only one (“sasdata”) so select that and click OK and your data will be loaded into the Power BI data model.

The usual memory limitation of R will of course apply to the SAS data being loaded, but in most of cases I think this method will be quite sufficient.

Of course you could export your data from SAS itself to a variety of PowerBI compatible formats, or use R to convert it to ie CSV before loading into Power BI, but I think the above method is quite convenient if you’re a SAS shop and an easy template to communicate even to people without prior knowledge of either SAS or R.

Doing a case sensitive REPLACE in T-Sql

Oh, the wonder of Scandinavian letters (and German, and French, and Dutch) … Well, I’ve just had to do a case sensitive text replacement on my Sql-box, which uses the otherwise case insensitive collation “Danish_Norwegian_CI_AS”.

Fortunately the REPLACE function can take a collation as part of its string expression so my letter substitution ended up like so:

	UPDATE [OEDBAdmin].[dbo].[KontrolRapporter] SET Navn = 
		REPLACE(
			REPLACE(
				REPLACE(
					REPLACE(
						REPLACE(
							REPLACE(
								REPLACE(
									Navn COLLATE Danish_Norwegian_CS_AS,'øe','oe'),
									'æ' COLLATE Danish_Norwegian_CS_AS,'ae'),
									'ø' COLLATE Danish_Norwegian_CS_AS,'oe'),
									'å' COLLATE Danish_Norwegian_CS_AS,'aa'),
									'Æ' COLLATE Danish_Norwegian_CS_AS,'Ae'),
									'Ø' COLLATE Danish_Norwegian_CS_AS,'Oe'),
									'Å' COLLATE Danish_Norwegian_CS_AS,'Aa')

Importing a file into a Sql Server varbinary column

 

Previously I’ve mostly achieved this by using Powershell, but when the file you want to import resides on the Sql server itself (or somewhere accessible to it) it can actually be done with just a few T-SQL statements.

However I need to dynamically name the the file to be imported, and that takes an extra hoop to jump through because the OPENROWSET doesn’t take variable names as a parameter for the file name. There are a good deal of suggestions on the web on how to do this, but I favour simplicity so my solution really tries to cut it down to the bare necessities.

First i created a “Documents” table for storing my files:

CREATE TABLE [dbo].[Documents](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Date]  AS (getdate()),
    [FileName] [varchar](512) NOT NULL,
    [DocumentData] [varbinary](max) NOT NULL,
CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

Then the import function:

DECLARE @FileName VARCHAR(200) = ‘e:\Wheat_vehicles_combine_harvester_farming_john_deere_1920x1200.jpg’;

DECLARE @sql varchar(max) = ‘SELECT ”’ + @filename + ”’,* FROM OPENROWSET(BULK N”’ + @fileName + ”’, SINGLE_BLOB) AS RS’;

INSERT INTO PERH..Documents (FileName, DocumentData)
    EXEC(@sql);

That is really all there is to it – happy coding Smile

PS. Checking for file existence, deleting source file after loading etc can be achieved via the extended stored procedures on your system such as xp_fileexist, xp_delete_file.

Create PDF from Sql with Powershell

Saving the results of a query to PDF would be a neat feature to have in SSMS – but second best is to have a small Powershell script to assist you.

I came across this article which contains a Powershell Module by Dr. Tobias Weltner that nicely wraps  PdfSharp-WPF.dll and then it was jus a matter of processing my own Sql (in this case a list of tables/columns) like so:

Import-Module E:\PowerShell\PDF\PDFTools

$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=LC7207SQL;Database=Watson; Integrated Security=true;”)
$query =
    “
    SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
    FROM INFORMATION_SCHEMA.COLUMNS
    ORDER BY TABLE_NAME, ORDINAL_POSITION
    ”
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter ($query, $connection)
$table = New-Object System.Data.DataTable
$adapter.Fill($table) | out-null

@($table) | Out-PTSPDF -Path $env:temp\report.pdf -Open -A -GroupBy TABLE_NAME

There are a number of parameters sto play with on the Out-PTSPDF module, and if required you can also pipe your data through a formatter, like Format-List, before PDF’ing it.

RegEx in R to extract values between square brackets

Except for RegEx always being a pain, one would think that parsing a string in R is a piece of cake. Well, it is – sort of…

So here goes:

install.packages("stringr")
library(stringr)

formulas <- c(
    "[000275]-[007825]-[007826]-[007827]",
    "[bru]-[007303]-[007301]-[007305]-[007307]"
    )

variables <- regmatches(formulas, gregexpr("\\[.+?\\]", formulas))

Getting R to run on Sql Server 2016

 

I have slowly been moving into the R space for a couple of years, and see it as both an exciting DSL as well as a journey from Sql and C# experience into the realm of serious data analysis and data science (and visualization).

It is a giant contribution that Microsoft is making by making the results of it’s Revolution Analytics acquisition more or less freely available to the world. I was able to attend the EARL conference on R applications in London some weeks back, and it was an exciting view into the world of analytics!

Today I got around to fire up some R on an Sql 2016, with an EARL session by Adam Rich as my starting point. It was somewhat laborious and the documentation and samples around the web were really convoluted or sometimes plain misleading. I really had to cut through a lot of extraneous information and so for future reference, here is what I did.

I had already installed Microsoft R and RStudio (and VS R tools, though I didn’t use them for this) as well as Sql Server 2016 with R, but at first I just wanted to check out the server with a very simple script:

EXEC sp_execute_external_script  @language =N’R’,   
    @input_data_1 = N’SELECT 42 AS TheAnswer’,
    @script = N’OutputDataSet <- InputDataSet’
    WITH RESULT SETS (([TheAnswer] int not null));

This failed with the message:

‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it.

The answer to this was to run these commands and then to restart the database service:

sp_configure ‘external scripts enabled’, 1; 
RECONFIGURE; 

 

Now it was time to try my hand at an R script that I would write locally on my PC and run remotely on the Sql Server with a trusted connection – in R parlance in a remote compute context – pulling a bit of data. First, however, the Microsoft R Client had to be installed – the version I had apparently didn’t  include the MRO stuff required to connect to the server – you can check the “c:\Program Files\Microsoft\MRO”  directory to verify that you have both an “MRO” and “R Client” directory.

A major problem was this message when setting up the server-connection (RxInSqlServer):

Error in .rxEnsureOdbcCredentials(connectionString, server, databaseName,  : 
  Trusted_Connection or User must be specified either in the connection string with the uid keyword or as a named parameter.
 

Most samples included a connectionstring for the RxInSqlServer-function, but this seems to be unnecessary.  I had to fiddle quite a bit with the parameters for the various special R server functions, ie those prefixed with RX. Also note that sometimes the prefix is Rx and sometimes rx… It appears that 2 connections are necessary: one for the R server computing context and one when executing Sql in the R context. But, to cut a long story short, this is what I got to work as a sort of minimum solution:

# Set up a trusted connection to R in Sql 2016

sqlCompute <- RxInSqlServer(
    server = “LC_SQL”,
    #wait = TRUE,
    #consoleOutput = FALSE,
    #shareDir = “e:/”,

    user = “Trusted_Connection”
    )

rxSetComputeContext(sqlCompute) # Switch script execution to Sql server

sqlConnString <- “Driver=Sql Server; Server=localhost; Database=master; Trusted_Connection = True”;

sqldata <- RxSqlServerData(sqlQuery = “select name, database_id from sys.databases”, connectionString = sqlConnString)
rxGetVarInfo(data = sqldata) # Show our variables
myframe <- rxImport(inData = sqldata) # Get data as a dataframe
print(myframe)
rxSetComputeContext(RxLocalSeq()) # Switch script execution back to local machine

 

Hope this can help someone struggling with the same challenges Smile

Use Powershell function parameters like Write-Host

It would seem straightforward, but I’ve spent a couple of hours googling and experimenting to solve the riddle, so here is what I found.

What I wanted to do, was to write a Powershell function with parameters that work like the Write-Host commandlet, where you can have both named parameters (ie –BackgroundColor) and a number of other parameters (words) that are not positional parameters but get collected into a string like so:

image

I my case I wanted a Text-Out function with an optional –Filename parameter where the output could be appended as well as written to the console like this:

Text-Out Lorem ipsum dolor -Filename "e:\test.txt" sit amet

Here is my Text-Outfunction:

function Text-Out
{
    [CmdletBinding(PositionalBinding=$false)]

    param(
     [string] $filename,
     [Parameter(ValueFromRemainingArguments=$true)]
     [string[]] $remainingargs
    )

    Write-Host ([string]::Join(" ", $remainingargs))

    if (-not $filename) {return}

    Add-Content $filename ([string]::Join(" ", $remainingargs))

}

 

The first point to note is the CmdletBinding, this is required in order that Powershell does not interpret the first word as a positional parameter, ie sets $filename = Lorem, in case the filename named parameter has been left out.

Second, the Parameter(ValueFromRemainingArguments=$true) ensures that all the words that are neither positional nor name parameters as such get collected into an array of strings.

Third, these words are then joined with the .Net [string]::Join() function. If the separating spaces were not needed, the built in Powershell –join function could be used instead. If Add-Content was just given the $remaingargs array, it would write one line for each word.

Can’t install Sql Management Studio on Windows 8, .Net 3.5 missing

It is really odd that it should be so hard getting SSMS 2014 on a Windows 8 box.

The problem is that SSMS needs .Net 3.5, but that the ordinary methods  to get .Net 3.5 installed (Windows Features in Programs and Features) do not work but only result in some very unhelpful messages!

Kunal Chowdry has made a nice description of the whole thing here, but to cut a long story short here is the gist of it:

DISM  /Online  /Enable-Feature  /FeatureName:NetFx3  /All  /LimitAccess  /Source:x:\sources\sxs

You run from an administrative command prompt – remember to replace “x:” whith the path to your Windows 8 installation path, ie a DVD.

DISM (Deployment Image Servicing and Management) has come to my help before. So sad that Windows has always lacked proper package management, so we need to rely on a mix of Control Panel, DISM, Installers, Chocolatey, Boxstarter etc.