Hi Community!

We have a cube containing an organisation dimension which has a hierarchy called "TechnologyArea". Meaning the organisation dimension has the default hierarchy and a hierarchy called "TechnologyArea". 

From a cube view in PAW which contains both the default hierarchy and the hierarchy "TechnologyArea", we want to drill to a SQL server view. We have a drill rule and a drill process in place.

It seems like the string which is returned for the organisation dimension changes. The order of the default hierarchy and hierarchy "TechnologyArea" switches places sometimes. 

Sometimes the string looks like this:
^ELTEK_R01^TechnologyArea:Service 
-> Default hierarchy first, then hierarchy "TechnologyArea".

In other cases it is the other way round:
^TechnologyArea:Service^ELTEK_D05
-> Hierarchy "TechnologyArea" first, then default hierarchy.

In order to make the drill work we need to parse this string in the drill process. The different orders in the string can of course be handled in the drill process but it would be nice to know what determines the order. Does someone know?

Thanks!
Mario

------------------------------
Mario Hasler
------------------------------

Hi Mario,

We provided a fix last year for the variables orders for MDX views used as a data source in TI process. That order is dictated by the order in which hierarchies appear in the MDX that defines the view. That should be the same order used in a drill through. Here is that page: https://www.ibm.com/support/pages/change-variable-order-mdx-views-used-turbointegrator-data-source

Best regards,

------------------------------
Svetlana Pestsova
IBM Planning Analytics Product Manager
------------------------------

Hi Svetlana, 

Thank you for your answer. I will take a look at the link and see if that explains the different order!

Best regards,
Mario 

------------------------------
Mario Hasler
------------------------------

Original Message:
Sent: Thu June 08, 2023 09:01 AM
From: Svetlana Pestsova
Subject: Drill to SQL Server with hierarchies

Hi Mario,

We provided a fix last year for the variables orders for MDX views used as a data source in TI process. That order is dictated by the order in which hierarchies appear in the MDX that defines the view. That should be the same order used in a drill through. Here is that page: https://www.ibm.com/support/pages/change-variable-order-mdx-views-used-turbointegrator-data-source

Best regards,

------------------------------
Svetlana Pestsova
IBM Planning Analytics Product Manager
------------------------------

Hey Mario,

Long time no see.

While I didnt have your issue of having different order here is some parsing code that may be of use. This one checks to make sure you drilled on a leaf element and I suppose adding a dimix() check could make it indifferent to the order in your string (?) 

------------------------------
David Pretorius
------------------------------

Hi David,

Thanks a lot for the code snippet! I solved the parsing in a similar way :-) I am just curious why the order changes but maybe Svetlanas link explains it.

Regards,
Mario

------------------------------
Mario Hasler
------------------------------

Original Message:
Sent: Mon June 12, 2023 02:58 AM
From: David Pretorius
Subject: Drill to SQL Server with hierarchies

Hey Mario,

Long time no see.

While I didnt have your issue of having different order here is some parsing code that may be of use. This one checks to make sure you drilled on a leaf element and I suppose adding a dimix() check could make it indifferent to the order in your string (?) 

sMember = dimensionElementPrincipalName('Counterparty', counterparty);

sDim = 'Counterparty';

 if(scan('^', sMember )>0);

        z =sMember  | '^';

        While (SCAN('^', z)>0 );

            d = subst(z,1,scan('^', z)-1);

            zz = d | ':';

 

             hier = subst(d,1, scan(':',d));

             member = subst(d, scan(':',d)+1, 1000);

 

             if(ellev(sDim, member) =0);

                  sMember  = member;

             endif;                                                                      

             z = subst(z, scan('^', z)+1, long(z) );

 

         end;                  

 

endif;

------------------------------
David Pretorius
------------------------------