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

SSIS – Error Handling and Error emailing for Packages

What the steps below do is to handle error handling for your SSIS packages. And then within the error handling enable it so that you can email the exact error to email addresses so that they are notified when there is an error and what the error was.

 

Example:

·         In our example we are going to be using a package named: SSAS-DroppingPartitions

·         This is what will be referenced below.

 

Enabling Error Handling in an SSIS Package

1.       Open your SSIS Package

2.       Then click on Event Handlers at the top.

3.       Then click on:

a.        Click here to create an ‘On Error’ even handler for executable ‘Package Name’

b.       EG

                                                               i.      Click here to create an ‘On Error’ even handler for executable ‘SSAS-DroppingPartitions’

                                                              ii.      clip_image002[8]

4.       Now you will see that your package should have the following at the top.

                                                               i.      clip_image004[8]

5.       Now your package is ready for specific event Handlers to be added.

6.       Below is what it will look like with the Event Handler Enabled

Enabling your package error to be emailed

Adding an SMTP – Connection Manager for the Send Mail Task

1.       The first thing that you need to do is to setup a new connection which will enable you to send the actual emails by doing the following below.

2.       Right click in the Connection Managers window and select the following:

a.        New Connection.

3.       Then click on:

a.        SMTP Connection manager for the Send Mail task

4.       Then click Add

5.       This will then open the SMTP Connection Manager Editor Window and you will need to fill in the following:

a.        Name:

                                                               i.      SMTP-Mail.Mailserver.com

                                                              ii.      NOTE: This is the name of our mail server which we are sending the email.

b.       Description:

                                                               i.      SMTP-Mail.Mailserver.com

c.        SMTP Server

                                                               i.      Mail.Mailserver.com

                                                              ii.      NOTE:

1.       This is the actual DNS address for your SMTP Server

d.       Tick

                                                               i.      Use Windows authentication.

                                                              ii.      NOTE:

1.       This is because in our current setup the mail server requires Windows Authentication in order to send out emails.

e.       Then click Ok.

6.       Now you will see your SMTP connection in your connection manager’s window.

7.       clip_image002[4]

 

Adding the Send Mail Task and configuring the Send Mail Task

1.       What we are going to do is to configure the send email task to send email to the people who require the email. And then configure what gets send out as part of the email.

2.       Click in the Toolbox and drag the Send Email task into your Event Handler Window

3.       Right click on the Send Mail Task and click on Edit

4.       Under General put in the following for the Name and Description

a.        Send email if Task Fails

5.       Then click on Mail in the left hand side.

a.        Where it says SmtpConnection click on the drop down and select the SMTP connection that you created in the section called Adding an SMTP – Connection Manager for the Send Mail Task above.

                                                               i.      EG:

                                                              ii.      SMTP-Mail.Mailserver.com

b.       Where it says From:

                                                               i.      This must be a valid FROM address that the mail server receiving the required email to relay will accept.

                                                              ii.      EG:

1.       user@domain.com

2.       NOTE: In some mail server setups you will have to ensure that the from email address is allowed to replay via your mail server.

c.        Where it says To:

                                                               i.      This is to whom you want to send the emails to.

1.       usertoemail@domain.com

                                                              ii.      Where it says Subject I put in the following:

                                                            iii.      SSIS Error: Package Name

                                                            iv.      EG:

1.       SSIS Error: SSAS-DroppingPartitions

d.       Where it says MessageSourceType this must be left as the default which is:

                                                               i.      Direct Input

e.       We will configure the Message Source in the next step.

f.         So once complete for now it will look like the following:

g.        clip_image004[4]

6.       Next is where we are going to dynamically input our error message and details per SSIS package using System Variables and expressions.

7.       To configure the Message Source type click on the Expressions in the left hand side.

a.        Click on the plus sign next to Expressions

b.       Then click on the Ellipses button, this will open the Property Expressions Editor

                                                               i.      Click under Properties and click on the drop down button.

                                                              ii.      Select the following:

1.       Message Source

                                                            iii.      Then once again click on the Ellipses button.

c.        This will open the Expression Builder

d.       Now this is where you will add the following into your Expression builder, an explanation will be below.

“Package:                              “+ (DT_WSTR, 50)  @[System::PackageName] +”.

Time:                                      ” + (DT_WSTR, 50) @[System::StartTime]  +”.

Task:                                       “+  (DT_WSTR, 50) @[System::SourceName]  +”.

Error Description:                ” + (DT_STR, 2000,1252)  @[System::ErrorDescription]

                                                               i.      NOTE: If you want to format your text, use Notepad and then copy and paste it from notepad into your Expression Window.

e.       What the above does the following:

                                                               i.      It starts with the Package name

1.       And then the package Name variable.

                                                              ii.      Then it is the time of the error

1.       And then the StartTime variable.

                                                            iii.      Next is the Task Name

1.       With the TaskName Variable

                                                            iv.      Finally is the Error Description

1.       With the ErrorDescription variable

2.       NOTE: Because the error is text you have to change the data type from the default which is DT_WSTR to DT_STR

a.        With this you then need to add the length and then also the code page

                                                                                                                                       i.      In our example which works is the code page 1252

f.         Then click on Evaluate Expression

                                                               i.      This should then come back with the expression in the Expression Value window above the Evaluate Expression button.

                                                              ii.      Below is what the Expression looks like

                                                            iii.      clip_image006[4]

                                                            iv.      Click Ok.

g.        Now to add an expression for our subject click on Property drop down in the Property Expressions Editor.

                                                               i.      Select Subject

1.       Then once again click on the Ellipses button.

                                                              ii.      This will open the Expression Builder

                                                            iii.      Now this is where you will add the following into your Expression builder, an explanation will be below.

“SSIS Error: ” +  (DT_STR, 50,1252)  @[System::PackageName]

1.       What this does is just takes the Package Name and puts it into the subject line in our email.

h.       Then click on Evaluate Expression

                                                               i.      This should then come back with the expression in the Expression Value window above the Evaluate Expression button.

                                                              ii.      As shown below is what it looks like

                                                            iii.      clip_image008[4]

i.         Then click Ok 3 times to get out of the Send Mail Task Editor.

8.       Once complete it will look like the following:

a.        clip_image010[4]

b.        

 

Testing your Send Mail Task

1.       The final step is to test to make sure that the Send Mail task works.

a.        NOTE: I would only do this for the first Send Mail task, because if this works it should work for all other packages as well.

2.       Go into your Control Flow and deliberately change a task so that it fails.

a.        NOTE: In our example I changed an Execute SQL task, and then change the Stored Procedure name so that I knew it would fail because the Stored Procedure does not exist.

3.       Once you have saved your change then run the package in Debug mode.

4.       When the package runs it will FAIL which is what we want.

a.        Now if you go and look in the Event Handlers Window you should see your Send email if Task Fails as green.

b.       clip_image012[4]

5.       You should also receive the email with the error as you configured above.

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