DRILLTHROUGH MDX statement or SQL statement

Hi

Ours is homogeneous OLAP with source data coming from SQL Server.The staging server pulls data from Production server and serves the purpose of source for the Analysis server.

We are using web based client to display data returned after executing queries these queries can be of both types SQL as well as MDX.We can use DRILLTHROUGH MDX statement to get row level information but the same can also achieved by writing SQL statements that can be executed against the Staging database to get the desired records [rows].

I think 2nd option would be better in the case where we need the row level details in reports. The main reason for my assumption is, since the row level data doesn’t contain any aggregation; MDX or analysis server is useful if we require aggregation. So I believe the 2nd [executing SQL query] option would be better/faster in the case where we need the row level details in reports.

Please tell me what you think about the same. Which option is better; MDX with DRILLTHROUGH or SQL queries for row level detail reports?

I hope; I am clear with what I mean to say :)

Thanks in advance for any feedback.

Regards;

Rakesh

Hi Rakesh,

Assuming that you're using AS 2005, this Aug. 2005 OLAP Newsgroup thread also discusses drillthrough vs. straight SQL, amongst other issues:

http://groups.google.co.uk/group/microsoft.public.sqlserver.olap/browse_frm/thread/b67c7fc032e313a3?hl=en&

>>

From:		Mosha Pasumansky [MS] - view profile
Date:		Wed, Aug 24 2005 7:46 pm
Email:		"Mosha Pasumansky [MS]" <mos...@.online.microsoft.com>
Groups:		microsoft.public.sqlserver.olap

> What I'm still seeking for is how to manage
> query performance and overhead with this increased flexibility, so that
> the solution works well.

As long as you don't query these fact (aka degenerate dimensions), there
will be no impact on performance. You can even make them hidden, so users
don't drag-and-drop them accidently, but you can still refer to them inside
your report or drillthrough actions.

> It seems that now, the only way for doing so is to write an application
> who
> SQL queries the relational database having the cell coordinates. Do you
> agree?

This may turn out to be pretty complex application, because it will have to
support all the flexibility and power of DSVs in addition to having to deal
with multiple partitions etc. So creating fact dimensions, is indeed the
correct approach.

--
==============================­====================
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL­og/mosha
Development Lead in the Analysis Server team
>>