Incorporate a plan figure

Hello experts,

I am new to the dimension modelling game.

I have a basic cube that counts activity using aggregation of the transactional fact table. I would like to include a plan figure in the model so that i can look at whether store x achieved a sales target by how much over a time period.

My knowledge is pretty basic so apologies if this is far too simple!!!!!!!!!!

Thanks
Bill

Typically you would create a new fact table in the dimensional model to store the plan/budget/target amount. This new fact table becomes an additional measure group in the OLAP cube. You can now compare actual vs. target in the cube. You can then determine for any time period if a given store met its target.

I am new to the dimension modelling game.

This is not a game... this is serious business!!!

(... I mean, we would like people to think so...)

Oh, oh, I'm in trouble then, because I've been having way too much fun at this!

Thanks for the feedback. I will give it a try. I have read something about virtual cubes but I will build the new fact table.

Does that mean i can combine measures from 2 separate fact tables in the cube?

I love forums groups for work they make the world an easier place.

And yes this is definately a serious business!!!!!!!!!!!

waspinall wrote:Does that mean i can combine measures from 2 separate fact tables in the cube?

Sure, at least if you are using SQL Server Analysis Services (SSAS). Maybe not if you are using SAP BW!

In SSAS each fact table becomes a "measure group" in the OLAP cube.

Thank you very much for your help.

With some jiggery pokery I have managed to get the cube populating correctly. I have created the second fact table and dimension, then associated this using the dimension usage tab. From there i have been able to add variance and variance percent.

This is exactly what I was hoping to achieve so thank you very much!

Get down.

Good deal!

At some point you might want to look at adding either some calculated measures and/or KPIs to your cube to identify if stores are meeting their target. However, that does require learning enough MDX to define the KPI, so don't be in too much of a hurry if what you've got is getting the results your users require!