Many to Many relationship in Dimension Modelling - Part 4 and Final (Intelligent Modelling)
Okay... So last time we discussed a model with WEIGHT or WEIGHING FACTOR, which helps in distributing the facts (Sales) across multiple Product Groups. It still had limitation on the maximum number of Groups, a Product can be assigned to.
So, now consider this scenario. What if we bring the grain down to PRODUCT-PRODUCT GROUP combinations? So, in our example, we treat Umbrella-Fashion as one entity, and Umbrella-Seasonal as another entity..??
This will raise a question - When actual Sales happens, what we know is only that a product called 'Umbrella' is sold!! Here, comes in the Intelligent Modelling. So, we can weave some kind of 'intelligence' at the Source or in ETL to classify the Product, or split the facts across multiple entities.
Imagine the whole new world this idea opens! If business agrees, the PRODUCT-PRODUCT GROUP combinations can be exposed to the source system. So the fancy, colourful umbrellas can be connected to Umbrella-Fashion, whereas the basic umbrella products can be connected to Umbrella-Seasonal. This will help more realistic information to be captured from the source. So, when executive examine the SALES Trend by Product Categories, they can exactly see whether people are buying my fashion products, or choosing Basics instead (quite possible situation in current time of financial crisis)..
Also, business might decide to NOT expose the categories to SOURCE system, but apply some Artifical Intelligence in the ETL logic. Then we will have just one entry from Source saying an Umbrella sold, but ETL can divide the measure or fact across Umbrella-Fashion and Umbrella-Seasonal using the 'Weight Factor' logic already discussed.
So, now consider this scenario. What if we bring the grain down to PRODUCT-PRODUCT GROUP combinations? So, in our example, we treat Umbrella-Fashion as one entity, and Umbrella-Seasonal as another entity..??
This will raise a question - When actual Sales happens, what we know is only that a product called 'Umbrella' is sold!! Here, comes in the Intelligent Modelling. So, we can weave some kind of 'intelligence' at the Source or in ETL to classify the Product, or split the facts across multiple entities.
Imagine the whole new world this idea opens! If business agrees, the PRODUCT-PRODUCT GROUP combinations can be exposed to the source system. So the fancy, colourful umbrellas can be connected to Umbrella-Fashion, whereas the basic umbrella products can be connected to Umbrella-Seasonal. This will help more realistic information to be captured from the source. So, when executive examine the SALES Trend by Product Categories, they can exactly see whether people are buying my fashion products, or choosing Basics instead (quite possible situation in current time of financial crisis)..
Also, business might decide to NOT expose the categories to SOURCE system, but apply some Artifical Intelligence in the ETL logic. Then we will have just one entry from Source saying an Umbrella sold, but ETL can divide the measure or fact across Umbrella-Fashion and Umbrella-Seasonal using the 'Weight Factor' logic already discussed.
The schema will look simple... We won't need PRODUCT GROUP to be seperate table now. We will have a dimension to hold all PRODUCT-PRODUCT GROUP combinations and then the Facts will be stored at that grain.
PRODUCT
Prod_ID Prod_Name Prod_Group
1 Umbrella Fashion
2 Umbrella Seasonal
3 Paracetamol Medical
4 Paracetamol Seasonal
Sales
Invoice Prod_ID QTY
1 1 5.4
2 1 4.6
3 1 3.7
4 2 4.3
So this schema no more has the upper limit on the number of Product Groups a Product can bind to. We need to be careful that the total of weights for each product should be one, so that total sales figure is not shrunk, or blown.
Please note that when business asked us to link a product with multiple product group, we thought ahead and proposed this solution to effectively trace the trend across Product Groups. We are proposing this because we are delivering 'business intelligence' and not just 'data warehouse'. There might be already some systems out there implementing this idea. They might be calling it something else. I called it 'Intelligent Modelling'. Please let me know what you think.
Your ideas, feedback, experiences - are most welcome!!
End of 'Many to Many relationship in Dimension Modelling'
Comments
I want explain better my case: In a university a student can attend 2 academic program simultaneously and an academic program can have many students. If a student who have 2 programs take a course in common of this two programs(ie. math level 1 for system enginnering and electrical enginnering) in the analysis we can't decide to witch program belongs the grade of the course. I only have a indicator to show me the number of programs of the student but i can't use this indicator like a weight factor because the grade of the course is unique and can't be divided with respect to the program.
I hope to have explained well my case.
Additionally i use cognos transformer 8.3 but i don't know how implement a schema different to star schema.
Thanks for your help.