Drill Through and Fact Dimensions

Fact Dimensions did not work in Beta 2. At the time I assumed that they were just “degenerate” dimensions, so that you could create dimensions directly from low-cardinality values stored directly in the fact table—such as whether a row was part of a promotion or something like that. I also assumed (without ever really looking at it closely) that Drill Through was basically the same as in AS 2000—a SQL Query retrieval of the rows that contribute to a specific cell.

In the Dec CTP, they fixed fact dimensions—and it turns out that they are completely different from what I had thought. A fact dimension is essentially using the fact table itself as a dimension—with multiple possible attributes—and it is the key to getting Drill Through to work.

Suppose that you had a PO number as a field in the fact table, but didn’t want to include that in any dimension. In 2000, you could retrieve the PO directly from the fact table via Drill Through and a SQL Query. In 2005, all Drill-Through requests go to the UDM (which actually seems internally consistent with the concept of the UDM). But that means that any column from the fact table that you want returned in the Drill Through result has to be part of a dimension. Hence the Fact dimension. First add a primary key to the fact table. (You can, in principle, use a combination of columns to get a unique key, but I think it’s good practice—and I’ve had better success—just adding an AutoNumber primary key column.) You then create a dimension using the fact table primary key as the key, and add any other columns from the fact table (such as the PO number) as attributes. Then make the dimension ROLAP storage. Once you add the dimension to the cube (and probably hide it from the users), you can include any of its attributes in the Drill Through Action definition. It actually worked quite well for us. Good luck.

--Reed