TM1 Feeders - Rule is nested If's and Source Cube is a Control Cube.

[DHaagenson]

Hello,

Here is my Rule:

['InvTransQty'] = IF(!mt_Time@=DB('}ElementAttributes_mt_InvTrans',!mt_InvTrans,'Month'),IF(!mt_InvTransTypes@=DB('}ElementAttributes_mt_InvTrans',!mt_InvTrans,'TransType'),DB('}ElementAttributes_mt_InvTrans',!mt_InvTrans,'TransQty'),0),0);

To summarize - The element InvTransQty is set in the above case if the current mt_Time dimension matches the Month attribute of the ItemNumber (which is !mt_InvTrans) and the current mt_InvTransType dimension matches the TransType attribute of the ItemNumber. If these values match, this element is set to the TransQty attribute of the ItemNumber.

This works great!

This is my problem:

When I display my cube to show the InvTransTypes, I get the correct values for the correct InvTransTypes, but I'm not getting a value for the TotalInvTransTypes, which is the consolidated rollup of the InvTransTypes. If I change my rule and make my rule real simple and use values instead of pulling from an element in another cube, I do get the consolidated roll up, so I am assuming that I have a feeder problem. Considering I am not sure how to write my Feeder for this that would make sense.

My Feeder:

Now I need to write a Feeder. This is the first time I've done this, but from what I understand the Feeder will need to be in the Source Cube rule, which in this case is the control cube, '}ElementAttributes_mt_InvTrans'. I am assuming I do not need 'SKIPCHECK' or 'FEEDERS' in my target cube rule.

Right now I have the following very simple Feeder in my control cube. I've tried adding the !mt_InvTransTypes and !mt_Time dimensions, but I get errors. When I make it this simple I can save the rule, even though this is probably not correct because I am not getting the results I need.

SKIPCHECK;

FEEDERS;
['TransQty'] => DB('mt_InvTrans', !mt_InvTrans, 'InvTransQty')

Can someone please help with the following:

1 - Do you think my issue with not getting my consolidated values is due to my feeder issue?
2 - Do I need SKIPCHECK and/or FEEDERS statements in my target cube rules?
3 - Please help me write this FEEDER in my source cube (control cube) rules.

I have googled this, read the best practices and I have been through many of these posts. I get the simple FEEDER examples, but this is more complex. I believe I understand the concept. I someone can help me through this example, I think I should be able to write the others I'll need going forward.

Thanks!

Deborah

[declanr]

Deborah,

Because you have written your rule with an = sign it is trying to perform the same calculation at the consolidated level instead of a standard consolidation. This presumably doesn't work as "TotalInvTransTypes" probably has the "TransType" attribute as blank.

Change the rule to be =N: and it will just calc at the nodal level and the consolidations will just use the standard consolidation method.
Your feeder looks reasonable.

[tomok]

Notwithstanding what Declan has told you, I'm not a fan of what you are doing with that design. You appear to have a list of transactions, against which you are recording the month, type, and quantity as attributes of that dimension, and then using a rule to push the quantity into a cube. Why the obfuscation? While what you are doing will work, it seems more complicated than it needs to be. Why not just have the user populate the quantity in the intersection of month and type in the cube instead of using a rule? It will perform much better and you won't need to worry so much about overfeeding, which you will definitely have unless you write some conditional feeders.

[DHaagenson]

Hi Declanar

Thank you, that does makes sense. However, when I change to =N: (see below) before my first IF Statement I don't get anything. I've tried adding it right before the DB's instead, but I get an error. I think you're right that I must have to indicate this somehow though.

['InvTransQty'] =N:IF(!mt_Time@=DB('}ElementAttributes_mt_InvTrans',!mt_InvTrans,'Month'),IF(!mt_InvTransTypes@=DB('}ElementAttributes_mt_InvTrans',!mt_InvTrans,'TransType'),DB('}ElementAttributes_mt_InvTrans',!mt_InvTrans,'TransQty'),0),0);

Hi Tom,

My data is coming from an Oracle database. Users will not be entering any data, so since the data has to come from someplace, I am uploading all my attributes with the initial Dimension data elements. This has been working fine, but I am very new at this, so I'm sure you might be right. This way though I can get all data quickly and easily for reporting.

[DHaagenson]

Hello,

I think there is something wrong with my second IF and it's not matching as expected. I put a value in the IF no match and my consolidation and leaf values were populated. Not quite as expected, but I believe this is the problem.

Thanks!

Deborah

[tomok]

My data is coming from an Oracle database. Users will not be entering any data, so since the data has to come from someplace, I am uploading all my attributes with the initial Dimension data elements. This has been working fine, but I am very new at this, so I'm sure you might be right. This way though I can get all data quickly and easily for reporting.

If the data is coming from Oracle and it includes the period, transaction type and quantity, why on earth would you write it to an attributes cube and rule it into your data cube instead of just letting the TI write it directly to your data cube? Never add rules where there is not a good business reason to do so. Rules are a good thing, but only when used properly.

[DHaagenson]

Hello,

Here's my reason:

With a cube all the data for all dimensions must be brought in together - You can't bring in some dimension and then other's in another TI. Also you must bring in all consolidations (Year rows, month rows etc.). To do this I would write queries in my TI ODBC connections or if possible execute stored procedures. Let's say that I did this and brought in Sales data, what if I wanted to include Customer Addresses in my cube. I would need a dimension for that data to include it in my cube (if I didn't store it as an attribute for the customer number). Would I have a Customer_Detail dimension in this case? Would that dimension include all other element (names, since no data would be populated in the dimension). So now I would have a dimensions with all these elements (element names) and no data. To see any of the Customer detail data I would need to bring the data in from Oracle into a cube. A cube must have two dimensions, so if I wanted to see the Customer detail data I would need to include a measure dimension in the cube, which makes no sense in this case, since I have none. I may not need a measure dimension for a TM1 cube, but for Framework I do. The nice thing about attributes is that there is already a control cube with all that data. I would imagine that's why they have these. To include data from the control cubes in your cube for reporting you would use rules wouldn't you? TM1 is based on rules and Turbo Integrator. I haven't read anything anywhere that says to limit your rules?

I need a way to keep this flexible and clean. No unnecessary dimensions to confuse and clutter up the model. I came from a SQL Data Warehouse background, where I built that myself for reporting. Then I used that to implement TIBCO Spotfire. The data I pulled in was ERP data. It was complicated and I created Stored Procedures for all the reporting. I am finding that TM1 requires a different way of thinking. I am working with actual data right now, but still creating demo models. I may end up with a different way of doing this when I start doing actual development, so if you can provide reasons for doing this another way I would sure like to hear them, given the information above.

Thanks,

Deborah

[lotsaram]

Hi Deborah welcome to the TM1 community,

If you do a search for the terms relational thinking on this forum you will find this type of discussion has been had many a time before. TM1 is a different beast, not just in terms of "design aesthetic" but especially in designing for performance and ease of use for the end user which is what the tool really is all about; end users and speed. The design you have will compromise speed, that is a certainty. Whether this matters at all depends on data volume, complexity of the model and user expectations. If you're giving up 0.1 sec on view recalculation probably no one will notice or care, but if there was a factor 100 difference and it was 10 seconds then probably they will.

In a TM1 cube the bare minimum of dimensions is not necessarily the best as "slice and dice" or the easy of self service analysis & reporting can be enhanced by adding extra analysis dimensions (or compromised by not including them). In TM1 we don't have the equivalent of the relational Group By on a column value, the only way to sum & filter is by dimension elements and hierarchies.

Certainly where you have "lower grained" data which has coordinates of only one or a few dimensions you definitely don't want to load this as input data to a multi-dimensioned cube. This is exactly the scenario where you would use rules to reference the data from an appropriately dimensioned cube into the multi-dimensional cube (usually for such "reference" type data also note that feeders are not usually required).

Last point if you had separate data sources for a given cube that isn't a big deal as the data load can be orchestrated by chaining processes together so from the end user point of view all data is updated at the same time. I wouldn't see this as an issue at all.

Anyway hope this gave you some food for thought, even if no answers. I hope you stick with it, and if you do it would be really interesting to hear your thoughts on this topic in 1 or 2 years' time when you have some more TM1 design experience under your belt.

[DHaagenson]

Hello,

Thank you!!

I just created 5 cubes in a test data model and for all but one (the one I had the questions on) I populated the cube directly using an ODBC connection to the data. I think I have a pretty good understanding of what the different methodologies are for me to get started without getting myself into trouble. That's what I'm hoping!

Deborah

[BariAbdul]

I have one question though,Sorry for my ignorance,If you intend to go with your actual design How does would it be helpful to you in TM1 reporting and what is the point of having key elements as attributes which wouldn't consolidate ?Thanks

[DHaagenson]

Hello,

There are at least two. I may find more uses as I begin actual development using TM1:

First as an alias, which is one type of attribute. I think the purpose of an alias attribute is obvious. If not, there is a lot of information out there that would provide you with information regarding an alias attribute.

Second, when you add an attribute TM1 creates two control objects, one is an attribute cube and other other is an attribute dimension. You can pull the cube control object attributes into a cube via a rule or you can use the control object dimension to populate the cube. This provides a lot of flexibility and objects that are created for you. For example, I have two item attributes, status and description. I can include the dimension control object with my item attributes when I load my cube and populate these two pieces of data without having to create this dimension myself.

When I am learning something new such as TM1, I want to learn everything I can so that I have the flexibility to do what I need to do when I developing something for someone.

I hope that helps!

Deborah

[BariAbdul]

There are at least two. I may find more uses as I begin actual development using TM1:

First as an alias, which is one type of attribute. I think the purpose of an alias attribute is obvious. If not, there is a lot of information out there that would provide you with information regarding an alias attribute.

Second, when you add an attribute TM1 creates two control objects, one is an attribute cube and other other is an attribute dimension. You can pull the cube control object attributes into a cube via a rule or you can use the control object dimension to populate the cube. This provides a lot of flexibility and objects that are created for you. For example, I have two item attributes, status and description. I can include the dimension control object with my item attributes when I load my cube and populate these two pieces of data without having to create this dimension myself.

When I am learning something new such as TM1, I want to learn everything I can so that I have the flexibility to do what I need to do when I developing something for someone.

Thanks a lot Deborah.I do know how alias and attributes works! There is common practice to hold status and description as aliases for ease of reporting.I was rather referring to this Tomok's reply:

Notwithstanding what Declan has told you, I'm not a fan of what you are doing with that design. You appear to have a list of transactions, against which you are recording the month, type, and quantity as attributes of that dimension, and then using a rule to push the quantity into a cube. Why the obfuscation? While what you are doing will work, it seems more complicated than it needs to be. Why not just have the user populate the quantity in the intersection of month and type in the cube instead of using a rule? It will perform much better and you won't need to worry so much about overfeeding, which you will definitely have unless you write some conditional feeders.

Anyway Good luck.Please let us know how you go! Thanks again.

[tomok]

I think what we're trying to understand is why you would pull dimension attributes into a reporting cube. Yes, I can understand that it would make it easy to report but what you are not understanding is the cost to performance and the size of your model if you do so. Let me give you an example:

A reporting cube with the following dimensions:

Version - 2 elements
Entity - 100 elements
Product - 2,000 elements
Customer - 10,000 elements
Region - 100 elements
Time - 50 elements
Measures - 10 elements

Now, let's say the Product dimension has the following attributes:

Description
SKU
Unit of Measure

and Customer has the following attributes:

Name
Address
Country
Tax ID

In TM1 data is stored at every intersection of every dimension. So, if you choose to pull the attributes for Product and Customer into your reporting cube then you are going to be populating that data for every intersection of every other dimension. For example, assuming you create a measure to hold the customer name, a single customer name will be populated into every element of Version, Entity, Product, Region and Time. A single address will then be populated 2,000,000,000 times (2 * 100 * 2000 * 100 * 50). Can you see how this could quickly consume vast amounts of memory? There are caveats, of course. If you don't feed the address in the cube then it will only use memory when reported but if you run a lot of reports out of the cube then memory can disappear quickly.

There are consequences to every design action you take in TM1. Pulling dimension attributes into a reporting cube can potentially have disastrous consequences that are not readily apparent until you actually start using the model and your size balloons. Unfortunately, you are not going to read about this in a manual anywhere. You either learn the hard way (I think I've made every stupid design decision there is at some point in my TM1 career) or you glean what you can from experienced users like the one's on this board or some combination of the two.

[DHaagenson]

Hi Tom - I really appreciate your experience and information - Thanks!!

Two questions

With your Customer Name example, where a measure is populated with the Customer Name attribute, would the memory difference between this and any other measure be the fact that one is a number and the other is a string? Otherwise It seems that no matter what the measure is that populates the intersection it's still populated for all the dimensions in the cube as you described.

What I'm trying to avoid is having a bunch of little dimensions for things like Item Description for example. An Item Dimension can have just one element, which is what is confusing to me. Either that element would be the Item Numbers or that element would be element names, such as 'ItemNumber, 'ItemDesc' etc and I think these would need to be in a Hierarchy unless they were measures, right or I would only be able to include one in my cube? I have been having trouble with the number of dimensions not matching up with the variables in a TI when I do this. For example if both an Item Number and an Item Description are in my Oracle data to bring into the cube, it seems that these need to both be in the same column and mapped to the same dimension, which makes no sense for these two pieces of data. Maybe I'm trying to include data that would normally not be included until I get over to Frameworks manager?

[tomok]

Stop thinking of a TM1 cube as if it were a relational database, it's not. Dimensions in a cube are like key fields, a way to stratify data so that you can summarize it and later retrieve it. Examples of a dimension would be Version, Product#, Customer#. Company#, Cost Center#, Time, Measure, etc. Most of the time you have the data broken down this way and would like to use the cube to help you analyze it. Customer Address would likely not be a dimension. Would you consider that to be a key field in your database? Possible but almost likely not. Same thing for customer name. What if two customers had the same name. No, the customer # would be the key and all the other fields associated with customer would be attributes of the customer.

I think you would benefit greatly from attending a TM1 Admin training class. IBM has one as well as many partners do. I'm not going to recommend any because I don't feel qualified to make a recommendation as I haven't seen what they are offering in quite a few years. Perhaps someone else can make a recommendation.

[DHaagenson]

Hi Tom - I understand that. I used this type of schema when I developed a data warehouse in MS SQL. I had fact tables and dimension tables. The difference is my dimension tables did not have just one piece of data. My keys were stored in my fact tables, but the dimension tables had additional attributes. I am thinking just as they do in this case. However, if I'm not able to use the attributes in TM1 the way that I would used them in my data warehouse than what good are they and how would I obtain this detail data? That's the issue. It doesn't seem that the TM1 structure is quite the same as either the star/snowflake or relational database structure. The point is, this detail data has to be somewhere and bringing it in for every sales transactions for example, would be incorrect and that would mimic a relational database schema. I don't want to do that. I want to have the Customer detail stored for me to obtain, just as I would if I had a key in a fact table and a dimension of customer attributes.

[tomok]

but the dimension tables had additional attributes.

Then they become attributes of the dimension in TM1.

However, if I'm not able to use the attributes in TM1 the way that I would used them in my data warehouse than what good are they and how would I obtain this detail data? That's the issue. It doesn't seem that the TM1 structure is quite the same as either the star/snowflake or relational database structure.

Of course it isn't. It's an OLAP database, not a relational database. They don't work the same way nor have the same functionality. If you want to report off of them you write an active form report and pull those attributes in with DBRA formulas. If you insist on pulling them into a single reporting cube, along with the data, then you have to live with the consequences to your model size and performance. Ruling these attributes into the reporting cube is the TM1 equivalent of denormalizing an SQL database.

[DHaagenson]

Hello Tom!

So it sounds like I just have to be patient and wait until I get Framework Manager and Report Studio up and running and then I should be able to pull the detail in at that point if I need to include it in a BI report, but probably using an ODBC connection to my Oracle database instead of from cube/dimension attributes. The TM1 cube is essentially to provide the key elements and measures. Is that right?

This is really a fundamental concept and should be addressed in books and online, but maybe it's just assumed. There is all kinds of information out there about attributes and how they are used, but it doesn't sound like you think they should be. Do you know if there is a significant difference in memory use though between storing a string measure and a numerical measure? If not, then I still don't see the issue, because that's really the difference.

All the books and tutorials are very basic, which is important for learning the basic functionality, but not for developing a real data model. I am thankful there is at least that though and this forum.

FYI - I'm not insisting on anything, I am trying to learn this. I do understand the structure of an OLAP cube. Please, if you can just answer the questions I have that would be the most helpful.

Thanks Again!

Deborah

[tomok]

There is all kinds of information out there about attributes and how they are used, but it doesn't sound like you think they should be.

Where did you ever get the idea you should not use attributes in TM1? I never said that. Attributes are a key feature in TM1. What I'm saying is you should leave the attributes in the attribute cube where TM1 puts them, not rule them into a reporting cube, which has the affect of denormalizing them!!!!

Do you know if there is a significant difference in memory use though between storing a string measure and a numerical measure? If not, then I still don't see the issue, because that's really the difference.

No, that's not the difference. The difference is you are taking a single attribute value, like "123 Main Street", and turning it into 2,000,000 copies of "123 Main Street". Can you not see this?

[DHaagenson]

Hi Tom,

I'm sorry but when this is new it can be very confusing.

So for example, the customer attribute Cust1Address would represent an intersection of all elements of all dimensions, Cust2Address would also represent an interasection of all elements of all dimensions. So in your example above, that would be 4,000,000 copies for the two addresses. Is that right? Even though each address would be the intersection of 1 customer dimension element and 1 element of each of the other dimensions. In other words, I would be populating a cell where there is an intersection with 1 piece of data for each dimension, just as a measure does. Would an Alias attribute cause this problem too. Isn't this just using one piece of data instead of another; the alias instead of the actual element?

If we just leave attributes in their own cubes how do we use them? I can see that we have drill downs and I guess we can use the control cubes for those, but I haven't tested this. Was I correct in my last post that I would be primarily using these attribute cubes outside of TM1, for example in Framework Manager and probably not in TM1?