Connecting Actual and Budget Fact table to same Product and Customer Dimension

View previous topic View next topic Go down

Connecting Actual and Budget Fact table to same Product and Customer Dimension

Post  HAROON8 on Wed Jul 15, 2015 6:23 am

Hello Folks,

I am seeking your advice on dimensional modelling problem I have.

I am modelling two fact tables of Actuals and Budget which are at different granularity, Actuals are at day, customer and product sub category level. Budgets are at month, Region and Product catgeory level.Month, Region and Product Category is present in Date, Region and Procut Category dimension respectively. I have only three dimensions as Customer, Product and Date. Linking those dimensions to Actual Fact table is not an issue, what is the best way and options are there to link budget fact table to those three dimensions.

Please advise.

Regards,
Haroon

HAROON8

Posts : 2
Join date : 2015-07-15

View user profile

Back to top Go down

Re: Connecting Actual and Budget Fact table to same Product and Customer Dimension

Post  ngalemmo on Wed Jul 15, 2015 12:37 pm

At the very least you need product category and region dimensions. A month dimension would be preferable but some people use the existing date dimension and limit the month level key to a specific day of the month (first day is easiest).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Connecting Actual and Budget Fact table to same Product and Customer Dimension

Post  HAROON8 on Wed Jul 15, 2015 3:26 pm

Many thanks for that. As you suggested to have region and category dimensions. Are those dimensions going to be related to customer and Product dimensions respectively in snow flake fashion.

HAROON8

Posts : 2
Join date : 2015-07-15

View user profile

Back to top Go down

Re: Connecting Actual and Budget Fact table to same Product and Customer Dimension

Post  ngalemmo on Wed Jul 15, 2015 9:59 pm

Not normally. They would be redundant to the information carried on the more granular dimensions.

When you combine the two facts, aggregate them on the attributes in common first.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Connecting Actual and Budget Fact table to same Product and Customer Dimension

Post  gvarga on Thu Jul 23, 2015 5:27 am

Letís focus on the Month. You should have a separated Month dimension. It should be a so called conformed rollup dimension: the attributes of Month dimension will be subset of the Date dimension (e.g. Month Key, Month Name Ö). These common attributes share the same structure and content. You will connect Budget fact to the conformed rollup tables where needed. If so you will be able to drill across between Actuals and Budget fact tables.

gvarga

Posts : 43
Join date : 2010-12-15

View user profile

Back to top Go down

Re: Connecting Actual and Budget Fact table to same Product and Customer Dimension

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