Hi,

I have a large ROLAP Fact Dimension with about 100 Million Rows. I have a
fact dimension defined on it. I have defined a simple drill through on the
cube. When I try to drill through in SS Mgmt. Studio, it just hangs. Looking
at the generated SQL query, it seems insanely large. Are there any ways to
optimize drill through actions?

Thanks,

Mukesh

Hi Mukesh,

If you try to run the generated SQL query directly against the fact
table, does it still hang up or time out? Also, are there any indexes on
the fact table, and do these correspond to the dimensions specified in
the drillthrough?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks Deepak,

It takes about two minutes in SSMS query window. If I simplify the query, it
takes a lot less time.

"Also, are there any indexes on the fact table, and do these correspond to
the dimensions specified in the drillthrough?"

That is one thing I am most appaled at. Even thought my MDX was pretty
simple and had one FactDimension column returned and a time filter applied,
the SQL emitted looks very complex. It includes all dimensions, all levels of
the dimensions and such.

"Deepak Puri" wrote:

Mukesh, is MOLAP storage of your fact dimension an option - this should
be much faster to query, but may incur long processing times?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Agree. But considering my fact table will have 300 million rows, I am worried
about processing time.. May be I should Try it out.

"Deepak Puri" wrote: