Incorporate a plan figure

View previous topic View next topic Go down

Incorporate a plan figure

Post  waspinall on Tue Nov 01, 2011 12:22 pm

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

waspinall

Posts : 3
Join date : 2011-11-01

View user profile

Back to top Go down

Re: Incorporate a plan figure

Post  VHF on Tue Nov 01, 2011 4:15 pm

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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Incorporate a plan figure

Post  ngalemmo on Tue Nov 01, 2011 5:33 pm

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...)
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Incorporate a plan figure

Post  VHF on Tue Nov 01, 2011 6:36 pm

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

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Incorporate a plan figure

Post  waspinall on Thu Nov 03, 2011 11:22 am

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

Posts : 3
Join date : 2011-11-01

View user profile

Back to top Go down

Re: Incorporate a plan figure

Post  VHF on Thu Nov 03, 2011 11:29 am

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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Thanks very

Post  waspinall on Fri Nov 04, 2011 11:37 am

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.

waspinall

Posts : 3
Join date : 2011-11-01

View user profile

Back to top Go down

Re: Incorporate a plan figure

Post  VHF on Fri Nov 04, 2011 11:43 am

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!

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Incorporate a plan figure

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum