locked
Querys very slows RRS feed

  • Question

  • Good morning, 
    My problem is as follows: 
    I have a table of products (Dimension products) and other customers 
    (dimension customers), each relates separately with the table 
    facts, but there is no link between them. 
    When I make a query from ProClarity that combines 
    customers 
    products will slow 
    taking a long time and finally get the error 'Query Failed'
    .
    How can I solve this problem?
    Thank you very much
    Tuesday, September 9, 2008 8:15 AM

Answers

  • If you have a large number of Customers and Products then I can understand why it would be slow.  If you constructed the query using ProClarity's UI, then the query is using some basic syntax similar to the following:

     

    NON EMPTY {{<Customer members>}*{<Product members>}}

     

    In order for the results to be generated, SSAS will first have to perform a cross product of the two dimensions before it attempts to remove the combinations that contain no data.  So, let's say you have 5,000 customers and you sell about 200 products.  This simple crossjoin will generate 1 million tuples/cells.  SSAS will then combine the cells with the rest of the query and go through 1 million rows, one at a time, to see if any of the columns contain data and eliminate those rows that don't have data.  As can be expected, this can take a while.

     

    You can speed up the process by turning on the NonEmptyCrossJoin (NECJ) feature found in the Options dialog under the File|Options menu.  This will modify your query to look similar to this:

     

    NON EMPTY {NonEmptyCrossJoin({<Customer members>},{<Product members>})}

     

    The NECJ function will run exponentially faster.  I have used it in certain situations to take a query that was running in 2 to 3 minutes and return the results in 10 seconds.  However, the function should not be used in all cases due to some inherent problems with it, which is why ProClarity does not turn it on by default.  Part of the problem with ProClarity's implementation though is that they will indiscriminately use the NECJ anytime a crossjoin is needed, eliminating records in some cases you did not want to.  They should have only used the NECJ when there was both a crossjoin and a Non Empty statement on an axis, seeing as how that is technically the equivalent concept.  

     

    SSAS 2005 came out with a replacement function called NonEmpty that works like the NECJ but without many of the problems.  Unfortunately, ProClarity never updated their tool to use this function.  You should find some additional information about both functions in Books Online as well as a number of blogs, such as Mosha's (www.mosha.com). 

    Monday, September 15, 2008 5:57 PM

All replies

  • Hi olroxa,

     

    Are you able to run the same query from the MDX sample app or the Management Studio?

    Tuesday, September 9, 2008 1:25 PM
  • hello Sean,
     
    thanks for your answer,
     
    If I run the query in Management Studio I get the following error:

    The server sent an unrecognized response.
    And not return any result

     

    Wednesday, September 10, 2008 2:36 PM
  •  

    If you can't get the query to run in SSMS it's not going to work in ProClarity either.  Your best bet is to dig down into the cube and find out why/where the query is breaking.
    Wednesday, September 10, 2008 4:50 PM
  • Feel free to post the MDX and we'll see if we can offer some suggestions as to the issue.

     

    Thursday, September 11, 2008 8:40 PM
  • If you have a large number of Customers and Products then I can understand why it would be slow.  If you constructed the query using ProClarity's UI, then the query is using some basic syntax similar to the following:

     

    NON EMPTY {{<Customer members>}*{<Product members>}}

     

    In order for the results to be generated, SSAS will first have to perform a cross product of the two dimensions before it attempts to remove the combinations that contain no data.  So, let's say you have 5,000 customers and you sell about 200 products.  This simple crossjoin will generate 1 million tuples/cells.  SSAS will then combine the cells with the rest of the query and go through 1 million rows, one at a time, to see if any of the columns contain data and eliminate those rows that don't have data.  As can be expected, this can take a while.

     

    You can speed up the process by turning on the NonEmptyCrossJoin (NECJ) feature found in the Options dialog under the File|Options menu.  This will modify your query to look similar to this:

     

    NON EMPTY {NonEmptyCrossJoin({<Customer members>},{<Product members>})}

     

    The NECJ function will run exponentially faster.  I have used it in certain situations to take a query that was running in 2 to 3 minutes and return the results in 10 seconds.  However, the function should not be used in all cases due to some inherent problems with it, which is why ProClarity does not turn it on by default.  Part of the problem with ProClarity's implementation though is that they will indiscriminately use the NECJ anytime a crossjoin is needed, eliminating records in some cases you did not want to.  They should have only used the NECJ when there was both a crossjoin and a Non Empty statement on an axis, seeing as how that is technically the equivalent concept.  

     

    SSAS 2005 came out with a replacement function called NonEmpty that works like the NECJ but without many of the problems.  Unfortunately, ProClarity never updated their tool to use this function.  You should find some additional information about both functions in Books Online as well as a number of blogs, such as Mosha's (www.mosha.com). 

    Monday, September 15, 2008 5:57 PM
  • Ok JPicker,

    Thanks for your anwers.

     

    Best Regards,

     

    Friday, September 19, 2008 11:16 AM