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.       

3.       Next I gave my KPI a meaningful name.

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

                                                               i.     

                                                              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.       

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.      

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.       

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.      

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.     

h.       This is the entire Trend Expression shown below:

i.        

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

a.       

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.      

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