Published TM1 cube through Cognos BI. Question on Null Value

[ChrisF79]

We have a TM1 cube that we're pushing out through Cognos BI and viewing in Analysis Studio. The speed is phenomenal and it's working pretty great but we have one tweak we'd like to make. If an intersection does not have a value (think a sales rep that hasn't sold a particular item), the cell in Analysis Studio shows a blank or null value. We'd like it to show $0. Is there a setting we could change to make that happen?

[tomok]

We have a TM1 cube that we're pushing out through Cognos BI and viewing in Analysis Studio. The speed is phenomenal and it's working pretty great but we have one tweak we'd like to make. If an intersection does not have a value (think a sales rep that hasn't sold a particular item), the cell in Analysis Studio shows a blank or null value. We'd like it to show $0. Is there a setting we could change to make that happen?

I don't have a clue but I do know this is not specified in TM1. TM1 always returns zeros for empty numeric intersections. Perhaps you should post your question in a Cognos BI forum.

[ChrisF79]

Thanks. I posted on a BI forum but haven't heard anything. I think part of the problem is that it seems very few people are publishing TM1 cubes through Cognos. Despite IBM touting how great it is, the integration hasn't been easy but we're almost there. I think their lack of planning turned off a lot of users.

[kaazimraza]

Um, not sure if it would help but you can try applying data formatting in TM1, on the particular measure, and then see how it's behaving in Analysis Studio.

[declanr]

Just to clear the bases... you don't happen to have UNDEFVALS defined in the cube rule do you?

[overflow]

Sounds like a zero suppression override.

If you had all data points appearing this would kill your speed gains, so maybe a mapping to flag those lines of data that require a specified zero, using a flag in the measure dimension ( in the example, 1 = Active):

For example:

Sales Rep / Product / Month / ActiveForSales (1 - true) / Measure 2 (Number)
Joe Bloggs / TV / Jan / 2013 / 1 / 23
Jane Doe / TV / Jan / 2013 / 1 / 0

This should populate a zero in the cube view, sufficient to allow the detail to be picked up in a cognos report.

Overflow

[whitej_d]

It might not be quite so simple.

TM1 naturally sends zeros to BI instead of nulls; however, one of the ways reporting over TM1 is optimized (not using dynamic query mode) is by setting the Useprovidercrossjointhreshold and UseNonEmptyOnDataQueryThreshold in the qfs_config.xml query service config file. This essentially pushes the zero suppression back to the TM1 server instead of BI trying to suppress millions of zeros in the dispatcher report service. In case nulls are sent instead of zeros.

As a result, I don't believe there's anything that could be done on the TM1 side which would be able to control how BI reports zero/null values.

However, in Analysis studio, you can go to settings -> Data format and enter 0 in the Missing Value characters and Zero Value characters field which will cause blanks to be displayed as zero.