cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RonSon2015
Frequent Visitor

Relationships slow down very much the performance

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.

 

2021-09-07_11h41_15.png

 

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.

2 REPLIES 2
RonSon2015
Frequent Visitor

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
Super User
Super User

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


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!!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors