Drillthrough aggregates

Until now I thought drillthroughs will return the single rows - but this is not the case. It groups by the Dimensionkeys. Well ok, but I had a design issue with that case:


We built up a cube with approximatly 1,000,000,000 records in the fact table. The business also wanted to have access to an fact attribute which is nearly the primary key of the facttable like a TransactionID. So there is no chance to load a dimension with this attribute. Our solution was to design this attribute as Non-Aggregatable measure in the cube. Worked wonderful. Due to the fact that Non-Aggregatable measures are only available at leaf level, this information was only available at Drillthrough.


BUT, some records were only distinguishable by this attribute - what did SSAS: it aggregates this attribute - very bad.
So first idea, Maybe a ROLAP Dimension helps, but at Drillthrough time, SSAS executes an SQL Query on the ROLAP dimension - so not working.


Our solution: create an surrogate key for the records which have the same Dimensionkey with:


ROW_NUMBER() OVER


(PARTITION BY <<DimensionKeys>> ORDER BY TransactionID)


Based on this attribute I was able to create a dimension with only a few records. Dimension is also hided and not used in my custom drillthroughs and, of course my Drillthrough resultset returns only non-aggregated rows.