20 Mil row dim on 1B fact slow on filter

[TinyTimothy2]

I have a dim with 20 million rows.
Fact with 1 B.
1 to many

Measures on facts run fine.

All the performance hits I get are when I try to Slice/Filter on the dim Cust to Fact
A simple slice at a page filter to filter on status (alive, dead) causes 30 seconds for data to return.

I duplicated the model customer dim but only left in 5 columns, same test, still 30 seconds.

I don't think there is any dax to fix.
I did all the 'traditional' troubleshooting.

Any other ideas?
Is there a sample LARGE online db to test like dax.do?

What if:
Status was snow flaked from customer, as a 2 row dim.
Partition customer? By Status? I have only partitioned Facts by year

[otykier]

In general, when filters need to propagate across large relationships (i.e. the "one" side of the relationship contains more than 1 mio. rows), it is expected that the performance will suffer.

Snowflaking the [Status] attribute out of the 'Customer' dim will not solve the problem, as the filter ultimately still needs to propagate from the large dim table to the fact.

Instead, consider creating a smaller "junk" dimension containing attributes from the 'Customer' dim that are commonly used, and connect the junk dimension directly to the fact table. As long as your junk dimension is small (i.e. less than 1000 rows), filtering on it will be much faster than filtering on the large dimension table.

Partitioning will not help either, since VertiPaq does not use partition elimination.

[TinyTimothy2]

By junk attributes, you mean put into the FACT table 'CUSOMTER STATUS'? I have no doubt that would faster. However, it seems to defeat the purpose of the dimension. Now i have just made my :
Fact Table larger
Duplicated data (same data in customer and fact and junk dim)
Make fact table harder to manage by likely continuing to add more dims directly into the fact table
More dim tables
Existing reports bases off ssas model need to changed to account for the new dims.

By far this is the worst part of my models

I hope i am misunderstanding your suggesting

[otykier]

Well, perhaps you have other attributes that are commonly used, like [Order Status], [Customer Segment], etc. The idea with a junk dimension is that you collect all these frequently used but low cardinality attributes in a single dimension, which you can then link to your fact table. That way, you will not have the expensive filter propagation for the majority of queries.