CubeCreate in TI

[shorsted]

Hi

I'm looking to see if there is a short cut to what I am trying to do as I feel like I'm going the long way around things

I need to create several new cubes which are replicas of other cubes but with a suffix on them. I know I can do this easily enough by copying the .cub files and renaming them which would give me fully populated cubes but with the same dimension names as the original cubes. However I also want to attach a suffix to some of the dimension names.

eg:
CubeName
Dim1
Dim2
Dim3
Dim4

copies to:
CubeName_New
Dim1_New
Dim2
Dim3_New
Dim4

The only way I can think to do this is to create the new dimensions (again I know I can do this with the .dim files), create the new cubes and then export and import the data

Surely there must be a simpler way?

I did start doing this the long way:

1.by creating a dimension that lists the cubes which I need to copy and also one which lists the dimensions that need changing in the new cubes.

2.I've then created a process which loops through the cubes and exports all their data to individual files (I did this basing each one on the max dim size I have of 10, therefore I always export 11 variables (10 + 1 Data) but in some cases the file will have emoty values for the last few variables. This is therefore my export files

3.I've also created a process which loops though the cubes and creates then new cubes using the new dimensions

4.So the last stage is getting the data in, the only way I can think to do this is creating a separate process for each new cube

If I could simply copy the .cub files and somehow change the internal referencing to the new dimensions without how to move data around, that would be simplest, failing that if I could combine my steps above into one easier process that would be better too

Thanks!

Regards
Sarah Horsted
TM1 9.1.4 FP1 64bit

[Steve Vincent]

Sarah, i do have some stuff which may help as i've had to do exactly the same thing but i don't have the time to post and explain it all now. Part of my grand plan (and the solution to the last part of your post) was foiled due to a bug in 9.0 which was solved "somewhere" in 9.1 but cognos couldn't tell me where. I'll share my work when i can, but there is hope

[David Usherwood]

A bug?! In 90?! (Pictures a somewhat updated HM Bateman cartoon)
I'd be very interested to know what it was.

[shorsted]

Steve

Thanks I'll look forward to that, in the meantime I'll continue with my long winded approach rationalising where possible. I will have to do this all again at the end of Dec so if you manage to post the details by then that would be great!

Regards
Sarah

[Alan Kirk]

A bug?! In 90?! (Pictures a somewhat updated HM Bateman cartoon)

Buh???

I'd be very interested to know what it was.

I believe that Steve refers to this:
http://forums.olapforums.com/viewtopic.php?f=18&t=256

I haven't reproduced it myself but then... I don't have any reordered cubes either so it's not something I've gotten around to yet.

[David Usherwood]

Thanks Alan.
Since we follow Joe Puztai's 'Best Practices' recommendation to use cube resequencing extensively, we'll watch out for this. I haven't seen it myself, and 90% of our clients are on 90 SP3.

[Steve Vincent]

thanks alan, that's the one. things are manic here right now, hope to get 10 mins towards the end of next week when i can post what i have

[Steve Vincent]

Sorry for the delay, time is something I’m struggling to get hold of these days :/

I’ll split this in to parts but here is the summary of what I started;

• 1st TI cycles thru named cubes and recreates the dims each one needs with a prefix determined by a user prompt.
• 2nd TI uses same list to create the cubes with the same prefix.
• 3rd TI cycles thru all the dims and where the prefix matches the one prompted, copies all the elements across.
• 4th TI populates the attributes for the archived dimensions.

I then wrote 1 TI that should have worked to copy the cubes data but due to the issues I found with this version (and Applix managed to replicate it) I never found if it worked our not.

Time permitting I’ll post what I can with as best explanation as possible. I’m sure some of it is sloppy and can be improved but it was a “it works so live with it” solution rather than a perfect one.

[Steve Vincent]

2nd attempt after my PC threw a wobbly and I lost all my previous post before I could submit it

This is the 1st TI in my batch of processes. It cycles through a list of cubes and creates all the dimensions they need, all prefixed with a value as determined when it is executed.

Process Name: _Arc01_CreateDims

Datasource
Type: TM1 Dimension Subset
Name: }Cubes->CubesToArchive

NB: the subset won’t exist yet, the TI creates it for you. The name above can be pasted in to the box in order for it to work the first time.

Variables:
Name: CubeName
Type: String:
Contents: Other

Parameters:
Name: Prefix
Type: String
Default value: »enter new cube prefix«
Prompt: »enter new cube prefix«

Prolog:

Code: Select all

#if our subset already exists, destroy it to ensure it’s a clean start
IF ( SubsetExists ( '}Cubes' , 'CubesToArchive' ) = 1);
    SubsetDestroy ( '}Cubes' , 'CubesToArchive' );
EndIF;

#create a blank subset
SubsetCreate ( '}Cubes' , 'CubesToArchive' );

#insert as many elements to the subset as necessary
#each one should refer to a cube which you intend to archive
SubsetElementInsert ( '}Cubes' , 'CubesToArchive' , 'Actuals' , 1);
SubsetElementInsert ( '}Cubes' , 'CubesToArchive' , 'Age Analysis' , 2);
SubsetElementInsert ( '}Cubes' , 'CubesToArchive' , 'Age Profile' , 3);
SubsetElementInsert ( '}Cubes' , 'CubesToArchive' , 'Demand' , 4);
SubsetElementInsert ( '}Cubes' , 'CubesToArchive' , 'Ops Plan' , 5);
SubsetElementInsert ( '}Cubes' , 'CubesToArchive' , 'Ops Plan and Actuals' , 6);
SubsetElementInsert ( '}Cubes' , 'CubesToArchive' , 'Starters and Leavers' , 7);
SubsetElementInsert ( '}Cubes' , 'CubesToArchive' , 'Utilisation Parameters' , 8);

#used on 1st run of process only or to help with debugging
#ProcessQuit;

Metadata:
Blank

Data:

Code: Select all

#set new cube name using the prefix entered by user
NewCubeName = Prefix | ' ' | CubeName;

#determine if new cube already exists
IF ( CubeExists ( NewCubeName ) = 1);
#if exists, ignore the cube
    ItemSkip;
Else;
#if doesn't exist, continue

#assume a max number of dims per cube is 16 as we 
#cannot count the value with any TM1 expressions
DimMax = 16;
DimNow = 1;

#cycle thru a max of 16 times
While ( DimMax >= DimNow );
    DimName = TabDim ( CubeName , DimNow );
    NewDimName = Prefix | ' ' | DimName;

#if tabdim is returning blanks, skip checking this cube
    IF ( DimName @<> '' );

#check to see if the new dim has already been created
        IF ( DimensionExists ( NewDimName ) = 1);
#skip it if its already been created
        Else;
#otherwise create the new dim
            DimensionCreate ( NewDimName );
        EndIF;

        DimNow = DimNow + 1;


    Else;
#stop checking this cube and go to next one
        DimNow = DimMax + 1;

    EndIF;

End;

EndIF;

Epilog:

Code: Select all

#run the next process
ExecuteProcess ( '_Arc02_CreateCubes' , 'Prefix' , Prefix );

Instructions:
• Copy all the code in to the relevant tabs.
• In the prolog tab, where the list of “SubsetElementInsert” lines are change the final sections to the names of the cubes to be archived (ie. Replace ‘actuals’ with your own cube name etc).
• Insert / comment out as many of those lines as necessary.
• Uncomment the process quit call at the end of the prolog then run it.
• This creates the subset but nothing more. It allows you to edit variable names and help debugging as TI doesn’t like saving with no data source.
• Once the variable details have been set, comment out the processquit line again.
• If you wish to run this alone, also comment out the line in epilog.
• When you run the TI, it will prompt for a prefix. In our case we prefix them numerically (10 Actuals, 11 Actuals, 12 Actuals, 13 Actuals etc) but it shouldn’t matter what you use. The TI will put a space in between the prefix and cube name.
• It then assumes a max number of dimensions per cube of 16 (easy to change in the data tab) and cycles through each one to find the dims that make up each cube.
• It then creates empty dims, skipping them if it’s been created already.

Results:

Original Cube – Actuals.
Dims – period, dept, tag, measure, site, name

Results of the TI if the prefix is “13” are the dimensions 13 period, 13 dept, 13 tag, 13 measure, 13 site, 13 name.

At this point they are not populated or part of any cube, that’s in the next TI.