Drill Through and dynamic row and columns subsets

[Olivier]

Hi All,

I have been fighting with drill through processes for a while and I don't manage to get my head around the efficient way of setting dynamic rows and columns on my target view.
To be more precise, I don't manage to understand how to recycle the dynamic subsets that I build for rows and columns.

I have set up several drill process within the same cube for different axis. i.e. by Account, by Location, by Week...
The idea was to give the ability to users to drill quickly from standard DBRW reports that we have set up and investigate lower levels details from a right click / drill.

All the title dimensions that I have set when creating my target view get the relevant selection through the drill process as it is done automatically when setting up the drill through.

The part I am struggling with is the configuration of the dimensions/subsets that I want to set as row and columns in the target view.

In Rows, I want to set the axis which is drilled through.
if I drill on Accounts and the selected Account is "Total Revenue",
I want the account dimension to be in rows starting from "Total Revenue" and expended to the lowest level.

In Columns, I want to set up a list of weeks based on the week selected when drilling. ( Last 5 weeks).

I thought it would be easily achieved using dynamic subsets and then ViewSubsetAssign, ViewRowDimensionSet and ViewColumnDimensionSet.

For the Row and Column dimensions, I have simply set up a MDX based subset.

When I run using this configuration, on the first execution, everything is working exactly as expected !

When running a second time, with the original code, I have an error (expected) as subsets assigned to columns and rows already exists and therefore they can not be simply "created".

This makes perfect sense to me so I understood that we need to manage the recycling/update of these subsets.

That is where I start to loose my marble when trying to manipulate this in the drill process.

In the drill process when trying to delete these subsets in my Prolog, I hit an error as the subset is used in a view and cannot be deleted.

So far so good, I can understand the issue as well. ( the error state actually the very problem so nothing to argue about...)

I then tried to destroy and recreate the view within the prolog but with no success.
I can see the difference between Zero Out and Drill in that we effectively use the view after it is generated by the process therefore cleaning up in the Epilog is not an option.

I tried to delete the view and then recreate it within the prolog, the subsets used previously in rows or columns can not be deleted and the message is always "Subset "Bla" is used in a view and cannot be deleted".

I also tried to assign a temporary subset so I could delete the previous one but without any chance.
This error even persists when I assign manually different subsets to rows and columns to the view and run the drill process again.

I would really appreciate if somebody could point me in the right direction on how to handle these columns and rows dynamic subsets in a drill process.

Kind Regards,

[MSidat]

Hi Olivier,

I think in this instance it might be easier for you to post the code from your current drill through process so people can assist.

Personally I have always stuck with the defined View for a drill through process and never had to delete/recreate the view. I have always just manipulated the subsets within the view by refreshing the elements within the existing subsets for that view using something like the following:

Code: Select all

   #Create Drill Subset
   strDrillDim = 'Product Code - Product Schedule';
   strDrillSubset = '_DrillSubset';
   IF (SubsetExists(strDrillDim, strDrillSubset) = 1);
       SubsetDeleteAllElements(strDrillDim, strDrillSubset);
   ELSE;
       SubsetCreate(strDrillDim, strDrillSubset);
   ENDIF;
   SubsetElementInsert(strDrillDim, strDrillSubset,Product_Dim, 1);

[Olivier]

Thanks for the example msidat,

I want mainly to understand the approach to take and if I am missing a logical obvious reason for doing it one way rather then the other.
I see why your suggestion will work for the reason that you do not destroy the subset for rows/columns.

Now I guess my next question is : is it the only way to manipulate row and column subsets in a drill through view

I understand that you empty the subset from it's elements and then repopulate it instead of destroying / recreating the subset itself.

I always assumed that the loop over the subsets/dimension elements would be slower then destroying / recreating the subset via mdx for larger dimensions
I can not use an MDX query to populate the subset which mean another loop to populate it...

I think the issue with deleting recreating the subsets has something to do with the way the prolog tab execute / state of the cached view / subsets and the sequence in which things are done...

[Olivier]

As expected deleting subset elements and looping around to populate on the base of a side MDX subset is much slower then destroying and recreating the subset itself...

I thought I would post the code I end up with as it might help some or get some positive criticism from others

Code: Select all

#Drill Target view name
#===========================================================================
View01 = 'Drill GL By Product - Test' ;

#Subset Names (static and temporary)
#===========================================================================
Sub01= 'zti_Drill_ByGL_Product';
Sub01Temp = 'zti_Drill_ByGL_Product_temp';

#Check if subset exists if yes, empty it else create it
#===========================================================================
IF ( SubsetExists ( 'Products', Sub01) = 1 ) ;
   SubsetDeleteAllElements( 'Products', Sub01 ) ;
ELSE;
   SubsetCreate ( 'Products', Sub01 ) ;
ENDIF;

#Check if subset exists if yes, empty it else create it
#===========================================================================
IF ( SubsetExists ( 'Week', Sub01) = 1 ) ;
   SubsetDeleteAllElements( 'Week', Sub01 ) ;
ELSE;
   SubsetCreate ( 'Week', Sub01 ) ;
ENDIF;


#GLAcc manipulation
#===========================================================================
#Remove temporary subset from dimension if exists
#===========================================================================
IF ( SubsetExists (  'Products', Sub01Temp ) = 1 ) ;
   SubsetDestroy ( 'Products', Sub01Temp ) ;
ENDIF;

#Create temporary/dynamic subset with MDX
#===========================================================================
SubsetCreatebyMDX ( Sub01Temp,
   '{TM1DrillDownMember( {TM1FilterByPattern( {TM1SubsetAll( [Products] )}, "'| Products |'")}, All, Recursive )}') ;

#Copy elements from temporary/dynamic subset to permanent/static subset
#===========================================================================
Count = 1;
nSubsetSize = SubsetGetSize ( 'Products', Sub01Temp ) ;

WHILE ( Count <= nSubsetSize ) ;

   Product = SubsetGetElementName ( 'Products', Sub01Temp, Count ) ;

   SubsetElementInsert ( 'Products', Sub01,Product, Count ) ;

   Count = Count + 1 ;
END ;

#Destroy temporary subset
#===========================================================================
SubsetDestroy (  'Products', Sub01Temp ) ;

#Assign Alias to subset
#===========================================================================
SubsetAliasSet( 'Products', Sub01, 'Full_Name' ) ;

#Assign static subset to view, and set as row
#===========================================================================
ViewSubsetAssign ( 'GL', View01, 'Products', Sub01 ) ;
ViewRowDimensionSet ( 'GL', View01, 'Products', 1 )  ;



#Week manipulation
#===========================================================================

SubsetElementInsert ( 'Week', Sub01 , Week , 1 ) ;

If ( Attrs( 'BW_Week', Week, 'IsAdjWeek' )  @= 'Yes' ) ;
   SubsetElementInsert ( 'Week', Sub01 , ElPar ( 'Week', Week, 1) , 2 ) ;
Else ;
   SubsetElementInsert ( 'week', Sub01 , ATTRS( 'Week', Week, 'PriorWeek') , 2 ) ;
   SubsetElementInsert ( 'week', Sub01 , ATTRS( 'Week', Week, 'PriorWeek1') , 3 ) ;
   SubsetElementInsert ( 'week', Sub01 , ATTRS( 'Week', Week, 'PriorWeek2') , 4 ) ;
   SubsetElementInsert ( 'week', Sub01 , ATTRS( 'Week', Week, 'PriorWeek3') , 5 ) ;
EndIF ;

#Assign static subset to view
#===========================================================================
ViewSubsetAssign ( 'GL', View01, 'Week', Sub01 ) ;
ViewColumnDimensionSet ( 'GL', View01, 'week' , 1 )  ;

#Apply Zero Suppress on View
#===========================================================================
ViewSuppressZeroesSet ( 'GL', View01, 1 ) ;