I have three tables, one fact and two dimensions. I want to make a referenced relationship between the fact table(measures) called InternetSales and the Geography table, se image(schemaCubeStructure".
The intermediate table is Customer. I first create the Geography and InternetSales tables and then the Customer with a field from Geography to use later when creating the ref. rel.
Everything works fine until browsing the data in the cube (se image "errorBrowseSalesCube". If i don´t make the relationship between the two tables i get image "correctBrowseSalesCube" which is what i want. That is i don´t get any data when processing with the rel.ref.
See image "dimensionUsageSalesCube for rel.ref.
Why is that i don´t get any data?
correctBrowseSalesCube
errorBrowseSalesCube
dimensionUsageSalesCube
schemaCubeStructure
Best Answer
What fields did you use when setting up your reference dimension relationship between Georgaphy and Internet Sales?
The in the AdventureWorksDW database, the Georgaphy dimension is a snowflake off of Customer dimension. In the AdvWrks cube project, Microsoft includes the geography table and corresponding attributes in the customer ssas dimension (red boxes in screenshot below). However, they could have, as it looks like you are trying to do, simply added the GeographyKey to the customer dimension (red arrow in screenshot below)
This exposes the GeorgaphyKey field when creating the reference dimension relationship so that you can properly define the relationship between the intermediate dimension (customer) and the reference dimension (geography):
After that, you can properly browse the Internet Sales facts by Georgraphy dimension attribute (and user) hierarchies: