DBRW returning values that don't exist!

[Steve Vincent]

I've seen TM1 and Excel do some very odd things before, but this morning it's trumped all its previous efforts to stump me well and truly.

We’ve recently done a big restructure on a dim and in my example the consolidation “Group Business Services” no longer exists, not even as a nodal. It used to hold some other consolidations such as Finance, Commercial, Sales & Marketing etc. These now sit under the parent that group business services used to.

I have checked and rechecked the dim, it 100% absolutely does not exist. Not as an alias, element or even in the XDI. Yet when I slice some data from the cube it’s used in and type group business services in to a cell and copy the formula down, it returns a value! There are other consolidations that we’ve removed that I have tested it with and they show *KEY_ERR as expected, and yet GBS returns a value that I cannot verify in the cube because it’s not there.

It’s like there is a ghost in the dim but if I cannot find out why and how to remove it it’s going to confuse users and screw up a large number of reports. Has anyone seen this type of behaviour before?

[David Usherwood]

Weird.
Does the problem survive a restart? (My normal starter for 10 when TM1 goes into X-files mode )

[Steve Rowe]

If you have not restarted the server or unloaded the cube then this doesn't suprise me too much.

The cube sitting in memory probably still contains the pointers to the old dimension elements, if a cube had to be rebuilt everytime you changed a dimension then it would get very slow to maintain dimensions.

If you have restarted then I've no idead what's going on!

Cheers,

[Steve Vincent]

Very

Service is restarted once each weekend and this change was done a couple of weeks back. I've just got a backup from the weekend running offline and it's doing the same in that, so whatever it is its doing it covertly and consistantly. I'm going to start opening the .dim with notepad next and see if that gives anything away, but i have a feeling this one will be reported to Cognos in the near future.

[Steve Rowe]

Out of interest how was the element deleted from the dimension? TI / manually /API/?

[Rauzer]

How about a subset with the same name?

[Steve Vincent]

Rauzer has it

I'd figured it out before coming back to the forum but its a subset thats confusing the issue.

I unravelled it by coming across a view in the cube that showed GBS as an element despite knowing it doesn't exist. I opened the .vue in notepad and saw the list of elements that had been manually picked for the view. I then compared that with a .vue i know uses subsets for its selections and noticed they both look the same from within notepad (ie. just a list of elements or the name of a subset).

The old structure had GBS as a consolidation, but we had a subset of the same name that included its children as well. The view looks the same because TM1 has swapped from using the element to using the subset. What i feel its doing wrong, but i understand why, is by representing the subset as an element when the user browses the view. Its confusing as hell when you can see an element in the view, click show all then search for it and it doesn't appear. Its similar to the rollup functionality, its just the common name throws you.

What i still don't understand tho is why the DBRW is using the subset name as a valid entry in the formula. If i create a subset called rubbish and point my DBRW to it, it shows KEYERR. If i delete and recreate the GBS subset, the DBRW also fails. So why would it still work if the element has gone just leaving the subset?

Chances of me being able to explain this to Cognos support are slim to none tho, i can barely get my head around it myself!

[Martin Ryan]

What i still don't understand tho is why the DBRW is using the subset name as a valid entry in the formula. If i create a subset called rubbish and point my DBRW to it, it shows KEYERR. If i delete and recreate the GBS subset, the DBRW also fails. So why would it still work if the element has gone just leaving the subset?

I'm surprised by you not being able to re-create this as this is a "function" of TM1, where you can create ad-hoc consolidations by creating a subset then referencing the subset, and it will sum things up for you.

Very dangerous if you have a subset called "Year" which is made up of Jan thru Dec plus "All Year" consolidation - you'll get twice the value you intended.

You can actually even make the subset an element within the dimension. Which is REALLY confusing and dodgy. There's a cfg parameter to turn it off, but I can't remember what it is - userdefinedconsolidations or something.

Martin

[Steve Rowe]

I have a support request on this asking for us to be able to control it in the cfg or as a property of the subset.

Raised it ages ago when I hit the issue, like Martin described.
FD built his own TM1 report, and referenced "2008" rather than the consolidated element "Yr-2008", "2008" is a subset containing the total and all the elements which meant that everything was double counted. Nasty and took me a long time to track down.

Issue is 11-80803720 and the Jira number is TM-6023, it's listed as high priority but no activity since August 2008 (that I can see).

I don't think there is a config parameter about for this yet, the one Martin mentions is I think for the roll up button in the subset editor when accessed through a cube viewer.

I had of course totally forgotten about it..
Cheers,

[Steve Vincent]

thanks for that, for now the easy solution is to stop creating subsets with the same name as an element. fine from my end but i can't stop users creating private ones like that...

[harrytm1]

Hi,

I'm encountering similar problem here. here's what happened:

I have a report that extracts data via DBRW. Consolidated accounts such as GOR and EBITDA are returning wrong data. E.g. GOR is returning 0.1234 when the underlying child elements should sum up to a much larger figure.

In the DBRW formula, the argument for the account is a cell reference to Column A in the spreadsheet.

Two funny things happened during troubleshooting:
1) The alias for GOR is the full name "Gross Operating Revenue". So on a hunch, I changed GOR to Gross Operating Revenue and then Shift-F9. The correct numbers appeared! So why is it recognising an alias but NOT the element code?

2) On a second try, I went to the Account dimension worksheet and did a Dimension Worksheet Save again. I then went back to the report and Shift-F9. and this time the numbers are correct while the accounts are in ELEMENT CODES. what gives?

FYI, in the tm1s.cfg, I have the following parameters. I'm also on TM1 v9.4.1 FP2. And in a separate post, after this bug happens, I encountered TM1serverconnect error whenever I open this report. sigh...

ReevaluateConditionalFeeders=T
UseStargateForRules=T
AllRuleCalcStargateOptimization=T
ViewConsolidationOptimization=T
DisableWorksheetView=T
UseSSL=F

[harrytm1]

FYI, the problem seems to be solved by uninstalling version 9.4.1 FP002 at the client and re-install 9.4.1 again. Server remains at FP002. Phew... Fix Pack NOT.