SSAS: Count distinct of field in fact table verses count of rows of dimension table

Question

Which version of the same measure would result in faster querying from resulting cube (or would they have the same query speed)?

Define the measure as a count distinct of <FactX> in the fact table.

OR

Create a dimension table with a primary key of <FactX>, and define the measure as a count of rows of this table.

Note: <FactX> has many levels: Count(Distinct FactX)/Count(*) ~ .8

Answer 1

I would go with the fact table and use the CountDistinct aggregation type. That way you can take advantage of aggregations instead of forcing the calculation at query time with the calculated member. Check out this whitepaper, too: http://sqlcat.com/sqlcat/b/whitepapers/archive/2008/04/17/analysis-services-distinct-count-optimization.aspx 

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz