can´t make referenced relationship to work

Question

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

Bulgaria

Answer 1

The correctBrowse sales cube can be easily explained. It means that the data is not calculated based on the Geography dimension and thus indicates that the connection between the Internet sales and the Geography is not correctly calculated.

I would suggest the following:

Try making the customer a Mesaure (or fact table). Do not rename it just make a measure i.e. the count of customers (can be used as a counter of customers/per region or can be invisible altogether).

Then The customer will appear as a measure in Dimension usage and then connect Geography to Internet sales via a many to many relation Using the customer measure table.

Flywheel

Answer 2

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:

Wheal

Bill you are a genius! thanks this saved me some head scratching. – Rahel