Oracle Communities

Oracle Analytics and AI

Child Item

Adding sub-totals by hierachy levels in a pivot table with Oracle Analytics Cloud

I'm creating a fairly simple tabular analysis in Oracle Analytics Cloud. My Semantic Model only has one dimension table (for suppliers) and one fact table (for spend to those suppliers). The Suppliers dimension has a logical hierarchy built into it: Category, which contains Supplier Type, which has Suppliers.

Having this hierarchy there allows me to roll up or unfurl the groups of suppliers in a pivot table visualization. I prefer this to simply listing all Categories with all Supplier Types and all Suppliers straight down the page like an Excel spreadsheet. With as many records as I'm looking at, doing this would make it challenging for a reader to focus on just the categories he wants.

What I would like to see is for the spend by Supplier to roll up to each Supplier Type, and from there, to roll up to each Category. When I mark the Totals for the rows - which are determined solely by the hierarchy - all I get is a single grand total for the entire report. There are no subtotals for any other level of the hierarchy.

When I look at the layout for the pivot table visualization, I don't see any object that looks like I could add or insert any kind of calculated value corresponding to the levels of the hierarchy in between the Suppliers and the grand total for the entire report.

How can I inject that kind of tabulation without losing the ability to roll up or unfurl the different hierarchy levels?

Find more posts tagged with

OAC

Accepted answers

Gianni Ceresa

If the model is really as simple as described, the default behavior without setting anything should already be enough.

No need for dimension based aggregation: you have a single dimension, and even if you had more, they all use the same aggregation.

Another thing to maybe check is the content level: your Dim - Supplier is set to be at the TOTAL level for your measure. Is there a reason behind that.

I really have no idea how your hierarchy can behave so weirdly, with all those numbers randomly coming up. I suspect there could be an issue with content levels and a lack of uniqueness in the sorting of the logical keys, which means that an higher level (totals and subtotals) is using a single record, but because of lack of strict sort order, it does pick a different record every time the query run.

You could probably confirm all that by looking at the executed queries.

Because it's a new piece of semantic model, I would undo most of the settings to keep things as simple and clean as possible. Check your hierarchy to make sure each level has a primary key identifying in a unique way the records in that level. And leave content levels alone, not having them defined in your case should work just fine.

Not setting things doesn't mean they don't exist, it just let the tool behave with the default behavior, while setting something already force it to behave in a given way.

And then look at the generated queries to see what the system is actually requesting to your source, and that should at least let you understand the behavior you see on the screen. Even if the numbers are wrong, by looking at the query you can see why they are wrong and what the system is trying to do (like selecting a single record as a total instead of summing all the records together etc.).

All comments

Gianni Ceresa

Hi,

Are you sure your semantic model is fully correct? Because by default, when having hierarchies, all the measures are aggregated all the time. As the picture posted below, you can see that enabling the "Grand Total" is even pointless because by default the measures aggregate all the time and therefore on the "Total Value" level of the hierarchy I have the Grand Total.

Because it isn't the case on your side, I would look for an issue in the model, something is wrong in there. But difficult to say what without having that model myself.

Also, I did this analysis in OAS 2024 (7.6), I don't have a current OAC available, but it would be quite a huge bug if the latest release did break the natural behavior that is part of the product for more than 10 years.

Something you could test: if you remove the hierarchy and use a column of each one of the Detail, Type and Category levels of your hierarchy, do they work fine giving you aggregates? (I mean to try 3 columns one by one, not the 3 columns at the same time, because that would just force the Detail level and then grand totals will do the job just fine).

PeteSiekierski

Thank you for responding so quickly, @Gianni Ceresa!

I suspected there might be something more I could do in my Semantic Model, but I'm not sure what. I'm having difficulty finding relevant documentation and examples that are specific to OAC as opposed to products like OAS or OBIEE.

The hierarchy looked fine, and when I replaced the hierarchy object with the individual columns that make up the hierarchy, the aggregation began to work like you and I expected:

OAC even set up the default drill to the lower levels of the hierarchy without my having to tweak the Interaction settings beforehand:

I began setting up the visualization by dropping the hierarchy object onto the canvas, which immediately turned it into a pivot table. Now I'm wondering if there isn't a different visualization type that would better accommodate my needs - or if simply using the hierarchy levels themselves with better drill and Interaction settings might do the trick.

I still think I'd prefer using the hierarchy object's ability to open and close levels on the visualization itself. I'm not sure what I would need to do to my Semantic Model to make it give me those totals - or if that is even possible using OAC.

BalagurunathanBagavathy-Oracle

@PeteSiekierski

Can you please make sure that the aggregation level based on a dimension is set as mentioned in the document https://docs.oracle.com/en/cloud/paas/analytics-cloud/acmdg/work-logical-column-aggregation.html#GUID-03CAA109-7713-4E99-B3A6-5D3EE1882FF6

Gianni Ceresa

You should not set dimensioned based aggregation when not trying to have different aggregation rules by dimension (like SUM for any dimension but LAST for time dimensions etc.). By default the aggregation rule of the measure is used.

The semantic model works just like the RPD and the same rules as OAS and OBIEE do apply there (it's the GUI that is mostly different, but behind the scene it is the same thing as OAS, which is the same as OBIEE for 15+ years: the RPD was already there and working before Oracle bought the product).

Just a thing: in your dimension, you should not have "Elements at this level" = 1 for the supplier detail. This info gives an hint to the BI Server of the size of your levels, it should generally be an increasing number from top to bottom of your hierarchy. For example think at a time dimension: year level = 10 elements, months level = 120 (12*10), day level = 3650 (365*10). No need for it to be exact, but it should at least have a similar ratio of the real data you have behind it.

You have no warnings and no errors in your semantic model, right?

Is this a brand new semantic model or something imported and moved around various environments? Behind your 2 logical tables Dim - Suppliers and Fact - Spend, do you have 2 physical tables or do they contain multiple logical table sources or joins between many tables?

PeteSiekierski

@Gianni Ceresa, I tried modifying a few pieces of the Semantic Model for the Measures (there's a screenshot of that further down), and now I get sub-totals in the hierarchy… but they are incorrect. What's more, as I open and close parts of the hierarchy on the Results tab, the values change!

As you can see in this first screenshot, the sub-totals at the Supplier Type level do not match the sum of the values at the Suppliers level. Additionally, when I first opened the Results tab after building the analysis, the Total began at 350, but then changed to 60 when I opened some of the child levels as shown here.

I'm not sure which aggregation rules are appropriate for the Measures, but based on your reply, I suspect they should be the same for all three of them. I originally had them set to a "Sum" aggregation rule, but that resulted in the earlier case where I had no sub-totals at all.

By changing the Measures' aggregation rule to "Based on Dimension", I now get sub-totals, but they're incorrect as shown above. I've tried with and without "Aggregation by Level", changed the "Logical Level" to different values, and experimented with "Data is dense" checked and unchecked. All of those resulted in something that looked like the screenshot above.

The dollar amounts of the Spend are by Supplier. All I want to see is a sum of Supplier spend at Supplier Type, then a sum for all Supplier Types at Category, and finally a Grand Total for the entire hierarchy (all Suppliers, Types, and Categories combined).

That suggests to me that the aggregation operator (Sum) should be the same at each level of the hierarchy, and thus it follows that I should not be using dimension-based aggregation as I have done (as shown below), correct?

I went back and adjusted the number of elements at each hierarchy level as you suggested. There are about a dozen Categories, a hundred Supplier Types, and about 1,600 Suppliers involved in the analysis.

There are no warnings or errors when I create, save, or deploy my Semantic Model.

The Semantic Model is brand new. I built it a few days ago in our OAC development instance, which is connected to an Oracle EBS database.

Since it's our development instance, I have a lot of control on the database side of things. Over there, I built two custom views - one for Suppliers and one for Spend. The Suppliers view combines AP_SUPPLIERS with a custom category mapping table, while the Spend view simply sits on top of AP_INVOICES_ALL.

Gianni Ceresa

If the model is really as simple as described, the default behavior without setting anything should already be enough.

No need for dimension based aggregation: you have a single dimension, and even if you had more, they all use the same aggregation.

Another thing to maybe check is the content level: your Dim - Supplier is set to be at the TOTAL level for your measure. Is there a reason behind that.

You could probably confirm all that by looking at the executed queries.

Not setting things doesn't mean they don't exist, it just let the tool behave with the default behavior, while setting something already force it to behave in a given way.

PeteSiekierski

Thanks again, @Gianni Ceresa. I want to convey my most heartfelt appreciation for the attention and advice you've provided. This was really a frustrating problem for me, especially given the data model's simplicity!

After removing the content levels from my three measures and preparing to take a series of screenshots to accompany yet another frustrating reply, I built yet another completely new analysis in precisely the same way I had no less than a dozen times before …

… and mysteriously, everything looks perfect now. I'm baffled.

What really has me scratching my head is that I swear I had already tried deleting the content levels once before, and doing that didn't seem to have any effect.

Trying this again after reading your last reply has sealed the deal. Thank you!

Gianni Ceresa

Great to hear it's back to working as expected @PeteSiekierski !

To be fair, it's maybe not your fault at all…

It happened to me a few times in the past that changes in the Semantic Model did save, but when I clicked to deploy it, despite a confirmation it was done, I never saw the changes when building an analysis.

Even refreshing the metadata to force it to reload the RPD never showed my changes and I had to deploy the semantic model again. Being all a black box, I can't say what was going on, but I definitely lost time deploying again and again a model waiting to see it deployed for real.

PeteSiekierski

This reminds me of something that I encountered a few months ago: in Oracle Analytics Cloud, the Writeback Template XML is part of the System Settings in the console, and you no longer need to click a button for changes made to the template to take effect - simply moving one's cursor outside the box prompts OAC to save the changes. You even get a little pop-up message indicating the change has taken effect.

It's not instantaneous, though.

I later discovered to my consternation that it took a few minutes between receiving that message and going back to the OAC front end for the writeback changes to appear, and I learned that I had to be patient each time I adjusted the syntax or the properties of the writeback before I could actually use it.

Gianni Ceresa

Yes, there are things happening behind the scene at some point when you change those settings (almost anything in that screen).

In the past it was just saving the new state and then there was a “restart” button appearing on screen to restart the required components (BI Presentation service mostly, sometime the BI Server).

Now it’s all hidden, it gives you the wrong impression it’s done in a second. Reality is another, and there is no known (to me) way to figure out when something is really applied other than trying again and again.

And sometime it can really take a long time! But you just can’t know.

It’s something where I would request more transparency. Having a “background tasks” pop up available showing what is going on in the background and their status.

Maybe one day…