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

 

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