BI-NSIGHT – Power BI (Upcoming Updates, Forecasting in Power BI, Q&A Updates, Row Level Security, Excel in Power BI, Cortana Integration, New Visuals, Power BI App on Apple Watch, Desktop Trend Lines, Desktop Drill Through, Desktop Conditional Formatting, Power Apps) – SQL 2016 – Part 2

Well with the Microsoft Data Insights Summit I was under no illusion that there was going to be a whole host of updates and great new features to Power BI. And it was great to see that I was not disappointed.

So here are the highlights that I thought would be great to mention for this week’s blog post.

Here is the link to the entire Blog Post from Microsoft, and below are my details from what has been released: Over 5 million subscribers are embracing Power BI for modern business intelligence

Power BI – Forecasting in Power BI

This looks like a really great feature because what it means in my interpretation is that we can now leverage Azure Machine Learning (ML) to help forecast or predict what will happen based on the data we have provided.

I do not know all the details, but I know that this will be very welcome and at least give an indication of where your data is going, either better or worse or the same.

I know that there are some other products that have this capability, but I do think that this can be optimized and over time become a lot more accurate.

Power BI – Q&A Updates

It was great to see that after the initial updates to Q&A, they now not only are providing the capability to improve the service in terms of making it easier to use. They are now giving the ability for Q&A to be run on Direct Query Data Sources.

I was not sure initially how this was going to happen in the past, but in my view I was sure that it would be a feature that would be created or designed.

I do think that having Q&A is not only a great option in terms of selling Power BI, but it also makes it really easy for users to just ask a question and get answers. Which they can then save and have as part of their dashboard. And this will be something I would even use when looking for a quick result. I can just ask the question.

Power BI – Row Level Security for Cloud and Direct Query Sources

This is really a great new feature, and as with my next highlight below what this shows is that Power BI is almost at the point of SQL Server Analysis Services (SSAS) Tabular is cloud ready or SaaS.

I know already that there are a lot of good reasons and processes to use Row Level security, so that we can re-use the one data source for multiple people and only show them the data that they need to see.

There is not a lot to talk about here in terms of how Row Level security works, because it has been around in SSAS Multidimensional for some time, but it is great to see it in the cloud or Power BI.

Power BI – Power BI Data in Excel

This leads on from the previous highlight that it appears now that Power BI is a SaaS, and that once it gets released you can use your Power BI Models in the cloud and interact with them using Excel.

This then means that you can use all the standard Excel functionality. As we all know Excel is the most widely used product from Microsoft. And people are very comfortable using Excel, as well as having great skills in what they do with the data in Excel.

Now this just gives them the capability to do this. As well as it means that you potentially do not have to create a Power BI Model and then an SSAS Tabular Model for Excel. It now can all come from one source.

Power BI – Cortana Integration

It is great to see that they are integrating more of Cortana into Power BI. And in my mind I would think that it is leveraging Cortana to convert the voice text, and then pass this to Q&A which in turn can then provide the required data. I could be totally off the mark, but I would imagine it is something along those lines.

I am sure over time that this will get smarter and more integrated.

Power BI – New Visuals

As you can see above Microsoft have released a whole host of new Power BI Visuals.

I do know that the SandDance visual got a lot of applause, and it does have some great applications that were designed with the required datasets.

I particularly like the Attribute Slicer, because it appears that this one gives you the ability to slice the data, but that it will not take up a lot of screen real estate, as well as giving the option to search for your attribute that you want to slice by.

I did think I read somewhere that now Power BI has more visuals for the users to use than any of the Microsoft’s competitors.

Here are more details of the SandDance visual: Visual Awesomeness Unlocked – SandDance

And you can to here to view all the visuals here: Welcome to Power BI custom visuals

Power BI – App on Apple Watch

Just a quick note to see that they will release an App for the Apple watch. This is great for people who like to have the updates coming onto their smart watches.

Power BI – Desktop Trend Lines

As it would happen they also show cased what is coming up in next release of Power BI Desktop.

And it is great to see that there is the ability to put in a trend line, which can often lead the user to understand the trend of the data that they are looking at.

Power BI – Desktop Drill Through

I personally know that this is something that I have been looking for, as well as very often people have said that Power BI is great but there is no drill through functionality.

Well now it is here and it is awesome. And I know that already when chatting to my fellow peers that they are happy and amazed that it has been done. Which means we can then leverage more of Power BI.

Power BI – Desktop Conditional Formatting

I know that often it helps people to better understand the data when there is some conditional formatting that has been applied.

It is great to see that we now have the ability to do this which will further enrich the Power BI experience.

Power BI – Power Apps

I have no doubt that as time goes on there will be a better or tighter integration with Power BI and Power Apps, as a means of getting data into Power BI which can then be used to further enrich the Power BI experience as well as the related data.

SQL 2016 – Part 2

In part to of the details of SQL Server 2016 they go into the SSAS improvements and new features.

I know quite a bit of this has been released before, but this is one central page where you can see all the improvements, to which there are many as well as additional features.

If you want to see all the improvements and updates you can read about it here: Enable business insights for everyone with SQL Server 2016: Part 2

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.

 

Example:

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

      ParallelPeriod([Time].[Hierarchy].[Hour],0,[Time].[Hierarchy].CurrentMember)),KPIValue(“CountOfOrders_ByHour_LastWeek”))

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

Case

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

End

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

ParallelPeriod([Time].[Hierarchy].[Hour],0,[Time].[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.

SSAS (SQL Server Analysis Services) Tabular – Error when Processing – OLE DB or ODBC error: Server: The operation was cancelled by the user ..

The above error is caused when processing an SSAS Tabular database.

 

NOTE:This only applies to when using MDX and an SSAS OLAP Cube to get your data into SSAS Tabular.

NOTE 2:The reason that I use MDX and the SSAS OLAP Cube to get data in is for 2 reasons.

·         The first is that as we know it is always a lot quicker and more efficient to get data out of SSAS OLAP.

·         Second is due to querying the SSAS OLAP, the underlying data warehouse can then continue importing data. So that there is no load on our data warehouse.

 

In our example below we want to extract from the AdventureWorksDW2012 Analysis Services Database and select the following:

·         Get the Internet Sales Amount for a specific day

o    NOTE:I did create a Member value, which originally generated the error

§  If you have to just use a standard measure this will NOT occur.

·         Use a filter to filter out our unwanted data for the specific date.

 

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

 

 

Below are the details as to why this happened and what caused it to happen. I will show this in a series of steps below with images to better explain.

 

I will also show how to resolve this error so that it will not happen in the future.

 

1.       First we will start with our MDX Query so that we can get our dataset, which when using SSAS Tabular will get imported with the valid columns.

Withmember [Measures].[CurrentDayAverage] as

       ([Date].[Date].&[20050701] ,[Measures].[Internet Sales Amount])     

 

Select  nonempty {[Measures].[CurrentDayAverage]} on 0

, nonempty {Filter(([Date].[Date].&[20050701]),[Measures].[Internet Sales Amount] > 0)} on 1

from  [Adventure Works]

a.        And here is the Results below:

b.       clip_image002

2.       Next I have modified the query using the filter so that our data has changed. This is so that if the next time the processing ran, we would get no data returned

a.        NOTE: As you will see below, our MDX query is valid and it does run successfully.

Withmember [Measures].[CurrentDayAverage] as

       ([Date].[Date].&[20050701] ,[Measures].[Internet Sales Amount])     

 

Select  nonempty {[Measures].[CurrentDayAverage]} on 0

, nonempty {Filter(([Date].[Date].&[20050701]),[Measures].[Internet Sales Amount] > 15000)} on 1

from  [Adventure Works]

b.       If you look above I have changed the filter from > 0 to > 15000

c.        And here is the Results below:

d.       clip_image004

e.       NOTE: Because in the Results Tab there is no column information returned as per the screenshot above when processing the SSAS Tabular it would error with the following:

                                                                                       i.      OLE DB or ODBC error: Server: The operation was cancelled by the user ..

f.         And if we had to click on the Message Tab we would see that the MDX Query was successful

g.        clip_image006

3.       Now the query below is how I modified it, so that even if there was no data returned it would still show the columns.

Withmember [Measures].[CurrentDayAverage] as

       ([Date].[Date].&[20050701] ,[Measures].[Internet Sales Amount])     

 

Select    {[Measures].[CurrentDayAverage]} on 0

, nonempty {Filter(([Date].[Date].&[20050701]),[Measures].[Internet Sales Amount] > 15000)} on 1

from  [Adventure Works]

a.        And here is the Results below:

b.       clip_image008

c.        NOTE: What I did was to remove the nonemptyfrom the query in the select on the columns (0), in order for it to display the column

d.       NOTE 2: In all the blogs I have read, it is recommended practice it always include the nonempty as part of the query. This speeds up the query and in most cases returns the required information.

4.       Now when we process our SSAS Tabular database it will not error.

a.        clip_image010

Excel – Using a Custom MDX Query in Excel Table

I had a requirement where I wanted to use my own Custom MDX Query within an Excel Table. And from the output of the table I then wanted to Pivot this data. I do know that you can do this in Power Pivot, but the requirement was to not use Power Pivot.

Below are the steps and it is a little bit of manipulation to get it working, but it works well in the end. I did not want to use any VBA or custom code to get this working. Only the standard tools and interface with Excel.

 

I did get the idea from Chris Webb’s Blog as a reference point: http://cwebbbi.wordpress.com/2009/12/18/binding-an-excel-table-to-the-results-of-an-mdx-query/

 

NOTE: THIs has been built using Excel 2013

 

In our example below we want to extract from the AdventureWorksDW2012 Analysis Services Database and select the following:

·         Date

·         Product Category

·         Sales Territory Country

 

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

 

Creating the connection

NOTE: In this section we are going to connect to a SQL Server database.

·         The reason is because we need to create a connection file which later we can then modify.

·         And we can only do this with a connection to the SQL Server Database connection type.

 

1.       Open a new Excel Work book.

2.       Click on the Data Tab, then click on the Drop Down for From Other Sources and select From SQL Server

a.        clip_image001[4]

3.       This will then start the Data Connection Wizard

a.        In the Server Name put the name of a SQL Server that you can connect to.

b.       Click Next

4.       Then in the Select database and Table which you can see we selected our AdventureWorksDW2012 as shown below:

a.        clip_image002[4]

b.       ClickNext

5.       On the Save Data Connection File and Finish click on Finish

6.       On the Import Data window click Ok to let it insert the data into your spreadsheet

a.        clip_image003[4]

7.       If you selected the name vAssocSeqLineItems you will see the following below:

a.        clip_image005[4]

 

Modifying the Excel Connection Properties to connect to an Analysis services Instance and add MDX Custom Query

NOTE: Here is where we use our existing SQL Server connection and modify it so that it will now connect to an Analysis Services Instance, and then add our Custom MDX Query.

 

1.       In your Excel Workbook click on the Data Tab

2.       Then under the Connections section click on Properties as shown below:

a.        clip_image006[4]

3.       This will then open the External Data Properties Window

4.       Now in the External Data Properties Window on the right hand side is the Connection Properties Button as shown below.

a.        Click on the Connection Properties Button

b.       clip_image007[4]

5.       Now you will see the Connection Properties Window.

6.       Click on the Definition Tab as shown below:

a.        clip_image008[4]

7.       Now where it says Connection string you are going to put in the following below.

a.        NOTE: This is the connection string to connect to Analysis Services 2012.

                                                               i.      That is why you will see the Provider is set to MSOLAP.5 because I currently have got SQL server 2012 installed.

                                                              ii.      If you are running SQL Server 2008R2 I think currently that is set to MSOLAP.4

b.       You can copy and paste this below into your Connection string, and I will explain what needs to change afterwards

Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=AdventureWorksDW2012;Data Source=AnalysisServices\BI_OLAP;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

c.        The first part is the Provider which is highlighted in RED

                                                               i.      This is the SQL Server 2012 Provider for Analysis Services

                                                              ii.      EG: Provider=MSOLAP.5

d.       Next we are using Integrated or Windows Authentication which is highlighted in Green

                                                               i.      EG: ;Integrated Security=SSPI;Persist Security Info=True;

e.       Next is the Initial Catalog which is the DatabaseName that you want to connect to on your Analysis Services Instance. This is highlighted in Purple

                                                               i.      As per our example we are connecting to the Analysis Services database called: AdventureWorksDW2012

                                                              ii.      EG: Initial Catalog=AdventureWorksDW2012

f.         Next is the Data Source, which is the Analysis Services Instance where your database from the above step is running on. This is highlighted in Orange.

                                                               i.      As per our example, our server name is called: AnalysisServices\BI_OLAP

                                                              ii.      EG: ;Data Source=AnalysisServices\BI_OLAP

g.        The rest of the options you can leave as the defaults.

8.       Now where it says Command Type change this from Table to Default.

9.       Finally where it says Command text this is where you will put in your Custom MDX Query.

a.        As per our Example we put in the following MDX Query:

Selectnonempty {[Measures].[Internet Sales Amount]} on 0

,nonempty {(

                      [Date].[Date].Children

                     ,[Product].[Category].children

                     ,[Sales Territory].[Sales Territory Country].children

                     )} on 1

from [Adventure Works]

10.    So now once Complete your Connection Properties Window will look like the following:

a.        clip_image009[4]

11.    Then Click Ok

12.    You will now get prompted with a Window, with the options of Yes or No

a.        What this Window is saying is that due to you changing the details in the connection it is no longer identical to the original connection.

b.       And because of this, the External Connection File will be removed.

c.        Finally it is asking if you want to proceed with making the changes to the connection.

d.       Click Yes

e.        clip_image011[4]

13.    Then on the External Data Properties Window click Ok.

14.    And now as seen below you can see the results of the Custom MDX Query being run

a.        clip_image013[4]

15.    You could then use Power View to then create a Power View Map from the table above.

a.        clip_image015[4]

 

SSAS – Getting a negative value for measure that has been used in a SUM

When you are browsing data from a cube, you see the grand total or a value in a column it has a negative value. This is while you know that it does not have any negative values so this must be wrong. The reason for the value being negative is due to the fact that data type in your cube is too small to show the correct value. To correct this you will need to change your data type with the details below

 

In this example we are working using the Adventure Works DW 2008R2, and using the Fact Internet Sales Measure Group.

 

1.       This is due to the data type that is used within the cube is too small for your value.

2.       So what happens is that it is an overflow and displays this with a negative sign at the start “-“

3.       To get around this you need to do the following:

1.       Open your cube where you are getting this error

2.       Click on the Cube Structure Tab

1.       clip_image002

3.       Then right click on your measure that is having the issue and click on Properties

1.       In our example this was Sales Amount

2.       Now in the Properties click on the plus sign next to Source

3.       Where it says DataType change this to a bigger value

a.        Example: Our data type was INT which was not big enough for the sum of our values, so once this was changed to a BigInt it went away.

b.       clip_image004

4.       Once that is done re-process your cube.

DAX Function error – An argument of function ‘LEFT’ has the wrong data type or has an invalid value

Today I was working in SSAS Tabular Mode for SQL Server 2012.

What my goal was, was to remove version numbers from Products. This was so that instead of having the following below in a report:

  • Microsoft .Net Framework 1.x
  • Microsoft .Net Framework 2.x
  • Microsoft .Net Framework 3.x
  • Microsoft .Net Framework 4.x

I would have one name instead:

  • Microsoft .Net Framework

I was using the DAX Left Function, and nested inside the Left Function I was getting the count of Characters to find out where the “.x” was.

Here is an example of my DAX Function”

=LEFT([ProductName],FORMAT(SEARCH(“.x”,[ProductName],1,-1),0)-3)

This was then giving me the error:

An argument of function ‘LEFT’ has the wrong data type or has an invalid value

At the time I did not realize that in my DAX Syntax for the Search Function I had said if it is not found then give it the value of -1. So SSAS Tabular was indeed correct in giving me the above error.

In order to fix this it was simply adding the IfError DAX Function in order to get it to work:

=IFERROR(LEFT([ProductName],FORMAT(SEARCH(“.x”,[ProductName],1,-1),0)-3),[ProductName])

Now in my PowerView Report it is showing just the Specific Products and not their Version Numbers, thereby making the report look a lot better and less cluttered.