SSAS LastNonEmpty Performance

• siva-214494

  Valued Member

  Points: 50

  More actions

  July 14, 2017 at 3:31 am

  #151095

  Hi,

  I have an SSAS cube which has about 100 measures, 10 dimensions and require a tweak for performance tuning please. Of the 100 measures, 70 of them are sum measures, 30 are lastnonempty measures - storage on MOLAP. When I select all of the 70 measures in a mdx query, it takes about 10 seconds. When I select few lastnonempty measures, results come in 10 seconds again... But when I select some sum measures of the 70 and some lastnonempty measures (say 20 of sum and 5 of lastnonempty), it runs for 30minutes.

  Any suggestions on how to fix this issue ? Thanks

  SELECT

  {

  [Measures].[Measure1],
  [Measures].[Measure2]
  }

  DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS,
  NON EMPTY CROSSJOIN(
  AddCalculatedMembers( Except(
    [Company].[Company Code].Members,
    {[Company].[Company Code].Levels(0).Members}) ),
  CROSSJOIN(
    Union(
    [ReportableHierarchy].[Consolidation Reporting Hierarchy].[Consol Group].AllMembers,
    AddCalculatedMembers(Descendants([ReportableHierarchy].[Consolidation Reporting Hierarchy].[Consol Group].&[Marketing], [ReportableHierarchy].[Consolidation Reporting Hierarchy].[Business Unit Group], SELF))),
    CROSSJOIN(
     [ReportableHierarchy].[Profit Centre].[Profit Centre].AllMembers,
     CROSSJOIN(
      AddCalculatedMembers( Except(
      [Scenario].[Scenario].Members,
      {[Scenario].[Scenario].Levels(0).Members}) ),
      Generate( [Calendar].[Dates].[Year].AllMembers, AddCalculatedMembers(Descendants([Calendar].[Dates].CurrentMember, [Calendar].[Dates].[Month], LEAVES)) ))))) DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON ROWS
  FROM [cmvCube]