Oracle Data Visualization and the Attribute function

The Attribute function is an important function of Oracle Data Visualization, useful when we attempt to create something interesting based on measure values. It was covered by Philippe Lions in the post "Leverage the 'Attribute' Function to Boost Data Insights", but I want to dig a bit deeper and show a simple use case.

First example why we need it:

 I want to create 2 groups one with profit over 500 and the other with 500 and bellow. So I create the following calculation :

In our case I used City and few measures (Row Count and Profit), as a result I have each city assigned to group:

In many cases I jut want the totals, for each group. So I'll remove the city and get a strange result:

What happened?  Our 'Case 1' is treated as a measure and aggregated!

In Answers we had an option to mark "Treat as an attribute column":

but in Answers we have many layers, this part is in criteria, and we don't really have it in data visualization that uses more direct and simple approach.  

The Attribute function should solve this problem.  

The explicit syntax for this function is:  

ATTRIBUTE(<expression> BY <attribute list> WHERE <predicate>)  

The BY clause may be empty, in which case grand total grain is assumed.

I will return to our example. Instead of case when Profit>500 then 'Group A' else 'Group B' end
I will use case when Attribute(Profit by City)>500 then 'Group A' else 'Group B' end

And replace the Case 1 with Case 2:

Great.

Lets talk a bit about the BY part:

The Profit or any other measure is summarized according to the BY clause. In  our case we wanted to summarize it by City. If we want it on any other level we should define it.

For example in my data the primary key of the data is Order ID and Order Line ID. We can use them in the BY clause:

In this case the profit is summarized by the lowest level of the data grain and return different results:

But this is exactly the case when we can omit the BY clause and get exactly the same result:

 

The wrong way to use Attribute function:

From what I have seen, the attribute function should be used as close to the measure as possible. For some reason, people (me including) tend to do it the other way, which seems to be the wrong way.

In our example using Attribute function outside the Case function:

Will get you an error with some advise

this can be resolved by having an administrator refresh the server metadata.

SQL Issued: SET VARIABLE ENABLE_DIMENSIONALITY = 1;

You don't have to, just keep the Attribute function close to the measure as described above.




If, for some reason, you insist on this approach, the workaround is to use numeric values as result.

Or you can always use some cast function on top of it.

It will work:

Did I mention it's an ugly and not recommended approach?