Design Tip #25 - Parent > Child > Child (Again)

[mostafa_mahrous75]

Applying Kimball Design Tip #25 on the following chart in which we’ve 3 Fact Tables as follows:
1.Fact Table F11 (left numeric digit represents the first level in Parent-Child hierarchy) which is dimensioned by a dimension set consists of 5 dimensions DX, D111, D112, D113 and D114 dimensions
2.Fact Table F12 which is dimensioned by a dimension set consists of 5 dimensions DX, D121, D122, D123 and D124 dimensions
3.Fact Table F21 (left numeric digit represents the second level in Parent-Child hierarchy) which is dimensioned by a dimension set consists of 4 dimensions D211, D212, D213 and D214 dimensions


We’d create a single fact table F whose grain is at the most granular level which is F21, also we’d also allocate facts of F11 and F12 at the most granular level. Hence we’d get the following chart in which we’ve a single Fact table F which is dimensioned by a dimension set consists of DX11, D111, D112, D113, D114, DX11, D121, D122, D123, D124, D211, D212, D213 and D214 (DX11 and DX12 dimensions are conformed dimensions of DX). The dimension set of fact table F (which consists of 14 dimensions) is a union of the dimension sets of F11, F12 and F21


Now, consider the following scenarios:
1. Fact table F21 is a child for F13,F14, F15 … etc as well
And / or
2. Most granular level of our business is much deeper (ex. represented by F51 fact table)

The number of dimensions that consists the resulting dimension set of fact table F will increase dramatically – in a complex business we may simply reach tens of dimensions which basically contradicts with the assumption that any business process should be represented by a maximum of 25 dimensions

Any solution for this dilemma!!

[a_sherbeeny]

I think the following chart will represent your original case:

Also, the following chart will represent your case after allocation:

That might help to understand your case...

[ngalemmo]

I don't follow the 25 dimension comment... its just a rule of thumb, not gospel.

The Tip #25 that I read was talking about a transaction that had measures at different grains, such as an invoice with a discount amount on the header and line information in the detail.

If that is the case, and the three facts you are talking about represent levels in the transaction hierarchy, then the fact at the lowest level should already be made up of every possible dimension, including those at the two higher levels. I don't understand why the final fact adds every dimension from all three facts. If these are in fact different dimensions, then I don't think that is what tip #25 is about.

[a_sherbeeny]

Thank you ngalemmo for your reply…

Here’s a Business Case that will make the question clearer. Let’s consider the following example...

Promotion is typically considered as one of the dimensions of Line Item Fact table. However, Promotion itself is a business event which we need to model as a separate Fact table which is dimensioned by its own set of dimensions such as Approving Employee, Promotion Type, Start Date and End Date. Promotion Fact Table will include some measures such as Forecasted and Actual Revenues.
In that case, what is the best practice for modeling the relation between Promotion and Line Item? Currently I’m thinking of following model:

Now, here’re my questions:

1. Firstly, what are your comments about the above model? Do you think that this is the best practice for modeling the above mentioned Business Case?

2. Back to Design Tip # 25, Should we consider it in this case and add Promotion Approving Employee, Promotion Type, Promotion Start Date and Promotion End Date as dimensions of Line Item Fact table (Line Item Fact will be dimensioned by 10 dimensions instead of 6)?


Appreciating your assistance…

[ngalemmo]

Normally, I wouldn't have forcast and actuals in the same fact, unless it is an aggregate, as they occur at different times for different reasons. Usually you have a promotions fact with forecast, then use the sales fact to calculate actuals. Other than that, the model is fine, but is it not the type of situation tip 25 is about.

Promotions forecast and sales are not dependent on each other. There isn't a parent/child relationship. They are simply two different facts that share dimensions and may be combined in analysis.

[mostafa_mahrous75]

Thank you ngalemmo, your replies were very helpful

[hang]

I agree the tip #25 (header DD allocation) is not applicable to your case, but simply because all the attributes that define the promotion are not facts per se, as in a fact table. Instead, they are dimension attributes.

I would combine the attributes in your Promotion Fact and Promotion Dim into a single Promotion Dim without any snowflakes, and have a PromotionSK in Line Item fact table. Don't get carried away by the numeric values at Promotion definition level. The Forecasted Revenue can stay in the Promotion Dim, and could also appear in the downstream aggregate fact table/views for comparison purpose. The Actual Revenue is an aggregated fact of the sales and should not be in the Promotion Dim, but perhaps will be in a consolidated fact table/view at aggregated (by promotion) level.

As a side note, there is normally a promotion coverage fact table that covers products to be promoted so that you may query about products that have been promoted but not in the actual sales. If you do have such a coverage fact, I wonder if the Forecasted Revenue can appear in a fact table/view at some level of downstream aggregation on the coverage table.

[Sponsored content]