Loading data into cube from csv

[eddoria]

Hi all

I have a cube containing 14 dimensions. The cube is working fine but now we want to start loading costs into the cube. I have created a new measure called freight costs and this is the element for the costs from our transport company which they will provide us.

From the file they send I have only one piece of information which is the shipment number. Most of the other dimensions can be loaded against a "blank" element which is fine. However, there is one dimension called Unit of Measure where I would like to load the cost to that data point. Unfortunately it is not in the shipment cost file from the freight company.

So my question is how can I get data from the cube to be used for the load? I have the shipment number so is it possible to grab all the datapoints from the cube for that line so I can get unit of measure?

The line of data I am using is:

CellPutN( vCharge, cCube , sYear , smonths , sOrderType, sRegions, '1', sBanners, sUnitofMeasure, sOrderSource, sPostCode, sRouteCodes, sChannel, vShipmentNumber, sCustomerFCA, 'Freight Costs' ) ;

vCharge is the value from the csv file. All the others will be "blank", except for sUnitofMeasure. I would like to actually get that from the cube.

So for shipment number 1213456 the sample line would be:

CellPutN(65.00,FreightCube,2013,June,Blank,Blank, '1', Blank, sUnitofMeasure, Blank, 2000, Blank, Blank, vShipmentNumber, Blank, 'Freight Costs' ) ;

Is this possible?


Hope this makes sense.

Regards

Vera

[jim wood]

I don't know if it's me being tired, but I think you may need to expand on what you have said. I found it a little confusing. Think of it more from a data flow perspective and that may help us.

[eddoria]

In my csv file I have a shipment number and a cost that the shipment incurred.

I need to load the cost into my freight cube.

However, all I have to help me load is the shipment number. To use a celllputn, I need to gather "n" elements from the other dimensions. Most of these will be an "n" element called blank. But for unit of measure I would actually like to load the value to correct UoM instead of just blank. We may want to to see what our "eaches" cost to send or our "outer shippers". So somehow I need to get this information from the cube for that shipment as it is not in our csv file. I do not know if there is a line of code to do that.

Hope this helps.

[Duncan P]

There isn't yet enough in your post about what information is in the system already and what is in the CSV file. You say that the CSV file only contains a shipment number and a value. Where is the information about which unit of measure applies to each shipment? Is it in another cube? Or perhaps it is in an attribute of the shipment dimension. You need to say which, or if neither then say from where this information will come. Also it is always useful to have samples of import files if you can manage to include one.

If for example the UOM is an attribute of the shipment dimension then you might use something like

Code: Select all

sUnitOfMeasure = ATTRS( 'Shipment', vShipmentNumber, 'Unit of Measure' );

but it depends how your model is structured. Please let us know.

[eddoria]

Thanks for the response.

UoM is not currently an attribute. All the data is held in our erp system so I would need to combine the shipment table in JDE and our csv file. Not sure that can be done in TI.

I will try and load as attribute. I am just not sure if there is a one for one. One shipment could potentially have different units. I will investigate.

Thanks for your patience.

Regards

Vera Hawkins

[Tm1andTm1]

Cube with 14 dimension.........****you need to work more on the modelling part of TM1......Creation of cube itself is not correct

Hi all

I have a cube containing 14 dimensions. The cube is working fine but now we want to start loading costs into the cube. I have created a new measure called freight costs and this is the element for the costs from our transport company which they will provide us.

From the file they send I have only one piece of information which is the shipment number. Most of the other dimensions can be loaded against a "blank" element which is fine. However, there is one dimension called Unit of Measure where I would like to load the cost to that data point. Unfortunately it is not in the shipment cost file from the freight company.

So my question is how can I get data from the cube to be used for the load? I have the shipment number so is it possible to grab all the datapoints from the cube for that line so I can get unit of measure?

The line of data I am using is:

CellPutN( vCharge, cCube , sYear , smonths , sOrderType, sRegions, '1', sBanners, sUnitofMeasure, sOrderSource, sPostCode, sRouteCodes, sChannel, vShipmentNumber, sCustomerFCA, 'Freight Costs' ) ;

vCharge is the value from the csv file. All the others will be "blank", except for sUnitofMeasure. I would like to actually get that from the cube.

So for shipment number 1213456 the sample line would be:

CellPutN(65.00,FreightCube,2013,June,Blank,Blank, '1', Blank, sUnitofMeasure, Blank, 2000, Blank, Blank, vShipmentNumber, Blank, 'Freight Costs' ) ;

Is this possible?


Hope this makes sense.

Regards

Vera

[lotsaram]

Cube with 14 dimension.........****you need to work more on the modelling part of TM1......Creation of cube itself is not correct

Maybe you are just stirring the pot but exactly what would you suppose is wrong with a cube with 14 dimensions? Perhaps you could enlighten us?

[tomok]

So my question is how can I get data from the cube to be used for the load? I have the shipment number so is it possible to grab all the datapoints from the cube for that line so I can get unit of measure?

So, you are saying that you don't have the UOM in the source file but, if you could query the cube during the load, you would pick it based on what UOM all the other data is already stored in? Is that correct? It is possible to insert looping code in the data tab of our process to cycle through the UOM dimension and find which element has the other data in it and use that to determine which UOM to store the freight costs against. If you only have a few elements in the UOM dimension you may be better of skipping the looping and just do a series of IFs. Something like:

Code: Select all

IF (CellGetN( cCube , sYear , smonths , sOrderType, sRegions, '1', sBanners, 1stUnitofMeasure, sOrderSource, sPostCode, sRouteCodes, sChannel, vShipmentNumber, sCustomerFCA, 'Substitute Data Element Here' ) <> 0;
  CellPutN( vCharge, cCube , sYear , smonths , sOrderType, sRegions, '1', sBanners, 1stUnitofMeasure, sOrderSource, sPostCode, sRouteCodes, sChannel, vShipmentNumber, sCustomerFCA, 'Freight Costs' ) ; 
ELSEIF (CellGetN( cCube , sYear , smonths , sOrderType, sRegions, '1', sBanners, 2ndUnitofMeasure, sOrderSource, sPostCode, sRouteCodes, sChannel, vShipmentNumber, sCustomerFCA, 'Substitute Data Element Here' ) <> 0;
  CellPutN( vCharge, cCube , sYear , smonths , sOrderType, sRegions, '1', sBanners, 2ndUnitofMeasure, sOrderSource, sPostCode, sRouteCodes, sChannel, vShipmentNumber, sCustomerFCA, 'Freight Costs' ) ; 
..........
ELSE IF (CellGetN( cCube , sYear , smonths , sOrderType, sRegions, '1', sBanners, nUnitofMeasure, sOrderSource, sPostCode, sRouteCodes, sChannel, vShipmentNumber, sCustomerFCA, 'Substitute Data Element Here' ) <> 0;
  CellPutN( vCharge, cCube , sYear , smonths , sOrderType, sRegions, '1', sBanners, nUnitofMeasure, sOrderSource, sPostCode, sRouteCodes, sChannel, vShipmentNumber, sCustomerFCA, 'Freight Costs' ) ; 
ENDIF;

[eddoria]

Thanks for the response.

You are right in that I do not have the UOM. I will give the code a go.

Re the number of dimensions. The user wanted to be able to slice and dice by many different criteria.

REgards

Vera Hawkins

[lotsaram]

If the UOM isn't mapped then your best option would be to create an attribute to store this and do the looping as Tomok suggested but IN ADVANCE in order to set the attribute then in the load just lookup the UOM via the attribute.

Doing the loop during the data load itself is massively redundant and won't give the best performance.