Linking multiple fact tables at different grains to the same dimensions

Question

Linking multiple fact tables at different grains to the same dimensions

haroon.rasheed8

SSC Veteran

Points: 225
More actions
July 15, 2015 at 5:32 am

#305869

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63436 More actions · Answer 1

I asked a DW guy about that not terribly long ago. He said that you have basically two fact tables which share the same dimension tables. The dimension tables are just joined to both fact tables.

Martin Schoombee SSCoach Points: 19116 More actions · Answer 2

You have quite a few different options for each of these...each with its own benefits and risks.

Let's chat about the date granularity issue first, as it's the easiest to address from a modeling perspective...

When joining a fact table like your budget fact (which is at the month level) to a date dimension (which is at the day level), we usually choose either the first day of the month or the 15th of the month as a representative date. As long as you are consistent, it doesn't really matter which one you choose.

Assuming region is an attribute of your customer dimension and product category/sub-category attributes of your product dimension...these are typically a bit more difficult to deal with.

Here's a few options and points to take note of (I'll only address the region problem, but the options apply to both scenarios):

Treat these as many:many relationships, which means that you will have to create a bridge table that contains some form of composite key (let's say region code for instance) and all customers within that region. Your budget fact table will then carry the region code.
If you are using OLAP cubes, setting up these many:many relationships will work...and when looking at budget numbers for region X at the customer level, it should show the same amount. At the region level though, you will see the same number as for each customer. You could further extend this by using scoped assignments in SSAS, forcing budget values below the region level to be blanked out. If you're not using OLAP cubes, handling this may be a little more tricky.
Another option with many:many relationships is to have a factor value in the bridge table. For instance, let's say that region X contains 10 customers...you will then add the factor (10 in this case) to your bridge table. For reporting purposes, doing this will allow you to divide each budget value by the factor and proportionally allocate it to each customer. This method requires quite a bit of maintenance in the bridge table, and there could be some hoops to jump through when it comes to fractions and rounding.
Some people may prefer to add a dummy customer for each region, with all other attributes defaulted. This customer key can then be used in the fact table. When looking at budget numbers at the customer level, it will be consistently assigned to the same customer, and it becomes a training issue at that point so that your end users know what they are looking at.
On the flip side...doing this may cause some inaccuracies when counting customers for instance, as you will now have more customer entries in your dimension as there really should be.
The last option would be to split these attributes into their own individual dimensions. While this may work for region (I advocate for a separate geography dimension in most cases), it may not necessarily be the case with the product attributes.

I would most likely lean towards the first option in most cases...but it really depends on your specific requirements and environment. Weigh the pro's and con's of all these options as they apply to your specific needs and requirements, and make the best possible decision from that.

Hope this helps...