Feeder for rule used in DB Function

Post Reply
Analytics123
Posts: 88
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Feeder for rule used in DB Function

Post by Analytics123 » Thu Aug 10, 2017 4:33 pm

Hi ,

In my inventory cube I have a rule to get the cost of the material from cost sets cube.

['Cost - Standard Price'] = N: DB('Cost Sets', !Plants, '10', !Currencies, !Months, !Items, 'Standard', 'Cost - Standard Price');

So my feeders for this is :

DB('Cost Sets', !Plants, '10', !Currencies, !Months, !Items, 'Standard', 'Cost - Standard Price')=> ['Cost - Standard Price']

Is my feeders correct ? is there a better way to handle this

Wim Gielis
MVP
Posts: 1571
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Feeder for rule used in DB Function

Post by Wim Gielis » Thu Aug 10, 2017 4:51 pm

Analytics123 wrote:
Thu Aug 10, 2017 4:33 pm
Is my feeders correct ?
No. Didn't TM1 complain when you wanted to save it?

In any case, the left hand side of a feeder rule is always an area in a cube specified with the [ ] notation. The DB function will follow after the arrow of the feeder.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 104 TM1 articles and a lot of custom code
Newest blog article: Delete a cube and its dimensions

User avatar
tomok
MVP
Posts: 2357
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Feeder for rule used in DB Function

Post by tomok » Thu Aug 10, 2017 5:09 pm

A lot depends on why you are feeding this. You don't have to feed it in order for your inventory cube to show the cost nor even to use it in the calculation of another measure in the same cube. Generally, the only reason to feed a measure like this is so that it doesn't zero suppress when using it in a report. The simplest thing would be to feed it with the measure that contains quantity unless there are other mitigating reasons as to why not. Like anything in TM1, it depends.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Analytics123
Posts: 88
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Feeder for rule used in DB Function

Post by Analytics123 » Mon Aug 14, 2017 6:16 pm

Hi Tomok ,

I am facing the issue you stated below , doing zero suppression removes the entire Cost - Standard Price column from the view , even though the rule calculated value is present in the intersections .

Attached the screenshot after and before zero suppression is turned on .

SKIPCHECK;

['Cost - Standard Price'] = N:DB('Cost Sets',!TestPlant, !TestCompanyCode, !Currencies, !Months, !TestItems, 'Standard', 'Cost - Standard Price');

Feeders;
['Cost - Standard Price'] => DB('Cost Sets',!TestPlant, !TestCompanyCode, !Currencies, !Months, !TestItems, 'Standard', 'Cost - Standard Price');

Source has 6 dimensions and 5 dimensions in the cost sets cube to retrieve the cost value.

Thanks,
Attachments
without zero supression.png
without zero supression.png (16.62 KiB) Viewed 633 times
With Zero Sup.png
With Zero Sup.png (21.77 KiB) Viewed 633 times

Wim Gielis
MVP
Posts: 1571
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Feeder for rule used in DB Function

Post by Wim Gielis » Mon Aug 14, 2017 7:00 pm

The feeder should probably read:

Code: Select all

['Total Qty on Hand'] => ['Cost - Standard Price'];
If you would zero suppress the rows but not the columns, you would have the same effect without a feeder.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 104 TM1 articles and a lot of custom code
Newest blog article: Delete a cube and its dimensions

Analytics123
Posts: 88
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Feeder for rule used in DB Function

Post by Analytics123 » Mon Aug 14, 2017 9:23 pm

why should feeder read based on quantity . what if i have only cost in the columns .I am not getting it .

Thanks,

Wim Gielis
MVP
Posts: 1571
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Feeder for rule used in DB Function

Post by Wim Gielis » Mon Aug 14, 2017 10:00 pm

If I understand you correctly then you only want to put a fed value for 'Cost - standard price' if you have a Quantity. But in reality I would not feed this calculation at all.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 104 TM1 articles and a lot of custom code
Newest blog article: Delete a cube and its dimensions

Analytics123
Posts: 88
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Feeder for rule used in DB Function

Post by Analytics123 » Tue Aug 15, 2017 12:17 pm

now assume in this cube i want to see the cost for like 50 materials , then I have the n level 50 items shown with cost .

Assume there is only cost for 35 materials and rest 15 are zero and I wanted to only see the materials with cost .

Then I hit zero suppression and everything disappears .I expect that 35 materials should show the cost . Here cost is not dependent on quantity ,it does a look up on other cube to get value .

SO cant I do a zero suppression on a rule based measure which is not dependent on any other measure .

Thanks,

User avatar
tomok
MVP
Posts: 2357
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Feeder for rule used in DB Function

Post by tomok » Tue Aug 15, 2017 12:29 pm

Analytics123 wrote:
Mon Aug 14, 2017 6:16 pm
SKIPCHECK;
['Cost - Standard Price'] = N:DB('Cost Sets',!TestPlant, !TestCompanyCode, !Currencies, !Months, !TestItems, 'Standard', 'Cost - Standard Price');
This is a perfect example of an intercube rule. Data is pulled in to Target from Source. All fine here.
Analytics123 wrote:
Mon Aug 14, 2017 6:16 pm
Feeders;
['Cost - Standard Price'] => DB('Cost Sets',!TestPlant, !TestCompanyCode, !Currencies, !Months, !TestItems, 'Standard', 'Cost - Standard Price');
Wrong. When feeding an intercube rule the syntax is Source to Target, meaning the feeder statement goes in the Source cube. Go back and read the docs again, specifically the section on intercube rules.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Analytics123
Posts: 88
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Feeder for rule used in DB Function

Post by Analytics123 » Tue Aug 15, 2017 9:18 pm

Okay , I have written the feeders in the source cube now . Bit I am not getting the complete correct result .

Now Zero suppression still shows me the cost .

but my question is cost has no relation with quantity , why does cells with zero quantity gets suppressed even it has cost .


Just trying to understand the basics .

Rule :
SKIPCHECK;
[ 'Cost - Standard Price' ] = DB( 'Cost Sets', !TestPlant, !TestCompanyCode, !Currencies, !Months, !TestItems, 'Standard', 'Cost - Standard Price' );


Feeders in Source :

Feeders;
[ 'Cost - Standard Price' ] => DB( 'Cost Sets', !Plants, !Currency Types, !Currencies, !Months, !Items, 'Standard', 'Cost - Standard Price' );


And one thing to be noted here is target has an additional dimension called country .but I assume that is like a constant and doesnt matter .
Attachments
Before Zero Suprrssion.png
Before Zero Suprrssion.png (17.04 KiB) Viewed 503 times
AfterZerosupression.png
AfterZerosupression.png (20.33 KiB) Viewed 503 times

User avatar
gtonkin
MVP
Posts: 534
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Feeder for rule used in DB Function

Post by gtonkin » Wed Aug 16, 2017 7:02 am

Analytics123 wrote:
Tue Aug 15, 2017 9:18 pm
...
And one thing to be noted here is target has an additional dimension called country .but I assume that is like a constant and doesnt matter .
I think your assumption would be wrong-you need to make sure you specify the complete address (set of coordinates) to feed the target-Your feeder does not appear to be feeding Country-If source is missing this then I would suggest you create a rollup of Countries e.g. Total Countries and then specify that in your feeder e.g.

Code: Select all

Feeders;
[ 'Cost - Standard Price' ] => DB( 'Cost Sets', 'Total Countries', !Plants, !Currency Types, !Currencies, !Months, !Items, 'Standard', 'Cost - Standard Price' );

User avatar
tomok
MVP
Posts: 2357
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Feeder for rule used in DB Function

Post by tomok » Wed Aug 16, 2017 12:15 pm

Analytics123 wrote:
Tue Aug 15, 2017 9:18 pm
Feeders in Source :

Feeders;
[ 'Cost - Standard Price' ] => DB( 'Cost Sets', !Plants, !Currency Types, !Currencies, !Months, !Items, 'Standard', 'Cost - Standard Price' );
Why are you referencing the source cube on the right hand side of the feeder statement? Shouldn't it be the target????? I'm guessing you didn't bother to go back and consult the docs like I suggested and if you are going to ignore the most relevant suggestion you've received to date in this thread then I'm checking out until you do so.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Wim Gielis
MVP
Posts: 1571
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Feeder for rule used in DB Function

Post by Wim Gielis » Wed Aug 16, 2017 1:34 pm

Indeed, I was even confused about whether it is 1 cube or 2 cubes in this story...
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 104 TM1 articles and a lot of custom code
Newest blog article: Delete a cube and its dimensions

Post Reply