ASO Query Tracking Trivia

A Network54 Essbase board user asked a question today that I’ve heard a few times without ever being sure of the answer:

“If the MDX report run after query tracking is enabled returns no data… ….does it still aggregate?”

In other words, if you turn on query tracking in ASO and then run queries, but those queries only return #Missing instead of finding some data, will the queries still affect the aggregate views that Essbase chooses?

Spoiler Alert: Yup.

If you aren’t familiar with the use of query tracking to optimize the selection of aggregate views, I would modestly recommend my own presentation from Kscope11 (free associate ODTUG membership required). This is one presentation I’ve given that really hasn’t aged at all, and that I still refer to myself frequently. A gratifying fringe benefit of speaking at conferences is that it leads me to document things I’ve learned but would otherwise soon forget.

In hindsight, this is so straightforward to test that I’m kicking myself for not having tried it sooner. I fired up ASOsamp.Sample, made sure query tracking was off, and asked for the recommended aggregate views:

Default ASOsamp.Sample Aggregate Views

Default ASOsamp.Sample Aggregate Views

 

I cancelled out of the Aggregation Design Wizard and found an MDX query that happened to return #Missing with the standard ASOsamp.Sample dataset. Then I turned query tracking on and ran the query:

SELECT {[Units]} ON COLUMNS,
       {[1st Half]} ON ROWS
  FROM ASOsamp.Sample
 WHERE ([Curr Year], [High End Merchandise], [IL])

I went back into the Aggregation Design Wizard and asked for recommended views again, this time checking the box for “User query tracking data during view selection”. The results this time:

Query Tracking ASOsamp.Sample Aggregate Views

Query Tracking ASOsamp.Sample Aggregate Views

 

This is, obviously, a very different list – and the only difference is that I have query tracked one single query, and one which only returned a #Missing result. A nice quick answer to the question!

Leave a Reply

Your email address will not be published. Required fields are marked *