Adding a Derived Column to a Logical Table in OAC Based on Records in Another Table

Question

I've been hammering away at this, and no matter what I try, I keep getting errors. I feel like I've missed something in the joins or maybe in the column syntax that is tripping me up, and I can't find relevant examples or documentation to advise me.

I have a Dimension table for suppliers with a hierarchy where the supplier detail is the leaf (underneath type, category, etc.). In the leaf are the supplier ID, supplier number, and supplier name. Also at the physical level, coming from the same database, is my Fact table of expenditures ("spend"), with each transaction joined to the supplier table by supplier ID.

That's the easy part.

Coming from a completely separate database is another physical table filled with supplier contract information. Fortunately, the supplier numbers in the contract table match the supplier numbers in the supplier table from the other database, so I could establish a physical join in OAC if I wanted to … keeping in mind that the contract data might be sparse and some suppliers might never have any contracts at all. I could make this into a Dimension table at any level if I needed to.

What I'd like to do is simply add a column to my suppliers Dimension in the logical layer that has a "Yes" if his supplier number appears in the contracts table, and a "No" if it is absent.

Since I know that my contract data is sparse, what would be a good way to accomplish this using Oracle Analytics Cloud's (May 2025) Semantic Modeler?

PeteSiekierski · Answer

That's right, @Gianni Ceresa, it's not as obvious. I tried it so many different ways and got either no data at all, or fresh new exciting errors so many times! Ultimately, here's how I solved the problem:

In the Logical Table Source for my suppliers dimension, I get most of my fields from a physical table (a view, actually) of suppliers that has a hierarchy based on category, type, and ultimately a detail level. I added one more column - the supplier number from the managed contracts dimension - to that detail level by putting both physical tables together in the source with an outer join.

This seems like it would break the hierarchy, as my detail level now has multiple "leaves" where before each supplier was its own leaf. By aggregating the count of the supplier number from the contracts table, however, I can still use the hierarchy in my analyses and also know if a supplier is present in the contracts dimension:  if the count of his appearances in the contracts dimension is '0', then he's not there; otherwise, he must be (count > 0), and is therefore considered "managed" by our contracts guys.

Gianni Ceresa · Answer

So, if your Contracts table had 0-1 rows for each supplier, it would be very simple, because that's just a simple snowflake you need "flat" into a single logical dimension. You do that by adding the 2 tables in the same logical table source of your logical dimension. The tool is smart enough to query the second (Contracts) table only when you use a column sourced from there. And by using an outer join you can then easily add a logical column that would be a true/false flag if the supplier has a contract.

But obviously that was too easy, and therefore your model now become more challenging, because the simple "exists" condition you have in mind is by far not that obvious.

PeteSiekierski · Answer

Thank you for responding, @Gianni Ceresa!

So far, I have been trying to do this with my contracts table defined as a Dimension table. Not all suppliers will have contracts, but a supplier having multiple contracts would be fairly common. I should mention here that I have no interest in the contract data itself - merely the presence of the supplier (as confirmed by his matching supplier number) in the contracts table.

I considered the likelihood of multiple contracts, but wasn't sure how to deal with that either by join or by defining columns or aggregation rules. It really never even gets that far, as every method I have tried either causes a fatal error preventing my deploying my semantic model, or else some kind of error in the Results tab of my analysis.

Does it make sense to try to use two logical tables, like in the screenshot above? I was thinking that I should be able to combine my two physical tables into a single logical table, even if it meant adding another level to my hierarchy to deal with the likelihood of multiple contracts per supplier number.

It seems like there ought to be some way to have a logical column in the "Dim - Coupa Suppliers" table that simply relies on an EXISTS condition and an outer join to the "Dim - Managed Suppliers" table based on matching supplier numbers.