locked
Very slow query time RRS feed

  • Question

  • Hello,

    Currently we have reports in excel sheet which has pivot tables created from cubes.

    I''' explain how these cubes are created:

    We have several cubes, then one cube (lets call it cube A)which links all other cubes. Now cube A has several calculations which are built from the different linked cubes.

    Then i combine all the calculations into one measure using a case statement. The case statement is like following

    CREATE MEMBER CURRENTCUBE.[Measures].[Combined]
     AS CASE

    WHEN [Goals Details].[Goal Description]=[Goals Details].[Goal Description].&[abc] then  [Measures].[abc]

    WHEN [Goals Details].[Goal Description]=[Goals Details].[Goal Description].&[xyz] then [Measures].[xyz] and so on. Like this we have uabout 50+measures combined in [Measures].[Combined].

    We use [Measures].[Combined] and  [Goals Details].[Goal Description] dimension attribute to get all the measures in pivot in excel. However, recently, as we are adding more measures, the time to refresh the excel sheet is increasing exponentially.

    Do you think "SCOPE" instead of "CASE" is a better approach?

    How do i optimize this? Please help. 

    Initially,( 3 weeks back) everything was fine. but now without any major changes suddenly things have slowed down and is creating a lot of problem.

    Thanks



    Wednesday, December 5, 2012 6:32 PM

Answers

All replies