Home > BI, SQL Analysis Services > Lazy-loading with MDX (MDX Pagination)

Lazy-loading with MDX (MDX Pagination)

In a conventional T-SQL query, pagination can be applied to retrieve a subset of the total result-set. In the web development, we can use this technique to achieve lazy loading. This post will discuss how to do pagination in MDX to achieve lazy-loading

The key is to use subset function in MDX. MSDN has documented the function here.

But before you use the subset function, you have to know the total number of the result set first to calculate the total page number. The below template illustrate how you get the row count:

Image

you can simply wrap the above MDX into a OpenQuery and assign to a variable.

Once you have the row count then you can decide how you control the pagination

Image

In a web page, the initial load is not so fast considering the row count is extra task. But once you go to page 2,3, etc, you’ll feel the performance is really fast.

Advertisements
Categories: BI, SQL Analysis Services Tags: ,
  1. Lavanya
    September 2, 2015 at 10:31 pm

    will this not lead to data integrity issues? For example if the cube is modified when we are running the query, the count will change rt?

    • September 2, 2015 at 10:47 pm

      I will not lead to data integrity issue. The reason the subsequent page can go fast is because the query engine has already cached the result set. If your cube is processed, the cache of the query engine will blow away and it will re-execute the MDX and get the latest dataset anyway.

  2. Lavanya
    September 2, 2015 at 10:32 pm

    Will this not lead to data integrity issues?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: