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

 

Example

·         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: http://msftdbprodsamples.codeplex.com/releases/view/55330

·         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:

Case

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

    Then 1

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

         And

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

    Then 0

    Else -1

End

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

Case

    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

End

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:

2.        

                       clip_image002[4]

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

Thanks

Gilbert

Book Review – Expert Cube Development with SSAS Multidimensional Models

http://www.packtpub.com/expert-cube-development-with-ssas-multidimensional-models/book

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: http://sdrv.ms/1dwycbX 

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

clip_image002[4]

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

clip_image004[4]

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

clip_image006[4]

 

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))

        ,KPIValue(“Orders_ComparisonByHour_PastWeek”))

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