I have a dimension (d_orga) with the following structure : http://dongorath.free.fr/d_orga.png.
As you can see, there is a hierarchy for each parallel branch.
My problem is determining the key path of a member at the l_site level, knowing that each member has a parent in every branch. An exemple member is : [d_orga].[l_site].&[grp]&[p3]&[e3]&[c3]&[eu]&[DE]&[ber]. This tells me it wants all levels in the order l_grp - l_pol - l_ent - l_com - l_reg - l_cou - l_site for my specific case, but those specific hierarchies can be different depending on the client (this example is our "demo" environment whereas a client could have different levels, or only 2 hierarchies, etc.). How can I determine the order of the wanted levels without having to hardcode it each time ? Does it depends on the creation order of the hierarchies ? An alphabetical order I failed to see ? Another arcane inner working of SSAS ?
It has, in fact, nothing to do with the structure of the dimension. The key path of a member is "simply" the key columns (property KeyColumns) defined on the attribute. They are ordered when defined and this is the order that must be used.
In the example of the question, I defined the key columns of the l_site attribute to be, in order, grp_code - pol_code - ent_code - com_code - reg_code - cou_code - site_code, thus, it is the order to be used.
Concerning the problem of specific hierarchies in client applications, the definition of the key columns being computed by the application, it can be safely re-computed by this very application.
