Converting a PowerPivot workbook w/ PowerQuery connections into SSAS Tabular

No, SSAS Tabular mode don’t support PowerQuery connections… (yet… But I’am confident that will be in the future)

But I was happy to verify that it’s very easy to change the connection and point into another table in a supported provider (ex: a SQL Server DW).

It’s true that you will loose all the transformations made in powerquery, but in the scenario that we are converting a self-service BI model made in Excel into a more enterprise level BI application where the transformations need to be made by a more “professional” ETL tool like SSIS tool, it’s good to know that it’s possible to convert the connection very easily.

PS – This is not the same history with Linked Tables in PowerPivot, when converting into a tabular database those tables will end up as Pasted Tables which are very hard to update, so stay away from Linked Tables… As an alternative I find preferable to create a powerquery into the Excel table and then load into the model.

Start converting the powerpivot workbook into a SSAS Tabular project:

image

Then go to Model –> Existing Connections, and you’ll find all your PowerQuery connections:

image

Try to process one of them, and you’ll get this error:

image

Now you need for each PowerQuery connection to change the connection provider into a supported one and point into a object that has the same name and schema (if it doesn’t have the same schema/name you will have to change the table partition query).

In this case I have a table named “Employee” with these columns:

image

Create a table in your datawarehouse with the same name and schema:

image

Edit the “PowerQuery – Employees” connection and change the provider:

image

Connect into my SQLServer DW:

image

image

The simply process the table and it will process sucessfuly, and the new data will be loaded:

image

image

My favorite default Query Load option in PowerQuery

By default PowerQuery loads the built/search query in a blank worksheet, but this can be a little bit time consuming when you have many queries or the query has a lot of rows.

So my recommendation is to by default disable the load of the query in the Options:

image

This way when you create/reference a query in PowerQuery it’s saved in the workbook but it’s not loaded into the worksheet nor the data model:

image

When you are finished with the queries you can choose where to load them, right-clicking the query and choosing the  “Load To…” option:

image

Another PowerQuery DateDimension query

This is nothing new and as already been blogued by Chris Webb and Matt Masson:

https://cwebbbi.wordpress.com/2013/11/19/generating-a-date-dimension-table-in-power-query/

http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

But based on their work a made a new one with some new quickwins:

  • Suport for the day of the week start (in Portugal tipicaly starts on monday not sunday)
  • Only StartDate is mandatory, in this case a Date Dimension is generated til the end of the year
  • Some changes in the columns values

image

image


let CreateDateTable = (StartDate as date, optional EndDate as nullable date, optional Culture as nullable text, optional FirstDayOfWeek as nullable number) as table =>  
 let    
    Culture = if (Culture = null) then "pt-PT" else Culture,
    FirstDayOfWeek = if (FirstDayOfWeek = null) then 1 else FirstDayOfWeek, 
    EndDate  = if (EndDate = null) then Date.EndOfYear(StartDate) else EndDate,
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1,
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),    
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "Quarter", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "Month", each Date.Month([Date])),
    InsertDay = Table.AddColumn(InsertMonth, "Day", each Date.Day([Date])),    
    InsertMonthName = Table.AddColumn(InsertDay, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthOfYear", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterOfYear", each "Q" & Number.ToText([Quarter]) & " " & Number.ToText([Year])),    
    InsertWeek = Table.AddColumn(InsertCalendarQtr, "Week", each Date.WeekOfYear([Date])),
    InsertCalendarWeek = Table.AddColumn(InsertWeek, "WeekOfYear", each "W" & Number.ToText([Week]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarWeek , "WeekDay", each Date.DayOfWeek([Date], FirstDayOfWeek ) + 1),
    InsertDayName = Table.AddColumn(InsertDayWeek, "WeekDayName", each Date.ToText([Date], "dddd", Culture), type text),
    InsertDayInt = Table.AddColumn(InsertDayName, "DateId", each [Year] * 10000 + [Month] * 100 + [Day]),
    #"Changed Type" = Table.TransformColumnTypes(InsertDayInt ,{{"Year", Int64.Type}, {"Quarter", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"DateId", Int64.Type}, {"Week", Int64.Type}, {"WeekDay", Int64.Type}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"MonthName", Text.Proper}, {"MonthOfYear", Text.Proper}, {"WeekDayName", Text.Proper}})

  in
     #"Capitalized Each Word"
in
    CreateDateTable

Session Contents Excel & PowerBI @ IT Pro Airlift (2015-01-22)

Thanks to all who attended, hope you had a good time attending as I had presenting.

As promissed here are the link to download the session contents here: http://1drv.ms/1Cm0BQ9

And see the Demos Video here: http://1drv.ms/18azgoV

image

image

image

image

A lightweight SQL Helper Powershell Module (@DevScope)

Recently at DevScope we developed a simple but powerfull powershell module to query databases that I want to share because its been of great use to me lately.

What differentiates this module is its simplicity and lighweight, with it you can:

    • Query any .Net Data Provider with only one cmdlet (SQLClient, OracleClient, OLEDB, ODBC,…)
      • For instance, with the same cmdlet you can query an Excel spreadsheet and insert its data into a SQL Server database
    • Easily execute a bulk copy into a SQL Server Database

You can download the module and a samples of usage at GitHub:

https://github.com/DevScope/sql-powershell-modules

Examples of usage:

Query an Excel Spreadsheet


$dataSet = Invoke-DBCommand -providerName "System.Data.OleDb" `
		-connectionString "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='$currentPath\ExcelData.xlsx';Extended Properties=Excel 12.0" `
		-commandText "select * from [Sheet1$]" -verbose

Insert into SQL Server


$dataSet.Tables[0].Rows |% {	
	$numRows = Invoke-DBCommand -providerName "System.Data.SqlClient" `
		-connectionString "Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Dummy;Data Source=.\sql2012" `
		-executeType "NonQuery" `
		-commandText "insert into dbo.Products values (@id, @name, @datecreated)" `
		-parameters @{"@id"=$_.ProductKey;"@name"=$_.EnglishProductName;"@datecreated"=[datetime]::Now}					
}

Bulk Copy into SQL Server


Invoke-SqlBulkCopy -connectionString "Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Dummy;Data Source=.\sql2012" `
		-tableName "dbo.Products" `
		-data $dataSet.Tables[0] `
		-columnMappings @{"ProductKey" = "Id"; "EnglishProductName" = "Name"} -verbose

Concatenate a row set into a sql column

Other day I made a post on how to concatenate a rowset into a TSQL variable: here

In this post I’ll show how to make a concat into a column without using any variable:

select (

select Col + ', '

from ( select 'Value 1' Col union select 'Value 2' union select 'Value 3' ) aux FOR XML PATH('')

) ConcatColumn

The trick is on "FOR XML PATH("")" that transform the rows into a xml string where the node name is passed by parameter, in this case I pass an empty string, so the result is:

clip_image001

Drop a SQL Server Schema and all related Objects

I love to organize my SQL Server database objects using schemas and in my current project I need to create a lot of them dynamically so I also need to manage a way to delete them :).

So I implemented a stored procedure that allows me to delete the schema and all it related objects in a database (also specified by parameter).

Usage:

exec dbo.DropSchema @schemaName = ‘schema’, @database = ‘[Database]’, @whatif = 1

You can download the procedure script here.

If you find any bugs or have any sugestion please fell free to comment.

Resources: