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 measure and dimension

Hi Folks,

i have big problem. my measure is call "VALUE".

and the Dimension "TYPE" (TYPE a,b)

The kpi should compare VALUE Type a and TYPE b.

How it works?

Can everyone help me. THX Greg

It depends on what you mean by comparing the two values, but here's an example of an MDX expression (in a calculated measure, but you could easily use it inside a KPI) that compares two values - Internet Sales Amount for Fridays and Saturdays - on the same dimension:

Code Snippet

with member measures.test as

iif( ([Measures].[Internet Sales Amount], [Date].[Day Name].&[6])

>

([Measures].[Internet Sales Amount], [Date].[Day Name].&[7])

, "Friday is greater", "Saturday is greater")

select [Date].[Calendar Year].members on 0,

[Product].[Category].members on 1

from [Adventure Works]

where(measures.test)

HTH,

Chris

|||

Hi Greg,

Edit your cube in BIDS and then go to KPI's tab.

Create a new KPI and complete the form with MDX script you need for Value, Goal, Status and Trend.

There you can also set Status indicator e Trend indicator (traffic light, gauge, standard arrow and so on).

Here you can find some instructions:

http://msdn2.microsoft.com/en-us/library/ms181183.aspx

|||

thx

this code help me

([Measures].[VALUE], ( [DIM].[NAME].[MEMBER] ))

KPI List Web Part in MOSS 2007 - How Do I Display an 'As of Date' For Indicators?

I have created an SQL 2005 SSAS cube with many different KPIs defined. In MOSS 2007 (SP2) I have created a site to display these various KPIs in several KPI Lists. Some of the indicators in the underlying cube are updated daily, others weekly, others quarterly, etc.

What are some ways to display to the user what date or time slice each specific indicator is "as of"?

Thanks in advance for any help you can provide.

-Steve

Hello. I do not think that there is a property in SSAS2005 that you can use for this.

The easy way is to create separate KPI:s depending on the time slice /or update window and simply name the KPI:s in SSAS2005 in this way.

I can think of ActSalesBudSalesMonth and ActSalesBudSalesYearToDate. I usually use shorter names.

Else I recommend you to find a property/information field in MOSS 2007

HTH

Thomas Ivarsson

KPI List Web Part in MOSS 2007 - How Do I Display an 'As of Date' For Indicators?

I have created an SQL 2005 SSAS cube with many different KPIs defined. In MOSS 2007 (SP2) I have created a site to display these various KPIs in several KPI Lists. Some of the indicators in the underlying cube are updated daily, others weekly, others quarterly, etc.

What are some ways to display to the user what date or time slice each specific indicator is "as of"?

Thanks in advance for any help you can provide.

-Steve

Hello. I do not think that there is a property in SSAS2005 that you can use for this.

The easy way is to create separate KPI:s depending on the time slice /or update window and simply name the KPI:s in SSAS2005 in this way.

I can think of ActSalesBudSalesMonth and ActSalesBudSalesYearToDate. I usually use shorter names.

Else I recommend you to find a property/information field in MOSS 2007

HTH

Thomas Ivarsson

KPI in SQL 2005

Hello,
I search informations or a tutoriel to carry out a KPI (Key Performance
Indicator) in Analysis Services 2005.
Can you help me.
Thanks!!
NicoHi
Look in SQL 2005 Beta 2 books online. Asking this question in the betagroups
for 2005 will also help.
"pralnico" wrote:

> Hello,
> I search informations or a tutoriel to carry out a KPI (Key Performance
> Indicator) in Analysis Services 2005.
> Can you help me.
> Thanks!!
> Nicosql

KPI in SQL 2005

Hello,
I search informations or a tutoriel to carry out a KPI (Key Performance
Indicator) in Analysis Services 2005.
Can you help me.
Thanks!!
Nico
Hi
Look in SQL 2005 Beta 2 books online. Asking this question in the betagroups
for 2005 will also help.
"pralnico" wrote:

> Hello,
> I search informations or a tutoriel to carry out a KPI (Key Performance
> Indicator) in Analysis Services 2005.
> Can you help me.
> Thanks!!
> Nico

KPI in SQL 2005

Hello,
I search informations or a tutoriel to carry out a KPI (Key Performance
Indicator) in Analysis Services 2005.
Can you help me.
Thanks!!
NicoHi
Look in SQL 2005 Beta 2 books online. Asking this question in the betagroups
for 2005 will also help.
"pralnico" wrote:
> Hello,
> I search informations or a tutoriel to carry out a KPI (Key Performance
> Indicator) in Analysis Services 2005.
> Can you help me.
> Thanks!!
> Nico