TM1 Forum

Hi,

I'm sorry if this will sound a bit vague, but I'm helping the MOD with a TM1 application, I'm not allowed to see the internet at work, send files home or bring out paperwork (might smuggle something out via wikileaks if this plea for help doesn't work) - so the following is my recollection of the problem. v8.4.2/Excel 2003

I'm running a csv file of accounting data into a cube - one piece of data ABC1234 relates to two different things - The first three letters are an account category and the first three digits are a site code. Final digit is a cost type which i am ignoring at the moment

In my cube i have set up a dimension with n level items ABC BCD CDE etc adding into a total cost. In another dimension, which already exists in another cube i have set up alias's against the site names for the three digit site code (I know it should have been set up as codes with descriptions, not how it is though)

When i run the process the error log shows all rows of data have failed to load because 'OBI' doesn't exist. When i set up the alias for the site code i exported into excel and then use =MID to read the three digits into the attribure via a DBSA - its a big dimension and so it read in some extraneous three letter codes in too. But what it was telling me was that it was skipping the numerical codes to find an alpha, and then failing because there was no match for the alpha

to try to work out where i was going wrong i changed my CellPutN so that it was reading in specific data that represented the first line inthe csv file

so it was CellPutN(45.81,Vcube, 'element', 'element', 'ABC', '123', 'InvAmmount'); where InvAmount is the measures dimension

It loaded this, except it loaded the data into '124' the next site in the dimension. I am able to use the Cube Viewer to find the intersection to manually enter the data (there are no rules on the cube). It may be doing this when i re introduce the site code variable into the CellPutN (i have slept since yesterday and reset the memory banks) where it skips one down the index

when i introduce the variable back in for the cost ( instead of ABC)the whole thing fails again with the OBI error message

I'm using the Mapping in TI to add in any missing elements in the csv file on the fly. The first cube i created where i didn't split the data works fine ...so it is something to do with the split of the code into two, the alias and s versus n elements - went round in circles for two hours yesterday with loads of error logs building up which upsets the IT department (every cloud....) who deign to allow me to view but not have any rights to the TM1 server

any hints or tips would be much appreciated

thanks

David

daitaylor wrote:so it was CellPutN(45.81,Vcube, 'element', 'element', 'ABC', '123', 'InvAmmount'); where InvAmount is the measures dimension. It loaded this, except it loaded the data into '124' the next site in the dimension.

It looks like your "Aliases" on the site code dimension are hosed. When you establish aliases for a dimension they have to be unique. Not just unique from each other, but also unique from the element names. I'm going to guess that you messed something up when you set the alias values. You need to open the cube }ElementAttributes_Site (or whatever the Site dimension is named) and check all your alias values and make sure they are correct before proceeding any further.

thanks - I'll try that tomorrow

Not just unique from each other, but also unique from the element names.

er but isn't the default alias name the same as the element name?

John Hobson wrote:

Tomok wrote:Not just unique from each other, but also unique from the element names.

er but isn't the default alias name the same as the element name?

Yes, that thought struck me too when I first read it but I think that what Tomok meant was that you can't assign (for example) Element 1's base name as the alias for Element 2. In other words, any given name can refer to only one element, regardless of whether you're talking about the alias, the base name, or another alias.

John Hobson wrote:

Not just unique from each other, but also unique from the element names.

er but isn't the default alias name the same as the element name?

What I mean is given two elements; Element_A and Element_B, you can't assign an alias value of "Element_A" to the element Element_B.

i think you are right that the alias names have to be unique - i came across that last week - TM1 won't let you save the dimension until it is fixed

it turned out teh solution was that i had defined two variables in the Metadata tab - when i moved them to the Data tab it all worked fine
so i don't know why that is ...but glad i have the solution

thanks for your help

daitaylor wrote:it turned out teh solution was that i had defined two variables in the Metadata tab - when i moved them to the Data tab it all worked fine
so i don't know why that is ...but glad i have the solution

That's a simple one. Variables defined in the Metadata or Data tabs are only valid inside those tabs. You can't define and/or set the value for a variable in one tab and expect it to be the same in another. The only tab that variables come over from unscathed is the Prolog tab. If you have a variable you want to use in both tabs and you want the values in the variable to be the same in both tabs you have to duplicate your code in both.

tomok wrote:

daitaylor wrote:it turned out teh solution was that i had defined two variables in the Metadata tab - when i moved them to the Data tab it all worked fine
so i don't know why that is ...but glad i have the solution

That's a simple one. Variables defined in the Metadata or Data tabs are only valid inside those tabs. You can't define and/or set the value for a variable in one tab and expect it to be the same in another. The only tab that variables come over from unscathed is the Prolog tab. If you have a variable you want to use in both tabs and you want the values in the variable to be the same in both tabs you have to duplicate your code in both.

If we're talking about user defined variables, that's not correct. (Obviously process variables will change with each record, and we don't define them ourselves on any tab, so this is only about the user defined type.) It's only true if you change the variable's value on the Data tab. If you define a variable as s='Metadata'; on the Metadata tab and then do an AsciiOutput of that value on the first line of the Data tab, you'll get "Metadata" as the output value. If you then change s to equal 'Data' you'll get "Data" as the output for each subsequent record. This is because each tab will process all of the records from the source in sequence; before the value is changed on the Data tab it will retain its value as set in the Metadata tab, after you change it it will retain its value from the previous iteration of the Data tab.