Hi everyone, Here is my problem :
Let’s consider one Fact table, called ‘Sales’
One Dimension table ‘Shop’
Then, i add one snowflake table called ‘Type of shop’ that is linked with the ‘Shop’ table on a 1-N relation (one shop can be linked to 1 or many types of shops)?
Now, when i create a query in web intelligence, using my measure object Sum(sells), as soon as i add any object from the ‘Type of shop’ table, my measure is multiplied for every shop that has links to the ‘Type of shop table’.
It is not a problem if i am going to display the Type of shop object in my document table, but if i only display Shop and Sales, i get too large values, not as it should be.
What you have is not a star schema! The star schema would be if the joins had these cardinalities:
Types of shops ---1:N--- shops ---1:N--- Sales
But you have:
Types of shops ---N:1--- shops ---1:N--- Sales
And it’s very different.
In such scenario, what you are experiencing is called a chasm trap and it needs to be properly solved. Usaully by creating different contexts. Check here:
Thank you very much.
The solution of separating objects in different contexts wouldn’t work here, because i couldn’t mix ‘Type of shops’ ‘Shops’ ans ‘Sales’ in the same table anymore.
But think about your data. If one shop can belong to more types of shops then you will always double-count the sales data. There is no way how to somehow distribute/split sales data of a given shop for all type of shops where the shop belongs to.
But i have another problem if i create a new separate context wich contains only ‘Shops’ and ‘Type of Shops’ :
Imagine i have a third dimension table ‘Client’ linked to the ‘Sales’ tables. I cannot mix objects from Client, Sales, Shops and Type of shops in one same table on my document. Indeed, BO will create two queries, one for Clients/Sales/Shops and one for Shops/Type of Shops, and it is impossible to mix objects from different queries that are not shared in both the two queries.
I can add in my table objects Sales, Shops, Type of shops, but cannot add Clients.