Changing a TI to create a new view on the fly is causing no loops through the data tab

[tmccollum]

I read the useful tips FAQ sorry in advance if I didn't follow it right

I'm in PAW. I think we're 2.0.0.84.

I have a TI process where the data source is a named view tiListSalesYTD. My goal is to change the TI to do the exact same thing but make the view in the prolog. I will post the code after this to both processes. I opened the current process and saved it as a new name. In the new TI I changed the data source from the tiListSalesYTD view to the cube's default view. I then added code to make a temp view TempListSalesYTD based on a mdx. I run it and it makes the view ok. The MDX matches the old view exactly. However there are no loops through the metadata or data tabs. If I change the data source of the old code from tiListSalesYTD to Default same thing happens. If I change the data source of the new TI to tiListSalesYTD it works.

Here's the original code. This is the version of the TI with the data source of tiListSalesYTD

Code: Select all

#Section Prolog

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

#  ===============================================================================================
#  Author:        Rick Stevenson
#  Created:      1/14/2007
#  Company:    ARCS Solutions LLC
#  Client:          <Client Name>
#  Definition:    Creates the BPM Office Non Category Off Invoice Data
#  Copyright:     ARCS Solutions LLC, 2008, All Rights Reserved 
#  ===============================================================================================

###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
### *** Start - SetUp and call Hartz standard Audit/EOJ Process  ***
###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

vtiProcName = GetProcessName();
vExecUser = TM1USER();
vNow = TIMST(NOW,'\Y-\m-\d \h.\i.\s');
vOutputDirectory=CELLGETS('cn_control','Output Directory', 'String');
vERRFile= vOutputDirectory | TRIM (vtiProcName) |  '_' | vNow | '.err';
vLocation = 'BEGIN';
vSendVerbose = 'N';
vSendMail = 'N';

vReturnValue = ExecuteProcess('Htz BPM std Audit-EOJ', 'pSendExecUser', vExecUser, 'pSendProcName', vtiProcName, 'pSendLocation', vLocation, 'pSendMail', vSendMail, 'pSendVerbose', vSendVerbose);

if(vReturnValue = ProcessExitSeriousError() );
ASCIIOUTPUT(vERRFile, 'Process exited with serious errors at', TIME, 'on', TODAY);
endif;

###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
### *** Finish - SetUp and call Hartz standard Audit/EOJ Process ***
###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


vCubName = 'rp_sales' ;
vCubControl = 'cn_control' ;

vSendValue = 1 ;
vExport = 1; 
vZeroSendValue = 1 ;

vHeaderRowCount = 0;
vExportHeaderRowCount = 0;

#===============                DEFINE Static Variables        ========================
vBookOriginal = 'Original' ;
vBookAllocation = 'Allocation' ;
vBookPreAllocation = 'Pre-Allocation';

vScenario = 'Actual' ;
vCurrency = 'Local';
vEntity = 'No Entity';
vAllProduct = 'All Business Groups' ;
vMonth = CELLGETS ( 'cn_control' , 'Process Sales Month' , 'String') ;

vTiName = 'Htz BPM EX Non Category Off Invoice Allocation';

vDimNameTi='}Processes';
vDimCustomer = 'Customer' ;
vDimProduct = 'Product' ;

vNow = TIMST(NOW,'\M \D, \Y at \H \p \i min \s sec');

ATTRPUTS(vNow,vDimNameTi,vTiName,'Start Time');

vOutputDirectory=CELLGETS('cn_control','Output Directory', 'String');
vDownloadDirectory = CELLGETS('cn_control','Download Directory' , 'String');

vLogFile = vOutputDirectory | TRIM (vTiName) |  '_' | vNow | '.log';
vExportFile = vDownloadDirectory | TRIM ( vTiName) | '.csv' ;
vErrorFile = vOutputDirectory | TRIM (vTiName) | '.err';

#PROCESSQUIT;
#Section Metadata

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



#Section Data

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

#========             Using Metadata tab to process only categories with blank Business Group attributes             =============

IF ( vSendValue = 1 ) ;

#========================                       NON CATEGORY O/I                         =================================

#====================                 ALLOCATION BASIS CALCULATION                             =========================
    vListSalesAll = CELLGETN ( vCubName , Book , Scenario , Currency , Entity , Customer , Location , 
    Territory , vAllProduct , vYear , vMonthYTD , profitability_msr ) ;

    vRatio = Value \ vListSalesAll ;

#=============================                       SPOILS                              =====================================
    vProfitabilityMsr = 'Non Category Off Invoice Spoils' ;

    vNonCategoryOI = CELLGETN ( vCubName , vBookPreAllocation , Scenario , Currency , Entity , Customer , Location , 
    Territory , vAllProduct , vYear , vMonth , vProfitabilityMsr ) ;

    IF(vExportHeaderRowCount = 0);
        ASCIIOUTPUT ( vExportFile ,  'vCubName' , 'vBookAllocation' , 'Scenario' , 'Currency' , 'Entity'  , 'Customer' , 
        'Location' , 'Territory' , 'Product' , 'vYear' , 'vMonth' , 'vProfitabilityMsr' , 'vNonCategoryOI' ) ;
        vExportHeaderRowCount=1;
    ENDIF;

    IF ( vExport = 1 ) ;
        ASCIIOUTPUT ( vExportFile , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , 
        Location , Territory , Product , vYear , vMonth , vProfitabilityMsr , STR ( vNonCategoryOI * vRatio , 20 , 5 ) ) ;
    ELSE;
        CELLPUTN ( vNonCategoryOI * vRatio , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , Location , 
        Territory , Product , vYear , vMonth , vProfitabilityMsr ) ;
    ENDIF;

#========================                       SPOILS OVERSPEND                             ==============================
    vProfitabilityMsr = 'Non Category Off Invoice Spoils Overspend' ;

    vNonCategoryOI = CELLGETN ( vCubName , vBookPreAllocation , Scenario , Currency , Entity , Customer , Location , 
    Territory , vAllProduct , vYear , vMonth , vProfitabilityMsr ) ;

    IF(vExportHeaderRowCount = 0);
        ASCIIOUTPUT ( vExportFile ,  'vCubName' , 'vBookAllocation' , 'Scenario' , 'Currency' , 'Entity'  , 'Customer' , 
        'Location' , 'Territory' , 'Product' , 'vYear' , 'vMonth' , 'vProfitabilityMsr' , 'vNonCategoryOI' ) ;
        vExportHeaderRowCount=1;
    ENDIF;

    IF ( vExport = 1 ) ;
        ASCIIOUTPUT ( vExportFile , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , 
        Location , Territory , Product , vYear , vMonth , vProfitabilityMsr , STR ( vNonCategoryOI * vRatio , 20 , 5 ) ) ;
    ELSE;
        CELLPUTN ( vNonCategoryOI * vRatio , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , Location , 
        Territory , Product , vYear , vMonth , vProfitabilityMsr ) ;
    ENDIF;

#==========================                       BROKER RESETS           =====================================
    vProfitabilityMsr = 'Non Category Off Invoice Broker Resets' ;

    vNonCategoryOI = CELLGETN ( vCubName , vBookPreAllocation , Scenario , Currency , Entity , Customer , Location , 
    Territory , vAllProduct , vYear , vMonth , vProfitabilityMsr ) ;

    IF ( vExport = 1 ) ;
        ASCIIOUTPUT ( vExportFile , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , 
        Location , Territory , Product , vYear , vMonth , vProfitabilityMsr , STR ( vNonCategoryOI * vRatio , 20 , 5 ) ) ;
    ELSE;
        CELLPUTN ( vNonCategoryOI * vRatio , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , Location , 
        Territory , Product , vYear , vMonth , vProfitabilityMsr ) ;
    ENDIF;

#====================                       BROKER RESETS OVERSPEND           ==============================
    vProfitabilityMsr = 'Non Category Off Invoice Broker Resets Overspend' ;

    vNonCategoryOI = CELLGETN ( vCubName , vBookPreAllocation , Scenario , Currency , Entity , Customer , Location , 
    Territory , vAllProduct , vYear , vMonth , vProfitabilityMsr ) ;

    IF ( vExport = 1 ) ;
        ASCIIOUTPUT ( vExportFile , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , 
        Location , Territory , Product , vYear , vMonth , vProfitabilityMsr , STR ( vNonCategoryOI * vRatio , 20 , 5 ) ) ;
    ELSE;
        CELLPUTN ( vNonCategoryOI * vRatio , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , Location , 
        Territory , Product , vYear , vMonth , vProfitabilityMsr ) ;
    ENDIF;

#===========================                       BACKHAULS                              ===============================
    vProfitabilityMsr = 'Non Category Off Invoice Non Terms of Sales - Backhaul' ;

    vNonCategoryOI = CELLGETN ( vCubName , vBookPreAllocation , Scenario , Currency , Entity , Customer , Location , 
    Territory , vAllProduct , vYear , vMonth , vProfitabilityMsr ) ;

    IF ( vExport = 1 ) ;
        ASCIIOUTPUT ( vExportFile , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , 
        Location , Territory , Product , vYear , vMonth , vProfitabilityMsr , STR ( vNonCategoryOI * vRatio , 20 , 5 ) ) ;
    ELSE;
        CELLPUTN ( vNonCategoryOI * vRatio , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , Location , 
        Territory , Product , vYear , vMonth , vProfitabilityMsr ) ;
    ENDIF;

#=====================                                   VOLUME DISCOUNTS                          =============================
    vProfitabilityMsr = 'Volume Discounts' ;

#=====================       Different from top vListSalesAll pulling 'All Locations'         ============================
    vListSalesAll = CELLGETN ( vCubName , Book , Scenario , Currency , Entity , Customer , 'All Locations' , 
    Territory , vAllProduct , vYear , vMonth , profitability_msr ) ;

    vRatio = Value \ vListSalesAll ;

    vNonCategoryOI = CELLGETN ( vCubName , vBookPreAllocation , Scenario , Currency , Entity , Customer , 'No Location' , 
    'No Territory' , vAllProduct , vYear , vMonth , vProfitabilityMsr ) ;

    IF ( vExport = 1 ) ;
        ASCIIOUTPUT ( vExportFile , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , 
        Location , Territory , Product , vYear , vMonth , vProfitabilityMsr , STR ( vNonCategoryOI * vRatio , 20 , 5 ) ) ;
    ELSE;
        CELLPUTN ( vNonCategoryOI * vRatio , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , Location , 
        Territory , Product , vYear , vMonth , vProfitabilityMsr ) ;
    ENDIF;

ELSE;

    IF(vHeaderRowCount = 0);
        ASCIIOUTPUT ( vLogFile , 'Book' , 'Scenario' , 'Currency' , 'Entity' , 'Customer' , 'Location' , 
        'Territory' , 'Product' , 'Year' , 'Month' , 'Profitability Msr' , 'Category OI Profitability Msr') ;
        vHeaderRowCount=1;
    ENDIF;

    ASCIIOUTPUT ( vLogFile , Book , Scenario , Currency , Entity , Customer , Location , 
    Territory , Product ,  vYear , vMonth , profitability_msr , vProfitabilityMsr ) ;

ENDIF ;
#Section Epilog

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

#================                  This will load into the }ElementAttribute_Processes cube  ========================
#================                         The time this process Ended when it was last run.     ========================
vNow = TIMST(NOW,'\M \D, \Y at \H \p \i min \s sec');
ATTRPUTS(vNow,vDimNameTi,vTiName,'End Time');


###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
### *** Start - SetUp and call Hartz standard Audit/EOJ Process  ***
###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

vtiProcName = GetProcessName();
vExecUser = TM1USER();
vNow = TIMST(NOW,'\Y-\m-\d \h.\i.\s');
vOutputDirectory=CELLGETS('cn_control','Output Directory', 'String');
vERRFile= vOutputDirectory | TRIM (vtiProcName) |  '_' | vNow | '.err';
vLocation = 'END';
vSendVerbose = 'N';
vSendMail = 'N';

vReturnValue = ExecuteProcess('Htz BPM std Audit-EOJ', 'pSendExecUser', vExecUser, 'pSendProcName', vtiProcName, 'pSendLocation', vLocation, 'pSendMail', vSendMail, 'pSendVerbose', vSendVerbose);

if(vReturnValue = ProcessExitSeriousError() );
ASCIIOUTPUT(vERRFile, 'Process exited with serious errors at', TIME, 'on', TODAY);
endif;

###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
### *** Finish - SetUp and call Hartz standard Audit/EOJ Process ***
###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Here's my new code. I changed the data source from tiListSalesYTD to Default and am making the same view dynamically in the prolog. When I run this I never run through metadata or data tabs. The only changes are in prolog

Code: Select all

#Section Prolog

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

#  ===============================================================================================
#  Author:        Rick Stevenson
#  Created:      1/14/2007
#  Company:    ARCS Solutions LLC
#  Client:          <Client Name>
#  Definition:    Creates the BPM Office Non Category Off Invoice Data
#  Copyright:     ARCS Solutions LLC, 2008, All Rights Reserved 
#  ===============================================================================================
#
# Change History
# 5/28/25     INT290     Replace static views with dynamic view created by mdx. Rename TI process to current standard
#
###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
### *** Start - SetUp and call Hartz standard Audit/EOJ Process  ***
###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

vtiProcName = GetProcessName();
vExecUser = TM1USER();
vNow = TIMST(NOW,'\Y-\m-\d \h.\i.\s');
vOutputDirectory=CELLGETS('cn_control','Output Directory', 'String');
vERRFile= vOutputDirectory | TRIM (vtiProcName) |  '_' | vNow | '.err';
vLocation = 'BEGIN';
vSendVerbose = 'N';
vSendMail = 'N';
vDataSourceView = 'TempListSalesYTD';

vReturnValue = ExecuteProcess('Htz BPM std Audit-EOJ', 'pSendExecUser', vExecUser, 'pSendProcName', vtiProcName, 'pSendLocation', vLocation, 'pSendMail', vSendMail, 'pSendVerbose', vSendVerbose);

if(vReturnValue = ProcessExitSeriousError() );
ASCIIOUTPUT(vERRFile, 'Process exited with serious errors at', TIME, 'on', TODAY);
endif;

###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
### *** Finish - SetUp and call Hartz standard Audit/EOJ Process ***
###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


vCubName = 'rp_sales' ;
vCubControl = 'cn_control' ;

vSendValue = 1 ;
vExport = 1; 
vZeroSendValue = 1 ;

vHeaderRowCount = 0;
vExportHeaderRowCount = 0;

#===============                DEFINE Static Variables        ========================
vBookOriginal = 'Original' ;
vBookAllocation = 'Allocation' ;
vBookPreAllocation = 'Pre-Allocation';

vScenario = 'Actual' ;
vCurrency = 'Local';
vEntity = 'No Entity';
vAllProduct = 'All Business Groups' ;
vMonth = CELLGETS ( 'cn_control' , 'Process Sales Month' , 'String') ;

vTiName = GetProcessName();

vDimNameTi='}Processes';
vDimCustomer = 'Customer' ;
vDimProduct = 'Product' ;

vNow = TIMST(NOW,'\M \D, \Y at \H \p \i min \s sec');

ATTRPUTS(vNow,vDimNameTi,vTiName,'Start Time');

vOutputDirectory=CELLGETS('cn_control','Output Directory', 'String');
vDownloadDirectory = CELLGETS('cn_control','Download Directory' , 'String');

vLogFile = vOutputDirectory | TRIM (vTiName) |  '_' | vNow | '.log';
vExportFile = vDownloadDirectory | TRIM ( vTiName) | '.csv' ;
vErrorFile = vOutputDirectory | TRIM (vTiName) | '.err';

# see if the dynamic zero out view exists and destroy it if it does. It is temp view and should never exist. This is a safety step
IF (ViewExists( vCubName, vDataSourceView ) = 1);
      ViewDestroy( vCubName, vDataSourceView );
ENDIF;

# set the MDX for the dynamic view
vDataSourceMDX = 'SELECT 
   {
   } ON 0, 
   {
      [book].[book].[Original]
   }*{
      [scenario].[scenario].[Actual]
   }*{
      [currency].[currency].[Local]
   }*{
      TM1SubsetToSet([entity].[entity],"mdxLeaf","public")
   }*{
      TM1SubsetToSet([customer].[customer],"mdxLeaf","public")
   }*{
      TM1SubsetToSet([location].[location],"mdxLeaf","public")
   }*{
      TM1SubsetToSet([territory].[territory],"mdxLeaf","public")
   }*{
      TM1SubsetToSet([product].[product],"mdxLeaf","public")
   }*{
      TM1SubsetToSet([year].[year],"mdxProcessSalesYear","public")
   }*{
      TM1SubsetToSet([month].[month],"mdxProcessSalesMonthYTD","public")
   }*{
      [profitability_msr].[profitability_msr].[List Sales]
   } ON 1 
FROM
   [rp_sales]';

# create the view
ViewCreateByMDX( vCubName, vDataSourceView, vDataSourceMDX, 1 );

# set View parameters
# exclude consolidated values
# include rule calculated values
# exclude zeros
ViewExtractSkipCalcsSet(vCubName, vDataSourceView,1);
ViewExtractSkipRuleValuesSet(vCubName, vDataSourceView,1);
ViewExtractSkipZeroesSet(vCubName, vDataSourceView,1);

# use the view as the source of this process
DataSourceType = 'VIEW';
DatasourceNameForServer = vCubName;
DatasourceCubeView = vDataSourceView;

#PROCESSQUIT;
#Section Metadata

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



#Section Data

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

#========             Using Metadata tab to process only categories with blank Business Group attributes             =============

IF ( vSendValue = 1 ) ;

#========================                       NON CATEGORY O/I                         =================================

#====================                 ALLOCATION BASIS CALCULATION                             =========================
    vListSalesAll = CELLGETN ( vCubName , Book , Scenario , Currency , Entity , Customer , Location , 
    Territory , vAllProduct , vYear , vMonthYTD , profitability_msr ) ;

    vRatio = Value \ vListSalesAll ;

#=============================                       SPOILS                              =====================================
    vProfitabilityMsr = 'Non Category Off Invoice Spoils' ;

    vNonCategoryOI = CELLGETN ( vCubName , vBookPreAllocation , Scenario , Currency , Entity , Customer , Location , 
    Territory , vAllProduct , vYear , vMonth , vProfitabilityMsr ) ;

    IF(vExportHeaderRowCount = 0);
        ASCIIOUTPUT ( vExportFile ,  'vCubName' , 'vBookAllocation' , 'Scenario' , 'Currency' , 'Entity'  , 'Customer' , 
        'Location' , 'Territory' , 'Product' , 'vYear' , 'vMonth' , 'vProfitabilityMsr' , 'vNonCategoryOI' ) ;
        vExportHeaderRowCount=1;
    ENDIF;

    IF ( vExport = 1 ) ;
        ASCIIOUTPUT ( vExportFile , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , 
        Location , Territory , Product , vYear , vMonth , vProfitabilityMsr , STR ( vNonCategoryOI * vRatio , 20 , 5 ) ) ;
    ELSE;
        CELLPUTN ( vNonCategoryOI * vRatio , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , Location , 
        Territory , Product , vYear , vMonth , vProfitabilityMsr ) ;
    ENDIF;

#========================                       SPOILS OVERSPEND                             ==============================
    vProfitabilityMsr = 'Non Category Off Invoice Spoils Overspend' ;

    vNonCategoryOI = CELLGETN ( vCubName , vBookPreAllocation , Scenario , Currency , Entity , Customer , Location , 
    Territory , vAllProduct , vYear , vMonth , vProfitabilityMsr ) ;

    IF(vExportHeaderRowCount = 0);
        ASCIIOUTPUT ( vExportFile ,  'vCubName' , 'vBookAllocation' , 'Scenario' , 'Currency' , 'Entity'  , 'Customer' , 
        'Location' , 'Territory' , 'Product' , 'vYear' , 'vMonth' , 'vProfitabilityMsr' , 'vNonCategoryOI' ) ;
        vExportHeaderRowCount=1;
    ENDIF;

    IF ( vExport = 1 ) ;
        ASCIIOUTPUT ( vExportFile , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , 
        Location , Territory , Product , vYear , vMonth , vProfitabilityMsr , STR ( vNonCategoryOI * vRatio , 20 , 5 ) ) ;
    ELSE;
        CELLPUTN ( vNonCategoryOI * vRatio , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , Location , 
        Territory , Product , vYear , vMonth , vProfitabilityMsr ) ;
    ENDIF;

#==========================                       BROKER RESETS           =====================================
    vProfitabilityMsr = 'Non Category Off Invoice Broker Resets' ;

    vNonCategoryOI = CELLGETN ( vCubName , vBookPreAllocation , Scenario , Currency , Entity , Customer , Location , 
    Territory , vAllProduct , vYear , vMonth , vProfitabilityMsr ) ;

    IF ( vExport = 1 ) ;
        ASCIIOUTPUT ( vExportFile , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , 
        Location , Territory , Product , vYear , vMonth , vProfitabilityMsr , STR ( vNonCategoryOI * vRatio , 20 , 5 ) ) ;
    ELSE;
        CELLPUTN ( vNonCategoryOI * vRatio , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , Location , 
        Territory , Product , vYear , vMonth , vProfitabilityMsr ) ;
    ENDIF;

#====================                       BROKER RESETS OVERSPEND           ==============================
    vProfitabilityMsr = 'Non Category Off Invoice Broker Resets Overspend' ;

    vNonCategoryOI = CELLGETN ( vCubName , vBookPreAllocation , Scenario , Currency , Entity , Customer , Location , 
    Territory , vAllProduct , vYear , vMonth , vProfitabilityMsr ) ;

    IF ( vExport = 1 ) ;
        ASCIIOUTPUT ( vExportFile , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , 
        Location , Territory , Product , vYear , vMonth , vProfitabilityMsr , STR ( vNonCategoryOI * vRatio , 20 , 5 ) ) ;
    ELSE;
        CELLPUTN ( vNonCategoryOI * vRatio , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , Location , 
        Territory , Product , vYear , vMonth , vProfitabilityMsr ) ;
    ENDIF;

#===========================                       BACKHAULS                              ===============================
    vProfitabilityMsr = 'Non Category Off Invoice Non Terms of Sales - Backhaul' ;

    vNonCategoryOI = CELLGETN ( vCubName , vBookPreAllocation , Scenario , Currency , Entity , Customer , Location , 
    Territory , vAllProduct , vYear , vMonth , vProfitabilityMsr ) ;

    IF ( vExport = 1 ) ;
        ASCIIOUTPUT ( vExportFile , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , 
        Location , Territory , Product , vYear , vMonth , vProfitabilityMsr , STR ( vNonCategoryOI * vRatio , 20 , 5 ) ) ;
    ELSE;
        CELLPUTN ( vNonCategoryOI * vRatio , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , Location , 
        Territory , Product , vYear , vMonth , vProfitabilityMsr ) ;
    ENDIF;

#=====================                                   VOLUME DISCOUNTS                          =============================
    vProfitabilityMsr = 'Volume Discounts' ;

#=====================       Different from top vListSalesAll pulling 'All Locations'         ============================
    vListSalesAll = CELLGETN ( vCubName , Book , Scenario , Currency , Entity , Customer , 'All Locations' , 
    Territory , vAllProduct , vYear , vMonth , profitability_msr ) ;

    vRatio = Value \ vListSalesAll ;

    vNonCategoryOI = CELLGETN ( vCubName , vBookPreAllocation , Scenario , Currency , Entity , Customer , 'No Location' , 
    'No Territory' , vAllProduct , vYear , vMonth , vProfitabilityMsr ) ;

    IF ( vExport = 1 ) ;
        ASCIIOUTPUT ( vExportFile , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , 
        Location , Territory , Product , vYear , vMonth , vProfitabilityMsr , STR ( vNonCategoryOI * vRatio , 20 , 5 ) ) ;
    ELSE;
        CELLPUTN ( vNonCategoryOI * vRatio , vCubName , vBookAllocation , Scenario , Currency , Entity  , Customer , Location , 
        Territory , Product , vYear , vMonth , vProfitabilityMsr ) ;
    ENDIF;

ELSE;

    IF(vHeaderRowCount = 0);
        ASCIIOUTPUT ( vLogFile , 'Book' , 'Scenario' , 'Currency' , 'Entity' , 'Customer' , 'Location' , 
        'Territory' , 'Product' , 'Year' , 'Month' , 'Profitability Msr' , 'Category OI Profitability Msr') ;
        vHeaderRowCount=1;
    ENDIF;

    ASCIIOUTPUT ( vLogFile , Book , Scenario , Currency , Entity , Customer , Location , 
    Territory , Product ,  vYear , vMonth , profitability_msr , vProfitabilityMsr ) ;

ENDIF ;
#Section Epilog

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

#================                  This will load into the }ElementAttribute_Processes cube  ========================
#================                         The time this process Ended when it was last run.     ========================
vNow = TIMST(NOW,'\M \D, \Y at \H \p \i min \s sec');
ATTRPUTS(vNow,vDimNameTi,vTiName,'End Time');


###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
### *** Start - SetUp and call Hartz standard Audit/EOJ Process  ***
###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

vtiProcName = GetProcessName();
vExecUser = TM1USER();
vNow = TIMST(NOW,'\Y-\m-\d \h.\i.\s');
vOutputDirectory=CELLGETS('cn_control','Output Directory', 'String');
vERRFile= vOutputDirectory | TRIM (vtiProcName) |  '_' | vNow | '.err';
vLocation = 'END';
vSendVerbose = 'N';
vSendMail = 'N';

vReturnValue = ExecuteProcess('Htz BPM std Audit-EOJ', 'pSendExecUser', vExecUser, 'pSendProcName', vtiProcName, 'pSendLocation', vLocation, 'pSendMail', vSendMail, 'pSendVerbose', vSendVerbose);

if(vReturnValue = ProcessExitSeriousError() );
ASCIIOUTPUT(vERRFile, 'Process exited with serious errors at', TIME, 'on', TODAY);
endif;

###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
### *** Finish - SetUp and call Hartz standard Audit/EOJ Process ***
###%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

I can't figure out for the life of me why this is happening. I've googled around and found some threads here and some on the ibm community. I've seen numerous posts suggesting mdx views as data sources are possible. I'm sure I'm doing something stupid and the answer is obvious. I just can't figure out what I'm doing wrong.

EDIT: Forgot to mention I also made it so my new view was not temporary. The mdx of current vs new view is exactly the same.

[MarenC]

Hi,

never used the CreateViewByMDX function before, so hopefully someone with more experience with that will be ale to help you out. I still prefer viewcreate.

But just to confirm, this is not a consolidated element:

mdxProcessSalesMonthYTD

Maren

[tmccollum]

It is a subset the contains a mix of consolidations and leaf elements. I did change the view to use a single leaf value in its place and got the same result (no loops through the data tab)

[tmccollum]

I also tried

ViewExtractSkipCalcsSet(vCubName, vDataSourceView,0); and it didn't change anything

[tmccollum]

I think what may be happening is all the intersecting cells are 0 and the old TI is making a file full of zeros while the new TI is not looping the zeroes through data and metadata. Trying to figure out a way to prove that

[wcoffen]

There is nothing "on 0" which would be the columns and I think you need to have a column set, at least one member. The old static views would allow not having a column dimension, but I think MDX is more particular.

Right now you have all your dimensions nested row-wise.

Move "[currency].[currency].[Local]" to a column position.

vDataSourceMDX = 'SELECT
{ [currency].[currency].[Local]
} ON 0,
{
[book].[book].[Original]
}*{
[scenario].[scenario].[Actual]
...

[tmccollum]

THANK YOU!

I did what you suggested and got a temporary memory pool error. So I decided to rewrite the mdx below

Code: Select all

# set the MDX for the dynamic view
vDataSourceMDX = 'SELECT non empty
   {  [currency].[currency].[Local] } 
   ON 0,  NON EMPTY
   { TM1SubsetToSet([entity].[entity],"mdxLeaf","public") }*
   { TM1SubsetToSet([customer].[customer],"mdxLeaf","public") }*
   { TM1SubsetToSet([location].[location],"mdxLeaf","public") }*
   { TM1SubsetToSet([territory].[territory],"mdxLeaf","public") }*
   { TM1SubsetToSet([product].[product],"mdxLeaf","public") }*
   { TM1SubsetToSet([year].[year],"mdxProcessSalesYear","public") }*
   { TM1SubsetToSet([month].[month],"mdxProcessSalesMonthYTD","public") }
   ON 1 
FROM
   [rp_sales]
where ( 
 [book].[book].[Original],
 [scenario].[scenario].[Actual],
 [profitability_msr].[profitability_msr].[List Sales]
 )   ';

Which seems to work fine. I also had to rearrange my data source variables as they no longer are in cube dimension order.
The order is
1 - Columns
2 - Rows
3 - Where clause

[tmccollum]

Also to note the requirement to at least have something on 0 doesn't apply when using the view as a zero out view. I've taken several of these without columns defined the view the same way and done a ViewZeroOut call with no problem. It seems it only applies to a view as a data source.