locked
Dax query topn out of memory very fast RRS feed

  • Question

  • Hi guys, I'm trying to take a look at some sample data in a tabular database table (that is quite large). I thought I could just use the topn function but I am getting an out of memory error. I understand I can filter the set down further before doing this topn (and I can in this case) but what I'm not clear on is if this error coming from the server or the client? Surely the topn is getting processed on the server, so I guess it is the server?

    My query is simply

    evaluate

    topn(

    20,'Email Events'

    , 'Email Events'[Customer Key]

    )

    And I run this in DaxStudio 2.3.6 and after 40secs I get

    31/03/2016 18:04:11  The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

    Can someone confirm that this is the server and not my machine that is out of memory before I raise this to increase the server RAM?

    Thanks

    Brett

    Thursday, March 31, 2016 5:15 PM

Answers

  • Hi Brett,

    According to your description, you are experiencing out of memory issue, now you need to know if this error coming from the server or the client, right?

    To confirm that this is the server and not my machine that is out of memory, you can check the available memory in Windows task manager or performance monitor in your client machine. OR you can connect to SSAS instance in SSMS and run you DAX query in that database.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Saturday, April 16, 2016 6:48 AM
    • Marked as answer by Charlie Liao Sunday, April 17, 2016 2:34 PM
    Saturday, April 2, 2016 2:56 AM
  • Memory issues aside, this query looks like you're trying to retrieve the 20 email events with the largest customer key. I couldn't think of a scenario where to use this, but anyway: when you have a large number of email events, you'll probably have many events for the same customer (key). In this case TOPN will return all email events for this customer, as it cannot determine which 20 rows to return. So your table could be much larger than you'd expect.
    • Proposed as answer by Charlie Liao Saturday, April 16, 2016 6:48 AM
    • Marked as answer by Charlie Liao Sunday, April 17, 2016 2:34 PM
    Monday, April 4, 2016 6:36 AM
    Answerer

All replies

  • Hi Brett,

    According to your description, you are experiencing out of memory issue, now you need to know if this error coming from the server or the client, right?

    To confirm that this is the server and not my machine that is out of memory, you can check the available memory in Windows task manager or performance monitor in your client machine. OR you can connect to SSAS instance in SSMS and run you DAX query in that database.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Saturday, April 16, 2016 6:48 AM
    • Marked as answer by Charlie Liao Sunday, April 17, 2016 2:34 PM
    Saturday, April 2, 2016 2:56 AM
  • Memory issues aside, this query looks like you're trying to retrieve the 20 email events with the largest customer key. I couldn't think of a scenario where to use this, but anyway: when you have a large number of email events, you'll probably have many events for the same customer (key). In this case TOPN will return all email events for this customer, as it cannot determine which 20 rows to return. So your table could be much larger than you'd expect.
    • Proposed as answer by Charlie Liao Saturday, April 16, 2016 6:48 AM
    • Marked as answer by Charlie Liao Sunday, April 17, 2016 2:34 PM
    Monday, April 4, 2016 6:36 AM
    Answerer