CODEWITHSUNDEEP
ssasmdx
letecmig
letecmig

Reputation: 1

MDX query performance: inconsistent processing times

I am puzzled by strange behavio/performance incosistencies when I run certain MDX query.

The processing times are very inconsistent when I run query for a single member or for two members 'on rows':

The hierarchy has 7 levels: for example, "A" is member on level 6. "A" has 2 children: "X" and "Y".

When I run the MDX with member "X" ‘On Rows’, it takes two secs.

When I run same MDX for a member "Y" ’On Rows’, it takes 2 secs as well. If I run same MDX query for both members: {member "X", member "Y"} ‘On Rows’
…. It suddenly takes 2-3 minutes to process this MDX!!

Any possible explanation for this strange behaviour?

The MDX query looks like this:

WITH 
  SET [XL3WithSet0] AS 
    {
      (
        [Measures].[Original Exposure Amount - Drilldown]
       ,[ExpPortfolioType].[ExposurePortfolioType].[All].[COREP - CRD IV].[Standardised]
       ,[PortfolioType].[PortfolioType].[All]
       ,[CRMGroup].[CRMGroup].[All]
       ,[CCFBand].[Hierarchy].[All]
       ,[DrilldownCreditRisk].[InflowOutflowFlag].[All]
      )
    } 
...**********another 25 sets combining [Measures] AND selected dimensions*****
SELECT 
  {
    [XL3WithSet0]
   ,[XL3WithSet1]
   ,[XL3WithSet2]
   ,[XL3WithSet3]
   ,[XL3WithSet4]
   ,[XL3WithSet5]
   ,[XL3WithSet6]
   ,[XL3WithSet7]
   ,[XL3WithSet8]
   ,[XL3WithSet9]
   ,[XL3WithSet10]
   ,[XL3WithSet11]
   ,[XL3WithSet12]
   ,[XL3WithSet13]
   ,[XL3WithSet14]
   ,[XL3WithSet15]
   ,[XL3WithSet16]
   ,[XL3WithSet17]
   ,[XL3WithSet18]
   ,[XL3WithSet19]
   ,[XL3WithSet20]
   ,[XL3WithSet21]
   ,[XL3WithSet22]
   ,[XL3WithSet23]
   ,[XL3WithSet24]
   ,[XL3WithSet25]
  } ON COLUMNS
  --Non Empty   
 ,{
    [DrilldownCreditRisk].[ExposureDrilldown].[All].[Legal Entity 2].[CBRC Repo style MarketParticipant  40089].[Exp No. 14660042].[UNSECURED].[Unsecured - Undrawn Tranche]
   ,[DrilldownCreditRisk].[ExposureDrilldown].[All].[Legal Entity 2].[CBRC Repo style MarketParticipant  40089].[Exp No. 14660042].[UNSECURED].[Unsecured - Drawn Tranche]
  } ON ROWS
FROM [COREP Credit Risk]
WHERE 
  (
    [Time].[Time].[All]
   ,[Scenario].[Scenario].[All]
   ,[ConsolidationProfile].[ConsolidationProfile].[All]
   ,[ExposureType].[ExposureType].[All].[COREP Reporting - CRD IV]
   ,[IntragroupFlag].[IntragroupFlag].[All]
  )
CELL PROPERTIES VALUE;

Upvotes: 0

Views: 67

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

If you use the [DrilldownCreditRisk].[Level 6] attribute hierarchy members X and Y on rows instead of the [DrilldownCreditRisk].[ExposureDrilldown] hierarchy is it fast (regardless of whether the query returns the same numbers)? Is your hierarchy natural? (Do attribute relationships exist between levels? Do you get a warning at design time about attribute relationships for that hierarchy?) If your hierarchy is unnatural then go to the Attribute Relationships tab and define a chain of attribute relationships that mirrors your hierarchy until the warning icon on your hierarchy goes away.

Performance of natural hierarchies will always be better than unnatural.

Upvotes: 3

Related Questions