In Crystal Analysis 10, if you invoke drill through on a partitioned
cube, only rows from the fact table belonging to the first cube
partition are displayed. This is a well known problem in Crystal
Analysis.

I know that Crystal Analysis internally uses PivotTable service to
query the cube. Can someone tell me whether the above defect arises
from a PTS defect or is it specific to Crystal Analysis.
I would like to know this because we are thinking of using PTS in our
application to display cube and drill-through data.

Thanks

Haven't seen this drill-through issue with Proclarity or Excel add-ins.
Maybe others have looked at more tools?
The client app needs to handle a rowset per partition:

http://msdn.microsoft.com/library/de.../en-us/olapdma
d/agdrillthrough_8583.asp
If you want to enable drillthrough for a multiple-partition cube, before
you begin the following procedure, make sure that the columns that you
want to display in the result set exist in the tables for all of the
cube's partitions. (The Drillthrough Options dialog box, where
drillthrough is enabled, displays the column names and table names for
only the cube's default partition.) Qualifying fact table names do not
need to be the same in all partitions. If necessary, Microsoft® SQL
Server™ 2000 Analysis Services automatically changes the query to
reference the appropriate fact table name for each partition.

Note If a cube contains multiple partitions, drillthrough within the
cube returns multiple result sets, one per partition. A client
application might attempt to merge these result sets before presentation
to the end user, thus yielding unexpected results.
...

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Drillthrough IS enabled in all partitions of the cube and the columns
DO exist in their respective fact tables. Drill through invoked from
Analysis Manager shows records from all partitions.
I had imagined just one resultset would be returned if I execute an
MDX query through an application that uses PTS. For example, in a VB
application if I do:
Dim MyCellSet As New ADOMD.Cellset
MyCellSet.ActiveConnection = "Provider=msolap; Data Source=LocalHost;
Initial Catalog=FoodMart 2000;"
MyCellSet.Source = "DRILLTHROUGH select {[Measures].[Unit Sales]} on
columns," & _
"order(except([Promotion Media].[Media Type].members," & _
"{[Promotion Media].[Media Type].[No Media]}),[Measures].[Unit
Sales],DESC) on rows " & _
"From Sales"
MyCellSet.Open

I would get a single set of records irrespective of whether the cube
is partitioned or not. If this is not the case, then probably multiple
recordsets are returned and Crystal Analysis shows records only from
the first set.
Can you please point me to a few free/evaluation Excel plugins that
use PTS to show drillthrough data, so that I can verify this myself.

Thanks





Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

Hello Yash,

The issue is that multiple partitions,
drillthough returns a collection of recordsets; one for each partition.
You need to loop through the collection.
Use rec.NextRecordset.

HTH
Jörg




"Yash" wrote:

As Joerg says, there are multiple recordsets returned, and Crystal
Analysis likely is only handling the first set.

As far as free Excel add-ins with drill-through support, I use an add-in
that someone on this group emailed me earlier. But at this point you may
wish to try Microsoft's new Office Excel Add-in for SQL Server Analysis
Services:

http://www.microsoft.com/office/solu...eladdin/defaul
t.mspx


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Yash,

This is a Crystal Analysis issue and not PTS. Client tools have to be ready
to handle multiple partitions (with different columns) when issuing the
drillthrough statement.

Cheers,
Richard

--
_______________
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.


"Yash" <yashgt (AT) yahoo (DOT) com> wrote