Enhancing Universes With Super Charged Aggregate Aware

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.

TheGrinch

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.

Simplified Data Model

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.
Aggregate Aware Setup

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.

Aggregate Aware Error Message

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.

Query Options

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.

Evil Aggregate Aware

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.

Agg Aware Parameters

 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.

Thorough Agg Aware Parameter

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.

Evil Aggregate Aware

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:

Agg Aware - Full 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:

Agg Aware - Fact Table 1 Context

Agg Aware - Fact Table 2 Context

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.

Aggregate Aware Error Message

We cool, right?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.