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

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.