need help creating new cube from flat file and existing dim.

[sh226]

Hello,
i have following:
1. A flat file contains data for my "MEASURE DIM" and two columns to map date and property with existing Period and Property code dimensions. i understand and can create cube from above three dimensions Not a problem.
2. Now i have same flat file and want to add following additional existing dimensions also when creating a cube. I do not have any column or data available in my flat files to Map these dimension. Please help me to add and map these dimensions to step 1. Thanks so much for your help.
Account dim (General Ledger Account)
PropDivision dim
propLocation Dim
Prop Organization Dim
PropOwner Dim
PropStatus Dim
PropThirdParty dim
PropVehicle Dim

[Duncan P]

It might help you to create dummy variables. On the variables tab there is a button for "New Variable". If you press this you get a new row in the table and on the right of the row is a button called "Formula". If you press this you can enter a formula for your variable (remembering to end with a ;).

Create a new variable for each of the extra dimensions and in the formula you can put the name of the item in that dimension to which you want all of the data to apply.

This technique saves you having to abandon the wizard and write your own script code. If you want to write your own code then this post http://www.tm1forum.com/viewtopic.php?f ... 838#p32846 has a couple of hints to get you started.

[sh226]

It might help you to create dummy variables. On the variables tab there is a button for "New Variable". If you press this you get a new row in the table and on the right of the row is a button called "Formula". If you press this you can enter a formula for your variable (remembering to end with a ;).

Create a new variable for each of the extra dimensions and in the formula you can put the name of the item in that dimension to which you want all of the data to apply.

This technique saves you having to abandon the wizard and write your own script code. If you want to write your own code then this post http://www.tm1forum.com/viewtopic.php?f ... 838#p32846 has a couple of hints to get you started.

I tried method creating dummy variables but i can not figure out the formula. Can you please give me an example of a formula that can refer to the existing dimension (eg NEWvariable=??????). SORRY I AM NEW IN TM1 AND STILL LEARNING. THANKS

[tomok]

2. Now i have same flat file and want to add following additional existing dimensions also when creating a cube. I do not have any column or data available in my flat files to Map these dimension. Please help me to add and map these dimensions to step 1. Thanks so much for your help.
Account dim (General Ledger Account)
PropDivision dim
propLocation Dim
Prop Organization Dim
PropOwner Dim
PropStatus Dim
PropThirdParty dim
PropVehicle Dim

If the data for these dimensions is not in your flat file, where is it going to come from? Where is the Account# going to come from? Do you have some logic that can deduce the account from the existing data? What about the Property Division and Location? How will you know what they are? I don't care how good you are with TM1, if you don't have the required data the model won't work.

[Duncan P]

I had (perhaps wrongly) assumed that as the file didn't contain the coordinates on these other dimensions then they would be hardcoded in the formulae for the extra variables.

This is however not the only option. It is possible to add parameters to the process and to use them in the formulae for the extra variable. Thus each on each call of the process a different set of coordinates could be given. So for example if a file contains the data for a company for a single period you could specify the file, the company and the period as the parameters and load the file into the appropriate space in the cube.

[EDIT] I should reiterate for OP that the variable, once it is defined represents an extra virtual column of data in the file, all the values of which are the same. You treat in the same way, mapping it to a dimension etc.

[sh226]

2. Now i have same flat file and want to add following additional existing dimensions also when creating a cube. I do not have any column or data available in my flat files to Map these dimension. Please help me to add and map these dimensions to step 1. Thanks so much for your help.
Account dim (General Ledger Account)
PropDivision dim
propLocation Dim
Prop Organization Dim
PropOwner Dim
PropStatus Dim
PropThirdParty dim
PropVehicle Dim

If the data for these dimensions is not in your flat file, where is it going to come from? Where is the Account# going to come from? Do you have some logic that can deduce the account from the existing data? What about the Property Division and Location? How will you know what they are? I don't care how good you are with TM1, if you don't have the required data the model won't work.

well the data is in my flat file. if you see #1 i mentioned my data dimension is "MEASURE DIM".
All of the following dimensions are existed already. I just want to bring all of these dimensions plus dimensions that i created from flat file eg. "MEASURE DIM (Data Dimesnion)" "Property code" into this new cube. so i can slice and dice by Account, Division,Location,Organization, Owner and/or status etc.
Account dim (General Ledger Account)
PropDivision dim
propLocation Dim
Prop Organization Dim
PropOwner Dim
PropStatus Dim
PropThirdParty dim
PropVehicle Dim
Thanks

[Duncan P]

This is getting too confusing. Could you please upload a sample of the file?

[sh226]

This is getting too confusing. Could you please upload a sample of the file?

i have attached few screen shots and sample of my flat file. First three columns A,B AND C is referring to Variable vprop and vperiod. hope it would help. Thanks for your support.

[Duncan P]

So when you run this process to which Division, Location, Organization, Owner etc. should the data be applied? This information is not in the file so how is it provided?

If the process is designed only to put the data into a specific division/location/organization/owner... combination then you can use e.g.

Code: Select all

vDivision = 'divisionname';
vLocation = 'locationname';
...

If on the other hand each time the process is used you need to specify the division name (etc.) then create parameters (e.g. pDivision, pLocation etc.) and the formula becomes

Code: Select all

vDivision = pDivision;
vLocation = pLocation;
...

Now each time you run the process it will prompt you for a division, location etc. and will use those values for all records in the file.

This is the key thing to understand. If you are using the extra variables approach you get the same value of these for all records in the file.

[sh226]

So when you run this process to which Division, Location, Organization, Owner etc. should the data be applied? This information is not in the file so how is it provided?

If the process is designed only to put the data into a specific division/location/organization/owner... combination then you can use e.g.

Code: Select all

vDivision = 'divisionname';
vLocation = 'locationname';
...

If on the other hand each time the process is used you need to specify the division name (etc.) then create parameters (e.g. pDivision, pLocation etc.) and the formula becomes

Code: Select all

vDivision = pDivision;
vLocation = pLocation;
...

Now each time you run the process it will prompt you for a division, location etc. and will use those values for all records in the file.

This is the key thing to understand. If you are using the extra variables approach you get the same value of these for all records in the file.

Duncan Thanks for your reply it is really helpful and i think i am getting closer now. I have attached one of my existing data load process and couple attachment please take a look and i hope you will get the point what and where i want to load data. Thanks