Relationships slow down very much the performance

[RonSon2015]

Hello,

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.

Problem:

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?

Thanks a lot in advance.

[RonSon2015]

Speed increased very much after using only one fact table. In the same term the size decreased dramatically as the key with high cardinality that links both fact tables diminished as well.

The joining is done directly in oracle DB source.

[watkinnc]

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.

--Nate