Is it possible to create a hierarchy (drill-down structure) based on other flat hierarchies in icCube?

Question

I would like to build hierarchies with as much as depth possible to offer users "navigation paths" to their data.

Often, I am blocked as the metadata can not be properly placed into multilevel hierarchies due to source data constraints. Or if I manage to solve it, I can only make one navigation path and not an alternative that mixes the levels.

Consider for example the following cost center structure: Business Unit > Manager > Department > Cost Center

To enforce this structure I have to do a lot of preparation in the ETL to ensure these relations will exist all the time.

Would it be possible in icCube to define for this (and similar) examples 4 hierarchies:

- Business Unit -> flat Business Unit -- links to fact
- Manager -> flat hierarchy Manager -- links to fact
- Department -> flat hierarchy Department -- links to fact
- Cost Center -> flat hierarchy Cost Center -- links to fact

and as a next step define the following hierarchies based on these (a bit similar to the icCube's MDX+ Category function):

hierarchy/ navigation path:

Cost Center (BMDC) 
level 1: [Business Unit]
level 2: drill down to [Manager]
level 3: drill down to [Department]
level 4: drill down to [Cost Center]

but also:

Cost Center (MBDC)
level 1: [Manager]
level 2: [Business Unit] (that belong to this manager)
level 3: [Department] (that belong to this Business Unit)
level 4: [Cost Center] (that belong to this Department)

or a slowly changing structure like this:

Cost Center (Year)
level 1: [Year]
level 2: [Business Unit]
level 3: [Cost Center]

or just a useful end-user navigation path like this:

Product - Customer
level 1: [Product]
level 2: [Customer]

Then, as a logical next step, I would like to use these navigation paths in the dashboard filters to display the hierarchy for the end-user to navigate through. Very powerful in my opinion.

Is something like this possible to do in icCube?

Answer 1

Creating a new 'physical' hierarchy is not the best idea if we want this feature for the reporting. Here I'm going to explain a solution that is based in icCube reporting.

The first point is defining our navigation strategy in the server. For this you can use a declared function that will be responsible for 'branching'. As parameter we will take the MDX member on which we want to drilldown (_member).

Something like :

CREATE FUNCTION ic3N_Simple(member_) as
     CASE
        WHEN member_.hierarchy is [Customers].[Geography] THEN [Time].[Calendar].[Year]
        WHEN member_.hierarchy is [Time].[Calendar] THEN Tail([Product].[Category],4)
        ELSE member_.children
     END

Once we've this we can go to the reporting and in any widget use this strategy :

There you've defined a strategy in the reporting that is defined in the server and can be reused in any report