TM1 Forum

How do I pass multiple hierarchies from TM1 to Cognos Reporter (aka BI)?

I have a dimension called "Report Period" that has three hierarchies. The first is the complete hierarchy - Total Year made up of halves, made up of quarters, made up of months. The second two are dummy hierarchies. I want to pass the current and previous month to Reporter and I can't use subsets so I've created a "Current Month" consolidation which has one child and "Last Month" which also has one child. I would like these two consolidations to show up as hierarchies.

When the BI guy fetches the dimension all he gets is the "Report Period" hierarchy, which isn't really a hierarchy, it's just the dimension. You can see my different hierarchies under the "Members" folder in the attached image - how do I get these coming through as hierarchies?

I've had a look for something obvious in the }HierarchyProperties cube but nothing jumps out at me. The }Hierarchies dimension only had only one element but adding a second didn't seem to do anything.

Any ideas appreciated.

Have you maintained the hierarchy properties levels data in the }HierarchyProperties cube for the dimension in question?

Generally alternate hierarchies are problematic. One approach that has worked for me is to create an artificial top node that has as its children all the real hierarchies. To avoid retrieving junk data make the weighting 0 for all bar one hierarchy that rolls into the top node.

I have, screenshot attached.

I think I'm more or less doing as you suggested by using the elements found in the initial Members folder. It works ok, the BI guy is just saying that this is not the normal way for him to report stuff.

Have you considered naming your hierarchies as such:

For dim Report Period, Hierarchy0:

level000 = Total Year
level001 = Current Month
level002 = Actual

Again not sure if it will solve your problem, but that is how I currently use }HierarchyProperties cube. I have also leveraged the technique suggested by lotsaram.

As far as I know, named levels for Multiple hierarchies in }HierarchyProperties cube is not an available feature yet. That is why we have only Hierachy0 element in the hierarchy dimension of this cube. We also implemented the idea that lotsaram mentions, but sometimes it might be preferable to have weight of 1 for one of the alternate hierarchies for the artificial top node so that user will see the data without making any drills into this dimension in a default view/analysis/report.

With this way, BI guys can still do the reporting as if they have the alternate hierarchies by using Captions as filter. They would need to put caption filter on the highest level element of any alternate hierarchy (the one that is the child of the artificial top node).

It is also good practice to name levels in }HierarchyProperties cube as this enhances the user experience in Cognos 8 side considerably.

Hi Martin, we are havinga similar dilema this end with mutiple hierarchies... what did you run with on this? Did you come up with a workable solution (other than the top node dummy with caption applied method described)?

Afraid not. Ended up hard coding some references e.g. [Current Month].children and so on. Not ideal. Maybe something they'll work on for Cognos 10.

Martin

lotsaram wrote:To avoid retrieving junk data make the weighting 0 for all bar one hierarchy that rolls into the top node.

Lotsram, if we have 2 or more hierarchies do we leave the remaining 2 hierarchies weight un touched.

Afraid not. Ended up hard coding some references e.g. [Current Month].children and so on. Not ideal. Maybe something they'll work on for Cognos 10.

thanks Martin,

Our solution with these limitations is to build advanced reports (report studio) with the developer understanding the multiple hierachies limitation. Report Users should not care for what the data source looks like. Any adhoc reporting will be Advisor/EV (we have Express), or we will develop purpose built reporting cubes with one hieracrhy per dim -this method also allows us to design better reporting cubes (rather than budget input cube reporting)

G