Page 1 of 1
Write a feeder with a "many to one" capability
Posted: Tue Oct 15, 2013 12:43 pm
by fabpas
I have a rule which "links" the data of two cubes, say S(ource) and T(arget). My question is related to the fact that we have a set of elements in the time dimension that allows storing "yearly values". I call them "direct input (DI_)". This is how it looks:
DimTime:
Code: Select all
2012 -\
2012/01
2012/02
...
2012/12
2013 -\
2013/01
2013/02
...
2013/12
DI_2012
DI_2013
Additionally, for each element, we have an attribute "Year" which tells us the year of the element. i.e. ATTRS('DimTime', '2012/01', 'Year') returns '2012'
The rule in the T(arget) cube is something like
['Measure1'] = DB('S', 'DI_' |ATTRS('DimTime', !DimTime, 'Year'), 'Measure2')*DB('VAT', ATTRS('DimTime', !DimTime, 'Year'),'value');
I don't know how to write a good feeder provided that any "DI_" element must "feed" the corresponding month elements.
Can anybody help me with this?
Re: Write a feeder with a "many to one" capability
Posted: Tue Oct 15, 2013 1:00 pm
by java_to_tm1
Fabpas,
you don't really need to feed from source to target.
If your target cube has even one other measure element (say 'Measure2' )that has a non-zero value at every slice that 'Measure 1' has a non-zero value, you can always feed from that element in the target cube like this:
['Measure2'] => ['Measure1'];
It is a common misconception that 'every rule has an equal and opposite feeder' (to misquote Isaac Newton).
Your business drivers are by far the better indicators of where and how you should feed.
Re: Write a feeder with a "many to one" capability
Posted: Tue Oct 15, 2013 1:14 pm
by fabpas
Mmh, I try to understand your point.. not sure if I get it right.
If I do a you suggested, i.e. feed from a measure which that has a non-zero value at every slice that 'Measure 1' has a non-zero value, the "check feeders" indicates that all related C-level cells are "not fed". Is it excepted in that case?
Re: Write a feeder with a "many to one" capability
Posted: Tue Oct 15, 2013 4:06 pm
by java_to_tm1
fabpas,
What are the dims in your S & T cubes? Can you attach screenshots with some of the cells (specifically the 'not fed' ones highlighted)?.. and the rule file as well.
Let me see if I can help you write the rule.
Re: Write a feeder with a "many to one" capability
Posted: Tue Oct 15, 2013 9:06 pm
by java_to_tm1
fabpas wrote:The rule in the T(arget) cube is something like
['Measure1'] = DB('S', 'DI_' |ATTRS('DimTime', !DimTime, 'Year'), 'Measure2')*DB('VAT', ATTRS('DimTime', !DimTime, 'Year'),'value');
Meanwile, you can try feeding to the consolidated element: If DI_2012, feeds to 2012 (the consolidated element), it feeds all the leaf level elements under '2012'.
Like this. If your consolidated year is '2012', your feeder would be this.
Code: Select all
['Measure2'] => DB ('T', SUBST (!DimTime, 4, 4), 'Measure1' );
If your consolidated element was '2012 -\', your feeder would be
Code: Select all
['Measure2'] => DB ('T', SUBST (!DimTime, 4, 4) | ' -\' , 'Measure1' );
A cleaner way to do this would be to have 2 different time dims:
1. Year dim - for cube 'S' which has data only @ the granularity of years
2. Year-Month dim for cube T', which has data @ the granularity of month
This way, you can get rid of the DI_ convention for the input
Your rules would now be
in 'T' (having the Yr-Month dimension)
Code: Select all
['Measure1'] = DB('S', ATTRS('DimTime', !DimTime, 'Year'), 'Measure2')*DB('VAT', ATTRS('DimTime', !DimTime, 'Year'),'value');
in 'S' (having the Yr dimension), write this feeder
Code: Select all
['Measure2'] => DB ('T', DimTime , 'Measure1' );
Re: Write a feeder with a "many to one" capability
Posted: Wed Oct 16, 2013 9:51 am
by fabpas
java_to_tm1,
Thanks for helping me. I have attached to this post a screenshot of both our source and target cubes. On the target cube,
if I select any of the cell containing 9000, I will get the unfed status. As you can see, our rule writes the same value in all the TimeMonth11Months elements (the reason is that we have only one version of our Budget 2014, so it does not matter from which month we observe it).
The rule is this one:
Code: Select all
['BU Qty', 'Actual','Providers':{'Supplier1'} ] =
if( ELLEV('TimeMonthPlanif',!TimeMonthPlanif)=0 ,
DB('SL_LogiQual','BU_'|ATTRS('TimeMonthPlanif', !TimeMonthPlanif, 'FYear'), !Brands, !Components,!Providers, 'DI_FY'|ATTRS('TimeMonthPlanif', !TimeMonthPlanif, 'FYear'), 'BU Qty') \
DB('DB_NbDaysMonthIME',!Providers,'FY'|ATTRS('TimeMonthPlanif', !TimeMonthPlanif, 'FYear'),'NbDaysMonth') ,
DB('SL_LogiQual','BU_'|ATTRS('TimeMonthPlanif', !TimeMonthPlanif, 'FYear'), !Brands, !Components,!Providers, 'DI_FY'|ATTRS('TimeMonthPlanif', !TimeMonthPlanif, 'FYear'), 'BU Qty') );
Essentially we look at the source cell "Versions":"BU_2014" / "TimeMonth11Months":"DI_FY2014" and we "link" it to Versions:"Actual" / TimeMonthPlanif:"FY2014" and all cells of TimeMonth11Months.
I imagine the logic is not so straightforward, but my rule works exactly as I want! The only problem is writing a correct Feeder for it!
Re: Write a feeder with a "many to one" capability
Posted: Wed Oct 16, 2013 3:04 pm
by java_to_tm1
In the source cube: Consider putting this feeder in and let me know if it works: The dim order might not match (since I dont know the dim order in your target cube ,or its name)
Code: Select all
['BU Qty'] => DB ( 'TGT_Cube' , 'Actual', !Brands, !Components, !Providers, 'FY' | SUBST(!TimeMonth11Months , 6, 4), 'FY' | SUBST(!TimeMonth11Months , 6, 4), 'BU Qty' );
The idea is
- Pull out a substring '2014' from 'DI_FY2014' from the 'TimeMonth11Months' dimension.
- Prefix 'FY' to the year we pulled out... 'FY2014'.
- Feed to the consolidation element 'FY2014': This feeds the consolidation and all its children.
Re: Write a feeder with a "many to one" capability
Posted: Wed Oct 16, 2013 4:03 pm
by fabpas
Wouahhh it works!
I have to say I understand the first "'FY' | SUBST(!TimeMonth11Months , 6, 4)" which makes sense to me. But not the second one, which matches the TimeMonth11Months dim. Your feeder doesn't address specifically the DI_FY.... elements, but all elements. Does it mean that feeders do not need to match directly the rules logic? I guess this is what you wanted to say in your first reply.