Exporting data from cube and build a new dimension

[Kazêh]

hi all, i want to export some data from a cube with 2 dimensions, and create a new dimension from that data.

Cube1 has the following dimensions:

1.- Elements ( 1,2,3,4..........400)
2.- Measures( Hijo, Padre )

as you already know, if you want to create a dimension from a txt file you need the data in columns:

Elements Hierarchy
TOTAL
A TOTAL
B TOTAL
C TOTAL

Here is the real example:

http://www.subirimagenes.net/i/140603090557515655.png

so , when i try to export the data i got this:


"SERVER:Dimensiones","1","Hijo","TOTAL CENTROS RESPONSABILIDAD"
"SERVER:Dimensiones","2","Hijo","DIRECTO"
"SERVER:Dimensiones","2","Padre","TOTAL CENTROS RESPONSABILIDAD"
"SERVER:Dimensiones","3","Hijo","0001000300"
"SERVER:Dimensiones","3","Padre","DIRECTO"
"SERVER:Dimensiones","4","Hijo","INDIRECTO"
"SERVER:Dimensiones","4","Padre","TOTAL CENTROS RESPONSABILIDAD"
"SERVER:Dimensiones","5","Hijo","0001000200"

now, somehow i need to write a script in turbo to create some colums and create de dimension, identifying who is the element and its consolidation.
this is the first time trying to do this and i'm a little lost on this... I will appreciate any tip to help me solve this problem

Thanks
Kazeh.

[jim wood]

Guessing by what you have this is a standard parent child file. In TI just set one variable as element and one as consolidation. If you then make your way through the tabs it should be pretty plain sailing,

Jim.

[declanr]

If I understand your post correctly I don't see why you need to export to a text file.

Just set your ti datasource as a cube view which only pulls the "hijo" measure and is zero suppressed then put the following in the metadata tab:

Code: Select all

DimensionElementInsert ( sDimName, '', vHijo, 'n' );
sParent = CellGetS ( sCubSource, vElementos, 'padre' );
If ( sParent @<> '' );
            DimensionElementComponentAdd ( sDimName, sParent, vHijo, 1 );
EndIf;

[Kazêh]

thanks for the reply declanr.

i save my view with only the "Hijo" as datasource, but what should i put in the variables tab, anda parameters?
the truth is i have never use turbo integratos for this kind of this, only to load data and that kind of stuffs.
excuse my ignorance, but could you tell what to write in the variables tab, and the parameters tab?

again, sorry for my ignorance.

[declanr]

The image you posted didn't show the full cube structure but if it's just 2 dimensional then I would name the variables vElementos and vHijo (setting them both as "String" and "Other"), leave the parameters empty.

The in the prolog put:

Code: Select all

sDimName = 'xxx';
sCubSource = 'yyy';

If ( DimensionExists ( sDimName ) = 0 );
                 DimensionCreate ( sDimName );
EndIf;

Where xxx is the name of the dimension you want to build and yyy is the name of the cube you've used as your source. You can probably make it easier on yourself by just using the wizard instead of writing the code but I haven't used the wizard for anything in years so am rusty on remembering what you need to select for it.

[Kazêh]

Declanr, i did it, thank you very much.
I just put the code and make litle modifications, and it works perfectly.
Again , thanks for the patience.

Kazeh.

[declanr]

Declanr, i did it, thank you very much.
I just put the code and make litle modifications, and it works perfectly.
Again , thanks for the patience.

Kazeh.

No worries, we all start somewhere. Glad it worked for you.

[Kazêh]

declanr, just one more question, if i wanted to create an atribute from the cube data, the "Alias" , how should i put it?

fiirts i need to create another var in the measure dimension named "Alias" so you can put the text there.
In one column you have the elements from the dimension, the second one had the Hierarchy, and the last one the alias.

i was readding about it and i found that i need attrinsert to make this happent.

here is the code:


Prolog

Code: Select all

#****Begin: Generated Statements***
#****End: Generated Statements****

ATTRINSERT('Centros de Responsabilidades 2.0','', 'Alias', 'A');
DIMENSIONDELETEALLELEMENTS('Centros de Responsabilidades 2.0');
sDimName = 'Centros de Responsabilidades 2.0';
sCubSource = 'Dimensiones';

If ( DimensionExists ( sDimName ) = 0 );
                 DimensionCreate ( sDimName );
EndIf;

with this i create the alias for the dimension.

Metadata

Code: Select all

 
#****Begin: Generated Statements***
#****End: Generated Statements****


sAtrib=CellGetS (sCubSource,vElementos,'alias');


DimensionElementInsert ( sDimName, '', vHijo, 'n' );
sParent = CellGetS ( sCubSource, vElementos, 'Padre' );
If ( sParent @<> '' );
            DimensionElementComponentAdd ( sDimName, sParent, vHijo, 1 );
EndIf; 

and here im trying to follow the same logic to get the data in "sAtrib" from the alias column...i dont really know if this is ok.


the thing is that i dont know how to put the sAtrib in the functions "Attrputs" to create the alias in the dimension.
I dont know if you understand me.

that's the last question, if you can let me a hand again,i would appreciate it.

Thanks.