BusinessObjects Board

Snowflake schema is messing my measures SUM

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.

Is there any way i can deal with this problem ?


tiberghv (BOB member since 2009-02-04)

Hi,

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:


Marek Chladny :slovakia: (BOB member since 2003-11-27)

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.


tiberghv (BOB member since 2009-02-04)

Yes, that’s correct.

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.


Marek Chladny :slovakia: (BOB member since 2003-11-27)

ok, if data is double counted, it’s logic.

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.


tiberghv (BOB member since 2009-02-04)