Feeder for rule used in DB Function
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Feeder for rule used in DB Function
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
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
-
- MVP
- Posts: 3121
- 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: Feeder for rule used in DB Function
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
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
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
-
- MVP
- Posts: 2832
- 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
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.
-
- Posts: 128
- 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
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,
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 (16.62 KiB) Viewed 8558 times
-
- With Zero Sup.png (21.77 KiB) Viewed 8558 times
-
- MVP
- Posts: 3121
- 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: Feeder for rule used in DB Function
The feeder should probably read:
If you would zero suppress the rows but not the columns, you would have the same effect without a feeder.
Code: Select all
['Total Qty on Hand'] => ['Cost - Standard Price'];
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
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
-
- Posts: 128
- 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
why should feeder read based on quantity . what if i have only cost in the columns .I am not getting it .
Thanks,
Thanks,
-
- MVP
- Posts: 3121
- 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: Feeder for rule used in DB Function
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
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
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
-
- Posts: 128
- 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
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,
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,
-
- MVP
- Posts: 2832
- 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
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 SKIPCHECK;
['Cost - Standard Price'] = N: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.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');
-
- Posts: 128
- 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
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 .
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 (17.04 KiB) Viewed 8428 times
-
- AfterZerosupression.png (20.33 KiB) Viewed 8428 times
- gtonkin
- MVP
- Posts: 1202
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Feeder for rule used in DB Function
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.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 .
Code: Select all
Feeders;
[ 'Cost - Standard Price' ] => DB( 'Cost Sets', 'Total Countries', !Plants, !Currency Types, !Currencies, !Months, !Items, 'Standard', 'Cost - Standard Price' );
-
- MVP
- Posts: 2832
- 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
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.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' );
-
- MVP
- Posts: 3121
- 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: Feeder for rule used in DB Function
Indeed, I was even confused about whether it is 1 cube or 2 cubes in this story...
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
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