[Issue] – The Mystery of the Hidden Column

Yesterday I was trying to setup a new Power BI dashboard for our SQL Server Analysis Services logging data.
We are running traces on most of our servers, in order to keep up with what is going on – this mainly because all other access to the boxes is restricted.

Now, having the data in a SQL Server database, the connection to Power BI Desktop is of course supported out of the box, no issues here what-so-ever, one should think. I however, came across creating a particular view on top of a table, the just didn’t compute in the Power BI Desktop Designer. As depicted below, the view in question would just not be possible to load/edit. Microsoft is looking into this.

View Error

I managed to get around the view issue by persiting the data in another table, consolidating and filtering to only have relevant data there.

Loading the data, from the table into Power BI Desktop Designer is easy, you just point to the server, pick the table and you are presented with two options, either to load or to edit. In this case I opted to load the data. I started playing around with the data and quickly discovered that I was missing out on some attributes in the data. For one thing I needed to convert my date to day, month and year (would have loved some basic week functionality as well, but maybe later eh?).
This functionality is actually build into the tool (Power BI Desktop Designer), so you can generate those extra columns by selecting “Add column” in the ribbon, select the desired date (if you have more) and choose from one of the many options in the “date convert picker”

Month Date Selector

– apologies for the screenshot in Danish.

The above selection will yield the Column you see in same screenshot, named [Month No], and as you can see, it returns a number. This is not very end user friendly, so a little convertion is in order.
The normal Excel IF(test, success, fail) does not work in the Power BI desktop Designer, we need to write it a little bit differently – this is how I convert month number into text:

if Text.Range([Month No],0,1) = "1" then "Jan" else 
if Text.Range([Month No],0,1) = "2" then "Feb" else 
if Text.Range([Month No],0,1) = "3" then "Mar" else 
if Text.Range([Month No],0,1) = "4" then "Apr" else 
if Text.Range([Month No],0,1) = "5" then "May" else 
if Text.Range([Month No],0,1) = "6" then "Jun" else 
if Text.Range([Month No],0,1) = "7" then "Jul" else 
if Text.Range([Month No],0,1) = "8" then "Aug" else 
if Text.Range([Month No],0,1) = "9" then "Sep" else 
if Text.Range([Month No],0,2) = "10" then "Oct" else 
if Text.Range([Month No],0,2) = "11" then "Nov" else 
if Text.Range([Month No],0,2) = "12" then "Dec" else 
"Unkn"

as a custom column:

User Defined Column MonthPlease note the little green check mark indicating everything’s Dandy (b|l|t)… No not that Dandy….

But to my great surprise, the column does not show up in the data source view (or whatever they call it in Power BI Desktop Designer) – Look for [Month Name] as I renamed the column from [Month] in a later step.

Data Source View
WTH is my column?

Loading

Run your cubes in Warp Speed – Really Really Fast!

So, finally Microsoft came up with a new feature for the MOLAP part of Analysis Services. The community has been hungering for updates ever since the new kid Tabular came around.

Now it apears that the SQL Server team has unlocked some underlying functionality in the Windows OS, to enable the MOLAP cubes to operate in Warp Speed.

Run the latest CU (get it here) and check out the newly added check box in the properties.

WarpSpeed for MOLAP Cubes
WarpSpeed for MOLAP Cubes

 

Loading

How to Process Multiple Dimensions via BIDS and SSMS

This is one of those tips I need to write down, before I forget…
Sometimes Analysis Services acts up and you need to do a process full on all the dimensions in the database. When that happens I usually turned to Visual Studio Business Intelligence Development Studio [BIDS] or SQL Server Data Tools [SSDT], which allows you to select more than one dimension at a time, and fire up the processing. See figures below:

Via BIDS

Open a connection to a “live” database

Open Connection
Open Connection

Choose which server/database to connect to:

Choose Server/Database
Choose Server/Database

Select all dimensions and right-click:

Select dimensions
Select dimensions and right-click

Finally, choose processing mode:

Processing Mode
Processing Mode

Via SSMS

Until today, I didn’t know you could actually do the same action through SQL Server Management Studio [SSMS]. You do as following:

Connect to the server:

Connect to Server
Connect to Server

Expand till you get to the Dimensions folder:

Find the Dimensions folder
Find the Dimensions folder

Hit F7 to open the Object Explorer Details:

Select All and Right-click
Select All and Right-click

Select Processing Mode:

Select Processing Mode
Select Processing Mode

 

Loading

Speaking at #SQLSatCambridge

Do you sometimes drift off, thinking/dreaming crazy scenarios, that you almost instantly know won’t ever become a reality?
I had one of those moments, when I spotted, that #SQLSatCambridge and the NFL game at Wembley between The Pittsburgh Steelers and Minnesota Vikings were scheduled only a day apart. Oh how I wished to be in England that particular weekend. I had a pretty strong feeling, that the wife back home wouldn’t be too happy to ship me of, on grounds of just the NFL game. She has every right to, on the account of our third child arrived in July. So I dared to submit two abstracts, one on Custom Assemblies which I presented at #SQLSatDenmark and one on Cube Security, which I have been working with intensively for the last couple of years. In my childish bliss, I drifted of on the thought of me being in Cambridge on Saturday, and at Wembley on Sunday. Double blast!

Imagine my reaction, when I recieved a mail from the Mark Broadbent (blog|twitter), stating that my abstract on cube security had been elected. The mail got in around midnight, and I had to re-read it several times, just to check I wasn’t making it up. 12 hours later, I replied and confirmed my availability to the event. Time to go Work the misses.

As my wife instantly recognized, how proud I was, to have been elected to speak at #SqlSatCambridge, she saw no reason to why I couldn’t stay an extra day and watch the game. She has been shaking her head for the last several years, when the NFL season begins and I claim the TV-set. She knows I’m a huge fan of football, as well as SQLServer 😉
Note to self: Go Large on wife’s Christmas gift

So sometimes the most outrageous, mind bending, far fetched ideas can become a reality. One of mine will, the last weekend in September this year.

Loading

Rolling period with shell dimension SSAS

Often I meet the business requirement of listing data for the last X months. In this blog post, I will try to give my shot at, how this is done elegantly and with maximum flexibility.

We start of backwards and enhance the Data Source View in the cube with a custom Named Calculation called UtilityKey (or whatever you like). The query is probably the most simple Named Calculation you’ll ever write, since it should be looking awfully a lot like the next figure:

When this Named Calculation is ready to go, we need to make use of the fact that having a dimension with the key = 0, means we have a hold of every row in that fact table! Enter shell dimension.
We create a table in SQL Server that holds the dimension we want to be able to slice by, using the utility key; In this case: A periodicity dimension. A dimension that hold the members by which we want to slice data. The table in this example looks as follows:

We then populate with data that matches our business requirements, in this case we need the members All, 1 Month, 3 Months, 6 Months and 12 Months:

This dimension table, we then use to create a dimension in the cube, based on the key and name columns.

Ending up with a result, after trimming the names, setting IsAggregatable = False etc:

The clever part of this shell dimension is that we then define a SCOPE in the cube that allows us to slice the data as we see fit. We use the regular time dimension present in the cube to navigate through the five (5) different members of our Periodicity dimension, like this:

Slicing the cube by Periodicity and not by the regular Calendar dimension yields the desired result to the business requirement.

Loading