I thought that the performance is so weak due to the measures applied. But I found out that the attributes from the dimensions table slow down the performance very much.
Here is my model. Sales Cond = fact table with customer number (of payer, receiver of products, receiver of invoice), product number, country, turnover Customer Master 1 = dimensional table that includes details of customer (payer) Customer Master 2 = dimensional table that includes details of customer (receiver of products)
Customer Master 3 = dimensional table that includes details of customer (receiver of invoice)
The 3 dimensional tables are all the same. The relationsship differs depending on which customer number is chosen for the relationship (payer, etc.). Basically I use all 3 dimensional tables to get the customer name.
The VertiPaq Analyzer shows that each customer_number_key (to link the tables) takes 4% of the total DB size in the dimensional tables. Cardinality is about 5m. However, the customer key in fact table is only 0,1% of DB and cardinality is about 9.000. The high difference is because only a few fact records are loaded while the dimensional tables includes all records.
The output is a pivot where the filter is set on coutry and month/year. The rows include customer number, customer text (just one combination) and 15 measures. If I change the country, the model cannot be calculated. I get a timeout. If I takeout the customer text, I've no issues.
What is causing the problem? Do I need to add the customer name as a relatedcolumn in the fact table? But I thought as much as possible should be with the dimensional tables!?
Another question in that sense. The tables are linked by one directional filters. Unfortunately, if I put customer names as filter in the excel pivot, it shows all customers regardless which country is filtered. How can this be solved? Only by using both-directional relationsships which may decrease performance?
You don't have to load the same dimension table three times. You can load it once, add the relationships ( all but the first will be inactive) and then use the USERELATIONSHIP function to activate it per measure. You should indeed also have the FactTable related to the customer table via customer.
I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!