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

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

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!

2 thoughts on “ASO Query Tracking Trivia”

1. Bill Bradstreet says:
   June 8, 2017 at 10:37 am

   Hmm. My mileage varies. I think I found a nuance to make your answer “it depends…”

   I am using 11.1.2.4 and a report script for my test. My customer doesn’t want me to hard code a member from the customer dimension. So, I created a dummy hierarchy group that will never have data. Based on what I read in your article, I should be able to focus on Level 2 from this dummy hierarchy and get aggregate views created in the “real” hierarchy that corresponds with Level 2. This did not bear out.

   If I focus on customer numbers that I know are #MISSING everywhere in the database for all my query tracking, then, when I run my aggregation on the query_data, I get a message in the log that no query has been run for the aggregation to be based on.

   In other testing, I found I could get a query to run, but the area that was #MISSING everywhere in the database would never get included in the aggregate views. All my views ended up pointing to Level 5 Customer (which equates to Total Customer). So, all views were useless for my purposes and could not be based on the query I ran during my query tracking step.

   I tried placing a value in the dummy hierarchy (but still in an area that was outside the scope of the query tracking report) and still could not get any aggregate views created.

   So, unless someone can figure out more on how all this works, my conclusion is that if the report script I run for query tracking purposes results in #MISSING everywhere, then the query tracking will not work.

   Let me know if you have any questions. I can try to carve out time to do more testing.

   Reply
   • TimG says:
     June 8, 2017 at 10:48 am

     Hi Bill, that’s really interesting – thanks for posting!

     When you say that you created a “dummy hierarchy” do you mean a separate hierarchy in a multiple hierarchies enabled dimension, or a separate branch in an existing hierarchy (I could definitely see those two scenarios resulting in different behavior).

     Assuming it’s the latter, do you get different results if you run a report script that differs solely in the fact that it points at a level two member that happens to have data?

     I wonder if there’s a difference between a query on a member that contains no data anywhere and a query on a member that happens to contain no data for this particular query; I’ll have to test that one in my environment (it’s easy enough to do by adding a new member).

     Reply