Is the old drillthrough functionality gone with SSAS 2005? I mean the ability
to return any columns from Fact Table or Dimension Tables related to a
particular cube cell.

The only way I have seen so far to use drillthrough in SSAS 2005 is by
defining a Drillthrough Action which is limited to columns defined by
measures and dimension attributes. Even that does not seem to work very well
if you have a measure aggregated with "Distinct Count" function.

On the other hand the MDX reference still includes the DRILLTHROUGH query
going back to the data source. How can you define the drillthrough contents
though?

Cheers,
Stan

This blog entry by Reed Jacobsen of Hitachi may help:

http://sqljunkies.com/WebLog/hitachi...5/02/23/8068.a
spx

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

posted on Wednesday, February 23, 2005 1:54 PM by hitachi

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***