Modelling KPI values

Hi

There is a requirement for the DW that we are building warehouse for the business managers to be able to set "targets" for their employees and for various the Contracts that we run.

A front end application has been built to collect and store this information in a SQL database which we have assumed we would store as a FACT table in our warehouse and then get them turned into KPI's, however now we're a bit stuck with how to implement this

The Employees and the Contracts are stored as seperate Type 2 dimensions and we have a Fact Table which captures the events we are interested in and this obviusly stores the corresponding surrogate keys for the Employee and Contract for that particular event/transaction.

However the targets that will be set are really against the dimensions naturalkeys ie the employeeid and contract id rather than the specific version of the dimension

any suggestions or pointers on how to attack this?

many thanks

What does your event fact table represent? Generally, forecast measures are kept in a separate fact table and then pulled in with the actuals fact table. You may have to summarize your actuals measures to the same grain as your forecast measures.

Hi

Our event fact table captures all the differnet "events" that our employees perform or that their customers do that they are realted to and these are recorded at day level. ie an event occurs on a particular day, examples of these are things like

Starts Course
Obtains Qualification
Gets Job

thats sort of thing

so the Targets to be set are things like a particlar employee should get say 5 of their clients/customers on a training course per week
but there could also be a Target for the overall contract that they maybe need to hit 50 starts per week that sort of thing

So could it be the case that we'd need to have a "Actual" field in the Targets facts table and run some sort of update from an MDX query to populate the "Actual" in that table?

thanks

meb97me wrote:However the targets that will be set are really against the dimensions naturalkeys ie the employeeid and contract id rather than the specific version of the dimension

any suggestions or pointers on how to attack this?

Since Employee and Contract are type 2 dimensions, the specific version can only be retrieved by specifying a point of time. For the daily grain fact, the dimension surrogate keys can be looked up based on date range constraint. However for the facts higher than daily grains like your target fact, say weekly or monthly, you need to nominate a day in the period based on the business requirement. Typically the period ending day is used to represent the desired version of dimension.

meb97me wrote:So could it be the case that we'd need to have a "Actual" field in the Targets facts table and run some sort of update from an MDX query to populate the "Actual" in that table?

Theoretically it is possible, but it’s not a trivial job to update a table stored in a relational database based on an MDX query against an OLAP server. The simple approach would be, as BoxesAndLines said, to aggregate your actual facts to the same grain as the target fact and then feed the combined fact to your cube and work out the KPI requirements by means of MDX or cube configuration.

When you add facts to the new table you associate the row with the current version of the type 2 dimension row.

The issue is when you generate reports, not when you load the fact. Use a self-join on the natural key in the dimension table to locate the most current row of a particular entity.

ngalemmo wrote:When you add facts to the new table you associate the row with the current version of the type 2 dimension row.

The issue is when you generate reports, not when you load the fact. Use a self-join on the natural key in the dimension table to locate the most current row of a particular entity.

Thank very much for the reply. It make me thinking about for my project.

This link below can show more info, you can find them at: Business KPIs

Tks again and pls keep posting.