Referencing a lookup cube or Attribute in a TI Process

[KaneHenderson]

Hi all,

I am in the process of building out a TI Process to transfer data from one cube to another. The source cube has 9 dims and the target has 10 dims. The 9 dims are the same across both cubes, with the additional dimension requiring mapping in the TI Process - the sub function dimension

I have a mapping cube that holds the relevant mappings that i need to map the data by, however the problem with this is the format of the cube.

For example, in my mapping cube, i have elements 1-10000 with a flat dimension holding all the elements containing the relevant columns of mapping. I had to use a numbered list because the elements that require mapping are not unique.

Cube structure
Cost Centre Sub Function
1 XXXXXX XXXX
2
3
4

My two ideas are:
Create attributes from my mapping cube by using the CC and Sub Function, mapping in a Sub Function attribute using a TI Process

Then using attributes populate the cube using a IF Statement in the Data tab of the TI Process

Or

Build Subsets and maintain them and refence the subsets in the CellPutN to load the relevant Data points.

Is there perhaps a more dynamic solution that i am overlooking?

I'm also having issues with my TI Process, it runs, but doesn't populate the data for elements with an attribute of other as an example:

IF(ATTRS('006 Main - Cost Centre','!006 Main - Cost Centre,'Sub Function')@='Other');
CellPutN( nValue, tCube, pVersion, vPeriod, vYear, vArea, vCC, vFunction , vGL,vTBLine, vCurrency, vRate, pMeasure);
ENDIF;

Any assistance would be greatly appreciated - thanks!!

[PavoGa]

A couple of questions:

I count 11 dimension variables in the CellPutN. Were you including the measure in the ten dimensions mentioned for the target cube?

Second, what variable in the CellPutN is dependent upon the Sub Function value?

What is this: '!006 Main - Cost Centre? Bang notation does not work in TIs. Perhaps snippets of the actual code would be better? And a few example lines of the mapping cube?

[KaneHenderson]

Hi Pavo,

Thanks for the reply.

I have actually have 10 dims in my source cube and 11 in my target cube - apologies! vValue holds the physical value.

Here is a extract of the variables i am creating in the prolog:

#===========================================================
# Set Variables
#===========================================================

# General
sCube = '05 TB - TB Oracle Data';
tCube = '05 TB - TB Oracle Data - IH Reporting by Function & Sub Function';

#vCCName ='006 Main - Cost Centre - IH Reporting';
vCCElements='!006 Main - Cost Centre';
vAttribute = 'Sub Function';

vFunction = 'OTHER';

sDimNmVR = '001 Main - Versions';
sDimNmPR = '002 Main - Period';
sDimNmYR = '003 Main - Years';
sDimNmAR = '004 Main - Area';
sDimNmCC = '006 Main - Cost Centre';
sDimNmGL = '007 Main - GL Codes';
sDimNmTB = '051 TB - Balances';
sDimNmCU = '021 FX - Currencies';
sDimNmFX = '022 FX - Rates';
sDimNmME = '009 Main - Measure';

nDataFirstRecord = 1;
nHardCopy = 0;

I then create the source view and create the clear view.

I was playing around with the attributes refence, he is what i was using previously:

IF(ATTRS(vCC,vCCElements,vAttribute)@='Other');
CellPutN( nValue, tCube, pVersion, vPeriod, vYear, vArea, vCC, vFunction , vGL,vTBLine, vCurrency, vRate, pMeasure);
ENDIF;

Good to know you can't use the same notation as rules!

Log Number Sub Function Cost Centre
1 PLANNING & WORKFLOW 9004605
2 BASELINE 95001100
3 BASELINE 95003350

The Sub Function and Cost Centres are held as elements which is causing me a little difficulty.

Apologies struggling to add screenshots....

[MarenC]

Hi,

Code: Select all

vCCElements='!006 Main - Cost Centre';

What is this meant to be?

Because when you use this:

Code: Select all

IF(ATTRS(vCC,vCCElements,vAttribute)@='Other');

You are asking is the attribute for an element called !006 Main - Cost Centre equal to other.

I suspect you really want to ask what the attribute for the elements in the cost centre dimension are as you loop through the records.

In which case this may produce better results:

Code: Select all

IF(ATTRS(sDimNmCC,vCC,vAttribute)@='Other');

Given you didn't know that rules notation couldn't be used in TI's I cannot account for any other basic issues you may have!

Maren

[KaneHenderson]

Hi Maren,

Thank you for the response. Your advice worked and my process now works - thank you again!

The majority of work i have been doing in the past with TI's has been cube to cube data transfers where there has never been a reason to use attribute references or anything similar... so thank you for assisting.

Kind Regards,
Kane

[PavoGa]

Hi Pavo,

Thanks for the reply.

I have actually have 10 dims in my source cube and 11 in my target cube - apologies! vValue holds the physical value.

Here is a extract of the variables i am creating in the prolog:

#===========================================================
# Set Variables
#===========================================================

# General
sCube = '05 TB - TB Oracle Data';
tCube = '05 TB - TB Oracle Data - IH Reporting by Function & Sub Function';

#vCCName ='006 Main - Cost Centre - IH Reporting';
vCCElements='!006 Main - Cost Centre';
vAttribute = 'Sub Function';

vFunction = 'OTHER';

sDimNmVR = '001 Main - Versions';
sDimNmPR = '002 Main - Period';
sDimNmYR = '003 Main - Years';
sDimNmAR = '004 Main - Area';
sDimNmCC = '006 Main - Cost Centre';
sDimNmGL = '007 Main - GL Codes';
sDimNmTB = '051 TB - Balances';
sDimNmCU = '021 FX - Currencies';
sDimNmFX = '022 FX - Rates';
sDimNmME = '009 Main - Measure';

nDataFirstRecord = 1;
nHardCopy = 0;

I then create the source view and create the clear view.

I was playing around with the attributes refence, he is what i was using previously:

IF(ATTRS(vCC,vCCElements,vAttribute)@='Other');
CellPutN( nValue, tCube, pVersion, vPeriod, vYear, vArea, vCC, vFunction , vGL,vTBLine, vCurrency, vRate, pMeasure);
ENDIF;

Good to know you can't use the same notation as rules!

Log Number Sub Function Cost Centre
1 PLANNING & WORKFLOW 9004605
2 BASELINE 95001100
3 BASELINE 95003350

The Sub Function and Cost Centres are held as elements which is causing me a little difficulty.

Apologies struggling to add screenshots....

So one more question: If Sub Function is not equal to Other, what happens?

I see @MarenC helped you get past the immediate step, but the original request was if there was something more dynamic. Would like to understand the extended logic. Got a few things that will help you out going forward.

[KaneHenderson]

Hi Ty,

Thanks for the reply and apologies for the delay in getting back to you.

I have missed a part of the code in my copy of the TI Script:

vFunctionUnmapped='Unmapped';
IF(ATTRS(vCC,vCCElements,vAttribute)@='');
CellPutN( nValue, tCube, pVersion, vPeriod, vYear, vArea, vCC, vFunctionUnmapped , vGL,vTBLine, vCurrency, vRate, pMeasure);
ENDIF;

It posts the data against an unmapped misc bucket!

Keen to hear what you've got in mind.

Thanks,
Kane