BI-NSIGHT – Power BI (Publish in Web App, Enterprise Gateway GA, Desktop Update, Troux Content Pack, New Visuals) – SQL Server 2016 (CTP3.3, eBook, R)

So from last week to almost having nothing to talk about, to this week having a whole host of updates.

Just to quickly mention I attended the first local Power BI User Group meeting in Brisbane this evening ( QLD Power BI User Group ), and it had a really great turnout, along with some great content for the first user group. I have no doubt that it will grow from strength to strength.

So let’s get into the details there is a lot to cover here.

Power BI – Publish in Web App

This has been one of the most requested things that have been voted on. And it is great to see that they have listened again to what the people and users are asking for and have delivered it.

There are a few things to be aware of is that once you embed it into another application all your security is not valid.

Along with this currently due to it being in preview there might be an additional cost to have this capability. Which I can understand in a way because they are actually providing this outside of Power BI, and by making it available for anyone to interact, that means that there are things that are happening within the cloud that needs to be accounted for.

You can find out the details here: Announcing Power BI “publish to web” preview

Power BI – Enterprise Gateway General Availability

This is something that I have covered in the past, and it is great to see that they have incorporated all the features into one product.

This means it will make it easier to install, configure and get people using your on premise data. As you can see that this is something that I am already using and will be a great feature going forward.

Also the ability to handle failovers, as well as having the performance counter information will be great for viewing what is happening as well as the performance of the gateway.

You can find out the details here: Announcing General Availability of Power BI gateway for enterprise deployments

Power BI – January Desktop Update

This was announced last week, and there are a whole host of updates in the Power BI Desktop. I will go through a few ones here that I think are really important.

As you can see above, you can now add borders as well as Cartesian charts’ plot area.

Along with this, I really liked that you can now refresh data in individual tables, because sometimes in the past you did not want to refresh everything including your largest table.

And finally I see that they are still making performance improvements and cross rending is great. It has always been a fast visualization, but to make even that little bit faster means that everything will be that much quicker and who does not like speed?

You can find details here: Power BI Updates This Week: New Report Authoring Capabilities

Power BI – Troux Content Pack

This is yet another content pack, and if you are an existing Troux customer then I am sure that using Power BI will give you some great insights into your technology investments. Which will help you understand your data and how best you can leverage on your information.

You can find the details here: Explore your Troux data in Power BI

Power BI – New Custom Visuals

As you can see above there have been 3 new great visuals that have been released. And to me they are concentrated around mostly financials, which is great to see.

You can go here to see all the visuals: Power BI Custom Visuals

SQL Server 2016 – CTP 3.3

It was a surprise to see that they have released CTP 3.3 and most of the updates are around SSRS and SSAS Tabular.

It is great to see that now as per the visual above you can add your own favorite reports to your SSRS view of the world. Which I think is something that is so simple, but at the same time so valuable.

Along with this is the details and updates for SSAS Tabular.

It is really good to see that you can now create calculated columns in Direct Query mode. As well as applying Row Level security in Direct Query mode also. As I know personally in the past I did not implement Direct Query mode, due to the limitations, which now they have resolved.

It is good to see that they are adding a lot of updates and features for Business Intelligence to SQL Server 2016.

You can find details around SQL Server 2016 CTP3.3 here: Access your favorite KPIs and reports with SQL Server 2016 CTP 3.3

And then if you want to find out the details around SSAS Tabular you can find that here: What’s new for SQL Server 2016 Analysis Services in CTP3.3

SQL Server 2016 – eBook

This is an update from the original eBook, and there is a lot of great content in here, especially if you are not fully aware of what is coming in SQL Server 2016.

There are two versions for desktop and mobile.

You can find information about the eBook here: Free eBook: Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud, Preview 2

SQL Server 2016 – R

As you can see above R has come a long way, and is a great tool to use if you have the specific requirement.

The screenshot above was from a presentation by Jen Underwood. And there is some really valuable information in this slide deck.

If this is something of interest to you, you can view the slide deck here: Microsoft R Server and SQL Server R Services

SSAS – KPI’s – Example of creating a KPI

Based on the previous BLOG post <font color="#ff000, we are going to explain how to create a KPI



·         Using our Internet Sales cube, we want to create a KPI in order to see if the guys are meeting their new target which is a 10% increase from their current sales.

·         So we will create a new KPI with the following:

o    The KPI Goal will be 10% greater than the current sales

o    The KPI Status will be if the KPI value is 100% greater than the KPI Value.

o    The KPI Trend will be based on the Previous Value selected from your Date Dimension using the Calendar Hierarchy


You can get a copy of this here:

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012


NOTE: We are using SQL Server 2014, and SSDT for Visual Studio 2013



1.       Go into the Adventure Works cube.

2.       Click on the KPIs and then select New KPI

a.        clip_image002

3.       Next we will give the KPI the following name and associate it to our Internet Sales Measure group

a.        clip_image004

4.       Now we will configure our Value Expression, which as per our example will be the Internet Sales amount

a.        clip_image006

5.       Next we will configure our Goal Expression which will be 10% greater than our Value Expression

a.        clip_image008

b.       NOTE: Due to using SSDT, the syntax is the following for the above:

Format([Measures].[Internet Sales Amount]*1.1,”$#,##0;($#,##0)”)

c.        NOTE: Because we are using a currency we formatted our Goal Expression so that when it displays it will display correctly.

6.       Next is the Status details.

a.        First what we did was to change the Status Indicator to a Shape as shown below:

b.       clip_image010

c.        NOTE: The reason that we did this, is so that when it is displayed in Excel you can see it easily based on the Shape Colour.

d.       Then for the Status expression we configured it with the following:

e.       clip_image012

f.         Here is the text below if this is not clear enough:


    When KpiValue( “Internet Sales Growth” ) / KpiGoal( “Internet Sales Growth” ) >  1

    Then 1

    When KpiValue( “Internet Sales Growth” ) / KpiGoal( “Internet Sales Growth” ) <= 1


         KpiValue( “Internet Sales Growth” ) / KpiGoal( “Internet Sales Growth” ) >= .85

    Then 0

    Else -1


g.        What we have done in the above Status expression is that if the growth is greater than 100% when comparing the Value to the Goal, then make the expression 1, which will make it green.

h.       What we have done in the above Status expression is that if the growth is less than 100%  but greater than 85% when comparing the Value to the Goal, then make the expression 0, which will make it yellow.

i.         Else it will be less than 85% in which case make it -1, which will make it red.

7.       Then for the Trend section we did the following:

a.        We changed the Trend indicator to the Status arrow.

b.       clip_image014

c.        NOTE: We did this so that when it is displayed in Excel it will display correctly.

d.       Then for the Trend Expression we configured it with the following:

e.       clip_image016

f.         Here is the text below if not clear enough


    When (KPIValue(“Internet Sales Growth”),[Date].[Calendar].CurrentMember) >

         (KPIValue(“Internet Sales Growth”),[Date].[Calendar].CurrentMember.PrevMember)

    Then 1

    When (KPIValue(“Internet Sales Growth”),[Date].[Calendar].CurrentMember) <

         (KPIValue(“Internet Sales Growth”),[Date].[Calendar].CurrentMember.PrevMember)

    Then -1


g.        NOTE: What we are doing above is we are taking the KPI Value and comparing it to the previous member in our Date.Calendar Hierarchy.

                                                                i.      The reason with using the Date.Calendar Hierarchy is that we can use any member or part of the hierarchy, it will then compare that to the previous value.

1.       EG: If you use quarter it will compare quarters. Or if you compare Month it will then compare months.

h.       If it is better than the Previous then make the trend 1 or Green Arrow.

i.         If it is worse or lower than the previous, then make the trend -1 or Red Arrow.

8.       Now if you process your cube and view the results in Excel it will look like the following for July 2005, where we are using the Date.

a.        clip_image018

SSAS – KPI’s – How Explanation of KPI Makeup

Below is an overall description of what is needed for the KPI’s when creating them in BIDS


1.       After you click on New KPI you will have a window shown below and underneath the picture are the details required for each section:



a.        Where it says Name:

                                                               i.      This will be the name for your KPI

                                                              ii.      NOTE: Make sure that it is descriptive and makes sense to the end user

b.       Associated Measure group:

                                                               i.      This is to which measure group you are associating the KPI.

c.        Value Expression:

                                                               i.      This is where you will use a measure to get the actual value of your KPI at Run time.

d.       Goal Expression:

                                                               i.      This is the goal or what you would like your KPI to get to

                                                              ii.      EG:

1.       You have people logging into your system and the more than can log in every minute the better.

2.       So it is decided that 500 logins per minute is the goal.

3.       Then in the Goal Expression you would put 500

e.       Status Expression:

                                                               i.      This is where you define that status is good, ok or bad for your gauge.

                                                              ii.      NOTE: You can change the status indicator to one that makes the most sense.

                                                            iii.      With the Status Expression it can also have three values

                                                            iv.      EG:

1.       1 is always good

2.       0 is always Ok

3.       -1 is always Bad

f.         Trend Expression

                                                               i.      This is where you define what the trend is for the KPI, meaning is it currently getting better or worse.

                                                              ii.      NOTE: You can change the Trend indicator to one that makes the most sense.

                                                            iii.      With the Trend Expression it can also have three values

                                                            iv.      EG:

1.       1 is always good

2.       0 is always Ok

3.       -1 is always Bad

Relocating to Australia – BI Job opportunities in Queensland

I thought I would let you guys know that I am about to relocate to Australia from South Africa. I have had an amazing time in South Africa, and learnt a whole lot whilst working at my past employer.

So this is a plug at anyone who has any lead or potential BI work in Queensland, Australia. I would really appreciate it, if you have anything to please email me.

I will be in Australia from 08 August 2014.

Below is a link to my CV and as well as my contact details.

CV-Gilbert Quevauvilliers-2014



Book Review – Expert Cube Development with SSAS Multidimensional Models

This book is a must read if you are looking for some useful and practical information in your SSAS Multidimensional models. But it must be noted that this is not an introduction into SSAS Multidimensional modelling, rather practical real world examples.

I found that the book is a wealth of information that can be applied to your working environment. There are a lot of real world examples that I have had a look at and made me check and update my SSAS Multidimensional models so that they can be that much faster and quicker.

It is also a great reference book, for when you are looking for a specific issue, where you can find the requirement and how to solve your particular issue.

I also really enjoyed the book, because it got straight into the requirement and then explained how to potentially solve or improve your requirement. Along with this I found that it was great where they applied their working experience in what they encountered and how they overcome an issue.

It is written by the guys who I consider to be some of the leaders in SSAS Multidimensional modelling. And you can see by the context of the detail in the book, as well as their examples that this happens often in the working environment, and how to get the best performance from your SSAS Multidimensional models.

A great read and I would recommend this book to anyone who is looking for a wealth of information as well as some great insight into the inner workings of SSAS Multidimensional models.

SCOM (Systems Center Operations Manager) Cube and Data warehouse

What I have done is I have found that it is very handy having all our SCOM data in a data warehouse and then having it in a SSAS (SQL Server Analysis Services).

Within the zip file which can be located here below it has all the details for the following:

·         SCOM Data Warehouse and OLAP Documentation.docx

o   This has all the details required to get up and running.

·         Restore the SQL Server Database

·         Deploy your SSIS Project

·         Deploy your SSAS Project

·         Deploy your SSRS Reports

Zip File location: 

So below is some examples of what can be achieved when the data is sitting inside SSAS.

SCOM report, to analyze and predict based on the past when disks will be running out of space.

·         This utilizes the KPIs within SSAS


Using Excel we can get a trend to analyze what has been happening on a server.

·         As I am sure you are aware there are a whole host of other additions that can be added to the Excel file to make it easier for people to use such as:

o   Slicers

o   Timeline – If Excel 2013


Using Excel we can also use the KPIs that have been created to look at our Goals, Status and trends



I have also created additional reports where based on the past average of CPU % Time Used, we then compared this to the current days average if it is greater than 150% then display the data. I have then put this into SSAS Tabular and used Power View in SharePoint in order for guys to quickly see the data.

I have tried to ensure that everything should work as expected.

If there are any issues or something that does not work, please contact me in the contact section below.

I do hope that this will be as useful for you as it has been and continues to be for me.

SSAS (SQL Server Analysis Services) – KPI Goal – Unknown Member is result set

What happened was when I created my KPIs everything was looking correct. But when I dragged in the hour value, I kept on getting an Unknown in my results set for my KPI Goal.


Below I will explain how this came about and how I got it resolved.


1.       Below is what my KPI Goal looked like as described in the above error

a.        clip_image002[4]

b.       As you can see above I have an Unknown which is causing it to look untidy and incorrect.

2.       Initially I thought that this had to do with the actual way I had complied the KPI Goal.

a.        This was because I was going back 7 days to the same hour as shown with the KPI Goal Expression below:

((ParallelPeriod([Date].[Date Hierarchy].[Full Date],7,[Date].[Date Hierarchy].CurrentMember),

                  ParallelPeriod([Time].[Time Hierarchy].[Hour],0,[Time].[Time Hierarchy].CurrentMember))


3.       So after playing around with the KPI Goal Expressions for a while I thought to go and check the Time Dimension instead.

4.       I then found the following under the Properties for the Time Dimension, by right clicking on the actual name of my Dimension and then selecting Properties

a.        clip_image004[4]

5.       I then changed the UnknownMember from Visible to Hidden as shown below.

a.        clip_image006[4]

6.       I then did a Process Full on my Dimension, after which I had to the process my cubes in order to get them back into a query able state.

7.       After which I refreshed my spreadsheet and I now saw the following:

a.        clip_image008


SSAS (SQL Server Analysis Services) – KPI – Comparing Date and Hour for the Previous Week or Multiple Values in your KPI Goal and Trend Expressions

What I wanted to do was to use a KPI in SSAS to compare values going back one week. But I wanted to get the same hour for the previous week.

I could not find an example were it had used multiple values in a KPI Goal or Trend expression. All just used one value.



·         For my data I wanted to take my current date which was 2013-10-14 and the hour of the day being 8 AM.

o    NOTE: In this example it was to get a Count Of Orders

·         I then wanted to go back one week so that my date would be 2013-10-07 and the hour would still be 8 AM.


Below are the details with an explanation on how I got this to work


1.       I went into SSAS, opened my Cube and clicked on the KPIs Tab

2.       I then clicked on New KPI to create my KPI

a.        clip_image002[4]

3.       Next I gave my KPI a meaningful name.

a.        In our example I gave it the name of:

                                                               i.      clip_image004[4]

                                                              ii.      NOTE: I only have one measure group in my cube.

4.       Then in the Value Expression I gave it my measure for the Count of Orders

a.        clip_image006[4]

5.       Now for the Goal Expression this was here I had to figure out how to write my MDX so that it would go back on week, but also to the same hour of the previous week.

a.        Below is the Syntax with an explanation afterwards

((ParallelPeriod([Date].[Hierarchy].[Full Date],7,[Date].[Hierarchy].CurrentMember),


b.       The first thing is that everything is contained within the round brackets which is highlighted in RED

c.        Next we had to put in another round bracket for our MDX Functions so that they would be seen as one MDX Function

                                                               i.      This is highlighted in GREEN

d.       Next is I used the Parallel Period function in MDX so that I would be able to go back 7 days from my current date.

                                                               i.      This is shown with the section below which is highlighted in ORANGE

                                                              ii.      (ParallelPeriod([Date].[Hierarchy].[Full Date],7,[Date].[Hierarchy].CurrentMember),

e.       Now this was the part that I had to figure out was because I wanted to also include the Hour for the previous week, all that I had to do was to put a comma after my first Parallel Period in order for it to be included in my Goal Expression.

                                                               i.      This is shown with the section below which is highlighted in PURPLE

                                                              ii.      ParallelPeriod([Time].[Hierarchy].[Hour],0,[Time].[Hierarchy].CurrentMember)

                                                            iii.      NOTE: For the Parallel Period function I used the value of zero, so that it would go back to the same hour for the previous week.

f.         NOTE: This second Parallel Period is still within the second set of round brackets.

                                                               i.      This is because as mentioned above this is to ensure that it is seen as one MDX Function for the Goal Expression

g.        This is what it looked like

h.       clip_image008[4]

6.       Next was the Status Expression, where I changed the Status Indicator to Shapes

a.        NOTE: The reason that I did this is because when you use this in Excel it will automatically use a shape that will change color.

7.       Now I put in the following for the Status Expression:

a.        clip_image010[4]

b.       As you can see above we configured it with the following

                                                               i.      If the KPI Goal is greater than 1% then make the Shape Green (1)

                                                              ii.      If the KPI Goal is between -0.00% and -5% then make it Yellow (0)

                                                            iii.      And if the KPI Goal is less than 5% then make it Red (-1)

8.       Now the other tricky part was the Trend Expression so that we could get the correct trend, when looking back at the Last weeks data, for the same hour.

9.       I changed the Trend Indicator to a Status arrow so that if used in Excel it would then change color.

a.        Below is the Syntax with an explanation afterwards


When (KPIValue(“CountOfOrders_ByHour_LastWeek”),[Date].[Hierarchy].CurrentMember,

      [Time].[Hierarchy].CurrentMember) >

     (KPIValue(“CountOfOrders_ByHour_LastWeek”),ParallelPeriod([Date].[Hierarchy].[Full Date],7,[Date].[Hierarchy].CurrentMember),

      ParallelPeriod([Time].[Hierarchy].[Hour],0,[Time].[Hierarchy].CurrentMember)) Then 1

When (KPIValue(“CountOfOrders_ByHour_LastWeek”),[Date].[Hierarchy].CurrentMember,

      [Time].[Hierarchy].CurrentMember) <

     (KPIValue(“CountOfOrders_ByHour_LastWeek”),ParallelPeriod([Date].[Hierarchy].[Full Date],7,[Date].[Hierarchy].CurrentMember),

      ParallelPeriod([Time].[Hierarchy].[Hour],0,[Time].[Hierarchy].CurrentMember)) Then -1


b.       Here in the Trend Expression it is different from what we used in the Goal Expression which is explained below.

c.        For the first part of our Trend Expression it required the following syntax

                                                               i.      KPI Value, Dimension Value 1, Dimension Value 2

                                                              ii.      From the example above:

1.       KPI Value highlighted in GREEN

a.       KPIValue(“CountOfOrders_ByHour_LastWeek”)

2.       Dimension Value 1 highlighted in PURPLE

a.       ,[Date].[Hierarchy].CurrentMember

3.       Dimension Value 1 highlighted in ORANGE

a.       , [Time].[Hierarchy].CurrentMember)

d.       As you can see from above this would be the first part of the Trend Expression comparison.

e.       For this part of the Trend Expression we are looking where the Current Date and Hour is greater than the Previous Weeks Date and Hour.

                                                               i.      So the Trend Indicator would be a Green Arrow and upward

1.       clip_image012[4]

f.         Now for the second part of the comparison we would be using our Parallel Period functions so that we could go back 7 days, to the same hour of the previous week.

                                                               i.      NOTE: this uses the same syntax as explained in step 9c above.

KPIValue(“CountOfOrders_ByHour_LastWeek”),ParallelPeriod([Date].[Hierarchy].[Full Date],7,[Date].[Hierarchy].CurrentMember),


g.        As from above we did exactly the same for the second part of the Trend Expression except we now would say for the Trend Expression if the Current Date and Hour is Less than the Previous Weeks Date and Hour, make the Trend Indicator Red and downward

                                                               i.      clip_image014[4]

h.       This is the entire Trend Expression shown below:

i.         clip_image016[4]

10.    Now as with my example you can see what it would look like in Excel

a.        clip_image018[4]

11.    Now from the above screenshot if I had to go and validate my data, by looking at the above which would be the Date of 2013-10-14 and the Hour of 8.

a.        So I would go back in my data to the Date of 2013-10-07 and the Hour of 8 and I would see the following

b.       clip_image020[4]

c.        So this now shows that my Trend and Goal Expressions are working as expected.