Advice on how to consolidate data within a dimension

[ChrisF79]

Hello:

We just recently combined warehouses into one and we want to consolidate our historical inventory data. The idea is that we have a dimension with USA - 5029, USA - 5028, USA - 39287, etc. as our old inventory locations. Now they're all USA - Consolidated. Would it be best to write a TM1 turbo integrator process to do this? If so, is this where the DB() formula is going to come into play? It's been so long since I've done this so I appreciate any help you can provide. Thanks!

[Wim Gielis]

Hello there

Assuming you want to copy your data to the (n) element USA - Consolidated, and then remove the individual USA elements.
You will want to write a TI process, yes.

But first export the data in the cube to a text file: right-click the cube name, then Export as text data... and in the particular dimension, filter on those elements starting with USA -

Then, the text file becomes the data source in a new process. You could even use the wizard of TI to create the process.

Load the data cumutatively, meaning a combination of a CellGetN and a CellPutN in the Advanced > Data tab of the process.

The DB() formula is inherent to rules, that is not what you will want to use in this case.

Wim

[ChrisF79]

Thank you so much for the advice. I hadn't thought about exporting it to a text file. Wouldn't it be possible to create a view in the existing cube and use that as the data source instead? If I do go the text file route, would I need to set every dimension to their N level elements? As an example, I have a dimension for item numbers. Can I set it to the consolidated element "All Item Numbers" or do I need to go down to the n level? Thanks again for all the help!

[Wim Gielis]

You can work with a view too. Either manually creating the view, either with code. That can be the data source for your process.

Most logical would be to use the same (n) elements in all other dimensions except the one in which you consolidate. You can change the level of detail in other dimensions as well, but then you have to decide what new (n) elements to use in these dimensions. Possibly creating new elements in some dimensions, there is a Metadata tab in the process

Wim

[ChrisF79]

I'm back to this now and making the process as we speak. So if I use the Cellgetn and Cellputn to move the data, it's really just copying it from one location to the other. Would I just do a second Cellputn to set the old location to zero after the move? Also, is there a way to set it to Null instead?

[Wim Gielis]

After you copied the data, and yes I stress, AFTER , you do:

VIEWZEROOUT('nameofthecube','nameoftheview');

That statement could come for instance in the Epilog tab of the process, but surely not in the Data tab.

Wim