Leaf members of a level-less hierarchy

SSAS and TM1 cubes can have unbalanced parent-child (recursive) hierarchies which end up not having any real levels as we are used to see in normal hierarchies. Plus their lowest level of detail (leaf members) could be on any level within the hierarchy...

Problem

No level references and referencing a level is darn useful when making reports. 

One example of an issue related to having no levels appears when we try to show all the leaf members in our report. We just don't know where the leaf member might be ending up...

The official workaround in IBM website suggests to filter all members and pick just those that have no children. Something like this:

filter(

[Cube].[Dimension].[Hierarchy],

  count([Cube].[Measures].[Value] within set

                      children(currentMember([Cube].[Dimension].[Hierarchy])))

  = 0)

However, this just gives all leaf members. What if we need to find leaf members of just one part of this hierarchy, which the user chooses in their prompt page?

Solution

This is what I made for dealing with such a request for a level-less hierarchy:

filter(

                      filter([Cube].[Dimension].[Hierarchy],

                                            ancestor(currentMember([Cube].[Dimension].[Hierarchy]),

                                            level([Selected Member])) = [Selected Member] )

,

  count([Cube].[Measures].[Value] within set

                      children(currentMember([Cube].[Dimension].[Hierarchy])))

  = 0)

[Selected Member] is a member from this hierarchy selected in the prompt page.

The inner filter returns all members of the hierarchy which have the [Selected Member] as their parent. Then we can use this narrowed set to find just the leaf members in it. That we do with the help of the outer filter expression which is the solution provided by IBM.

Jep, sometimes we do miss the MDX capabilities of SSAS where this code would be so much shorter... :)

Force Cognos portal to give more feedback to users

I don't see the numbers for yesterday. Has the data been loaded? Is all OK? These questions start getting asked every time data load goes wrong. And they pop up in our users heads every day before they run their reports. We have to shape up and be more proactive in giving feedback to our users so we get less questions and get bonus points in user-friendliness.

One way to give data load status feedback is to create a special report (maybe even push it out as the starting portal tab, if you dare). However that would not be cool at all. Having to run a report forces the user to go and look for this information. In real life this type of information can only be consumed if it is in the users sight at all times and without any extra effort.

Currently there is no built-in functionality helping dedicate a small piece of the portal to provide a system health-check report. It is time to hack the Cognos portal! Amm.. I mean customize.

Can you have too much pie?

Jep, you can!

One of the leading contestants at the Error Message of 2015 contest is another gem from good old Cognos:

One of the reports from the default Audit report package produced this one :)

Another good reason to not use these charts!

M.

Framework Manager Error BMT-MDC-0001 Unable to publish

You might encounter this error message while publishing a FM model.

Usual cases are that the user trying to publish has no Write access to the destination.

I ran into this myself and well... I am admin... So the real cause in this case was that there already was a folder with exactly the same name. There obviously is no feedback from the system about this, so it is all about finding that out yourself!