Mapping Cube Setup

[tstagliano]

We are running Cognos TM1 version 10.1 and we would like to setup a mapping table so we could map the current listing of GL accounts currently in a cube to point to a new GL account structure we have. I am new to TM1 so I was looking for some guidance on how to best approach these along with some technical steps.

Thank you

[tomok]

The simplest way would be to create a string attribute on the Account dimension and populate it with the new account number. That will allow you to lookup the new account number in TM1, whether in rules or TI, using the ATTRS function. As to how to create an attribute, it is extremely easy and is covered more than adequately in the documentation, with examples. Suggest you give it a read.

[tstagliano]

I have already ran thru the attribute setup for our account dimension and associated the new account number we want to map to. I have then tried to update or create a process to utilize that attribute but this is where I am getting stuck. any guidance?

[declanr]

TI Data source is a view of your existing GL structure where vsOldAccount is the old account and vnValue is the value held in that cube.

Code: Select all

sNewAccount = AttrS ( sDimOldAccount, vsOldAccount, 'Map To' );
CellIncrementN ( Value, sNewCube, vDim1Element, vDim2Element,..., sNewAccount, 'Measure' );

CellIncrementN is mentioned as you may be combining what used to be 2 accounts in to 1 new account and therefore you would want to increment a value as opposed to just doing a cellputn.
And remember to start with a zero out.

[tomok]

I have already ran thru the attribute setup for our account dimension and associated the new account number we want to map to

Then why didn't you tell us that at the start? You completely wasted my time.

I have then tried to update or create a process to utilize that attribute but this is where I am getting stuck. any guidance?

What exactly does this mean? We can't read your mind. Suggest you read the request for assistance guidelines, then come back with specific questions. What is "getting stuck" What are you trying to do? What have you tried so far? Where is your code?

[tstagliano]

Sorry tomok for not being more clearer and I don't mean to waste your time and I appreciate your guidance and responsiveness to this post. I will try to be more clearer.

Purpose: To take the current GL accounts stored in the cube and map them to a new set of GL accounts we have created.

Steps Take so Far:
- Created an attribute called "MappingAccount" in our Account dimension and populated the new GL account associated to each current GL account.
- Created a Turbo Integrator process with the current DataSource values:
Type = ICAS --> Cube View
DataSource Name = Finance Cube --> Trial Balance View
- On the Variables Tab, I set all variables to a Content value of "Element" except for the Accounts Variable where I set that to "Attribute"
- On the Maps tab, I left No Action selected on the Cube tab, set the Element Variable to each Dimension on the Dimensions tab.
- Where I am getting stuck is when I go to the Attribute tabs under the Maps tab, I see the Accounts Dimension listed under the Attribute Variable column, but when I select the dimension column, my Accounts Dimension does not appear in the list, thus I am not allowed to associate the new attribute with the new GL accounts to the existing values.

[tomok]

- On the Variables Tab, I set all variables to a Content value of "Element" except for the Accounts Variable where I set that to "Attribute"

This is not correct. Setting a variable equal to "Attribute" means you are telling TM1 that the value in this variable is an attribute itself. In your case it is not, it is the original account. What you want to do is replace the account variable with an attribute of that element. Go back to the Account variable and change it's definition to "Other". Then create a new variable (by clicking on the New Variable button), name it "NewAccount", set the Variable "Type" as String, Set the Contents as "Element' and then set the Formula assign the following formula: NewAccount = ATTRS('Account', Account, "MappingAccount");. In the Mapping tab choose this variable as your Account element.

[tstagliano]

thanks for the information

[declanr]

tomok,
I ran thru you suggest changes and when I add the below formula to this new variable, I receive the syntax error message. The actual name of our dimension is "Accounts" so I adjusted the formula but still receiving the error. Would having subsets cause some adjustments to this formula?

NewAccount = ATTRS('Accounts', Accounts, "MappingAccount");
Line 1: Syntax error on or before: "MappingAccount"); invalid string expression

Put single quotes around 'MappingAccount' instead of double quotes. All string items in TM1 rely on single quotes.

[tstagliano]

made that change and was able to get the error message to disappear.

[tstagliano]

So I was able to run the process, but it produced the following error message in the log file. There are a number of these errors in the log file. Any insight as to what this message means and how to fix?

"Actual","Mar-2014","Current Budget Rate","Consulting","12650000 - Qatar","No Partner","71620000 - Consultant Base Salary","Amount","42426.58",Data Source line (298) Error: Data procedure line (4): Rule applies to cell

[declanr]

So I was able to run the process, but it produced the following error message in the log file. There are a number of these errors in the log file. Any insight as to what this message means and how to fix?

"Actual","Mar-2014","Current Budget Rate","Consulting","12650000 - Qatar","No Partner","71620000 - Consultant Base Salary","Amount","42426.58",Data Source line (298) Error: Data procedure line (4): Rule applies to cell

TM1 logs can sometimes be hard to decipher but this one is exactly what it says on the tin.
A rule applies to the cell that you are trying to change the value for in Line 4 of your data tab.

Assuming the rule is there for a reason you should check to see if the TI process can avoid that row of data in the source of just use a cellisupdateable if statement wrapped around your cellput.

[qml]

Any insight as to what this message means and how to fix?

It's right there in the error message: "Rule applies to cell". It means you have a calculation rule attached to the cube that applies to the same area of the cube that you are trying to write values to. If a cell is rule-calculated, it is impossible to write values to it directly. You need to decide which one it is that you want - the TI values or the rule values and either amend the rule or change the TI process to write only to non-rule-calculated cells. An easy solution (but not always the correct one! - think what you want to achieve first) is to test whether a cell can be written to by checking the condition CellIsUpdateable and only executing CellPutN/CellIncrementN if the condition is met.

Code: Select all

IF( CellIsUpdateable( ... ) = 1 );
   CellIncrementN( ... );
ENDIF;

[tstagliano]

I have confirmed that the cube I am trying to update does have a rule associated to it. What I am really trying to do is just update the account number to be something new but the values are not being updated but just moving to be associated to the new accounts we have. I will try to check the condition as part of the process to see if that resolves my issue.

[tomok]

Based on what you are trying to do you should have configured the data source view to skip rule calculated values. Is there a particular reason you didn't do this?

[tstagliano]

I did not set up the view so I do not know the reason for the calculated rules. Can I go back and change the data source view to skip the calculated values as this point?

[BariAbdul]

I think,That is exactly what Tomok is suggesting to you.Thanks

[tomok]

I did not set up the view

if you didn't create the view then who did? Please tell me you are not using a view that was created for viewing as a source in a Ti process. Depending on row, column and title settings you can get unpredictable results. You should always create your view dynamically in the prolog of your TI process, which will create a flat view. It is during that creation you can set the view to skip zeros, consolidations and rule calculated values.

[tstagliano]

Yes I am using an existing view as the Datasource so I can take whatever is currently in the view and re-map to the new accounts. Again I am brand new to TM1 so I apologize if my questions are so beginner level.

[tomok]

It appears you are just flying by the seat of your pants instead of trying to learn a little bit about the tool by taking a training class, or at the least, reading the documentation, before heading off and trying to productionalize things. Be warned, as you sow so shall you reap. Time invested in training and/or reading the docs is going to pay dividends. While your way is certainly one way to learn the tool, it's probably not the best and it's gonna give you a lot of heartburn along the way.