Just posting this out there if there is any way to create hierarchies/ drill paths leveraging multiple dimensions? e.g. Columns A from DIM1 (parent) drills down to Column B from DIM2 (child). The model is a simple star wherein the two dimensions are joined through a fact.
You can create Dimension Hierarchy based on multiple dimension tabels, The important thing is this dimension tables need s to have join relationship between them.
Next Step: create the logical table in BMM Layer on DIM1 >> Next Drag the columns from DIM2 and drop in the same Logical table(DIM2) >> Now check the LTS Make sure you have only single LTS Displayed, save the changes and right click the logical table create Hierarchy.
Note: If u have multiple LTS we cannot creae the Hierarchy.
As of last evening we tried creating multiple physical layer alias's of the dim tables in question such that we could create mutiple hierarchies for varied drill downs with "preffered drill paths". While doing the same we kind of figured out the same as to why head down that route wherein we could just have 4 aliases for 2 dimension tables and make use of a single LTS name within a logical table with multiple sources. This way using a single LTS name with multiple source joins, we could solve this.
Thanks. I will mark as helpful since if I had read it earlier it would have directed us in the right direction much faster.