Cube to Cube Consolidation

[vasek1192]

Hi, I am attempting to transfer data from cube A_VNO_Zdroj into cube A_VNO_Marze_Mio. A_VNO_Marze_Mio has one less dimension - A_VNO_T_Cas_M. Data I need to transfer is allocated to elemnt Marze in dimension A_VNO_M_Zdroj.

Help2.PNG (11 KiB) Viewed 2363 times

I attempted to do it by creating temporary View made out of just the two necessary dimensions in Prolog in TI and then use CellPutN in Data. But it always inserts the data just in the first cell in the Target Cube. Please help. Code is as follows.

Help1.PNG (24.38 KiB) Viewed 2363 times

Prolog:

Code: Select all

SourceCube = 'A_VNO_Zdroj';
TargetCube = 'A_VNO_Marze_Mio';

SubsetName = 'TMP_Marze';
ViewName = 'TMP_Marze';
TempFlag = 1;

G_Rada_Filter = '{TM1SUBSETALL( [A_VNO_G_Rada] )}';
M_Zdroj = '{TM1FILTERBYPATTERN( {TM1SUBSETALL( [A_VNO_M_Zdroj] )}, "Marze")}';

SubsetCreatebyMDX(SubsetName, G_Rada_Filter, TempFlag);
SubsetCreatebyMDX(SubsetName, M_Zdroj , TempFlag);

ViewCreate(SourceCube, ViewName, TempFlag);
ViewSubsetAssign(SourceCube, ViewName, 'A_VNO_G_Rada', SubsetName);
ViewSubsetAssign(SourceCube, ViewName, 'A_VNO_M_Zdroj', SubsetName);

Data:

Code: Select all

CellPutN(Value, TargetCube, A_VNO_G_Rada, 'Marze');

[paulsimon]

Hi

There are a few things that could be improved.

I note that the Source and Target cubes only have one dimension in common, as well as the Source having an extra dimension.

You are reading from a cube that has one more dimension than the target but you are not specifying the source element in the A_VNO_T_Cas_M dimension

You need to create a subset on that. Presumably it needs to select the top level consolidation in that dimension as I am guessing that you just want the total for that dimension?

Using MDX is certainly one way of defining a subset. Here is another:

Code: Select all

vDim = A_VNO_T_Cas_M' ;
vSub =  SubsetName;
vElem = Whatever the top level consolidation is called ;

IF( SubsetExists( vDim , vSub ) = 0 ) ;
  SubsetCreate( vDim , vSub , 1 ) ;
ELSE ;
  SubsetDeleteAllElements( vDim, vSub ) ;
ENDIF ;

SubsetElementInsert( vDim, vSub, vElem , 1 ) ;

When defining a view you should always specify the ExtractSkip values. The default values have changed over time so it is always best to specify them even if only for documentation. In your case, you probably need to set SkipCalcs (actually means skip consolidations) to 0 as you will need consolidated values from the extra dimension - A_VNO_T_Cas_M

vCube = SourceCube ;
vView = ViewName ;

IF( ViewExists( vCube, vView ) = 1 ) ;

ViewDestroy( vCube, vView ) ;

ENDIF ;

ViewCreate( vCube, vView, 1 ) ;

ViewExtractSkipZeroesSet ( vCube, vView, 1 ) ;
ViewExtractSkipCalcsSet ( vCube, vView, 0 ) ;
ViewExtractSkipRuleValuesSet ( vCube, vView, 1 ) ;

Your subset on G_Rada_Filter needs to change. With a view reading from consolidated levels, you will get every consolidated level which will give too many records and you cannot write to a consolidation.

It is probably best to define a permanent subset. In the Subset Editor just record yourself doing a Subset All followed by a filter for Level 0, and add on an A-Z sort You should get something like this:

Code: Select all

{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [A_VNO_G_Rada] )}, 0)}, ASC)}

Save the subset as A_VNO_G_Rada_base (Substitute 'base' for whatever makes sense in your language)

You then need to assign the subsets using this bit of code

vDim = 'A_VNO_G_Rada';
vSub = 'A_VNO_G_Rada_base' ;
ViewSubsetAssign( vCube , vView , vDim, vSub ) ;

vDim = 'A_VNO_T_Cas_M'';
vSub = SubsetName ;
ViewSubsetAssign( vCube , vView , vDim, vSub ) ;

vDim = 'A_VNO_M_Zdroj'';
vSub = SubsetName ;
ViewSubsetAssign( vCube , vView , vDim, vSub ) ;

You didn't show your Source tab so the following will ensure that the source is the temp view

DataSourceType = 'VIEW' ;
DatasourceNameForServer = vCube ;
DataSourceCubeView = vView ;

When writing to a cube, you should always clear data first

You therefore need to define a view on the target cube like this. In this case you do want to skip consolidations, since you will be writing at the base level and you cannot clear a consol anyway.

Code: Select all

vCube = 'TargetCube ;
vView = ViewName ;

IF( ViewExists( vCube, vView ) = 1 ) ;

  ViewDestroy( vCube, vView ) ;

ENDIF ;

ViewCreate( vCube, vView, 1 ) ;

ViewExtractSkipZeroesSet ( vCube, vView, 1 ) ;
ViewExtractSkipCalcsSet ( vCube, vView, 1 ) ;
ViewExtractSkipRuleValuesSet ( vCube, vView, 1 ) ;

You will need to assign a subset for 'Marze' in the A_VNO_M_Marze_Mio dimension so that you only clear that part of the cube. You cannot re-use the subset created above as this is a different dimension - A_VNO_M_Marze_MIO. Just adapt the code that I provided above for creating the temp subset.

Then you do the following. The first statement turns off logging so you don't generate a large log file. The second clears the target area of the cube. This is so that if data changes, it does not get left around in the target cube.

Code: Select all

CubeSetLogChanges( vCube , 0 ) ;
ViewZeroOut( vCube , vView ) ;

In the Epilog put the following to turn logging back on again.

Code: Select all

CubeSetLogChanges( TargetCube , 1 )

Looking at your Variable names you seem to have a space in them but I cannot tell if the underscore has been lost in the image. They should have an underscore like your image of server explorer.

Your Data Tab is OK. However, if you subsitute the hard-coded 'Marze' with the variable A_VNO_M_Zdroj then it will be more flexible in future if you want to adapt this process to transfer something other than Marze. For example you could define a parameter for whatever Marze is, presumably a month, and reference that in the source and target subsets.

Regards

Paul Simon

[vasek1192]

Thanks a lot for the reply.

I implemented some of the changes you suggested. I did not change the subset on G_Rada_Filter because it is a flat hierarchy and there are no consolidations. I implemented the ExtractSkip values and DataSOurceType parts. I did not implement the Logging since it is just a practice exercise and it has a tiny size. I will implement the target view to practice, but for now, if I understood it correctly the reason my script does not work is that I did not assign subset for A_VNO_T_Cas? And I need to assign consolidated subset at highest possible hierarchy (total)? If so, how should I proceed if the A_VNO_T_Cas is flat hierarchy also (no consolidations viz picture)?

[paulsimon]

Hi

Either add a consolidation on top of your Cas dimension and implement my original suggestion
Or
Change your CellPutN to CellIncrementN, as you will be getting multiple rows from your data source and you need to add them all together in the target cube

Regards

Paul Simon

[vasek1192]

Thanks, I tried CellIncrementN before. It correctly sums the Marze for the the first element of the A_VNO_G_Rada but it leaves the others blank.

zdroj.PNG (30.9 KiB) Viewed 2285 times

Code: Select all

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


SourceCube = 'A_VNO_Zdroj';
TargetCube = 'A_VNO_Marze_Mio';

SubsetName = 'TMP_Marze';
ViewName = 'TMP_Marze';
TempFlag = 1;

G_Rada_Filter = '{TM1SUBSETALL( [A_VNO_G_Rada] )}';
M_Zdroj_Filter = '{TM1FILTERBYPATTERN( {TM1SUBSETALL( [A_VNO_M_Zdroj] )}, "Marze")}';
Cas_M_Filter = '{TM1SUBSETALL( [A_VNO_T_Cas_M] )}';


SubsetCreatebyMDX(SubsetName, G_Rada_Filter, TempFlag);
SubsetCreatebyMDX(SubsetName, M_Zdroj_Filter , TempFlag);
SubsetCreatebyMDX(SubsetName, Cas_M_Filter , TempFlag);

ViewCreate(SourceCube, ViewName, TempFlag);
ViewSubsetAssign(SourceCube, ViewName, 'A_VNO_G_Rada', SubsetName);
ViewSubsetAssign(SourceCube, ViewName, 'A_VNO_M_Zdroj', SubsetName);
ViewSubsetAssign(SourceCube, ViewName, 'A_VNO_T_Cas_M', SubsetName);

ViewExtractSkipZeroesSet(SourceCube, ViewName, 1);
VIewExtractSkipCalcsSet(SourceCube, ViewName, 0);
VIewExtractSkipCalcsSet(SourceCube, ViewName, 1);

DataSourceType = 'VIEW'; 
DatasourceNameForServer = SourceCube;
DatasourceCubeview = ViewName;

Code: Select all

CellIncrementN(Value, TargetCube, A_VNO_G_Rada, 'Marze');

[lotsaram]

I tried CellIncrementN before. It correctly sums the Marze for the the first element of the A_VNO_G_Rada but it leaves the others blank.

Code does what it is programmed to do. The CellIncrementN function is no exception and sums whatever it is instructed to.

If the target cube is only ending up with data in the "1" element for dimension G_Rada then either
* the source cube only has data for this element
* the source view only contains this element (or some combination of element in OTHER dimensions where only "1" contains data)
* the CellIncrementN is hardcoded to the "1" element
* you made some other mistake in your code

[paulsimon]

Vasek

In your Data tab put a

TextOutput( 'temp.txt', A_VNO_G_Rada ) ;

Run the process and then look at the output in temp.txt and you will see what data it is getting

Your screen shot only showed the source dimension in the title area. Is it possible that as Lotsaram says that you only have source data for element 1.

If all you are doing is experimenting, I would suggest that you use some more realistic names. However, an element called '1' in more than one dimension is going to get very confusing.

Regards

Paul