Comparison of a Stored and Dynamic ASO Hierarchy

I've heard from many people on many occasions that when designing an ASO database you should make as many dimensions Stored as possible. Just how much will this improve performance?

There are many different way this can be tested. I will start out with a simple test case to gauge retrieval performance. I have four scenarios in mind for use with the ASOsamp database:

1. Store dimension set to Stored. No aggregations.
2. Store dimension set to Dynamic. No aggregations.
3. Store dimension set to Stored. Recommended aggregations.
4. Store dimension set to Dynamic. Recommended aggregations.

I will run two different queries to measure retrieval performance. Query 1 will be the default retrieve. Query 2 will zoom to all levels of the Store dimension with the following grid (all twelve months are not shown for size purposes).

The Results

The Analysis

For Query 1 with no aggregations, the Stored dimension is faster but on Query 2, the Dynamic dimension is faster. With aggregations, Query 1 is the same between Stored and Dynamic. With aggregations, Query 2 is slightly faster with the Dynamic dimension than with Stored.

I have no idea why the Dynamic dimension is faster for Query 2. Any reason I could come up with would be purely speculative. Is it the size of the input data, the distribution of the data, the size of the dimension, the full moon? I have no clue. I did rerun these tests many times so it wasn't just a fluke -- the results were consistent. I'm going to have to explore this further with more testing. Any insights or ideas on further test cases on this would be appreciated.

Measuring NONEMPTYMEMBER/TUPLE in MDX Part 2

Welcome to MDX Monday. I'll pick up where I left off in Measuring NONEMPTYMEMBER/TUPLE in MDX Part 1 last Monday. I ended that part with the desire to test out the same non-empty directives within member formulas. In this test I will use the calculation portion of the query in Part 1 and place it into a member formula. Then I'll try using NONEMPTYTUPLE and NONEMPTYMEMBER in order to see if there is a difference in performance in the ASOsamp database.

The Member Formula

NONEMPTYTUPLE ([Units], [MTD])

  Sum(

       {

        ClosingPeriod(Time.Generations(5), Time.CurrentMember),

        Time.CurrentMember.Lag(1),

        Time.CurrentMember.Lag(2)

        }, 

        Units

   )

The Tests

In this test I will duplicate the data pull that I used in Part 1 by zooming to the bottom on the Store Manager and Stores dimensions with the month of March selected. These are the four tests. Each retrieval will be identical.

1. Run the example as is, including the NONEMPTYTUPLE statement.
2. Remove the NONEMPTYTUPLE statement.
3. Change the NONEMPTYTUPLE ([Units],[MTD]) to NONEMPTYMEMBER [Units]
4. Change the NONEMPTYTUPLE ([Units],[MTD]) to NONEMPTYMEMBER [MTD]

The query time results are below. Each returned the exact same set.

The Results

1. 0.39s
2. 9.72s
3. 0.29s
4. 9.7s

Note: I got the same warning in the log for the 3 Month Units member as I got in Part 1 during the MDX query execution for Test 4. 

Warning: Non empty member directive [MTD] for member [3 Month Units] will be ignored since it is from a different dimension.

Analysis and Conclusions and Another Test

These results were almost exactly in line with the results in Part 1. They were all +.07 or +.08 the respective test in Part 1, the difference probably attributed to overhead/network latency associated with Smart View. The non-empty directive can help ASO member formulas. I wondered if it had something to do with the fact that an attribute dimension (Store Manager) was used in the query. So I removed that and reran the test.

1. 0.34s
2. 0.39s
3. 0.24s
4. 0.38s

It looks like the attribute dimension was hurting the performance when not using the non-empty directive. The difference in performance without the attribute dimension is very minor but there still is a difference albeit very small. The bottom line is that you should probably at least try to use the NONEMPTYMEMBER and NONEMPTYTUPLE when the application log suggests it.