Nested DB();

Post Reply
Abhi30
Posts: 11
Joined: Mon Aug 08, 2016 1:45 pm
OLAP Product: Ibm Cognos
Version: 10.2.2
Excel Version: 2016
Location: Mumbai

Nested DB();

Post by Abhi30 »

Hello all,

i used nested DB for my current task which is min salary as per grade.
In my first cube i included grade element in measure diemension and also created picklist into grade element like 1 2 3 4 5.
and in my second cube 1r_Salary_Count, Min salary value is there for 1 2 3 4 & 5 Grade.

I used nested DB() like this
['Min Basic' ] = N: DB('1r_Salary_Master', DB('1r_Headcount', 'Grade', !t_Period, !v_Version, !1h_Hierarchy), DB('1r_Salary_Master', !v_Version, 'Min Salary'));

But the value doesnt get fetched??

is it correct form of using nested db??
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Nested DB();

Post by declanr »

If the measure is a Numeric element then it will need to be converted to string before being used as an element name.
Declan Rodger
Abhi30
Posts: 11
Joined: Mon Aug 08, 2016 1:45 pm
OLAP Product: Ibm Cognos
Version: 10.2.2
Excel Version: 2016
Location: Mumbai

Re: Nested DB();

Post by Abhi30 »

which element??
Grade??
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Nested DB();

Post by declanr »

If the "grade" element where people choose the picklist drop down is an N element it can not be directly used in a DB statement.
You can either swap it to an S element which I probanly would as you don't get much benefit from it being a number. Or you could convert it to string within the rule which would be less efficient but still functional.

It could of course be something completely different but on the little information you have given that would be the first thing I would be inclined to check.
Declan Rodger
User avatar
qml
MVP
Posts: 1094
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Nested DB();

Post by qml »

Declan is very likely right. The DB() function only takes strings as parameters. So if another, nested DB() function returns a number it will need to be converted to a string before being injected as a parameter for another DB(). To convert a number into a string use the STR() function.
Kamil Arendt
Abhi30
Posts: 11
Joined: Mon Aug 08, 2016 1:45 pm
OLAP Product: Ibm Cognos
Version: 10.2.2
Excel Version: 2016
Location: Mumbai

Re: Nested DB();

Post by Abhi30 »

can u plz tell me how to attach screenshots to this reply??

i mean if i want to attch screenshot with post in this forum??
User avatar
qml
MVP
Posts: 1094
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Nested DB();

Post by qml »

Abhi30 wrote:can u plz tell me how to attach screenshots to this reply??

i mean if i want to attch screenshot with post in this forum??
Below the post window there's an area with Options and Attachments tabs. Go to Attachments, click 'Add files' and then 'Place inline' once your upload is finished.
Kamil Arendt
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Nested DB();

Post by lotsaram »

Abhi30 wrote:which element??
Grade??
No. Specifically, not Grade.
In the code sample you provided 'grade' is a hardcoded string so we have to assume this is a fixed reference and an element name in the 2nd dimension of the 1r_Headcount cube.
DB('1r_Salary_Master', DB('1r_Headcount', 'Grade', !t_Period, !v_Version, !1h_Hierarchy), DB('1r_Salary_Master', !v_Version, 'Min Salary'))
but is this the actual code? ... because you seem to have some kind of weird circular reference . You are looking up a value in 1r_Salary_Master with a nested DB from the same cube. How many dimensions does 1r_Salary_Master have? and what are they? because your "lookup" looks very odd indeed. From the DB ref it looks like 1r_Salary_Master has 2 dimensions being v_Version and I assume a measure, yet v_Version is being looked up from 1r_Headcount which has MORE dimensions INCLUDING v_Version. Well that just doesn't make sense, not even a tiny speck.

If you need help (and just the dimension names alone indicate that you do because it seems pretty clear that the "solution architect" of this model's knowledge of TM1 could be written on a postage stamp) then I suggest you include screenshots of the 3 cubes showing the dimensionality and a proper description of what you are trying to do.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Nested DB();

Post by lotsaram »

qml wrote:Declan is very likely right. The DB() function only takes strings as parameters. So if another, nested DB() function returns a number it will need to be converted to a string before being injected as a parameter for another DB(). To convert a number into a string use the STR() function.
Have a look at the nested DB with references to 1r_Salary_Master within 1r_Salary_Master. Beyond ensuring the data is type string it would seem likely that there could be a fundamental error of wrong cube reference or missing/wrong dimensionality.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Abhi30
Posts: 11
Joined: Mon Aug 08, 2016 1:45 pm
OLAP Product: Ibm Cognos
Version: 10.2.2
Excel Version: 2016
Location: Mumbai

Re: Nested DB();

Post by Abhi30 »

here i am attaching my 2 cubes..

if u can see their is a grade element in Headcount cube
i just want to do if i select grade 6 in that element

then the value should be fetched from 2nd cube which is salary master
Attachments
2.png
2.png (43.98 KiB) Viewed 5633 times
1.png
1.png (75.39 KiB) Viewed 5636 times
User avatar
qml
MVP
Posts: 1094
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Nested DB();

Post by qml »

You don't need the second nested DB(). Something like below should work, provided you store Grade in 1r_Salary_Master as a number. If you store it as a string then you don't need the STR() treatment.

Code: Select all

['Min Basic'] = N: DB('1r_Salary_Master', STR(DB('1r_Headcount', 'Grade', !t_Period, !v_Version, !1h_Hierarchy),2,0), 'Min Salary');
You will also need to supplement it with a feeder, but in this case I would not do an intercube feeder and just feed within 1r_Salary_Master from 'Grade' to 'Min Basic'.

Edit: You will also need a v_Version reference in the rule somewhere, like in Wim's code, but it's impossible to know from your screenshots what the order of dimensions in your cube is.
Last edited by qml on Fri Aug 12, 2016 11:32 am, edited 1 time in total.
Kamil Arendt
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Nested DB();

Post by Wim Gielis »

Please help the helpers. It's worthless to show a screenshot of cube names and dimension names,
if we cannot see what dimensions go in what cube. Including, the order of dimensions in each cube.

1 step in the good direction, a guess (of course):

Code: Select all

['Min Basic'] = N: DB('1r_Salary_Master', !v_Version, DB('1r_Headcount', !t_Period, !v_Version, !1h_Hierarchy, 'Grade'), 'Min Salary');
I assume measures dimensions to be the last in the cube.

Please do not waste your own time by guessing and trial and error. Read the documentation on the DB function, and follow training courses. This is basic TM1 rule writing.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply