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
>>

DRILLTHROUGH MDX statement n SQL AS 2005

With SQL RS 2000 and SQL AS 2000 I'm using DRILLTHROUGH MDX statement. But when I try to use it with SQL RS 2005 and SQL AS 2005 I get an error:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

Failed to parse the query to detect if it is MDX or DMX. (MDXQueryGenerator)

Do you have any solution?

If you're trying to use the Analysis Services Provider in RS 2005, you may want to try the OLE DB for OLAP 9.0 Provider instead, based on this thread from the SQL Server OLAP newsgroup:

http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_frm/thread/f5e2bc488f4f78bf/4870097ed8fcffda#4870097ed8fcffda

>>

How about trying the OLE DB option? I couldn't get the following AW
Drillthrough query to work with the RS 2005 Analysis Services Provider;
but it returned 8 records when I used OLE DB for OLAP 9.0 instead:

Drillthrough
Select [Ship Date].[Calendar].[Calendar Year].&[2001] on columns,
[Customer].[Customer Geography].[State-Province].&[TAS]& on rows
from [Adventure Works]
where [Measures].[Internet Order Quantity]

>>

Drillthrough action

Hi,

Is there any way in AS2005, I can specify a cube action which will bring up an HTML page displaying the Drillthrough MDX for the cell that I just initiated the action on?

Also, will this MDX work across all cube partitions (no need for FIRSTROWSET clause)?

Thanks,

JGP

Drillthrough actions return a DRILLTHROUGH statement to client application (e.g. cube browser) which in turn executes the statement and displays the results. The results will contain data for all partitions (no need for FIRSTROWSET clause). See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp for more info.

HTML actions are unsafe and BI Dev Studio does not let you create them. If you do want to expose the DRILLTHROUGH statement to the user, you have to plug in some code (depends on the client app) that retrieves the actions schema rowset, extracts the statement and displays it.

|||

Any idea how I would go about retrieving the action schema rowset? Any samples would be greatly helpful.

I'm trying to get this working from the ProClarity clients (Standard and Professional).

Thanks,

JGP

Drillthrough action

Hi,

Is there any way in AS2005, I can specify a cube action which will bring up an HTML page displaying the Drillthrough MDX for the cell that I just initiated the action on?

Also, will this MDX work across all cube partitions (no need for FIRSTROWSET clause)?

Thanks,

JGP

Drillthrough actions return a DRILLTHROUGH statement to client application (e.g. cube browser) which in turn executes the statement and displays the results. The results will contain data for all partitions (no need for FIRSTROWSET clause). See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp for more info.

HTML actions are unsafe and BI Dev Studio does not let you create them. If you do want to expose the DRILLTHROUGH statement to the user, you have to plug in some code (depends on the client app) that retrieves the actions schema rowset, extracts the statement and displays it.

|||

Any idea how I would go about retrieving the action schema rowset? Any samples would be greatly helpful.

I'm trying to get this working from the ProClarity clients (Standard and Professional).

Thanks,

JGP

Drill-down using "n" levels using a table?

How should i create a "n" levels drill-down report using a table based on MDX query?

I can only seem to use the visible or invisible based on a know number of levels ("columns in the table") what if i don't know that exact number? Or it is a unbalanced hierarchy?

I cannot use the Matrix because my columns are static and with two levels...

You can use show/hide recursive groups in a table based on parent child hierarchies.|||How do i use that option in SSRS 2005 ?

Drill down matrix report slow performance

I have created OLAP cubes and we are creating reports on top of them. The reports take incredibly long to render (40 seconds to 5 minutes). The MDX for the reports runs in less than 15 seconds when executed as a query in Mgt. Studio. Browsing is also fast. The reports are drill down matrix-type reports and they do eventually display properly. Not that this should help with the mechanics of the issue but the report data source query will show how we drill across dimensions:

<code>

SELECT NON EMPTY { [Measures].[Number of Queries Run] } ON COLUMNS, NON EMPTY { ([ClientProjectDim].[Project ID].[Project ID].ALLMEMBERS * [UserDim].[UserName].[UserName].ALLMEMBERS * [QueryDim].[Status].[Status].ALLMEMBERS * [DateDim].[Date Hierarchy].[ClusteredHourOfDay].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Ad Hoc] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

</code>

The RS and security logs don't have anything meaningful. As the MDX works well when executed as a query but not in the reports I can't tell if it's a rendering issue somewhere. Has anyone experienced this issue and is there a resolution aside from restructuring the report?

I am experiencing similar issues but no answers, did you ever resolve youra?|||We did not resolve this, but rather restructured our reports. You might consider this. You can set up your reports with params for drill-through.|||Thanks, however I did parametize this report. I will try to restructure and do this long and laborious way. I noticed in the AS browser and MDX query designer they are using nice drill through tree for the parameters, but no drill through in the rendered form. Is that due to a property setting I am missing.