locked
PLEASE HELP PoClarity query is Slow... only sometimes (10-20 min or more for a report) RRS feed

  • Question

  • I run reports on ProClarity on a daily basis and I have encountered several issues but all minor except for this one. Let me explain a situation in which I encounter the problem then I will tell you what happens. At our company we have 20,000+ products and 40,000+customers, or something like that, maybe more. I will create a set/member of products (lets say 10 products) and I put them in the background and put customer name on the rows section so I can see which customers buy those products and exactly how much we are selling to them (lets say by month, for 1 year). So it is pulling all 40,000 customers to see if we sell those items to them and I have the filter on to remove customers with zero sales.

    Here is the issue: There will be times when the query runs quickly (less than 30 seconds) but then there are times like now where it appears it will never produce a result (even after 15, 20, 25 min). I waited about 15 min a little while ago for a 12 month report that had only 8 customers buying the member of products I have in the background. Why in the world will it run so slow/not produce any result, and other times produce near instant results?

    Here are my exact report query items: Rows-Customer Name (40,000 or more toil customers), Columns-Month (12 months), Background-sales and the set of products, then I have a filter to remove blank rows and columns so it only gives me customers with data for those products.

     

    HELP Me?

     

    Wednesday, September 8, 2010 9:28 PM

All replies

  • In addition to the info above, I don’t know what version of ProClarity I have, but my company is using SQL server 2005

    Wednesday, September 8, 2010 9:38 PM
  • One thing to try here would be to disable the query size check.  Does the issue still happen with this disabled?

    http://blogs.technet.com/b/proclarity/archive/2010/04/19/increasing-professional-performance-by-disabling-query-check.aspx


    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, September 9, 2010 1:46 AM
  • Sean,

    This does not help, but i found something that may help you answer my question. Like i said before, thousands of customers, and thousands of products. But i created a set of only 4 products to run in the background(combined) to see which customers have data.

    Here is what i found: When i put my set in the back ground and combine it it doesnt even provide results in 20 min and i have to shut it off (ive tried making the set a member too). If i run them sliced one at a time it is instant. If i were to search with the search option for the products and select all of them manually and then combine them it will run it instantly for all products (manual selection of the products and combine them in the background without creating a set).

    Here something else that will help you: I noticed a lot of talk about what the MDX editor says, so here is the difference between the two. When i have my set that i combine in the background the mdx editor will show its running the report for the aggregate of my combine set, and for the manual selection and combine them, instead of saying aggregate of set, it list the code for each product seperatley.

    These are the 1st parts of the MDX everything is the same between the two:

    My combined set:

     

    WITH MEMBER [Product].[Product].[All Product].[ Aggregation] AS ' AGGREGATE( { [<##<SET!gavin.phillips!KOO BTN+>##>] }) ', SOLVE_ORDER = 0  SELECT { [Fiscal Year].[All Fiscal Year].[Year].&[2011].&[H1 FY2011].&[Q1 FY2011].&[2011]&[7], [Fiscal Year].[All Fiscal Year].[Year].&[2011].&[H1 FY2011].&[Q1 FY2011].&[2011]&[8] } ON COLUMNS ,

     

     

    Manual selection with products combine:

    WITH MEMBER [Product].[Product].[All Product].[ Aggregation] AS ' AGGREGATE( { [Product].[Product].[Product Description].&[123921], [Product].[Product].[Product Description].&[126112], [Product].[Product].[Product Description].&[126113] }) ', SOLVE_ORDER = 0  SELECT { [Fiscal Year].[All Fiscal Year].[Year].&[2011].&[H1 FY2011].&[Q1 FY2011].&[2011]&[7], [Fiscal Year].[All Fiscal Year].[Year].&[2011].&[H1 FY2011].&[Q1 FY2011].&[2011]&[8] } ON COLUMNS ,

     

     

    The manual selection runs perfectly and the combined set wont run at all. Due to the fact that i run reports containing hundreds of products, i cannot select them manually each time so i create sets on the MDX editor using product codes.

     

    Mabye you or someone can use this extra information to help me?

    Thursday, September 9, 2010 3:24 PM