Many to Many relationship in Dimension Modelling - Part 3 (Fundamental Flaw, Weighing factor)

As discussed in previous post, the idea of attaching one product to many product group is fundamentally flawed. In previous example, we have seen that every time there is jump in Umbrella sales, they will be jump in Fashion sales and Seasonal sales. So, this is clearly not going help the trend analysis or intelligent decision making.
Also, the schema proposed in previous example has an upper limit on the number of groups a product can belong to.
If the business is happy with above limitations, you don't need to read further. However, business also agrees that it is a flaw and they would like to see what options are available - we can a step further.
Depending on the nature of the business, we will assign a weighing factor to each product group for each item. This weighing factor (now on referred to as WF) will be less than 1 for each entry, such that the sum of WF for a product equals ONE.
In our example, Umbrella is more of a seasonal product. It is also a fashion (because there are alternate products like Wind-Cheater, and sale of Umbrella will be affected by whether they are in trend, or wind-cheater), but it is less of that. So, we can probably say it is 70% seasonal, and 30% fashion. Whereas, something else like Tinted Sunglass is 50% seasonal, 50% fashion. And Scarf can be 30% seasonal, 70% fashion. You would sit with business and finalise the weighing factors. It can be a slowly changing attribute too.
Then we store the weighing factors in our dimension table as attribute. So, your dimension looks like this:

Prod_ID Prod_Name Prod_Group PG_WF Secondary_Prod_Group SPG_WF1
1 Umbrella Fashion 30 Seasonal 70
2 Paracetamol Medical 50 Seasonal 50
3 Babyspoon Medical 40 Other 60

Now we can write reports/dashboards showing the SALES trends, using the Weighing Factor. It will better project the trends and help business take smarter decisions.
We are still left with the problem of upper limit on number of product groups. In my next (last post of the series), I will propose a new schema, I call as 'intelligent modelling'.

No comments: