KPI that compares the growth over years

Hi there,
I'm new to SSAS and MDX. Creating my first dimensions and cubes went fairly fine. But now I hang on KPI.
I'm trying to create a KPI which shows the growth of bookings comparing one year to the previous year.
I want to set a fiterexpression for the time-dimension (e.g. YearString equals 1998). If a filter is set, it should show the bookings for that year ([Measures].[Bookings]) as value and the bookings of the previous year as goal.
My problem is, that I cannot access the selected YearString, to get the bookings of the previous year. I tried the function parallelPeriod() and also a self-created Calculation, but it seems that [dimension time].[hierachy].CurrentMember is always [dimension time].[hierachy].[(All)].

My data structure is as followed:

Fact-table: FactBookings
SeasonID (FK)
YearID (FK)
AgencyID (FK)
CountryID (FK)
RegionID (FK)
IncomeID (FK)
AgeID (FK)
AgencyID (FK)
Bookings

Dimensions:
Income, Age, Agency:
[dimensionname]ID
[dimensionname]String

Dimension geography (having a hierarchy):
RegionID
CountryID
RegionString
CountryString
primary key: RegionID+CountryID

Dimension time (having a hierarchy):
YearID
SeasonID
YearString
Season String
primary key: YearID+SeasonID

Is there a way to access the filterexpressions for a KPI, or does anyone has an idea, how a calculation might has to be, which shows the value of the previous year compared to the actual year?

It would be very nice, if anybody could give me an idea how to create such a KPI.

Regards,
Maik.

First, you need to create the calculated member for the PreviousYear. You can use either Time Intelligence Wizard, you use formulas from the following blog post: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

After that, you can enter the expression for KPI as (Time.PreviousYear, Measures.Booking) and it should work fine.

HTH,

Mosha (http://www.mosha.com/msolap)

|||Thank for your answer.
I've tried it for hours.
Now I have a dimension [Time Calculations]
with a calculated member:
CREATE [Time Calculations].[Prior Year] =
(ParallelPeriod([Dimension Time].[YearString - SeasonString].[YearString]), [Time Calculations].[Current Period]);

But for my KPI something like
([Time Calculations].[Prior Year], [Measures].[Bookings])
as the goal-expression wasn't working.

Finally, after turning IsAggregatable to false for YearString in dimension time, I was able to select a year at the KPI-Browser and the goal-expression showed up the measure-value of the previous year. Yeah! .
But I'm still not completely happy. Because of turning IsAggregatable to false, the measure in the Cube-Browser is fixed either to the first year or to one year specified as a filter.
At this point I'm unable to summarise/aggregate the bookings over all time or more than one year.

Any idea for that?

BTW: nice blog Mosha .|||You shouldn't have had to set IsAggregatable to False on Year attribute. Just remember - when it is aggregatable - you will have All level, so the current member inside your Time dimension will be All Years, so you will need to manually position to specific year in order to see a previous year.

KPI design and implementation

Hi all,

I'm fairly new to analysis services and am encountering some difficulties when it comes to designing KPI's. I want these to be designed so that they may be displayed in a KPI list on Sharepoint 2007 server.

The basic premise for the simple data warehouse pilot was to present usage data for a number of customer sites split up but time etc. These sites also have a potential usage value attached to them. I'd like to implement a KPI that shows the current progress in the current month for there usage against the site's potential. A cylinder or thermometer type diagram i believe would work well to show the progress as a percentage value.

This may be to vague a description and i'm willing to elaborate further if necessary but could someone please help me understand how best to approach this?

Many thanks in advance,

Grant

Hello. First, if you have installed the SQL Server 2005 samples you will find an SSAS2005(Analysis Services) project, Adventure Works, with a lot of KPI:s in the cube editor. This is to get to know the MDX syntax for KPI:s.

To get the KPI:s to Sharepoint is not done directly. You will need a separate solution for this.

You can query and display KPI:s in Reporting Services 2005 but you will only see the values not the graphic/icons.

One option is to use Excel 2007 and construct a KPI spreadsheet report and publish it in Sharepoint. Another option is to use ProClarity Analytics platform and use their webparts for Sharepoint. Both these alternatives are not free of charge.

A third option is Microsofts Business Scorecard Server. It uses Sharepoint directly. This product and ProClarity will be included in the new Performance Point Server that will be on the market this summer.

HTH

Thomas Ivarsson