How do you deal with this BI reporting / TM1 feeder issue?

[tsw]

We've ran into this a couple of times now, and wondering how other people deal with this.

Basically, the issue can simplified as follows:
2 dimensions:
Org consolidation -> Proj detail level
Measures A, B, and C

Code: Select all

rule file:
FEEDSTRINGS;
SKIPCHECK;

['C'] = N: ['A'] \ ['B'];
['C'] = C: ['A'] \ ['B'];

Feeders;
['A'] => ['C'];

If the data in element level Proj is either 0 in Measure A or B, there's no value for C. However, there should be a value for C at the consolidation level (0.88 as seen in screen capture) since it's rule derived.
You can see the problem if you zero-suppress in TM1, as the whole Measure C goes away.... but visually, you can still see a value if you don't zero-suppress.

The problem really shows itself when we report from Cognos BI, as the TM1 engine never returns a value for the consolidation level... even though a consolidation rule tells the TM1 server that there's override behavior to ['Org I', 'C'] cell.

Business users think it's weird that an IBM product TM1 has the 0.88 value, yet another IBM product Cognos BI (by default) doesn't show the value.

Note: we're able to workaround the issue by using the new IsUnderFed cube property of newer version of BI... but at the expense of performance since TM1 is now returning all of the cube (kind of defeats the purpose of memory optimization).

Has anyone else experienced this in their design? and what have you done to work with this limitation?

[David Usherwood]

Your use case is pretty strange I'd say. I would suggest making C the parent of A and B. Think that would fix it.

[tsw]

Your use case is pretty strange I'd say. I would suggest making C the parent of A and B. Think that would fix it.

Hi David,

brilliant! I haven't tried it in BI yet, but at least the TM1 engine won't zero-suppress it.
So, is the idea to use C as a consolidation level to make it unnecessary to use feeders?
if so, would that have some performance impact since it basically has to recalculate C for all A and B ?
regarding strange case, we have some financial KPIs that need to calculate % across enterprise wide. And for certain organizations.. they really only have numbers at detail level for either A or B, but we still need the KPI for their Org level.

Thanks!

UPDATE: Just tried this from Cognos BI, and it works! Thank you

[paulsimon]

Hi

I would also suggest trying a single rule, rather than separate N: and C: rules

['C'] = ['A'] \ ['B'] ;

Not sure if it will help but it might.

If not, try feeding from B as well as A

If not then David's idea is perhaps the only way.

I think that you are possibly getting the issue because all the base level values of C are zero, and therefore perhaps TM1 thinks that there is nothing to consolidate and therefore there is no feeding for the All Orgs consol combined with C

Regards

Paul Simon

[qml]

There is another aspect to this. If you are utilising the Dynamic Query Mode and are on TM1 10.1.1 or above and Cognos BI 10.2 or above then the default behaviour is for TM1 to do server-side zero suppression and it becomes impossible to see unfed values in BI regardless if you have zero suppression in your report. This default behaviour can be changed in those cases where this behaviour is not wanted (there could be genuine cases where you may want to underfeed your cube but still see the numbers in BI - but be aware of the potential performance impact of pushing more records between TM1 and BI). The way to do it is through a text cube attribute in TM1 called IsUnderFed. Below is a list of instructions on how to do it.

• a) Log in to Architect > select a model; Under the model > Cubes> select a cube by double clicking
  b) Select Edit from the Menu
  c) Select Edit cube Attributes
  d) Select Edit > Add new attribute
  e) Enter name > IsUnderFed (case insensitive)
  f) Select Text radio button and click ok
  g) Add a T (for True) to any cubes that we should avoid TM1-side suppressing
  h) Restart BI (not TM1) services for the change to take effect.

[tsw]

Hi

I would also suggest trying a single rule, rather than separate N: and C: rules

['C'] = ['A'] \ ['B'] ;

Not sure if it will help but it might.

If not, try feeding from B as well as A

If not then David's idea is perhaps the only way.

I think that you are possibly getting the issue because all the base level values of C are zero, and therefore perhaps TM1 thinks that there is nothing to consolidate and therefore there is no feeding for the All Orgs consol combined with C

Regards

Paul Simon

Hi Paul,

Thanks for your suggestions.
These are things I've tried already. I didn't think to try what David suggested, especially because I already have A and B in another consolidation.
Not sure how TM1 query engine works, but wouldn't it be easy for it to know which consolidation cells are rule-calculated and just always return a value for those cells?

[tsw]

There is another aspect to this. If you are utilising the Dynamic Query Mode and are on TM1 10.1.1 or above and Cognos BI 10.2 or above then the default behaviour is for TM1 to do server-side zero suppression and it becomes impossible to see unfed values in BI regardless if you have zero suppression in your report. This default behaviour can be changed in those cases where this behaviour is not wanted (there could be genuine cases where you may want to underfeed your cube but still see the numbers in BI - but be aware of the potential performance impact of pushing more records between TM1 and BI). The way to do it is through a text cube attribute in TM1 called IsUnderFed. Below is a list of instructions on how to do it.

• a) Log in to Architect > select a model; Under the model > Cubes> select a cube by double clicking
  b) Select Edit from the Menu
  c) Select Edit cube Attributes
  d) Select Edit > Add new attribute
  e) Enter name > IsUnderFed (case insensitive)
  f) Select Text radio button and click ok
  g) Add a T (for True) to any cubes that we should avoid TM1-side suppressing
  h) Restart BI (not TM1) services for the change to take effect.

Hi qml,

Yep, using the IsUnderFed property is how we are getting it to work right now. However, it seems like a workaround rather than a solution to the issue.

[BrianL]

I just ran a quick test using the latest 10.2 rp2 fp3...

Using these lines in the rule, the entire 'C' column is zero supressed

Code: Select all

['C'] = N: ['A'] \ ['B'];
['C'] = C: ['A'] \ ['B'];

However, if I replace that with the single rule line

Code: Select all

['C'] = ['A'] \ ['B'];

or even

Code: Select all

['C'] = N: ['A'] \ ['B']; C: ['A'] \ ['B'];

I do NOT see the 'C' column get zero suppressed.

[tsw]

I just ran a quick test using the latest 10.2 rp2 fp3...

Using these lines in the rule, the entire 'C' column is zero supressed

Code: Select all

['C'] = N: ['A'] \ ['B'];
['C'] = C: ['A'] \ ['B'];

However, if I replace that with the single rule line

Code: Select all

['C'] = ['A'] \ ['B'];

or even

Code: Select all

['C'] = N: ['A'] \ ['B']; C: ['A'] \ ['B'];

I do NOT see the 'C' column get zero suppressed.

Hi Brian,

Thanks for trying it out on newer version.
I was told that 10.2 has some auto-feeding features.. wondering if that's causing a difference?
Or IBM finally realize this is a product defect and that IsUnderFed is just a work around ?

[paulsimon]

Hi tsw

Given Brian's results, I am just wondering if you have AllowSeparateNandCRules in your TM1S.CFG?

Regards

Paul SImon

[BrianL]

Given Brian's results, I am just wondering if you have AllowSeparateNandCRules in your TM1S.CFG?

Good point. I did my test without AllowSeparateNandCRules set. If I set that config param I never see the column get zero suppressed.

[tsw]

Given Brian's results, I am just wondering if you have AllowSeparateNandCRules in your TM1S.CFG?

Good point. I did my test without AllowSeparateNandCRules set. If I set that config param I never see the column get zero suppressed.

Hi Paul, Brian,

I do have AllowSeparateNandCRules=T, and I tried putting in 1 line as Brian... same result.
Basically, IBM has reclassified this as an "enhancement request"
But, it seems using measure C as a consolidation (design workaround) is better than the IsUnderFed (reporting workaround)... at least for new cube development.