KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is above or below a goal. Managing the goal numbers can be a headache because they made need to be updated often. By placing the goal numbers on a table you can dynamically keep the goals up to do by simply updating the table.

The following example uses the adventure works data warehouse. You will need to create a new table. Here you can see one named KPI Goals and the data on it. This table contains the internet sales goal for each year.

Next you will need to add this table to the DSV in SSAS and create a relationship between it and the year column on DimDate. You can map the granularity to any attribute like Quarter or Month.

Then create a new measure group in the cube using the KPIGoals Table. You can delete the count measure it creates, you only need the internet sales goal.

Next you will create the relationship in the cube under the dimension usage tab, set it to a regular relationship on the year attribute.

Now you are ready to create a dynamic KPI. Name the KPI Yearly Internet Sales Goal. Use the Internet Sales Amount as the value expression.

You want the goal to only show when the user is at the year level when browsing in excel. A Case statement checking the level will accomplish this. We are also using a tuple to get the goal for each year.

Case When ([Due Date].[Date].CurrentMember.level is [Due Date].[Date].[Calendar Year]) then

([Due Date].[Date].CurrentMember,[Measures].[Internet Sales Goal])

Else Null

End

The Status will check the goal value, if the goal is empty then the status will be null. This will ensure the status only shows on the year level. Then you compare the value to goal and set the numbers to 1, 0, or -1 based on the comparison.

Case

When Isempty(KPIGoal(“Yearly Internet Sales”)) Then Null

When KPIValue(“Yearly Internet Sales”) > KPIGoal(“Yearly Internet Sales”) Then 1

When KPIValue(“Yearly Internet Sales”) = KPIGoal(“Yearly Internet Sales”) Then 0

Else -1

End

Now in Excel you will see the KPI status next to each of the years only. The goals are based on the values from the KPI Goals table.