Drill-To-Detail in ProClarity

Hi

When we do a Drill-To-Detail in ProClarity, I get the "Key" columns of the dimensions. This would not make sense to the user.

All my dimensions except a few like Date dimension, have 'KEY' as one of the attributes. This 'Key' attribute is set with Usage = Key. I presume that this is a sort of primary key for the dimension. The relational tables of all dimensions contain the Key column, which is referenced in the Fact table.

Although I dont display the Key/Id attribute in any cubes, I thought that they are necessary for faster access to the data and for aggregations.

Either I have to remove the 'Drill-To-Detail' feature from ProClarity or make it sensible to users.

What I tried is: I deleted the Key attribute and made Name attribute as Usage=Key in one of the dimensions. Then I made all attributes directly or indirectly related to the new key attribute (NAME). Next I re-deployed the ProClarity graph without the 'Key' column attribute. Now 'NAME' is the key attribute. ........ but when i did a Drill-To-Detail, I am only seeing 'Unknown' for the paticulary dimension.

so finally, what do i need to do to solve this problem?

Regards

Hi Vijay,

From your description, it sounds like you're using AS 2005. In that case, you should be able to control the columns returned by a Drill-Through query (which is what I think the Proclarity Drill-To-Detail executes) by configuring a default Drillthrough Action. I think that tweaking the key columns of dimensions for this purpose may cause other issues.

This MSDN paper describes how to configure DrillThrough actions - you can also refer to the Adventure Works "xxDetails" actions for examples:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp

>>

Enabling Drillthrough in Analysis Services 2005

T.K. Anand
Microsoft Corporation

July 2005

Applies to:
SQL Server 2005 Analysis Services

Summary: Discover the new Analysis Services 2005 drillthrough architecture. See how to set up drillthrough in Analysis Services 2005 and get guidance on migrating drillthrough settings from Analysis Services 2000 databases.

...

Clearly drillthrough fits in very cleanly into the actions framework. But the real advantage of drillthrough actions is that it provides the cube designer with the ability to pre-define the return columns of the DRILLTHROUGH statement (Figure 2). This is analogous to the Analysis Services 2000 experience where the database administrator specifies the tables and columns in the Drillthrough Options dialog in Analysis Manager.

There is an interesting Boolean property called Default on a drillthrough action. A cube can have multiple drillthrough actions with Default=true. The Default property does not affect the behavior of the action itself. 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. If such an action is found, the server uses the return columns from that action. If there are multiple drillthrough actions that meet these criteria, the server picks one arbitrarily. Thus default drillthrough actions enable the cube designer to override the default RETURN clause.

...

>>

|||

Thanks Deepak.

Will try this and let you know.

Regards

DrillThrough Actions not working in Proclarity web standard (Vs Proclarity professional)

Hi,

I created some Drillthrough actions in Proclarity web professional. When I right click in professional and select the aciton they open a new window and display the correct corresponding values.

after publishing the graph, I tried the same in the ProClarity Web Standard. Here the actions are not displayed in the right-click menu, but are displayed in the left navigation tab.

Clicking on the same drillthrough action, for the same cell and dimension values, opens a new window, which only keeps showing "Please wait, Loading Details....." I waited for 30 minutes. and still it did not show any results.

Since there is only one row that it has to display (as seen in proclarity professional for the same data), I think that there is something else that has to be configured for the proclarity web standard.

Please help.

thanks and regards

Anybody worked on ProClarity and ssas 2005?|||

On the web, there is no right click, just click on the cell and a menu will pop up for Drill to detail. You cannot drill on calculated measure cells.

Also check the permissions of the user in IIS that is running the PAS site. Try using an administrator account as anonymous and see if that works.

|||

Hi,

thanks for the reply,

Yeah , there is no right click on the web standard. (only in professional).

but i am able to see the drillthrough actions!!!! in web standard also.

it appears in the right side navigation tab , under the "Actions" header.

but clicking on those actions , opens a window, but does not get results.

I will try out what you specified.

Regards

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.