Every once in a while, you run into an issue that upon resolving you feel like you’ve leveled up as a user. Like a tech version of the Grinch your brain grows 3 sizes.
I don’t know why I had this saved as TypicalConsultant.jpg
The issue I encountered revolved around Aggregate Aware and resulted in an error message in Webi under certain conditions. However, before I can go further on the issue, I need to explain how the data foundation and semantic layer were set up. The universe had 2 fact tables each sharing a dimension table and separated by contexts.
Not pictured, the actual universe because Rule #1 of consulting is protect client data
In the Semantic Layer, there was only one object for Customer Id. Aggregate Aware was set on this object and pointed to the Customer Id in each of the fact tables. This served 2 purposes, reduce the number of Customer Id objects from 3 down to 1 and reduce the number of joins where only the Customer Id is needed.
To quickly summarize Aggregate Aware, the function @Aggregate_Aware() allows one object to reference multiple table columns and/or multiple business layer objects. Based off the end-user’s selection made when creating a query, one of the referenced columns or objects is dynamically selected at run time.
For a more in depth look at Aggregate Aware, check out Michael Welter’s Blog. It’s very helpful for beginners and those that are rusty.
Now that we’ve covered the universe, let’s get back to the issue. Whenever the client would create a query that contained the Customer Id object (with Aggregate Aware) and an object from each Fact Table, an error message would appear.
Just when you start getting comfy with IDT, I’m here to ruin your day
What the client was expecting was that the 3 objects would result in 2 queries. Each query would have the Customer Id point to the correct fact table and the 2 queries would be joined with a full outer join. We knew this was possible because one of their 3 universes had this capability. With the knowledge that it was possible, I searched for a solution.
I started my search looking in all the usual places.
- Aggregate Navigation
- The Customer Id Object
- The Contexts
- Query Options
None of these provided a solution. The Aggregate Navigation was identical between the universe with the issue and the one that worked fine. I checked the @Aggregate_Aware() function in the Customer Id object and it was set up fine. When testing the object, Customer Id pulled from Table 1 when only Table 1 measures were in the query. The object also worked when only Table 2 measures were present. I checked the contexts and they were set up properly. My final check was on the query options on the universe. It was set up to allow multiple SQL statements for each measure.
Just like that, I had exhausted all of my options. The error haunted me. It clearly worked in one universe but the other did not. It was maddening.
Oh, you checked everywhere? Well I’m still here.
I talked to a co-worker and they suggested that I check the parameters list and compare them between the two universes. So I went to the Business Layer and clicked on the universe.
When I clicked on Parameters, and compared them between the two universes, I noticed one difference. The universe that threw the error message was missing a parameter that was present in the other. That parameter was called THOROUGH_AGGREGATE_AWARE.
In that moment, I swore I could hear angels chanting
So I decided to test out if this was truly the solution. I added THOROUGH_AGGREGATE_AWARE to the parameters and assigned the value Yes to it. Lo and behold, the error message went away. If I changed the value from Yes to No, the error message came back.
Before you change that parameter back, can we just talk about it?
Just like that, my issue was solved. However, what did that parameter do? From what I can gather, it seems as though the parameter modifies how a query is processed. THOROUGH_AGGREGATE_AWARE seems to put the context resolution before the aggregate navigation resolution.
Let’s look at an example using this query:
Our two measures are contained in different contexts and the customer id has aggregate aware. If the aggregate navigation is resolved first, our two measures are incompatible with each other. Therefore, our query returns an error message.
However, if the context is resolved first, our query essentially gets split out like so:
Now our aggregate navigation finds no issue with our two queries because our context has already split the original query. Essentially, the THOROUGH_AGGREGATE_AWARE parameter has change the order in which the query is resolved thus fixing our issue.
As universe designers, it is our job to build in common sense into our universes. There should never be any hidden “gotchas” or secret rules for the end users. It is our job to make sure that our universes are intuitive and straight forward. We should never hear users exclaiming that the universe is broke nor should they have to ask us what something means or how it works. Utilizing the THOROUGH_AGGREGATE_AWARE parameter is a tool that I will forever have saved away in my mental toolbox. It allowed me to create a universe that just works.
Every once in a while, you run into an issue that tests your skill and resolve. Upon resolving, you come out a better user and feel like a smarter person. In the end, you might even be grateful for the experience.
We cool, right?