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