Load data from another cube

[Rashed]

I am trying to create a new cube that has slightly different structure to the old one, e.g. removing a few dimensions and adding a few new ones. In order to move the data across to the new cube, I have built a process that loads data from an existing view of the old cube. Even through the view seems to be a small one (only 2-3% to the actual cube), TM1 crashes with Memory crisis error. Just wondering if there is any other efficient way of transferring data across to a new cube. Help would be appreciated.

[par3]

Try exporting the cube data into a Ascii file either by using TI or using the export to Ascii function when you right click on a cube.

[Alan Kirk]

I am trying to create a new cube that has slightly different structure to the old one, e.g. removing a few dimensions and adding a few new ones. In order to move the data across to the new cube, I have built a process that loads data from an existing view of the old cube. Even through the view seems to be a small one (only 2-3% to the actual cube), TM1 crashes with Memory crisis error. Just wondering if there is any other efficient way of transferring data across to a new cube. Help would be appreciated.

No, ordinarily I'd say going with a cube view as the source would be the most efficient way.

You haven't given much information on your platform, though. I'm guessing that you're on 32 bit? Which version? How close to the limits of the server memory are you normally? Are you exporting rule or consolidation-driven values, or N levels? (I'm thinking that if they're consolidations or rules, the calculation of the values (which are calculated "on demand") could be pushing your server over its limit.) Have you tried writing those values out to a text file using ASCIIOutput, and if so, do you still get a crash?

If you really are too close to the limit of your memory, the only suggestion that readily comes to mind is to try unloading all of the other cubes from memory before you run the export process, if that option is available to you.

[Rashed]

Thanks guys for you prompt response. I am using Tm1 9.1 on a 32 bit windows 2003 server. The total memory size is 3.7 GB and I have monitored the memory usage to reach around 3GB before crashing. I have also tried using ASCII output, but with not much help. I am loading only one measure element and it does not have any rules associated with it.

The dimension structure is as follows,

Version - only 5 elements - laoding only one, Actual.
Year - only 4 elements - loading only one, FY09
Location - 100 elements - loading all of them
Salesperson - loading one, n/a
Customer - loading one, n/a
Period - 53 elements - loading all
product - 11000 elements - loading all
measure - 30 elements - loading one, Retail Sales

So all I am trying to load is retail sales (actual) of all the products for all the stores.

[Steve Vincent]

might help if you can show us the dims that make up the source and destination cubes, along with your TI code. It sounds like the TI is in a loop but it's hard to know without seeing the whole picture. If you have some differeing dims between the 2 cubes then you must be telling TI to do something other than just copy from cube a to cube b, so it is possible a small error in the code is doing it.

[Steve Rowe]

Rashed,
This could be expected behaviour.

If the server is running close to the 3GB mark before hand and you try and add more data to this system then TM1 will shut itself down when it hits the 3G mark anyway.

With 32 bit TM1 on a 32 bit box there is no way to address more than 3GB of RAM on a single TM1 instance.

You may be able to achieve what you want by linking your new cube to the old cube using rules as this may use less memory depending on how the system is being used, though this will still eventually crash the server if this is your problem.

From the information you've supplied and I could be wrong but it sounds to me that you are approaching the limits of what you can do with your current environment and you need to look at moving to 64bit and or optimising your current32 bit environment by looking at dimension order or archiving history or reviewig the application and seein what can be dropped.

Out of interest, which release of 9.1 are you on and how do you find the stabilty and performance? Have you moved to this from 9.0, how do they compare?

HTH

[Rashed]

Apologies for a late reply....Had to move my house (such a nightmare I had to go thru)...

I would have thought its a very simple piece of code since I am populating data from and to N levels. May I am doing something really stupid (:D)...Below are a few more details...

Old Cube (Merch2)
1. Version
2. Year
3. Location
4. Salesperson
5. Customer
6. Period
7. product
8. Merch Measures

New Cube (Merch3)
1. Version
2. Location
3. Year
4. Period
5. product
6. Product Status
7. Merch3 Measures

In order to migrate data, I created a view from the old cube with the dimesions having only N level values. Following is the TI codes I am using,

product_status = 'n/a';
measure_name = 'Retail Sales';
cube_name = 'Merch3';

NCellOldValue = CellGetN (cube_name, Version, Location, V2, Period, product, product_status, V3);
CellPutN (Value + NCellOldValue, cube_name, Version, Location, V2, Period, product, product_status, V3);

May be there is a better way of doing this, but all I am trying to do is get rid of 2 dimensions from Merch2 cube which no longer in use and add a new one. and subsequently by the help of this simple code transfer data across.

Once again, thanks very much for you replies...

[Rashed]

Oh...forgot to mention the version....TM1 9.1 SP3 (Build: 9.1.30000.340)

[ScottW]

Rasheed,

Version, memory, etc has nothing to do with your issue of why you aren't getting data into the second cube!

Why you aren't getting data into the cube is clear: if this is an exact copy/paste of the code in your TI, it is because you are copying FROM cube2 TO cube2 instead of FROM cube 1 TO cube2.

In effect you are "summarising away " the salesperson and customer dimensions. Unless you are picking only a specific customer/salesperson combination then what you need to do is copy data from "All Salespeople" and "All Customers." In TM1 you must specify an element for each and every dimension in a cube to query data, otherwise you will get nothing (except an error value.)

Your current process is set up on a view in your "to cube". If you want to process efficiently and skip null records then it must be set up in your "from cube." Your processing view extract should be for your selected N elements in the matching dimensions and for specific elements in the non-matching dimensions (i.e. All Customers & All Salespeople). The view should NOT skip calc values but should skip blanks.

This also gives a hint as to why the TI might be hanging (although this is speculative). As you are trying to run the process from the destination not the source you are probably not skipping blanks (how else would you know where a value should be in the source cube) this would explain why the process is taking such a long time to run and seems to be "hanging" the server.

[Rashed]

Thanks Scott....It worked!!!!