KPI Goals and Percentages as Dimensions or Store as Facts?

We are using PPS Monitoring for our Dashboards and we are creating our KPIs within PPS and SSAS. Our client would like to create a dimension with these KPI goals and percentages for each goal per department and measures or add to the fact table.

Now please do not judge us by these topics sometimes but we are at a client site that is different is the word we will use.

We are looking for input from others regarding our question above and ideas if any who would like to respond.

Note: the data marts are being refreshed nightly we are incorporating the BUS architecture.

REASON: The client wants the business to be able to use an excel spreasheet to manually update when ever they would like to change the goals and percentages. The excel document would get loaded through ETL into it's dimension or fact nightly and the cube KPI's would pull the new goals and percetages to be used by PPS (maybe looking into this still) and into ProClarity existing briefing books.

Thank you for any replies and advice.

Scoop

I see the KPI goals no differently than typical budget data. I think it should be a fact table. It makes no difference that they want to have ETL reload them every night.

Agreed. Actuals and goals are fact measurements.

This is interesting to me, we're currently working on a dimensional model for KPI's as an alternative to Cognos Metric Studio. Metric Studio has some great features but the need to present data through scorecards is limiting when compared to the absolute flexibility of a star schema.
So our current whiteboard design includes a METRIC dimension. The metric one contains attributes about the KPI's themselves such as name, group, owner, format (percent, absolute, dollars etc), tolerance (for assessing whether actual v's target is an issue or not) and tolerance format.
There is no actual or target in the dimension of this design, these are in the fact table. Also in the fact table at present are variance (pre-calculated target - actual) and some other additive and non-additive facts.
Scoop, can you share your star schema or at least more attributes of your KPI dimension? Happy to share ours.

We are still in design mode waiting on business to make some decisions on these goals and percentages so, as soon as we have a schema will be glad to share.

This is a subject area I'm getting involved with in terms comparing actuals vs target. I am in favour of storing the 2 budgeted figures as a lookup which will be used in association with the Cognos FE. However, I am still pretty new to the dimensional modelling realm so would appreciate it if I am way off track

However, is it efficient to populate say two columns on your Fact with two additional target figures when these values are in fact duplicated for all Fact rows? Surely there is a more efficient method for applying budgetary type data as just adding a new column or columns with the budgeted figures for a selected time period or other dimensional attribute which will be the same for each Fact row loaded is introducing a lot of redundancy of data? Say you have 2m Fact rows, you add 4m redundant columns as the info is repeated if you have 2 extra cols to add for the budgetary figs.

My apologies if I have misunderstood anything about the previous posts.

DR

We are still in requirements of this yet I have been fooling around with some models, SSAS, ProCalrity, and PPS monitoring and have found adding a separate fact table for GOALs and percentages is more of a best practice then storing these in the same fact with units and measures to eliminate redundancy and to only relate the dims that are related to those fact goals and percentages.

I may be wrong and if so; please reply anyone with correct way or best practice.

Thank you,
Scoop

PS. we find this forum very helpful so, thank you.

However, is it efficient to populate say two columns on your Fact with two additional target figures when these values are in fact duplicated for all Fact rows?

It sounds to me like you have an unaggregated fact involved, I don't know enough to comment on your design but for the one I'm working on, we would only have perhaps 20,000 rows of aggregated measures/metrics/facts. In this scenario you are right in that some of the targets will be duplicated - eg the target for Student Satisfaction might be 95% and might be this for every school or faculty or even every course but the design allows it to be different, I think this is important.

have found adding a separate fact table for GOALs and percentages is more of a best practice then storing these in the same fact with units and measures to eliminate redundancy and to only relate the dims that are related to those fact goals and percentages

I don't think this will work for me because my goals are sometimes percentages, although it is true that not all dimensions related to all measures being stored in the fact. This is the issue I'm wrestling with at the moment. There seem to be three major issues at play here:

- The benefit of having all metrics in one fact table - easier reporting and direct comparison across all metrics
- The grain of that fact being different for some metrics - some are annual, some are multi-annual (maybe 10-20/yr)
- The dimensions not all being relevant for all metrics - do I have a 'Not Applicable' dimension row for these?

I'll post a star schema on here later to help explain the design a little more

I forgot you can't directly add images. I've put the basic star schema design here, all comments very welcome
http://blog.une.edu.au/robbi/2009/04/14/metric-facts/

robhale wrote:

However, is it efficient to populate say two columns on your Fact with two additional target figures when these values are in fact duplicated for all Fact rows?

It sounds to me like you have an unaggregated fact involved, I don't know enough to comment on your design but for the one I'm working on, we would only have perhaps 20,000 rows of aggregated measures/metrics/facts. In this scenario you are right in that some of the targets will be duplicated - eg the target for Student Satisfaction might be 95% and might be this for every school or faculty or even every course but the design allows it to be different, I think this is important.

have found adding a separate fact table for GOALs and percentages is more of a best practice then storing these in the same fact with units and measures to eliminate redundancy and to only relate the dims that are related to those fact goals and percentages

I don't think this will work for me because my goals are sometimes percentages, although it is true that not all dimensions related to all measures being stored in the fact. This is the issue I'm wrestling with at the moment. There seem to be three major issues at play here:

- The benefit of having all metrics in one fact table - easier reporting and direct comparison across all metrics
- The grain of that fact being different for some metrics - some are annual, some are multi-annual (maybe 10-20/yr)
- The dimensions not all being relevant for all metrics - do I have a 'Not Applicable' dimension row for these?

I'll post a star schema on here later to help explain the design a little more

Thanks for the reply - I was reading more on the topic over the weekend and yes, I totally agree that the targets need to be joined at a summary level (and in our LDM their relation to their associated dimensions is at a higher level of granularity than the grain of the fact). Thanks again

Hi

You can find this info by using search box in the top of website with some keywords related before posting questions.

People also can refer from: KPI metrics

Hi,

Good ideal, pls try to keep posting. I like this topic very much and I will digged this one. Tks again.

Scoop wrote:We are still in requirements of this yet I have been fooling around with some models, SSAS, ProCalrity, and PPS monitoring and have found adding a separate fact table for GOALs and percentages is more of a best practice then storing these in the same fact with units and measures to eliminate redundancy and to only relate the dims that are related to those fact goals and percentages.

I may be wrong and if so; please reply anyone with correct way or best practice.

Thank you,
Scoop

PS. we find this forum very helpful so, thank you.

If you want to get more materials that related to this topic, you can visit: KPI metrics

Best regards.