How to import a CSV file to a cube with two extra dimensions

Post Reply
vino1493
Posts: 30
Joined: Tue Mar 14, 2017 12:01 pm
OLAP Product: cognos tm1
Version: 10.2.2
Excel Version: excel 2013

How to import a CSV file to a cube with two extra dimensions

Post by vino1493 »

Hi All,

I'm trying to import a csv file into a cube with 6 dimensions, whereas the source CSV file only has data for 4 dimensions.
The business logic is load the data to all the members in the remaining two dimensions.

For this I'm using a clustered while loop, the first while loop to go over the leaf level elements of fifth dimension and the second while to go over the leaf level elements of sixth dimension. Then I use "CELLPUTN" to input data to measure.

I find this hinders the performance very much. For even a small amount of data, the TI process takes a minute to complete.

Is there any other workaround for this? If yes, please do let me know. Thank you very much.


-Vino
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: How to import a CSV file to a cube with two extra dimensions

Post by Wim Gielis »

Please explain the logic to put the same value on every cell on a quadrant, being the multiplication of x * y cells. Not: x + y cells but x * y cells.
x leaf level elements from the 5th dimension, y leaf level elements from the 6th dimension.
What is the reasoning ?
You could also load the value in 1 spot and have a rule retrieve that value to show it in the other cells ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: How to import a CSV file to a cube with two extra dimensions

Post by tomok »

Whoever made the decision to load to all the members of the non-represented dimensions does not understand TM1 and the consequences of that. Standard practice is to create a member in each of these dimensions called "No account", or "No Cost Center", etc., and load the data there.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
vino1493
Posts: 30
Joined: Tue Mar 14, 2017 12:01 pm
OLAP Product: cognos tm1
Version: 10.2.2
Excel Version: excel 2013

Re: How to import a CSV file to a cube with two extra dimensions

Post by vino1493 »

Wim Gielis wrote: Fri Dec 14, 2018 4:20 pm Please explain the logic to put the same value on every cell on a quadrant, being the multiplication of x * y cells. Not: x + y cells but x * y cells.
x leaf level elements from the 5th dimension, y leaf level elements from the 6th dimension.
What is the reasoning ?
You could also load the value in 1 spot and have a rule retrieve that value to show it in the other cells ?
@Wim Gielis ... I like the idea of loading the value in one dummy combination and load the same for other combinations. But is this possible using a rule file?

I know if I go for TI, again I will have to use WHILE loop.
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: How to import a CSV file to a cube with two extra dimensions

Post by Mark RMBC »

Hopefully vino will put us out of our misery and tell us the logic behind this!

I saw something like this once where within TM1 there was a need to reconcile 2 different TM1 models because data was transferred between the 2 models and for whatever reason the organisations chart of accounts looked different in the 2 models (both models had say 6 account key dimension but only 4 of them matched each other and only one of them matched the actual general ledger)

In order to reconcile the 2 models the % of the 6 part to the 4 part was calculated for one model and the values were then apportioned back to the 6 part in the other model! Yes the world out there can be that crazy!

I think in the end the values were exported to excel and the reconciliation was done there instead of TM1.

I hasten to add that I had nothing to do with this model, other than looking at it incredulously!
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: How to import a CSV file to a cube with two extra dimensions

Post by Mark RMBC »

Hi,

I did not post the above comment into this post yesterday! I am pretty sure I posted this back in December!

Is there a TM1forum forum!

cheers, Mark
Post Reply