Many Oracle EPM Cloud customers have heard about Supplemental Data, not so many have actually used it. This post aims at demystifying Supplemental Data, with a concrete but simple business example.
What is QBAI?
QBAI stands for Qualified Business Asset Investment. It is a concept used in the calculation of the GILTI and FDII adjustments introduced by the 2017 US tax reform (Tax Cuts and Jobs Act). It is a measure of a company’s tangible property, that is used to calculate its intangible income (after a series of other calculation). The detailed rules and regulations are a bit more complicated, but for now, let’s assume it is the property of an entity, depreciated using the ADS (Alternate Depreciation System) table, which is a straight-line depreciation method defined by the IRS here.
Supplemental Data
Supplemental Data is one of the modules included in FCCS and TRCS and lets users enter data at a more detailed level than what would be reasonable in the main cubes of the application. In particular, supplemental is very effective for “transactional” level of detail, or when there is no way to pre-define members in a dimension. Supplemental Data is organized around three main paradigms:
- Data Sets: This is how the data is stored. Data sets are defined by a list of attributes that are like the columns of a database table.
- Forms: This is the way by which users interact with data in the data sets. They define which columns are visible and how they are summarized and mapped in the cube.
- Workflow: This is how forms are made available to users. Each entity is assigned to a user or group with certain roles, and forms are then deployed to specific scenarios and periods.
We will now look at these three aspects one by one in the context of our QBAI example, starting with data sets in this post, and then forms and workflow in a future post.
The end result will look something like the form below, where the user assigned to an entity can enter an asset ID and description, select the asset class from a list which will determine the life of the assets, enter the year the asset was put in service and its gross value. Supplemental data will then calculate the depreciation and net value, and aggregate the totals.
Data Sets
Data sets are where the data is stored. Creating a data set is a bit like creating a table in a database, you must first define the columns that will contain the data. The good news for us is that TRCS is guiding us in this process and shields us from having to learn barbarisms such as the NVARCHAR2 data type.
To create a data set, we start by giving it a name, and optionally a description:
Then we go to the Attributes section and that’s where it starts getting interesting:
Entity is the only mandatory attribute, it also is a key identifier. Everything else is up to you.
When you create an attribute you can choose to make it input or calculated, and give it a type, e.g. text, number, date, yes/no. In our example, the asset ID and description are text, asset class and life are text and integer, the year put in service asset and depreciation values are numbers with 0 decimals.
For all your input attributes, you can include validations, for example for my “year put in service”, I added a validation to make sure the year was greater than 1900:
For calculated attributes, you can define mappings, conditional logic or “scripted” calculations based on the other attributes in the data sets and two dozen possible functions. Below is an example for the calculation of the ADS depreciation (using a straight line method):
Supplemental Data Dimensions
Some other attributes are from what supplemental data calls dimensions. These dimensions can either come from the TRCS application, in which case they are called system dimensions and cannot be edited by the user. Other dimensions are user-defined, in which case you can add attributes and data to these dimensions. One important thing to call out is that dimension here does not mean the same thing as a dimension in a cube. In supplemental data, the concept of a dimensions is similar to the concept of dimension in a star schema (see more details here: https://en.wikipedia.org/wiki/Star_schema). In particular it does not support hierarchies like dimensions in a cube do. Think of it as a list of attributes that apply to your data.
In our QBAI example, each asset depreciate over a number of periods that depend on the type of property. So we can create a user-defined dimension that associate the asset class with the asset life. Asset class is the key attribute; once you select it, the corresponding asset life is automatically selected. It acts as a kind of mapping.
Once you have defined the attributes of your dimension, you can add members to it. In the QBAI example, what I did is upload the ADS table from the IRS website:
Finally, you can just add these dimension attributes to your data set. Any time a user selects the asset class of an asset in a form, the associated depreciation duration will be selected automatically.
That’s it for now. In part 2 we will look at how to create a form, how to make it available to users, and how to configure it so the data can be posted in the main TRCS cube. Stay tuned!
Close In The Cloud 