Data Augmentation

JT_DC_2023

Hello everyone,

We are currently planning to extend our existing Oracle HCM data by creating new custom tables. I was wondering if anyone has experience with extending the Oracle data model in this way. Before we begin the implementation, I have a few questions:

1. Filtering: If we create these custom tables, how will the existing filters be affected? For example, if I only have the Person ID in the custom table, can I still use Oracle attributes (e.g., department) to filter the data from the custom tables, even if the attribute does not directly reside in the custom table?
2. KPI Functionality: If we create KPIs using data from the custom tables, will these KPIs work correctly with the filters? I am curious if anyone has experience with how well custom tables integrate with the existing Oracle filtering logic.

I would also greatly appreciate any recommended materials or videos on this topic.

Thanks a lot for your support!

Aaron Leggett

Hey,

What's the purpose of the custom tables? We're using a bunch of custom tables, views and materialized views in our rollout.

Tables

We're using NSAW (NetSuite's version of FDI) and there's no native transactions + rolled-up balances tables, so we've built a custom pipeline to build out a Transactions Fact table and a Balances Fact table. We retain all the foreign keys to the seeded/boxed tables from NSAW (i.e. Account, Department, Fiscal Period, Item, Invoice Details etc.). With this model, we created 2 new subject areas (Transactions and Balances) and hooked up the fact table to the seeded dimension tables built and maintained by Oracle.

Views

We have a number of different source systems feeding into ADW, some come in with a very raw json format. We've used views to normalize + de-nest the JSON into a proper relational structure. We also have a ton of custom records/lists (similar to Fusion DFFs), and built denormalized views to create flattened hierarchies (e.g. Flattened Account Hierarchies, Department Hierarchies, Supervisor Hierarchies etc.)

Materialized Views

For certain highly-bespoke reporting, we've built denormalized materialized views that contain all the facts, dimensions and custom calculations in one wide table. These refresh after our daily pipeline from NetSuite and Snowflake data augmentation.

To answer your questions:

1. Filtering: You will need to extend your subject area(s) with the new table. If it's a Fact table, I highly recommend studying Kimball dimensional modelling; you'll likely want more keys than just Person ID (e.g. a Date Key as well at least, and anything else you want to slice and dice the measures by, so Department ID, Expense Account ID, Position ID, Job ID etc.). If you're planning on creating a bespoke Dataset in OAC, you'll again want to ensure the foreign keys are inside your new table so you can join onto the Oracle-built dimension tables. If you're planning on simply extending your Person Dimension and nothing else, you create the custom table and slot it in via SME.
2. KPI Functionality: Same as above; they'll work so long as you populate the table with the foreign keys for the dimensions you want to slice and dice by.

Remember to surface custom tables to make them available for Semantic Modeller Extensions; you'll need to grant the OAX$OAC user access to the table from your OAX_USER account:

grant select on "OAX_USER"."YOUR_CUSTOM_TABLE_NAME" to "OAX$OAC";

If you're simply doing Data Augmentation from Fusion (and not creating truly custom tables yourself), you will have an option to apply the new tables to Subject Areas automatically when you go through the DA setup, without having to create anything bespoke in the database, surfacing and extending manually (albeit we have still done this in our implementation as NetSuite can be a bit more fiddly in places than Fusion :) )

Thanks

Aaron

Jamie Anderson-Oracle

Hi! For some recommended materials, check out the About Augmenting Your Data section in the product documentation:

https://docs.oracle.com/en/cloud/saas/analytics/24r3/fawag/augmenting-your-data.html#GUID-9629669C-3DCF-451D-BDA3-187366350973

"Use the Data augmentation capability to bring data into the warehouse and then use the Semantic Model Extensibility capability to create the joins and expose that data to the subject areas that you want. This enables flexibility and better performance of both the capabilities."

Essentially, by creating the joins using the Semantic Model Extensibility capability, you should be able to achieve your end goal of using the FDI out-of-the-box facts/attributes in harmony with the external data that you're pulling into FDI.

I encourage others in the community who have experience implementing data augmentation and semantic model extensibility to provide further insights! This is a common use of FDI and your experience is valued in the forums! (Thank you, @Aaron Leggett!)