Planning Analytics

 View Only

  • 1.  Hierarchy Creation Results in OutofMemory Exception

    Posted Fri February 14, 2020 10:47 AM
    Edited by System Fri January 20, 2023 04:19 PM
    All,

    I have a dimension with about 225K leaves and one primary hierarchy.  I created a TI process to create four additional hierarchies using dimension member attributes.  Each additional hierarchy has one root and two or three levels (including the leaves).  My model was using about 16GB of memory on a Windows Server 2016 workstation with 64 GB RAM.  The PA version is 2.0.8.  The source dimension is used in one cube.

    When I first ran the TI process it generated the additional hierarchies with no issue.  However, after that first generation, subsequent refreshes consistently lead to out of memory exceptions.  The PA server never crashes but the hierarchies are never created.  The memory remains at critical levels until the PA server is restarted.

    The hierarchy creation code is posted below.

    Does anyone have an idea why a refresh of the hierarchy is blowing out memory?

    Thanks!
    Chris

    CODE:


    #-----------PROLOG-----------

    vDimName = 'CTS_Item';
    vMgrHierName = 'Manager';
    vMgrTotalElement = 'All Managers';

    IF(0=HierarchyExists(vDimName, vMgrHierName));
    HierarchyCreate(vDimName, vMgrHierName);
    ELSE;
    HierarchyDeleteAllElements(vDimName, vMgrHierName);
    ENDIF;
    # add the root for the hierarchy
    HierarchyElementInsert(vDimName, vMgrHierName, '', vMgrTotalElement, 'C');


    vMerHierName = 'Merchant';
    vMerTotalElement = 'All Merchants';

    IF(0=HierarchyExists(vDimName, vMerHierName));
    HierarchyCreate(vDimName, vMerHierName);
    ELSE;
    HierarchyDeleteAllElements(vDimName, vMerHierName);
    ENDIF;
    # add the root for the hierarchy
    HierarchyElementInsert(vDimName, vMerHierName, '', vMerTotalElement, 'C');


    vBHierName = 'Brand';
    vBTotalElement = 'All Brands';

    IF(0=HierarchyExists(vDimName, vBHierName));
    HierarchyCreate(vDimName, vBHierName);
    ELSE;
    HierarchyDeleteAllElements(vDimName, vBHierName);
    ENDIF;
    # add the root for the hierarchy
    HierarchyElementInsert(vDimName, vBHierName, '', vBTotalElement, 'C');



    vPCHierName = 'ParentCompany';
    vPCTotalElement = 'All Parent Companies';

    IF(0=HierarchyExists(vDimName, vPCHierName));
    HierarchyCreate(vDimName, vPCHierName);
    ELSE;
    HierarchyDeleteAllElements(vDimName, vPCHierName);
    ENDIF;
    # add the root for the hierarchy
    HierarchyElementInsert(vDimName, vPCHierName, '', vPCTotalElement, 'C');


    # Build the subset to use to create the hierarchy
    lRoot = 'All Items';
    lMdx = '{TM1FILTERBYLEVEL({DESCENDANTS([' | vDimName | '].[' | lRoot | ']) }, 0)}';
    lSrcSubset = 'zz_level0_' | vDimName;
    SubsetCreateByMDX(lSrcSubset, lMdx, 1);

    DataSourceType='SUBSET';
    DatasourceNameForServer=vDimName;
    DatasourceDimensionSubset=lSrcSubset;

    #-------------METADATA--------------------

    # Manager hierarchy
    lManager = ATTRS(vDimName, vItem, 'MANAGER_ID') | '-' | ATTRS(vDimName, vItem, 'MANAGER_NAME');

    # only insert the hierarchy members if the attribute was not empty
    IF(LONG(TRIM(lManager))>0);
    HierarchyElementInsert(vDimName, vMgrHierName, '', lManager, 'C');
    HierarchyElementInsert(vDimName, vMgrHierName, '', vItem, 'N');
    HierarchyElementComponentAdd(vDimName, vMgrHierName, vMgrTotalElement, lManager, 1);
    HierarchyElementComponentAdd(vDimName, vMgrHierName, lManager, vItem, 1);
    ENDIF;

    # Merchant hierarchy
    lMerchID = NUMBERTOSTRING(ATTRN(vDimName, vItem, 'MERCHANT_ID'));
    lMerchant = lMerchID | '-' | ATTRS(vDimName, vItem, 'MERCHANT_NAME');

    # only insert the hierarchy members if the attribute was not empty
    IF(LONG(TRIM(lMerchant))>0);
    HierarchyElementInsert(vDimName, vMerHierName, '', lMerchant, 'C');
    HierarchyElementInsert(vDimName, vMerHierName, '', vItem, 'N');
    HierarchyElementComponentAdd(vDimName, vMerHierName, vMerTotalElement, lMerchant, 1);
    HierarchyElementComponentAdd(vDimName, vMerHierName, lMerchant, vItem, 1);
    ENDIF;

    # Brand hierarchy
    # Actual a Brand/Sub-Brand hierarchy
    lBrand = ATTRS(vDimName, vItem, 'BRAND_ID') | '-' | ATTRS(vDimName, vItem, 'BRAND_NAME');
    lSubBrand = ATTRS(vDimName, vItem, 'SUB_BRAND_ID') | '-' | ATTRS(vDimName, vItem, 'SUB_BRAND_NAME');

    # handle the case where the Brand and SubBrand are the same
    IF(TRIM(lBrand) @= TRIM(lSubBrand));
    lSubBrand = 'sub_' | lSubBrand;
    ENDIF;

    # only insert the hierarchy members if the attribute was not empty
    IF(LONG(TRIM(lBrand))>0);
    HierarchyElementInsert(vDimName, vBHierName, '', lBrand, 'C');
    HierarchyElementInsert(vDimName, vBHierName, '', lSubBrand, 'C');
    HierarchyElementInsert(vDimName, vBHierName, '', vItem, 'N');
    HierarchyElementComponentAdd(vDimName, vBHierName, vBTotalElement, lBrand, 1);
    HierarchyElementComponentAdd(vDimName, vBHierName, lBrand, lSubBrand, 1);
    HierarchyElementComponentAdd(vDimName, vBHierName, lSubBrand, vItem, 1);
    ENDIF;

    # ParentCompany hierarchy
    lPCID = NUMBERTOSTRING(ATTRN(vDimName, vItem, 'PARENT_COMPANY_ID'));
    lParentCompany = lPCID | '-' | ATTRS(vDimName, vItem, 'PARENT_COMPANY_NAME');


    # only insert the hierarchy members if the attribute was not empty
    IF(LONG(TRIM(lParentCompany))>0);
    HierarchyElementInsert(vDimName, vPCHierName, '', lParentCompany, 'C');
    HierarchyElementInsert(vDimName, vPCHierName, '', vItem, 'N');
    HierarchyElementComponentAdd(vDimName, vPCHierName, vPCTotalElement, lParentCompany, 1);
    HierarchyElementComponentAdd(vDimName, vPCHierName, lParentCompany, vItem, 1);
    ENDIF;


    ------------------------------
    Christopher Wolfe
    ------------------------------
    #PlanningAnalyticswithWatson


  • 2.  RE: Hierarchy Creation Results in OutofMemory Exception

    Posted Mon February 17, 2020 01:07 PM
    Hi,

    What happens if you only rebuild one hierarchy at a time?  Maybe add a parameter to determine which hierarchy to rebuild.

    One other note, on the checks if the attributes are empty, you should probably use greater than 1 because in most cases you are adding a "-" so even if the attributes are empty the consolidation will always have a length of at least one .

    ------------------------------
    Scott Brown
    ------------------------------

    Original Message


  • 3.  RE: Hierarchy Creation Results in OutofMemory Exception

    Posted Mon February 17, 2020 01:51 PM

    We've experienced several issues maintaining alternate over the last year that have resulted in bug fixes. Some of the issues were related to using the function HierarchyDeleteAllElements. As a test, I would suggest deleting and recreating the hierarchies using HierarchyDestroy and HierarchyCreate and see if that resolves the issue.

    Here's a snippet of an email I had with one of the developers that may provide some insight…

    TI follows the metadata edit pattern of duplicate, modify the duplicate, dimension update. But it does the duplication and update automatically, duplicating during Prolog/Metadata when calls like HierarchyInsertElement occur, and updating at the transition from Metadata to Data tabs.

    But the Dimension/HierarchyDeleteAllElements TI function uses an old trick. They don't take the duplicate. Instead, they introduce an "duplicate" that is just a brand new, empty, unregistered object. The code that auto updates at the Metadata/Data transition doesn't know the difference. 

    If delete/recreate resolves the issue I would suggest submitting a support ticket.

    Chris



    ------------------------------
    Chris Courim
    ------------------------------

    Original Message


  • 4.  RE: Hierarchy Creation Results in OutofMemory Exception

    Posted Fri March 27, 2020 01:10 PM
    All - 

    Thank you for the replies.  Creating the each hierarchy separately in its own TI process worked.

    I appreciate the responses!
    Chris

    ------------------------------
    Christopher Wolfe
    ------------------------------

    Original Message