We have been having some performance issues with the calculation of our Year-to-Date values in our Regulatory Reporting cube.
If I select a Consolidated Accounts structure + Jun 2015 YTD , the query will complete in about 1 second.
If I select our Jun 2015 YTD consolidation against a leaf level account (485 Account lines) I get the "Building Cube View...." message & this will sit like this until my patience runs out (10mins+ when really REALLY patient).
Now if I select the Jun 2015 YTD consolidation + the Month Elements of Jan 2015, Feb 2015, Mar 2015, Apr 2015, May 2015 & Jun 2015 I get a result in <1 second.
Has anyone experianced anything similar?
We use the exact same Consolidation rule in our BAU GL Cube and have not experianced this behaviour, even when looking at a much larger list of elements.
Below is the rule that we have in almost all of our General Ledger Cubes:
Code: Select all
# Logic for displaying B/S or Nonfinancials when a multiperiod consolidated rollup is selected. Note:
# current month: DB('z_ctrl_measures','current month','string 1')
# check if version is NOT forecast (ie Version is Actuals): ELISANC('Version', 'Forecast Versions', !Version)=0
# check if version has Variance Versions as a parent
[] = C:
# consolidate immediate children based on ConsolidateChildren attribute for Account+Version
IF( AttrS('Account',!Account,'ConsolidateChildren')@='Y' % AttrS('Version',!Version,'ConsolidateChildren')@='Y' ,
If( AttrS('Account',!Account,'ConsolidateChildren')@='Y' & AttrS('Version',!Version,'ConsolidateChildren')@<>'Y' ,
ConsolidateChildren('Account'),
If( AttrS('Account',!Account,'ConsolidateChildren')@<>'Y' & AttrS('Version',!Version,'ConsolidateChildren')@='Y' ,
ConsolidateChildren('Version'),
ConsolidateChildren('Account', 'Version')
)
) ,
# consolidate immediate children for versions that have Variance Versions as a parent
#If ( ELISPAR('Version','Variance Versions',!Version)=1,
# ConsolidateChildren('Version'),
#check no. of periods is multiple and the Balance Type is not AVG Calc (as this needs to be consolidated for AVG multiperiod calculation)
If ( ELCOMPN('Period', !Period)>1 & !BalanceType@<>'AVG Calc',
#if multiperiod attribute specifies "FIRST" (eg BOP # Accounts), then display 1st period in subset
If ( Attrs('Account', !Account, 'MultiPeriod') @= 'FIRST',
DB('Fin_APRAGL',!Version,!SectionA,!SectionB,!SectionC,!Company,!Affiliate,!FunctionalCell,!Product,!ProductProcessor,!ResponsibilityCentre,!Account,!CurrencyCode,
ELCOMP('Period', !Period,1),!BalanceType,!MeasureType) ,
#if account is balance sheet or "LAST" attribute (eg EOP # of Accounts) then...
If ( Attrs('Account', !Account, 'MultiPeriod') @= 'LAST' %
Attrs('Account', !Account, 'GL Category') @= '1' % Attrs('Account', !Account, 'GL Category') @= '2' %
Attrs('Account', !Account, 'AccountType') @= '1' % Attrs('Account', !Account, 'AccountType') @= '2' % Attrs('Account', !Account, 'AccountType') @= '3' %
Attrs('Account', !Account, 'AccountType') @= '7' % Attrs('Account', !Account, 'AccountType') @= '8' ,
#if period rollup is an ancestor of current month AND version is actuals (ie not an ancestor of Forecast Versions rollup) then...
If ( ELISANC ('Period', !Period, DB('z_ctrl_measures','current month','string 1') )=1 & ELISANC('Version', 'Forecast Versions', !Version)=0 ,
#...then use current month balance
DB('Fin_APRAGL',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Company,!Affiliate,!FunctionalCell,!Product,!ProductProcessor,!ResponsibilityCentre,!Account,!CurrencyCode,
DB('z_ctrl_measures','current month','string 1'),!BalanceType,!MeasureType) ,
#...else display balance of last period in subset
DB('Fin_APRAGL',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Company,!Affiliate,!FunctionalCell,!Product,!ProductProcessor,!ResponsibilityCentre,!Account,!CurrencyCode,
ELCOMP('Period', !Period,ELCOMPN ('Period', !Period)),!BalanceType,!MeasureType) ),
Continue) ),
Continue)
);
#=============================================================================