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 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 Reporting Service

Hi All,

How to integrated the KPI from SSAS 2005 to SSRS 2005 ?

So the value and indicator from KPI can be retrieve/view by SSRS 2005...

Any suggestion will be very helpful.

THX.

Hi

I have the exact same problem ...

But when deploying scorecards from Business Scorecard Manager to Reporting Services it is possible to get reporting services to show images as well as values. However when you look at the report in Visual Studio / Report Layout there is no give-away as to how the image is generated ...

A home-made syntax may be a solution as the KPI-value can be seen to be (e.g.) "=Fields!Advertising_comp_to_Turnover_Goal.Value". Exchange of "Value" with "Indicator" does not work however.

Any other suggestions?

Chr.

|||

I found a description of how to make it work ... not for me however!!

Chr.

http://www.sqljunkies.com/WebLog/sqlbi/archive/category/325.aspx

Report Builder Model with UDM

postedTuesday, November 15, 2005 6:13 AM bymarcorusso

Today I lost a lot of time digging on this problem so a I hope post about this will save time of other developers!

I wanted to import the UDM into a model for Report Builder. You have to do these steps:

go intoSQLServer Management Studio

KPI in Reporting Service

How to integrated the KPIs from SSAS 2005 to SSRS 2005 ?

Moving this thread to the reporting services forum where someone should be able to help

Keeping SSAS Projects in the dark ?

The feature with SSIS packages to "keep them in the dark" with configurationfiles and the use of variables. How does or does that apply for SSAS projects ?

Since we keep dev, test and production sepperate, the cubes datasource differ according to witch enviroment they are running on. Is there a way to fix this, or what is best practice on this front ?

Whe you talk about "keep them in the dark" are you referring to the following article : http://www.sqlmag.com/Articles/ArticleID/47688/pg/2/2.html ?

I guess this is not widely accepted terminology.

For being able to change some project properties while deploying you can take a look at the Deployment Wizard utility. It gives you abilty to change connection strings during deployment.