none
can anyone help me decipher this SQL Trace?

    Question

  • So I've setup a cube based on numerous discussion on this board for sales data and metrics that we would like to see. I've loaded about 4.5 million record into the cube and the cube takes quite sometime to return the results when browsing the cube (60-90sec). 

     SELECT 
     NON EMPTY { 
    			[Measures].[Sales], 
    			[Measures].[ASP Current] 
    		    } ON COLUMNS, 
    NON EMPTY { 
    			(
    			 [BU].[BU].[BU].ALLMEMBERS * 
    			 [Customer].[CustLevels].[CUST NUMBER KEY].ALLMEMBERS * 
    			 [Period to View].[PeriodHierarchy].[CAL MONTH ID].ALLMEMBERS
    			 ) 
    		  } 
    DIMENSION PROPERTIES MEMBER_CAPTION, 
    MEMBER_UNIQUE_NAME ON ROWS 
    FROM ( 
    	  SELECT ( { [Period to View].[PeriodHierarchy].[CAL YEAR ID].&[2.012E3] } ) 
    			 ON COLUMNS FROM [CCG PV]
    	  ) 

    I ran a SQL trace on the following query that was generated by reporting services to see what was going on but I do not have experience with SQL Trace so I am not sure how to read/improve based on this

    trace file can be found here

    http://dl.dropbox.com/u/45410834/sqltrace.trc

    • Edited by k1ng87 Thursday, March 01, 2012 6:48 PM
    Thursday, March 01, 2012 6:43 PM

All replies

  • Hi K1ng87

    You can read the trace file by using the below command. Function used for this is fn_trace_gettable.

    FROM fn_trace_gettable(‘D:\TraceFile.trc’, default) Trace

    More Informations you will get on belo link

    Viewing and Analyzing Traces with SQL Server Profiler

    Suhas_Akole


    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Friday, March 02, 2012 5:21 AM
  • Hello Suhas,

    I can read the SQL Trace file but I jsut don't understand the content inside and how I can use it to improve the performance of my cube. 

    Friday, March 02, 2012 2:15 PM
  • Have you created any partitions for this cube?


    please remember to mark as answered if the post helped resolve the issue.

    Friday, March 02, 2012 3:53 PM
  • Hi K1ng87,

    I believe your goal is to improve the query.

    So, most important thing is the column "Duration"

    1. Open the trace file

    2. go to

    a.File --> properties

    b.goto "event Selection" tab 

    c.click on "Organize columns" 

    d. move "duration" column next to even class , click ok

     At the end of the trace file , 3rd line from the bottom

    There is a event called "Query End" , that is the end of the query

    amd duration is 76233 mili seconds

    Means your query takes around 76 seconds

    3. Other than the cube I see five subcube or subselect of your query

    and you can see duration corresponding to those queries, as shown in the figure below:

    I hope this answers your question ,

    please remember to mark as answered .

    thanks

    -Ashim

    • Proposed as answer by AshimM Saturday, March 03, 2012 5:14 AM
    Saturday, March 03, 2012 5:14 AM
  • Hello Ashim,

    Thank you for the response. Although very helpful, i'm looking for ways to improve query response time by using the data in sql trace. Not that familiar with sql trace in ssas but was hoping it could point to ways I could improve the mdx or structure of my cube to improve query response times. such as adding in partitions, aggregations, etc...

    • Edited by k1ng87 Monday, March 05, 2012 3:39 PM
    Monday, March 05, 2012 3:30 PM
  • Hi Rok1,

    I created partitions by quarters of years (5 partitions total from q1 2011 through q1 2012) which have about 1 million rows in each but still no performance gain.

    Monday, March 05, 2012 7:17 PM
  • Firstly, Is that trace you posted before or after you created partitions. If its before, can you run those queries and run profiler on the background while running those queries and post back the sql trace like you did before?

    Before you run your queries it'd helpful if you can run the xmla clear cache statement (you just need to update yourdatabase name to your ASDB)

    <

    ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

    <

    Object>

    <

    DatabaseID>YOURDATABASENAME</DatabaseID>

    </

    Object>

    </

    ClearCache>


    please remember to mark as answered if the post helped resolve the issue.

    Tuesday, March 06, 2012 5:46 PM
  • Hi,

    There are many approaches to performance tuning. Just soem questions:

    How large is the measure group you are accessing? How large is the result you get? Is the runtime much shorter if you rerun the same query (cahcing effect)? Do you have complex calculations in your calculation script?

    Frank

    Tuesday, March 06, 2012 7:43 PM
  • Hi k1ng87,

    In your query, the part of most consuming-time is the cross join of 3 sets as below -

     ([BU].[BU].[BU].ALLMEMBERS *
      [Customer].[CustLevels].[CUST NUMBER KEY].ALLMEMBERS *
      [Period to View].[PeriodHierarchy].[CAL MONTH ID].ALLMEMBERS)

    If you can exclude some useless members of the sets under business logic, that will reduce query time. From your query logic, i think first what we could do is use  [Period to View].[PeriodHierarchy].[CAL YEAR ID].&[2.012E3].children for the replace of [Period to View].[PeriodHierarchy].[CAL MONTH ID].ALLMEMBERS. Secondly, we could remove "DIMENSION PROPERTIES MEMBER_CAPTION, EMBER_UNIQUE_NAME" which is generated automatically by SSAS server and there is no impact and benefit on the query result but consume system resource. So, with my knowledge based on the query you provide, the query could be writen like this -

    SELECT
     NON EMPTY {
       [Measures].[Sales],
       [Measures].[ASP Current]
          } ON COLUMNS,
    NON EMPTY {
       (
        [BU].[BU].[BU].ALLMEMBERS *
        [Customer].[CustLevels].[CUST NUMBER KEY].ALLMEMBERS *
        [Period to View].[PeriodHierarchy].[CAL YEAR ID].&[2.012E3].children
        )
        }  ON ROWS
    FROM  [CCG PV]

    To use the query directly in the Reporting Services, you need swich to query mode on the query designer. See http://msdn.microsoft.com/en-us/library/ms403829.aspx to learn how to swich the query mode, then you could copy + paste the query in the query pane for use.

    In addition, as i mentioned above, you could further exclude some useless members for the other 2 sets. For example, if the measure on the BU dimension is empty, then that BU member will unmeaningful to join the customer and period, so you could exclude such kind of BU members with NonEmpty function like this -

    NonEmpty([[BU].[BU].[BU].ALLMEMBERS],[Measures].[Sales])

    See http://sqlblog.com/blogs/mosha/archive/2006/10/09/mdx-nonempty-exists-and-evil-nonemptycrossjoin.aspx for more information about MDX optimzation and the usage of NonEmpty.

    If you want more exact and optimal query, provide more details of business requirement.

    thanks,
    Jerry

    Wednesday, March 07, 2012 2:47 AM
    Moderator
  • Hey Everyone,

    thanks for the responses. I guess I should have stated that I'm doing the SQL trace when I open the excel file that is connected to the cube (which refresh when it opens). The reason for this is that I want to mimick what the end user will experience. The plan is the have this cube connected via excel by users so they can slice the data they want to. 

    On Frank PI comment. I think is has to do with the way some of my dimensions are set up, specifically [Customer] and [Sku]. The [Customer] dim contains about 77,987 members and [Sku] has about 279,185 members. I have calculations that happen at the Cust Number+Sku match. For instance, I want to see the average sale price of the Skus that that Customer purchased for all Customers which I calculated like this...

    SCOPE([Measures].[ASP Current]);
    SCOPE([Sku].[Part Number key].[Part Number key].members, [Customer].[CustLevels].[CUST NUMBER KEY].members);
    THIS = IIF([Measures].[Qty]=0, NULL, ([Measures].[Sales]/[Measures].[Qty])
    );
    END SCOPE;
    END SCOPE;

    I also have a series of calculation that need to look at the same Sku sold to the same Cust Number and compare those over time such as this one...

    SCOPE([Measures].[Price Impact Fy]);
    SCOPE([Sku].[Part Number key].[Part Number key].members, [Customer].[CustLevels].[CUST NUMBER KEY].members);
    THIS = IIF(([Measures].[Sales Repeat]=0), 
                Null,
                 ((([Measures].[ASP Current]-[Measures].[Full Ly ASP])*[Measures].[Qty]))
    );
    END SCOPE;
    END SCOPE;

    the above calculation also references [Full Ly ASP] and [Sales Repeat] calculations which are the following...

    ----------Full Ly ASP----------------
    
    SCOPE([Measures].[Full Ly ASP]);
    SCOPE([Sku].[Part Number key].[Part Number key].members, [Customer].[CustLevels].[CUST NUMBER KEY].members);
    THIS = IIF([Measures].[Full_Ly_Qty]=0, NULL, ([Measures].[Full_Ly_Sales]/[Measures].[Full_Ly_Qty]));
    END SCOPE;
    END SCOPE;
    
    
    
    --------------Sales Repeat---------------
    
    CREATE MEMBER CURRENTCUBE.[Measures].[Sales Repeat] AS NULL;
    
    SCOPE([Measures].[Sales Repeat]);
    SCOPE([Period to View].[CAL YEAR ID].[CAL YEAR ID].MEMBERS);
    THIS = [Measures].[Full Ly Sales Repeat];
    END SCOPE;
    SCOPE([Period to View].[CAL MONTH ID].MEMBERS, [Period to View].[CAL QTR ID].[CAL QTR ID].MEMBERS);
    THIS = (
            [Measures].[Sales Repeat],
            Ancestor(
                     [Period to View].[PeriodHierarchy].currentmember, [Period to View].[PeriodHierarchy].[CAL YEAR ID]
                    )
            );
    END SCOPE;
    END SCOPE;

    The calculations work with the logic I need to implement but the cube just TAKES FOREVVER to comeback with results (not long to processes though, about 20min with a 2.5million record count). 

    I'm guessing my dimenions for [Customer] and [Sku] need to optimized? Thoughts?


    • Edited by k1ng87 Wednesday, March 07, 2012 2:38 PM
    Wednesday, March 07, 2012 2:37 PM
  • would that many members cause an issue?
    Thursday, March 08, 2012 1:29 PM
  • Hi,

    In most cases, it is more efficient in Analysis Services to do leaf level calculations when loading/processing the cube. And the names "Part number key" and "Cuts number key" hint into that direction.

    So I would assume that pushing this calculation logic to some calculated columns in the DataSourceView, or maybe even to the fact table or fact view that the DSV accesses, would speed up the query runtime, and probably not increase processing time dramatically.

    Analysis Services is good at aggregating precalculated values, but not always at aggregating values that need to be calculated on leaf level at runtime.

    Frank

    Friday, March 09, 2012 5:37 PM
  • Hi Frank,

    I agree with you that if possible to push calculations to the DSV. But in this case I don't believe that is possible. The reason I say that is I have data at a daily transaction level and users usually view the data by month, qtr, or year and thats why i don't believe I can do the calculation at the DSV. I simple average calculation such as Sales/Qty would give different results when aggregated at different levels.

    Friday, March 09, 2012 6:32 PM