Drill to detail in ProClarity

Hi,

This is from a previous post I had put,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=973310&SiteID=1

ProClarity gives a "Drill-To-Detail" by default. When you click on that it shows the key columns for the fact tables. It uses Drillthrough for this.

I tried defining drillthrough actions in ssas 2005, but they come up seperately as actions in proclarity. The "Drill-To-Detail" is still displayed on right-click at proclarity.

The actions are working in ssas 2005, but according to the definition, if a drillthrough is executed, if no return columns are specified, then it picks the return columns from the action that resembles similar query target. This does not seem to work for me!

if nothing can I at least remove the "Drill-To-detail" from ProClarity? please help.

Regards

Hi Vijay,

Could you explain this in more detail, because it doesn't sound identical to the Drillthrough whitepaper verbiage:

"then it picks the return columns from the action that resembles similar query target."

http://msdn2.microsoft.com/en-us/library/ms345125.aspx

>>

...

When a client sends a DRILLTHROUGH statement that does not contain the RETURN clause, the server looks for a default drillthrough action whose target subspace contains the cell coordinate for which drillthrough is being executed.

>>

In what way does it not work for you - what results do you get? Also, have you tried accessing the "xxxDetails" Drillthrough actions in the Adventure Works cube from Proclarity - do they work as expected?

|||

Hi Deepak,

Thanks for the reply.

Apologies, this is what I meant by my statement :

"When a client sends a DRILLTHROUGH statement that does not contain the RETURN clause, the server looks for a default drillthrough action whose target subspace contains the cell coordinate for which drillthrough is being executed."

Here is the query that ProClarity sends out when you right-click on any cell and click on the "DrillThrough"

DRILLTHROUGH MAXROWS 1000 SELECT { ( [Measures].[ABC] ) } ON COLUMNS ,

{ ( [DATE].[NAME].&[1.001734E6] ) } ON ROWS

FROM [ZZZCUBE]

WHERE ( [DATE].[DAY OF MONTH].[All], [DATE].[DIM MONTH NAME].[All], [DATE].[DIM YEAR NAME].[All], [LL_USER_TYPE].[DIM_LL_USER_TYPE_KEY].[All], [LL_USER_TYPE].[DIM_LL_USER_TYPE_NAME].[All], [LL_USER_TYPE].[DIM_LL_USER_TYPE_SHORT_NAME].[All], [SLICE_LOCATION].[KEY].[All], [SLICE_LOCATION].[NAME].[All], [SLICE_LOCATION].[SHORT NAME].[All], [LOCATION].[KEY].[All], [LOCATION].[NAME].[All], [LOCATION].[SHORT NAME].[All], [APPLICATION].[DIM_APPLICATION_KEY].[All], [APPLICATION].[DIM_APPLICATION_NAME].[All], [APPLICATION].[DIM_APPLICATION_SHORT_NAME].[All], [DATACENTER].[DIM_DATACENTER_KEY].[All], [DATACENTER].[DIM_DATACENTER_NAME].[All], [DATACENTER].[DIM_DATACENTER_SHORT_NAME].[All], [MONTH].[DIM_MONTH_NAME].[All], [MONTH].[DIM_MONTH_SHORT_NAME].[All], [MONTH].[KEY].[All], [ALL_COUNTRIES].[KEY].[All], [ALL_COUNTRIES].[NAME].[All], [ALL_COUNTRIES].[SHORT NAME].[All] )

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>CON4QA</Catalog><Timeout>0</Timeout><Format>Native</Format><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><Dialect>MDX</Dialect><MdxMissingMemberMode>Error</MdxMissingMemberMode><LocaleIdentifier>1033</LocaleIdentifier><DbpropMsmdMDXCompatibility>2</DbpropMsmdMDXCompatibility></PropertyList>

Notice, it contains no return columns. but it contains a <PropertyList>.

This cube's measureGroups contains 2 measures, out of which one is selected for the graph. The cube's measureGroup uses 3 dimensions, two are used in the graph. that would be, let us say, Measure M1 (background) , Dim Date (rows) , Dim Type(columns).

So I created a drill through action for the same MeasureGroup. Then in return columns, I selected only one measure (the one used in graph- M1). Then I selected the dimensions which I have used in the graphs (Dim Date.Name, Dim Type.Name).

After saving everything, I browse the same Proclarity graph , right click on a cell and click on Drill-To-Detail. I get the following columns in the drillthrough window that gets opened.

Date.Name (because Date dimension has Name as its key attribute)
Type.Key
location.key (not in graph)

M1 (measure)
M2 (measure) (not in graph)

My problem is , why are dimensions and measures that are not used in the graph coming? (although they are used by the measuregroup as displayed in the dimension usage tab of BI studio)

In any case, I do not want the "Key" attribute of dimension to be displayed in the drill-to-detail. If I get the "Name" attribute instead of the key attribute, then it would be okay with me. that is what I am trying to do.

note. In the BI studio browser, we do not have a default drillthrough in the UI. If we define a drillthrough action on the measureGroup, then it gets rendered in the BI studio browser. Also this action is visible in Proclarity, under 'actions'. but the drill-to-detail still exsits. The actions that I have created are working fine by themselves.

Please tell me if you need any other information?

How can I solve this,

Regards

|||

Hi Deepak,

I forgot to ask this,

what does "target subspace contains the cell coordinate......" mean?

How can I create a default drillthrough, which will be used whenever the user does a drillthrough on any of the cells of the a particular cube/measuregroup/dimension combination?

regardig the 'xxxDetails' drillthrough in Adventure Works, I will check it.

Thanks!

Regards

|||

The TargetType for Drillthrough Actions is "Cells" - for example, the definition of the "Reseller Details" action in Adventure Works includes:

<TargetType>Cells</TargetType>
<Target>MeasureGroupMeasures("Reseller Sales")</Target>

So it applies only to cells in the "Reseller Sales" Measure Group - but you can select <All> as the Target for a Drillthrough which applies across all Measure Groups. You should test your Drillthrough Action directly, using an MDX query in Management Studio, to ensure that it is returning the correct columns. For example:

>>

Drillthrough

select {[Measures].[Reseller Order Quantity]} on 0,

[Date].[Calendar].[Month].&[2001]&[7] on 1

from [Adventure Works]

where [Product].[Product Categories].[Product Name].&[300]

-

[Reseller Sales].[Reseller Sales Amount] [Reseller Sales].[Reseller Order Quantity] [Reseller Sales].[Reseller Extended Amount] [Reseller Sales].[Reseller Tax Amount] [Reseller Sales].[Reseller Freight Cost] [Reseller Sales].[Discount Amount] [Reseller Sales].[Reseller Unit Price] [Reseller Sales].[Unit Price Discount Percent] [Reseller Sales].[Reseller Total Product Cost] [Reseller Sales].[Reseller Standard Product Cost] [$Reseller].[Reseller] [$Promotion].[Promotion] [$Employee].[Employee] [$Delivery Date].[Date] [$Sales Territory].[Sales Territory Region] [$Reseller Sales Order Details].[Carrier Tracking Number] [$Reseller Sales Order Details].[Customer PO Number] [$Reseller Sales Order Details].[Sales Order Number] [$Reseller Sales Order Details].[Sales Order Line] [$Product].[Product] [$Ship Date].[Date] [$Source Currency].[Source Currency Code] [$Date].[Date] [$Geography].[City] [$Geography].[State-Province] [$Geography].[Country] [$Destination Currency].[Destination Currency Code]
809.76 1 809.76 64.7808 20.244 0 809.76 0 699.0928 699.0928 Original Bicycle Supply Company No Discount Jos Edvaldo. Saraiva July 13, 2001 Canada 4E0A-4F89-AE PO18473189620 SO43661 1 HL Mountain Frame - Black, 48 July 8, 2001 CAD July 1, 2001 Toronto Ontario Canada USD

>>

The list of columns corresponds to those specified in the definition of the "Reseller Details" action.