Page 1 of 1
Feeders Between Two Cubes with Different Dimensions
Posted: Fri Mar 25, 2011 8:51 pm
by alex.steimel
Hello Everyone,
I'm having feeder problems between two cubes which are causing TM1 to freeze and I generally have to restart the services to get it functioning again. Basically, actual data is housed in the SKU cube, and I want to feed that information to the Model cube. Examples of the dimensions in each cube are shown below.
DB('SKU Level Forecast', !Company, !Version,!Week,!Customer Base ,!SKU , ,!Forecast Measures)
DB('Model Level Forecast',!Company,!Scenario,!Version,!Week,!Customer Group,!Model,!Model Level Measures,!Forecast Measures)
As you can see there are four places where the cubes differ. I've attempted to write a footer that would work in the SKU cube, but it seems to freeze TM1 for some reason.
['Invoice $', {'Rolling 12 Month', 'CY Actuals', '2009 Actuals', '2010 Actuals'}] =>
DB('Model Level Forecast', !Company, 'Model Level Consensus', !Version, !Week, ‘Total Customers’, ‘Total Products’, 'Data from High Level' , ‘Invoice $’);
Also, there were previous feeders put in place by our consultants that don't seem to be cutting it either. I don't quite understand why they used formulas rather than just feeding the parent of those elements, but then again I'm fairly new to this.
['Invoice $', {'Rolling 12 Month', 'CY Actuals', '2009 Actuals', '2010 Actuals'}] =>
DB('Model Level Forecast', !Company, 'Model Level Consensus', !Version, !Week,ELPAR('Customer Base', !Customer Base, 3), ELPAR('SKU', !SKU, 1), 'Data from High Level' , !Forecast Measures);
Would any of you know a good way around this? Thanks in advance for any help.
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Sat Mar 26, 2011 10:20 am
by Michel Zijlema
Hi Alex,
In your post I see an empty entry in the 'SKU Level Forecast' DB formula - I assume this is a typo in your post.
Based on the information supplied I assume the system is freezing because of massive overfeeding: feeding to 'Total Customers' and ‘Total Products’ in effect feeds every single N element underneath these top level elements (for every N-level cell in the feeder area).
Looking at the consultant's feeder - this is much more restricted, as the formula derives a single customer(group) and product to feed. If this feeder is not working correctly, I assume this is because of the ELPAR constructs, especially the one for the Customer dimension. This dimension appears to have multiple hierarchies as the functiom tries to feed the 3rd parent. This is a tricky construct - you need to be sure that the 3rd parent is actually the parent that you want to feed.
You could test this by pasting the N-level elements of the 'Customer Base' dimension in an Excel sheet and add Elpar functions for parents 1 to 3 in the columns to the right of the pasted elements. Then you can check whether the labels on parent 3 are actually elements in the 'Customer Group' dimension.
Michel
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Sat Mar 26, 2011 10:26 am
by Wim Gielis
Hi Alex
Did you already try a couple of things like hard-coding the destination in the feeder rules, just to make sure that / a particular feeder is completely correct? Then generalising (increasing the number of fed positions).
You must know that if you feed a consolidated value, you implicitly feed all of its components... if you do this for 4 dimensions, you end up with the carthesian product of the number of lowest level elements below the fed parents. Could be huge possibly.
If you lack a certain dimension in the destination cube, why not feeding to one particular element rather than a consolidated level?
Wim
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Sat Mar 26, 2011 7:29 pm
by lotsaram
There is some good information on this topic if you search for it. Try
http://www.tm1forum.com/viewtopic.php?f=3&t=1529
http://www.tm1forum.com/viewtopic.php?f=3&t=3373
http://www.tm1forum.com/viewtopic.php?f=3&t=3834
which was just the first few topics I found. If you try search terms like "asymetric feeder" you should get a quite a few hits or in general read up on other threads where pople are having issues with feeders and it will give you quite a few strategies on how to improve your current situation.
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Mon Mar 28, 2011 2:19 pm
by alex.steimel
First off, thanks to everyone who helped out, I really appreciate it.
@Michael - I get what you're saying now with using those formulas rather than the parent. Also, you're right that the dimension has multiple hierarchies, although I'm not familar with how it was constructed as consultants did that for us. That's a great idea on using Excel (haha, I wish I had thought of that. It seems really helpful) and I've shown those results below. It seems like the order of the hierarchy changes based on the element that is being used. In the example below, the first three elements seem to be correct, but the following three are out of order. For my purposes, I believe I could just us an IF statement to say "if ELPAR 1 is longer than 2 characters, use it, otherwise use ELPAR 2" but this seems like it would be more inefficent than just fixing this structure.
Cust........Cust Class..............Total Channels........Dealer Priority.....................Region
.............1st ELPAR..............2nd ELPAR.............3rd ELPAR..........................4th ELPAR
121770.....11......................Independent Dealers..Unassigned Comm Region........4111
121945.....11......................Independent Dealers..Unassigned Comm Region........4111
122030.....11......................Independent Dealers..Unassigned Comm Region........4111
122810.....Misc....................13.......................4111................................Unassigned Comm Region
125159.....InterCompany.........4115....................Unassigned Comm Region........E1
121836.....Misc....................13.......................4131................................Unassigned Comm Region
@Wim - I haven't thought of using manual feeders before, but I can see how that would help with a lot of troubleshooting. I'll give that a try and see how it comes out, but I have the feeling that given what I explained to Michael above it will only solve part of the problem. Great tip though, I think that will save a lot of headaches going forward.
@lotsaram - Thanks for pointing me in the right direction about "asymetric feeders", as that wasn't exactly what I was looking for in my search so I came up with more vauge answers. Those posts do seem to help though.
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Mon Mar 28, 2011 4:40 pm
by tomok
alex.steimel wrote:['Invoice $', {'Rolling 12 Month', 'CY Actuals', '2009 Actuals', '2010 Actuals'}] =>
DB('Model Level Forecast', !Company, 'Model Level Consensus', !Version, !Week,ELPAR('Customer Base', !Customer Base, 3), ELPAR('SKU', !SKU, 1), 'Data from High Level' , !Forecast Measures);
I don't quite understand why they used formulas rather than just feeding the parent of those elements.
I don't know your model but having done this sort of thing on numerous occasions I have a pretty good idea of what they did. It looks like Customer Base is a dimension that has all the detailed customers while Customer Group is at a higher level, which coincides with a parent node in the Customer Base dimension. The same thing is likely true of products. Model coincides with a grouping of SKU's. So, the feeder the consultant did looks spot on, in that if a particular customer group has data in the source cube then you are feeding the parent of that customer group which will feed all it's customer children. The same scenario for SKU and model. The "3" in the ELPAR function for the customer means there are at least three hierachies and hierarchy number three is the one that contains the tree of how the customers roll up to customer group. If this feeder worked initially and all of a sudden stopped working you need to dig further into the relationship of customer to customer group and SKU to model. Did these hierarchies change? Could someone have changed the dimension maintenance process so that customer group is no longer the third hierarchy in the customer dimension? How about SKU to model. Were there any changes to this?
Your change to feed all customers and all models from every source element is definitely the wrong thing to do. It resulted in massive overfeeding, taking so long as to lock up your server for extended periods. Your time would be more wiselt spent figuring out what happened to the relationships that existed when your consultant built the app, rather than try to re-engineer it. Of course this begs the question, why have you not called the consultant who originally did the project? Seems like that should have been your first course of action.
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Mon Mar 28, 2011 4:45 pm
by alex.steimel
Tomok,
The reason against contacting our consultants is that our contract has ended for this project, and the significant fees associated with one-off questions is quite steep. My management wouldn't exactly approve of a $1000 afternoon based on a few questions, although I agree that it would probably be the best thing to do.
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Mon Mar 28, 2011 6:22 pm
by alex.steimel
So if we assume that the customer hierarchy is wrong for some reason, what would be the best way to correct that?
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Mon Mar 28, 2011 6:42 pm
by tomok
My first step would be to get the documentation the consultant left behind and then read the section(s) about dimension maintenance, especially those pertaining to customer, customer group, SKU and model. Look at structure of the customer/customer group. Is the current structure different than what the documentation says? If so, who changed it. Who are the TM1 administrators, could they have changed it. It could be any number of things. This is probably one of those situations where you're just going to have to pony up to the consultant for support. If your company doesn't really know what they have, meaning you didn't take ownership for it or didn't insist on adequate documentation then that's the way it goes. Companies always want to be cheap when it comes to documentation and/or support. TM1 models are generally not turn-key systems. You really have to have a TM1 administrator that understands 100% of what they've got or you need a support contract with the company that built the model.
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Mon Mar 28, 2011 6:58 pm
by mastertito4
Hi Alex,
As someone who is quite familiar with your model
the likely cause is what the others have been posting about:
The "3" in the ELPAR function for the customer means there are at least three hierachies and hierarchy number three is the one that contains the tree of how the customers roll up to customer group. If this feeder worked initially and all of a sudden stopped working you need to dig further into the relationship of customer to customer group and SKU to model. Did these hierarchies change? Could someone have changed the dimension maintenance process so that customer group is no longer the third hierarchy in the customer dimension? How about SKU to model. Were there any changes to this?
If the issue is that the hierarchy has been altered somehow so that the "3" in the ELPAR is not functioning correctly, the first thing I would do is look at dimension closely and then the process that is loading the SKU dimension. If I recall, the entire dimension was to be reloaded each time because of the numerous changes to how the SKUs are classified (i.e. SKUs getting moved between models and product codes often). Somewhere in that process the hierarchy is not being loaded how it was previously (maybe), and when the ELPAR function looks for the parent in the 3 hierarchy it is not finding the correct parent (which has already been diagnosed as an issue). I would think it would be unlikely that someone changed the process that loads the SKU dimension, it is much more likely that the data source has changed somehow and thus the process needs to be tweaked to adapt to that new change, but there is no way I can say for sure. So as a starting point I would take a good hard look at that SKU dimension and get a really good feel for each one of the hierarchies and how it works to make sure I fully understand what I am trying to accomplish when I look through the SKU dimension load process and try to see if I can find anything that will fix it.
he reason against contacting our consultants is that our contract has ended for this project, and the significant fees associated with one-off questions is quite steep. My management wouldn't exactly approve of a $1000 afternoon based on a few questions, although I agree that it would probably be the best thing to do.
It may take a few hours to figure out and find the issue and resolve it since it has been awhile and many different things could have changed....
Also...
My first step would be to get the documentation the consultant left behind and then read the section(s) about dimension maintenance, especially those pertaining to customer, customer group, SKU and model. Look at structure of the customer/customer group. Is the current structure different than what the documentation says? If so, who changed it. Who are the TM1 administrators, could they have changed it. It could be any number of things. This is probably one of those situations where you're just going to have to pony up to the consultant for support. If your company doesn't really know what they have, meaning you didn't take ownership for it or didn't insist on adequate documentation then that's the way it goes. Companies always want to be cheap when it comes to documentation and/or support. TM1 models are generally not turn-key systems. You really have to have a TM1 administrator that understands 100% of what they've got or you need a support contract with the company that built the model.
I agree here.
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Mon Mar 28, 2011 7:20 pm
by lotsaram
This is a good example which it is not recommend to ever use ELPAR in rules or feeders as this renders your rules liable to break due to changes in dimension structure or order. It would be a better design to have a "customer group" attribute in the customer base dimension and use the attribute for the feeder. That way you eliminate the possibility of this happening again. You coul ddo this either after you resolve your current problem or as the solution to the current problem.
For what it's worth if this system is in production and you have an inoperable system or users hanging on results that aren't there (or worse numbers that are there but are incorrect) then a 1/2 day of consulting is probably worth it.
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Tue Mar 29, 2011 3:51 pm
by alex.steimel
Tomok -
My first step would be to get the documentation the consultant left behind and then read the section(s) about dimension maintenance, especially those pertaining to customer, customer group, SKU and model.
I agree, but the documentation we currently have is sparse on details. Basically the only mention is when to run the process that updates these dimensions. If there was an explination of the manual inputs they put into the Advanced tab (or any part) of the TI process, or even a general plain-speak explination of what is going on I would have a lot more to go off of.
If your company doesn't really know what they have, meaning you didn't take ownership for it or didn't insist on adequate documentation then that's the way it goes. Companies always want to be cheap when it comes to documentation and/or support.
I can understand your point, but from my frame of reference it wouldn't seem unnecessary to request "adequate" documentation. If documentation is written into the contract I would think that the adequacy of such is implied.
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Tue Mar 29, 2011 4:06 pm
by alex.steimel
mastertito4 -
So as a starting point I would take a good hard look at that SKU dimension and get a really good feel for each one of the hierarchies and how it works to make sure I fully understand what I am trying to accomplish when I look through the SKU dimension load process and try to see if I can find anything that will fix it.
I have a handle on the dimension, it's the process that is confusing to me. As you know there are two processes that would load that dimension: one for historical data going back five years, and one for the current month. The two processes have numerous differences between them that aren't intuitive, so the process of deciphering those is puzzling.
It may take a few hours to figure out and find the issue and resolve it since it has been awhile and many different things could have changed....
While I appreciate the response, I honestly feel that it would be more than a few hours to resolve this. The process itself takes an hour to run, so one diagnostic and one check run already eats into that time entirely.
Per your quote of Tomok:
I agree here.
I can appreciate the difficulty of consulting with such a complex product. While our business (including myself) could have done more in respect to taking responsibility for the project, I'm sure we can both agree that upon your departure our model was far from ideal.
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Tue Mar 29, 2011 4:11 pm
by alex.steimel
lotsaram -
It would be a better design to have a "customer group" attribute in the customer base dimension and use the attribute for the feeder.
I can understand your point on not using ELPAR and using an attribute instead, but I believe the reason we went in this direction was to avoid the constant maintenance of that dimension. While I can see how that customer list may be small for some applications, ours happens to be in the thousands so the upkeep would be difficult.
For what it's worth if this system is in production and you have an inoperable system or users hanging on results that aren't there (or worse numbers that are there but are incorrect) then a 1/2 day of consulting is probably worth it.
Currently we're still in development due to various issues and renovation of the model, but that's definitely a valid point.
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Tue Mar 29, 2011 4:18 pm
by alex.steimel
Per someone's suggestions, I've copied the Metadata of the Process that loads our customer dimension. Maybe this will help out.
Code: Select all
#****Begin: Generated Statements***
vTotalRegions='Total Regions';
vTotalDealerPriority='Total Dealer Priority';
vTotalChannels='Total Channels';
vTotalCustomerClass='Total Customer Class';
vDealerPriorityToLoad=IF (COMM_DEALER_PRIORITY@=' ',
'Non Tiered Dealer',COMM_Dealer_Priority);
vRegionToLoad=IF (CD_REGION@=' ',
'UnassignedRegionCode',CD_REGION);
vCommRegionToLoad=IF (Comm_CD_REGION@=' ',
'Comm Region Unassigned',COMM_DEALER_PRIORITY|' '|'Comm Region '|Comm_CD_REGION);
vTerritoryToLoad=IF (CD_TERR_TM@=' ',
'UnassignedTerritoryCode',CD_TERR_TM);
vTerritoryNameToLoad=CD_TERR_TM|' '|NA_TERR;
vRegionNameToLoad=CD_reGION|' '|NA_REGION;
vCustomerGroupToLoad=
IF(CUSTOMER_GROUP@='Corporate Express','B.I. by Staples',CUSTOMER_GROUP);
DIMENSIONELEMENTINSERT('Customer Base','',NUM_CUST_BAS,'n');
DIMENSIONELEMENTINSERT('Customer Base','',CD_CU_CLASS,'c');
DIMENSIONELEMENTINSERT('Customer Base','',CHANNEL,'c');
DIMENSIONELEMENTINSERT('Customer Base','',vTotalRegions,'c');
DIMENSIONELEMENTINSERT('Customer Base','',vTotalDealerPriority,'c');
DIMENSIONELEMENTINSERT('Customer Base','',vTotalChannels,'c');
DIMENSIONELEMENTINSERT('Customer Base','',vTotalCustomerClass,'c');
DIMENSIONELEMENTINSERT('Customer Base','',vDealerPriorityToLoad,'c');
DIMENSIONELEMENTINSERT('Customer Base','',vRegionToLoad,'c');
DIMENSIONELEMENTINSERT('Customer Base','',vCommRegionToLoad,'c');
DIMENSIONELEMENTINSERT('Customer Base','',vTerritoryToLoad,'c');
DIMENSIONELEMENTINSERT('Customer Base','',vCustomerGroupToLoad,'c');
DIMENSIONELEMENTCOMPONENTADD('Customer Base',CD_CU_CLASS,NUM_CUST_BAS,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Base',CHANNEL,vCustomerGroupToLoad,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Base',vTotalRegions,vRegionToLoad,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Base',vTotalDealerPriority,vDealerPriorityToLoad,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Base',vTotalChannels,CHANNEL,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Base',vTotalCustomerClass,CD_CU_CLASS,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Base',vDealerPriorityToLoad,vCommRegionToLoad,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Base',vRegionToLoad,vTerritoryToLoad,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Base',vCommRegionToLoad,NUM_CUST_BAS,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Base',vTerritoryToLoad,NUM_CUST_BAS,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Base',vCustomerGroupToLoad,NUM_CUST_BAS,1.000000);
#****End: Generated Statements****
#Unique Situation we have manually added.
DIMENSIONELEMENTINSERT('Customer Base','','021982','n');
DIMENSIONELEMENTINSERT('Customer Group','',vCUSTOMERGROUPToLoad,'n');
DIMENSIONELEMENTINSERT('Customer Group','','NO CUSTOMER GROUP','n');
DIMENSIONELEMENTINSERT('Customer Group','',CHANNEL,'c');
DIMENSIONELEMENTINSERT('Customer Group','',vTotalChannels,'c');
DIMENSIONELEMENTINSERT('Customer Group','','Office Depot','c');
DIMENSIONELEMENTINSERT('Customer Group','','Staples','c');
DIMENSIONELEMENTINSERT('Customer Group','','Office Max','c');
DIMENSIONELEMENTCOMPONENTADD('Customer Group',CHANNEL,vCUSTOMERGROUPToLoad,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Group',vTotalChannels,CHANNEL,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Group','Office Depot','Office Depot BSD',1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Group','Office Depot','Office Depot Retail',1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Group','Staples','Staples Retail',1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Group','Staples','Staples Contract and Commercial',1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Group','Staples','Corporate Express',1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Group','Office Max','OM Workspace',1.000000);
DIMENSIONELEMENTCOMPONENTADD('Customer Group','Office Max','Office Max Retail',1.000000);
DIMENSIONELEMENTINSERT('Channel','','NO CHANNEL','n');
DIMENSIONELEMENTINSERT('Channel','',CHANNEL,'n');
DIMENSIONELEMENTINSERT('Channel','',vTotalChannels,'c');
DIMENSIONELEMENTCOMPONENTADD('Channel',vTotalChannels,CHANNEL,1.000000);
#END--------------------------------------------------------------------------------------------------------------------------------------------------
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Tue Mar 29, 2011 5:26 pm
by tomok
alex.steimel wrote:While I appreciate the response, I honestly feel that it would be more than a few hours to resolve this. The process itself takes an hour to run, so one diagnostic and one check run already eats into that time entirely.
I applaud you for your effort in trying to get your problem figured out but I think you are in way over your head on this one. It's completely unrealistic to think that some free help on an internet message board is going to get you where you want to be on this. It sounds like a fairly complicated process and there really is no way anyone is going to be able to help you without investing some significant time in this. I can't speak for the others but I try to limit my help to people that are learning the tool and maybe need a little nudge in one direction or the other or to provide some ideas as to how a particular business problem can be solved inside TM1. I'm not inclined to diagnose problems in existing models when the poster has a better alternative to get their issue fixed.
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Wed Mar 30, 2011 7:21 am
by garry cook
Second post in a row that I've written this but imo, that's a bit harsh tomok - we've all been there where there's no budget to get consultancy in, there's no expertise on the software in the area because scope creep has eaten up the consultancy time and you've been left with a gaping problem, no documentation and a user base questioning the validity of TM1's data integrity. Given TM1's small expert base, where else should he go if there's no money in the pot and has a problem that needs fixing by yesterday?
I don't think the problem's as bad as has been made out personally, it's pretty much been answered earlier in the chain by lotsaram - put an attribute in the dim, do a viewzeroout of this attribute on a nodal subset on the dimension as a data source in the prolog of the load process and then send the required parent into the attribute in during the load process at the same point as it's building the consolidation that's currently being used to feed. Then get the ELPAR out of the feeder (which I agree with the above posts is very bad practice in rules / feeders if you're dealing with a ragged heirarchy) and replace it with a reference to the attribute.
Run in a test environment first obviously then when it's built, run an ELISANC check to ensure that you've loaded in the correct parent before switching your feeders. I'd personally then try and get rid of the consol by checking if it's needed elsewhere but I'd leave it in for now to be safe until you're more comfortable with TM1's inner workings.
Theoretically given the problem outlined, this should resolve the issue and give a repeatable process for fixing - it's a very common way of getting round this issue. The OP's said further up when lotsaram's suggested this that he doesn't want to have to maintain the extra attribute but it'll be auto-maintained by the TI. If there's further TI's that affect the dim rebuild, do the same there but be sure to narrow down the initial zeroout view.
HTH
Re: Feeders Between Two Cubes with Different Dimensions
Posted: Wed Mar 30, 2011 7:04 pm
by alex.steimel
Thank you to everyone who pitched in, I appreciate the insight.