none
Cube is slow when selecting Item SKU

    Question

  • My Cube has Brand, DateTime, and Item (product) dimensions. Everything works ok until users select/add Item No. as a row field.  At this point Cube gets very slow and does not return any data. SQL profiler shows "Query Subcube 1- Cache data - 00000000000000000,100000,0" after MDX (generated by SSAS).  Is it some sort of loop?

    SELECT NON EMPTY CrossJoin(Hierarchize({DrilldownLevel({[DateTime].[Month].[All]},,,INCLUDE_CALC_MEMBERS)}), {[Measures].[Qty on Hand],[Measures].[Items Cost]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[DateTime].[Month].[Month].[Quarter] ON COLUMNS , NON EMPTY CrossJoin(Hierarchize({DrilldownLevel({[Brand].[Brand Name].[All]},,,INCLUDE_CALC_MEMBERS)}), Hierarchize({DrilldownLevel({[Item].[No].[All]},,,INCLUDE_CALC_MEMBERS)})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Item].[No].[No].[Brand Code],[Item].[No].[No].[Bridge Size],[Item].[No].[No].[Colour Code  Frame],[Item].[No].[No].[Colour Code  Lens],[Item].[No].[No].[Diagnostic],[Item].[No].[No].[Disco Date],[Item].[No].[No].[Discontinued],[Item].[No].[No].[Frame Model Code],[Item].[No].[No].[Full Unit],[Item].[No].[No].[Gender Code],[Item].[No].[No].[Item Category Code],[Item].[No].[No].[Item Description],[Item].[No].[No].[Item Status Code],[Item].[No].[No].[Lens Group Code],[Item].[No].[No].[Material Code],[Item].[No].[No].[Product Group Code],[Item].[No].[No].[Size Diameter],[Item].[No].[No].[Style Code],[Item].[No].[No].[Treatment Coating Code],[Item].[No].[No].[Vendor Category Code],[Item].[No].[No].[Vendor No] ON ROWS  FROM (SELECT ({[DateTime].[Month].&[2013-11-01T00:00:00], [DateTime].[Month].&[2013-10-01T00:00:00]}) ON COLUMNS , ({[Brand].[Brand Name].&[ENCORE]}) ON ROWS  FROM [cubeInventoryStatus]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS


    • Edited by BrBa Tuesday, November 19, 2013 5:15 PM
    Tuesday, November 19, 2013 5:14 PM

All replies

  • If you wait and wait, does the cube eventually return data? or do you get an out of memory exception?

    Are there a lot of those events (Query Subcube 1- Cache data) being generated each time the query executes? If so, then the query is probably being evaluated in Cell-by-Cell mode. 

    How many items are we talking about in this context? 5k, 100k, 10mm, more?


    BI Developer and lover of data (Blog | Twitter)

    Tuesday, November 19, 2013 8:35 PM
  • No Errors, Cube rarely/sometimes returns some item data.

    Items count approx. 17k unique Items, but Inventory has daily Qty and Cost snapshots on them. Inventory table 170 mils broken down by 3 partitions.



    • Edited by BrBa Tuesday, November 19, 2013 9:10 PM
    Tuesday, November 19, 2013 9:09 PM
  • Also could it be due to Time dimension being extensive - from 1/1/2007 till 12/31/2019 and it's stored in DSV

    Could such Time dimension slow down the performance?

    If this could be the cause, how to re-create time dimension? Is it better to store it in a database table? (I used Dim Wizard to create it)

    Tuesday, November 19, 2013 9:49 PM
  • No Errors, Cube rarely/sometimes returns some item data.

    Items count approx. 17k unique Items, but Inventory has daily Qty and Cost snapshots on them. Inventory table 170 mils broken down by 3 partitions.



    I'm a bit confused, are you saying sometimes it returns without data or is the query cancelled before it returns?

    Are you saying the inventory table has 170 millions records?  

    have you tried more simple queries just as qty/cost by item for a single date?


    BI Developer and lover of data (Blog | Twitter)

    Tuesday, November 19, 2013 10:19 PM
  • Also could it be due to Time dimension being extensive - from 1/1/2007 till 12/31/2019 and it's stored in DSV

    Could such Time dimension slow down the performance?

    If this could be the cause, how to re-create time dimension? Is it better to store it in a database table? (I used Dim Wizard to create it)

    Seeing as this is a snapshot table, are the Cost/Qty measures using the LastNonEmpty aggregation function or are you using Standard Edition and rolling your own via the MDXScript?

    The original query you posted is 


    BI Developer and lover of data (Blog | Twitter)

    Tuesday, November 19, 2013 10:21 PM
  • Yes, Yes and Yes

    1. Cost/Qty measures are semi-additive, actual Cost/Qty measures are set as "last Value", I'm on Standard Edition and I used calculated measures with own mdx to show last month/period  (currently removed to test performance improvement). At some point , when I tested this calculated measure  and the performance was not that bad (my DateTime dimension was 2010-2013 at that moment. After that I expanded DateTime dimension to from 1/1/2007 till 12/31/2019....and performance slowed down

    Could such Time dimension slow down the performance?If this could be the cause, how to optimize time dimension?

    2. For some filter combinations (limited by date and brand) I was able to get to the ItemNo. Also it works much better in Visual Studio environment (Browser tab) than when using Excel and connecting via remote datasources. In Excel ItemNo would not populate.

    3. if this is Cell-by-Cell mode, how it could be optimized?

    4. I broke ItemInventory with 170mils into 3 partitions and applied "usage aggregations" on them,  but no improvent.



    • Edited by BrBa Wednesday, November 20, 2013 2:17 PM
    Wednesday, November 20, 2013 1:52 PM

  • 1. Cost/Qty measures are semi-additive, I'm on Standard Edition and I used calculated measures with own mdx (currently removed to test performance improvement). At some point , when I tested this calculated measure, and the performance was not that bad (my DateTime dimension was 2010-2013 at that moment. After that I expanded DateTime dimension to from 1/1/2007 till 12/31/2019....and performance slowed down

    Could such Time dimension slow down the performance?If this could be the cause, how to optimize time dimension?

    2. For some filter combinations (limited by date and brand) I was able to get to the ItemNo. Also it works a bit better in Visual Studio environment (Browser tab) than when using Excel and connecting via remote datasources.

    3. if this is Cell-by-Cell mode, how it could be optimized?

    4. I broke ItemInventory with 170mils into 3 partitions and applied "usage aggregations" on them,  but no improvent.

    1. yes - a large time dimension could slow down the performance for a roll-your-own last-non-empty calculated measure. can you post your calculated measure?

    2. could just differences in the way the 2 tools are generating the MDX

    4. what does "170mils" mean.  Is that "170 million records"?


    Also, is there a noticeable performance difference for cold cache vs warm cache executions?


    BI Developer and lover of data (Blog | Twitter)

    Wednesday, November 20, 2013 2:13 PM
  • Thank you for prompt response!

    1  -here is the calculated measure code - it should be hierarchy agnostic but it may be not ( I have Year-Week-Date and Year-Quarter-month-date), as I'm not getting accurate results on month level.

    ([measures].[quantity],
    TAIL(NonEmpty(Existing [DateTime].[Date].[Date].Members,[measures].[quantity]),1).Item(0))

    2 - yes, tool issue and also it looks like excel creates manual filter for ItemNo and does not list them as expected. So after I removed Calculated measures and repartitioned and did usage aggregations, I got better performance. ItemNo gets returned but Excel hides it under manual row filter (another issue)

    3 - Item inventory has 170 millions records

    4 - I did not do warm/cold cache thing (due to luck of knowledge on the subject)

    Wednesday, November 20, 2013 2:50 PM