Any help would be much appreciated. I am guessing my source data are what the process is looking at on the data tab, but I could be wrong.
Code: Select all
PROLOG
# Declare Global Variables
NUMERICGLOBALVARIABLE( 'PrologMinorErrorCount' );
PrologMinorErrorCount = 0;
# Process;
sProcessName = GETPROCESSNAME();
### CLEAR OUT ALLOCATIONS
TimeStamp = TIMST( NOW, '\Y\m\d\h\i\s');
ZeroOutView = 'z' | sProcessName | '_ZeroOut_' | TimeStamp;
ViewName = ZeroOutView;
ZeroOutSubset = ZeroOutView;
sCube = 'bpmFinance';
bElement = 'Corp Allocations' ;
cElement = 'no_AFE';
dElement = pYear;
eElement = pScenario;
fElement = pPeriod;
SubsetName = ZeroOutSubset ;
IF ( VIEWEXISTS ( sCube , ZeroOutView ) = 1 ) ;
VIEWDESTROY ( sCube , ZeroOutView ) ;
ENDIF ;
VIEWCREATE ( sCube , ZeroOutView ) ;
# Book
sDim = 'bpmBook' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 ) ;
SUBSETDESTROY ( sDim , ZeroOutSubset ) ;
ENDIF ;
SUBSETCREATE ( sDim , ZeroOutSubset );
SUBSETELEMENTINSERT ( sDim , ZeroOutSubset , bElement , 0 );
VIEWSUBSETASSIGN ( sCube , ZeroOutVIew , sDim , ZeroOutSubset);
# bpmSubAccount
sDim = 'bpmSubAccount' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 );
SUBSETDESTROY ( sDim , ZeroOutSubset );
ENDIF;
SUBSETCREATEBYMDX ( SubsetName , '{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[bpmSubAccount].[ALL_SEGMENTS_AND_JVs]}, ALL, RECURSIVE )}, 0)}, ASC)}');
VIEWSUBSETASSIGN ( sCube , ZeroOutVIew , sDim , ZeroOutSubset);
# bpmAccount
sDim = 'bpmAccount' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 );
SUBSETDESTROY ( sDim , ZeroOutSubset );
ENDIF;
SUBSETCREATEBYMDX ( SubsetName , '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[bpmAccount].[Consolidated Net Income]}, ALL, RECURSIVE )}, 0)}');
VIEWSUBSETASSIGN ( sCube , ZeroOutView , sDim , ZeroOutSubset);
# AFE
sDim = 'bpmAFE' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 ) ;
SUBSETDESTROY ( sDim , ZeroOutSubset ) ;
ENDIF ;
SUBSETCREATE ( sDim , ZeroOutSubset );
SUBSETELEMENTINSERT ( sDim , ZeroOutSubset , cElement , 0 );
VIEWSUBSETASSIGN ( sCube , ZeroOutVIew , sDim , ZeroOutSubset);
# Year
sDim = 'bpmYear' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 ) ;
SUBSETDESTROY ( sDim , ZeroOutSubset ) ;
ENDIF ;
SUBSETCREATE ( sDim , ZeroOutSubset );
SUBSETELEMENTINSERT ( sDim , ZeroOutSubset , dElement , 0 );
VIEWSUBSETASSIGN ( sCube , ZeroOutVIew , sDim , ZeroOutSubset);
# Scenario
sDim = 'bpmScenario' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 ) ;
SUBSETDESTROY ( sDim , ZeroOutSubset ) ;
ENDIF ;
SUBSETCREATE ( sDim , ZeroOutSubset );
SUBSETELEMENTINSERT ( sDim , ZeroOutSubset , eElement , 0 );
VIEWSUBSETASSIGN ( sCube , ZeroOutVIew , sDim , ZeroOutSubset);
# bpmPeriod
sDim = 'bpmPeriod' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 );
SUBSETDESTROY ( sDim , ZeroOutSubset );
ENDIF;
SUBSETCREATE ( sDim , ZeroOutSubset );
SUBSETELEMENTINSERT ( sDim , ZeroOutSubset , fElement , 0 );
VIEWSUBSETASSIGN ( sCube , ZeroOutVIew , sDim , ZeroOutSubset);
# bpmCompany
sDim = 'bpmCompany' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 );
SUBSETDESTROY ( sDim , ZeroOutSubset );
ENDIF;
SUBSETCREATEBYMDX ( SubsetName , '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[bpmCompany].[Total Company]}, ALL, RECURSIVE )}, 0)}');
VIEWSUBSETASSIGN ( sCube , ZeroOutView , sDim , ZeroOutSubset);
#ZERO OUT
VIEWZEROOUT ( sCube , ZeroOutView );
# DESTROY ZERO OUT VIEW
IF ( VIEWEXISTS ( sCube , ZeroOutView ) = 1 );
VIEWDESTROY ( sCube , ZeroOutView );
ENDIF;
# DESTROY SUBSETS
sDim = 'bpmBook';
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
sDim = 'bpmSubAccount' ;
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
sDim = 'bpmAccount' ;
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
sDim = 'bpmAFE' ;
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
sDim = 'bpmScenario' ;
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
sDim = 'bpmPeriod' ;
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
sDim = 'bpmCompany' ;
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
sDim = 'bpmYear' ;
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
### ZERO OUT POOLS
sCube = 'bpmAlloc_Pools';
VIEWCREATE ( sCube , ZeroOutView ) ;
# bpmSubAccount
sDim = 'bpmSubAccount' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 );
SUBSETDESTROY ( sDim , ZeroOutSubset );
ENDIF;
SUBSETCREATEBYMDX ( SubsetName , '{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[bpmSubAccount].[ALL_SEGMENTS_AND_JVs]}, ALL, RECURSIVE )}, 0)}, ASC)}');
VIEWSUBSETASSIGN ( sCube , ZeroOutVIew , sDim , ZeroOutSubset);
# bpmAccount
sDim = 'bpmAccount' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 );
SUBSETDESTROY ( sDim , ZeroOutSubset );
ENDIF;
SUBSETCREATEBYMDX ( SubsetName , '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[bpmAccount].[Consolidated Net Income]}, ALL, RECURSIVE )}, 0)}');
VIEWSUBSETASSIGN ( sCube , ZeroOutView , sDim , ZeroOutSubset);
# Year
sDim = 'bpmYear' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 ) ;
SUBSETDESTROY ( sDim , ZeroOutSubset ) ;
ENDIF ;
SUBSETCREATE ( sDim , ZeroOutSubset );
SUBSETELEMENTINSERT ( sDim , ZeroOutSubset , dElement , 0 );
VIEWSUBSETASSIGN ( sCube , ZeroOutVIew , sDim , ZeroOutSubset);
# bpmPeriod
sDim = 'bpmPeriod' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 );
SUBSETDESTROY ( sDim , ZeroOutSubset );
ENDIF;
SUBSETCREATE ( sDim , ZeroOutSubset );
SUBSETELEMENTINSERT ( sDim , ZeroOutSubset , fElement , 0 );
VIEWSUBSETASSIGN ( sCube , ZeroOutVIew , sDim , ZeroOutSubset);
# bpmAlloc_Driver
sDim = 'bpmAlloc_Driver' ;
IF ( SUBSETEXISTS ( sDim , ZeroOutSubset ) = 1 );
SUBSETDESTROY ( sDim , ZeroOutSubset );
ENDIF;
SUBSETCREATEBYMDX ( ZeroOutSubset , '{TM1SUBSETALL( [bpmAlloc_Driver] )}');
VIEWSUBSETASSIGN ( sCube , ZeroOutVIew , sDim , ZeroOutSubset);
#ZERO OUT
VIEWZEROOUT ( sCube , ZeroOutView );
# DESTROY ZERO OUT VIEW
IF ( VIEWEXISTS ( sCube , ZeroOutView ) = 1 );
VIEWDESTROY ( sCube , ZeroOutView );
ENDIF;
# DESTROY SUBSETS
sDim = 'bpmSubAccount' ;
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
sDim = 'bpmAccount' ;
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
sDim = 'bpmPeriod' ;
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
sDim = 'bpmYear' ;
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
sDim = 'bpmAlloc_Driver' ;
IF ( SUBSETEXISTS( sDim, ZeroOutSubset ) = 1 );
SUBSETDESTROY( sDim, ZeroOutSubset );
ENDIF;
### CREATE DATA SOURCE VIEW
ProcessName = GETPROCESSNAME();
## Views and subsets
DataSourceView = 'z' | ProcessName | '_DataSource_' | TimeStamp;
DataSourceSubset = DataSourceView ;
ViewName = DataSourceView;
CubeName = 'bpmAlloc_Receivers';
SubsetName = DataSourceSubset;
# View
IF ( VIEWEXISTS ( CubeName , ViewName ) = 1 );
VIEWDESTROY ( CubeName , ViewName );
ENDIF;
VIEWCREATE ( CubeName , ViewName );
# Scenario
DimName = 'bpmScenario';
IF ( SUBSETEXISTS( DimName , SubsetName ) = 1 );
SUBSETDESTROY( DimName , SubsetName );
ENDIF;
SUBSETCREATE ( DimName , SubsetName );
SUBSETELEMENTINSERT( DimName , SubsetName , pScenario , 1 );
VIEWSUBSETASSIGN( CubeName, ViewName , DimName , SubsetName );
VIEWTITLEDIMENSIONSET( CubeName , ViewName , DimName );
# Year
DimName = 'bpmYear' ;
IF ( SUBSETEXISTS( DimName , SubsetName ) = 1 );
SUBSETDESTROY( DimName , SubsetName );
ENDIF;
SUBSETCREATE ( DimName , SubsetName );
SUBSETELEMENTINSERT( DimName , SubsetName , pYear , 1 );
VIEWSUBSETASSIGN( CubeName, ViewName , DimName , SubsetName );
VIEWTITLEDIMENSIONSET( CubeName , ViewName , DimName );
# Period
DimName = 'bpmPeriod' ;
IF ( SUBSETEXISTS( DimName , SubsetName ) = 1 );
SUBSETDESTROY( DimName , SubsetName );
ENDIF;
SUBSETCREATE ( DimName , SubsetName );
SUBSETELEMENTINSERT( DimName , SubsetName , pPeriod , 1 );
VIEWSUBSETASSIGN( CubeName, ViewName , DimName , SubsetName );
VIEWTITLEDIMENSIONSET( CubeName , ViewName , DimName );
# Subaccount
DimName = 'bpmSubaccount' ;
IF ( SUBSETEXISTS( DimName , SubsetName ) = 1 );
SUBSETDESTROY( DimName , SubsetName );
ENDIF;
SUBSETCREATEBYMDX ( SubsetName , '{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[bpmSubAccount].[ALL_SEGMENTS_AND_JVs]}, ALL, RECURSIVE )}, 0)}, ASC)}');
VIEWSUBSETASSIGN( CubeName, ViewName , DimName , SubsetName );
VIEWTITLEDIMENSIONSET( CubeName , ViewName , DimName );
# Driver
DimName = 'bpmAlloc_Driver' ;
IF ( SUBSETEXISTS( DimName , SubsetName ) = 1 );
SUBSETDESTROY( DimName , SubsetName );
ENDIF;
SUBSETCREATEBYMDX ( SubsetName , '{TM1SUBSETALL( [bpmAlloc_Driver] )}');
VIEWSUBSETASSIGN( CubeName, ViewName , DimName , SubsetName );
VIEWTITLEDIMENSIONSET( CubeName , ViewName , DimName );
DATASOURCECUBEVIEW = ViewName;
DATA tab
SenderCube = 'bpmFinance';
PoolCube = 'bpmAlloc_Pools';
DirectCosts = 'G&A DIRECT';
AllocatedCosts = '6130100';
CapOH = 'G&A CAP OH';
AllocatedCapOH = '6130111';
StockComp = 'G&A LTIP';
SenderDriver = ATTRS ( 'bpmSubAccount' , vSubAccount , 'Alloc_Driver' );
PercentCube = 'bpmAlloc_%Results';
Company = ATTRS ( 'bpmSubAccount' , vSubAccount , 'Company' );
TotalSubAccounts = 'ALL_SEGMENTS_AND_JVs';
IF ( SenderDriver @= 'Headcount' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ELSEIF ( SenderDriver @= 'ROW Miles' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ELSEIF ( SenderDriver @= 'Horsepower' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ELSEIF ( SenderDriver @= 'Thruput' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ELSEIF ( SenderDriver @= 'Growth Cap' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ELSEIF ( SenderDriver @= 'MMF' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ELSEIF ( SenderDriver @= 'MMF no CDM' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ELSEIF ( SenderDriver @= 'MMF no Other' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ELSEIF ( SenderDriver @= 'MMF w/ SUN' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ELSEIF ( SenderDriver @= 'MMF w/ SUN & SXL' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ELSEIF ( SenderDriver @= 'MMF Mid & Trans' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ELSEIF ( SenderDriver @= 'MMF Trans & NGL' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ELSEIF ( SenderDriver @= 'MMF Mid Trans 447' );
CostToAllocate = CELLGETN ( SenderCube , pScenario , 'Total Company' , vSubAccount , 'Total AFE' , DirectCosts , 'Balance' , pYear , pPeriod );
ENDIF;
Credit = CostToAllocate * -1;
IF ( (CostToAllocate <> 0));
CELLPUTN ( Credit , SenderCube , pScenario , Company , vSubAccount , 'No_AFE' , AllocatedCosts , 'corp allocations' , pYear , pPeriod );
ENDIF;
IF ( (CostToAllocate <> 0));
CELLPUTN ( CostToAllocate , PoolCube , SenderDriver , vSubAccount , AllocatedCosts , pPeriod , pYear );
ENDIF;
EPILOG
# DESTROY DATA SOURCE VIEW
DataSourceView = 'z' | ProcessName | '_DataSource_' | TimeStamp;
ViewName = DataSourceView;
CubeName = 'bpmAlloc_Receivers';
SubsetName = DataSourceSubset;
IF ( VIEWEXISTS ( CubeName , ViewName ) = 1 );
VIEWDESTROY ( CubeName , ViewName );
ENDIF;
#DESTROY SUBSETS
# Scenario
DimName = 'bpmScenario' ;
IF ( SUBSETEXISTS( DimName , SubsetName ) = 1 );
SUBSETDESTROY( DimName , SubsetName );
ENDIF;
# Year
DimName = 'bpmYear' ;
IF ( SUBSETEXISTS( DimName , SubsetName ) = 1 );
SUBSETDESTROY( DimName , SubsetName );
ENDIF;
# Period
DimName = 'bpmPeriod' ;
IF ( SUBSETEXISTS( DimName , SubsetName ) = 1 );
SUBSETDESTROY( DimName , SubsetName );
ENDIF;
# Subaccount
DimName = 'bpmSubaccount' ;
IF ( SUBSETEXISTS( DimName , SubsetName ) = 1 );
SUBSETDESTROY( DimName , SubsetName );
ENDIF;
# Driver
DimName = 'bpmAlloc_Driver' ;
IF ( SUBSETEXISTS( DimName , SubsetName ) = 1 );
SUBSETDESTROY( DimName , SubsetName );
ENDIF;