Category: Data Modeling

Business Intelligence (BI) Development, Data Analysis, Data Development, Data Modeling, Power BI, Reporting

Calculated Columns vs. Measures in Power BI: Understanding the Differences

When working in Power BI, two common ways to add custom calculations to your data model are calculated columns and measures. While they both use DAX (Data Analysis Expressions), their purposes, storage, and performance implications differ significantly. Understanding these differences can help you design more efficient and maintainable Power BI reports.

1. What They Are

Calculated Column
A calculated column is a new column added to a table in your data model. It is calculated row-by-row based on the existing data and stored in the model like any other column.

Measure
A measure is a calculation that is evaluated on the fly, usually aggregated at the visual level. Measures don’t exist as stored data in your table—they are computed dynamically based on filter context.

To create a Calculated Column or a Measure, either from the Home menu …

… or from the Table Tools menu …

… select “New Column” (to create a Calculated Column) or “New Measure” (to create a new measure). Then enter the relevant DAX for the column or measure as shown in the next section below.

2. DAX Syntax Examples

Imagine a Sales table with columns: Product, Quantity, and Unit Price.

Calculated Column Example
Creating a calculated column:
Total Price = Sales[Quantity] * Sales[Unit Price]

This new column will appear in the table and will be stored for every row in the Sales table.

Measure Example
Creating a measure:
Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])

This measure calculates the total across all rows in the current filter context—without physically storing a column for every row.

3. When They Are Computed

FeatureCalculated ColumnMeasure
When computedDuring data model processing (data refresh).At query time (when a visual or query is run).
Where storedIn-memory within the data model (VertiPaq storage).Not stored—calculated on demand.
Performance impactIncreases model size (RAM & disk space).Consumes CPU at query time, minimal storage overhead.

4. Storage and Performance Implications

  • Calculated Columns
    • RAM & Disk Space: Stored in VertiPaq compression format. Large columns increase your .pbix file size and memory footprint.
    • CPU: Low impact at query time since results are precomputed, but refresh time increases.
    • Good for: Fields you need for filtering, sorting, or joining tables.
  • Measures
    • RAM & Disk Space: No significant impact on storage since they’re not persisted.
    • CPU: Can be CPU-intensive if the calculation is complex and used across large datasets.
    • Good for: Aggregations, KPIs, and calculations that change based on slicers or filters.

5. When to Use Each

When to Use a Calculated Column

  • You need a field for row-level filtering or grouping in visuals.
  • You need a column to create relationships between tables.
  • The calculation is row-specific and independent of report filters.

Example:

Sales Category = IF(Sales[Quantity] > 100, "High Volume", "Low Volume")

When to Use a Measure

  • You want calculations that respond dynamically to slicers and filters.
  • You want to avoid inflating your data model with unnecessary stored columns.
  • The calculation is aggregate-based.

Example:

Average Order Value = DIVIDE([Total Sales], DISTINCTCOUNT(Sales[Order ID]))

6. When They Cannot Be Used

SituationCalculated ColumnMeasure
Relationship creation✅ Can be used❌ Cannot be used
Row-level filtering in slicers✅ Can be used❌ Cannot be used
Dynamic response to slicers❌ Cannot recalculate✅ Fully dynamic
Reduce model size❌ Adds storage✅ No storage impact

7. Summary Table

FeatureCalculated ColumnMeasure
Stored in modelYesNo
Calculated atData refreshQuery time
Memory impactHigher (stored per row)Minimal
Disk size impactHigherMinimal
Dynamic filtersNoYes
Best forFiltering, relationships, sortingAggregations, KPIs, dynamic calcs

8. Best Practices

  • Default to measures when possible—they’re lighter and more flexible.
  • Use calculated columns sparingly, only when the calculation must exist at the row level in the data model.
  • If a calculated column is only used in visuals, try converting it to a measure to save memory.
  • Be mindful of CPU impact for very complex measures—optimize DAX to avoid performance bottlenecks.

I hope this was helpful in clarifying the differences between Calculated Columns and Measures, and will help you to determine which you need in various scenarios for your Power BI solutions.

Thanks for reading!

Analytics, Business Intelligence (BI) Development, Data Analysis, Data Development, Data Integration, Data Modeling, Data Munging, Data Wrangling, Power BI, SQL

Understanding UNION, INTERSECT, and EXCEPT in Power BI DAX

When working with data in Power BI, it’s common to need to combine, compare, or filter tables based on their rows. DAX provides three powerful table / set functions for this: UNION, INTERSECT, and EXCEPT.

These functions are especially useful in advanced calculations, comparative analysis, and custom table creation in reports. If you have used these functions in SQL, the concepts here will be familiar.

Sample Dataset

We’ll use the following two tables throughout our examples:

Table: Sales_2024

The above table (Sales_2024) was created using the following DAX code utilizing the DATATABLE function (or you could enter the data directly using the Enter Data feature in Power BI):

Table: Sales_2025

The above table (Sales_2025) was created using the following DAX code utilizing the DATATABLE function (or you could enter the data directly using the Enter Data feature in Power BI):

Now that we have our two test tables, we can now use them to explore the 3 table / set functions – Union, Intersect, and Except.

1. UNION – Combine Rows from Multiple Tables

The UNION function returns all rows from both tables, including duplicates. It requires the same number of columns and compatible data types in corresponding columns in the the tables being UNION’ed. The column names do not have to match, but the number of columns and datatypes need to match.

DAX Syntax:

UNION(<Table1>, <Table2>)

For our example, here is the syntax and resulting dataset:

UnionTable = UNION(Sales_2024, Sales_2025)

As you can see, the UNION returns all rows from both tables, including duplicates.

If you were to reverse the order of the tables (in the function call), the result remains the same (as shown below):

To remove duplicates, you can wrap the UNION inside a DISTINCT() function call, as shown below:

2. INTERSECT – Returns Rows Present in Both Tables

The INTERSECT function returns only the rows that appear in both tables (based on exact matches across all columns).

DAX Syntax:

INTERSECT(<Table1>, <Table2>)

For our example, here is the syntax and resulting dataset:

IntersectTable = INTERSECT(Sales_2024, Sales_2025)

Only the rows in Sales_2024 that are also found in Sales_2025 are returned.

If you were to reverse the order of the tables, you would get the following result:

IntersectTableReverse = INTERSECT(Sales_2025, Sales_2024)

In this case, it returns only the rows in Sales_2025 that are also found in Sales_2024. Since the record with “D – West – $180” exists twice in Sales_2025, and also exists in Sales_2024, then both records are returned. So, while it might not be relevant for all datasets, order does matter when using INTERSECT.

3. EXCEPT – Returns Rows in One Table but Not the Other

The EXCEPT function returns rows from the first table that do not exist in the second.

DAX Syntax:

EXCEPT(<Table1>, <Table2>)

For our example, here is the syntax and resulting dataset:

ExceptTable = EXCEPT(Sales_2024, Sales_2025)

Only the rows in Sales_2024 that are not in Sales_2025 are returned.

If you were to reverse the order of the tables, you would get the following result:

ExceptTableReverse = EXCEPT(Sales_2025, Sales_2024)

Only the rows in Sales_2025 that are not in Sales_2024 are returned. Therefore, as you have seen, since it pulls data from the first table that does not exist in the second, order does matter when using EXCEPT.

Comparison table summarizing the 3 functions:

FunctionUNIONINTERSECTEXCEPT
Purpose & OutputReturns all rows from both tablesReturns rows that appear in both tables (i.e., rows that match across all columns in both tables)Returns rows from the first table that do not exist in the second
Match CriteriaColumn position (number of columns) and datatypesColumn position (number of columns) and datatypes and valuesColumn position (number of columns) and datatypes must match and values must not match
Order Sensitivityorder does not matterorder matters if you want duplicates returned when they exist in the first tableorder matters
Duplicate HandlingKeeps duplicates. They can be removed by using DISTINCT()Returns duplicates only if they exist in the first tableReturns duplicates only if they exist in the first table

Additional Notes for your consideration:

  • Column Names: Only the column names from the first table are kept; the second table’s columns must match in count and data type.
  • Performance: On large datasets, these functions can be expensive, so you should consider filtering the data before using them.
  • Case Sensitivity: String comparisons are generally case-insensitive in DAX.
  • Real-World Use Cases:
    • UNION – Combining a historical dataset and a current dataset for analysis.
    • INTERSECT – Finding products sold in both years.
    • EXCEPT – Identifying products discontinued or newly introduced.

Thanks for reading!

Business Intelligence, Business Intelligence (BI) Development, Data Modeling, Power BI

Add a “Last Refreshed Date” notification on a Power BI dashboard

It is customary to add a “Last Refreshed Date” to dashboards to notify users of when the dashboard was last refreshed. Here will go through the steps of adding a “Last Refreshed Date” to a Power BI dashboard.

Open the dashboard you want to add the notification to in Power BI Desktop, and then Get Date -> Blank Query. Name the new query as “Last Refreshed Date” or something like that.

Then, set the value as shown below, by typing “= DateTime.LocalNow()” into the formula area:

Next, convert the value to a Table by clicking the “To Table” icon, and then clicking the “To table” option:

A table with the single date column is created

Set the data type as Date/Time …

… and rename the column to something meaningful, such as “Last Refreshed Date” …

Close and apply your changes.

The table will be added to your Power BI model. It should have no relationships.

Now, add a Card visual to your dashboard, place it where it is visible but out of the way, such as in the top-right or bottom-right corner, and add the Last Refreshed Date column to it from the new table. Now, whenever your dashboard is refreshed, this new table and visual will also be updated/refreshed.

Thanks for reading!

Analytics, Business Intelligence, Data Development, Data Modeling, Data Munging, Data Wrangling, Power BI

Transpose vs. Pivot vs. Unpivot in Power BI

In this post, I will quickly show how to perform 3 key transformations in Power BI – Transpose, Pivot, and Unpivot – and what impact they have on the data they are applied to. These transformations are often needed to manipulate the original data to get it into the shape needed to create the Power BI model and reports. We will use a simple example so that you can see clearly the effect of each transformation.

This is the original dataset – Student Grades:

Transpose

Transpose the data by going to the Transform ribbon and clicking “Transpose”.

This is what the data looks like after the Transpose transformation is applied. The rows are converted into columns, and the columns are converted into rows. Default headers / column names are applied.

Pivot Column

Starting with the original dataset, we will now Pivot the dataset. Go to Transform ribbon, click on “Pivot Column”.

The Pivot Column dialog opens. As noted, it will use the values in the Names column to create new columns, and will use the Score column as the Values Column.

This is what the data looks like after Pivot is applied as described above. Pivot Column converts the selected Name column values into column headers.

Unpivot Columns

Starting with the original dataset, we will now Unpivot the dataset. First select all 4 name columns by using “Ctrl + Click”.

Then, go to Transform ribbon, click on “Unpivot Columns”. This is what the data looks like after the Unpivot Columns transformation is applied. Unpivot Columns converts the selected columns (the name columns in this case) into a column of values (Attribute) and the column values are stored in the Value column.

Thanks for reading!

Business Intelligence, Business Intelligence (BI) Development, Data Modeling, Power BI

How to create an Index column or ID column in Power BI

When working with data, you may need to add an index column (or an ID column) to your data. For example, you may have a list of products (names and descriptions) but you do not have an ID for each product, and you need to assign one to each product, perhaps for data modeling purposes.

To add an index or ID column to each row, you will do this in the Power Query Editor. With your query (table) open, go to the “Add Column” tab where you will see the “Index Column” option. 

You can click the menu item to use its default option – which will add an index column that starts at zero (0) – as shown below.

Or you may click the dropdown (to the right of the Index Column menu item) to choose from 3 available options (From 0, From 1, or Custom).

“From 0” will add an index column that starts at zero (0) as shown above (in the default example).

“From 1” will add an index column that starts at one (1), as shown below:

“Custom” will open a dialog for you to enter the “Starting index” value and the “Increment” between each value. In the example below, the starting index value is set to 1000, and the increment is set to 5.

This means that the first value in the index column will be 1000, the second value will be 1005, the third 1010, and so on. This is shown below.

Once your index column is added, you can rename it and move it as desired. For example, you may rename the column name to Product ID and move the column to the front of the table as shown below.

Thanks for reading. I hope you found this useful.

Business Intelligence, Data Integration, Data Modeling, Data Warehousing

Oracle Business Intelligence Applications (OBIA) Fact Tables

Dimensionally modeled (star-schema designed) data warehouses are primarily made up of two types of tables – Fact and Dimension.  Fact tables store the measurements generated by business events (# of orders, amount of dollars, etc.); and Dimension tables store the descriptive attributes that provide context to the measurements (product [product name], customer [customer type], date, etc.).

This post describes the types of Fact tables found in Oracle Business Intelligence Applications (OBIA) data warehouse – Oracle Business Analytics Warehouse (OBAW).  There will be future posts that describe in detail the other table types in OBIA (Dimension, Internal, etc.).

The 5 types of Fact tables used in the OBAW are:

  1. Transactional
  2. Aggregate
  3. Cycle Lines
  4. Snapshot
  5. State Transition.

The Transactional Fact Table is the main type of fact table. It stores the lowest-level of information from transactional sources. An example of a Fact table in OBIA (Financial Analytics) is: W_GL_BALANCE_F
Note: Fact tables in OBIA end with “_F”.
This table stores the current balance for GL accounts by GL_ACCOUNT and other dimensions.

The Aggregate Fact Table is typically used for performance improvements.  It is a summarized or rolled-up version of the Transactional fact table.  Instead of querying the data at the transactional level – which is the most detailed level and the level with the most records, the Aggregate table allows you to query the data at a more rolled up level when appropriate.  One of the most frequent roll-ups is time – for example, a transactional table at a day level is rolled up to the month level.
Aggregate tables can be tens of times less (or even hundreds) than their transactional versions.  These types of tables are also very common in OBIA and in data warehousing in general.

An example of an Aggregate Fact Table in OBIA (Financial Analytics) is: W_GL_BALANCE_A
Note: Aggregate Fact tables in OBIA end with “_A”.
This table stores the GL account balances aggregated by GL Account Segment and other dimensions. Instead of having data at the GL_ACCOUNT level as in the Transactional fact table, the data is at the GL Account Segment level in the Aggregate table.  Aggregate Fact tables are derived from Transactional Fact  Tables or other Aggregate Fact tables. This table is derived from the transactional fact table mentioned above: W_GL_BALANCE_F.

The Snapshot Fact Table stores “snapshots” of measurements taken at well-defined, predetermined time intervals – such as daily, monthly, annually, etc.  Examples include Inventory and Account Balance snapshots, and AR/AP aging snapshots.  Common items such as financial reports or bank statements are examples of reports from Snapshot Fact tables.

An example of a Snapshot table in OBIA(Supply Chain Analytics) is: W_INVENTORY_DAILY_BAL_F
Oracle’s description of this table will help to clarify its makeup and purpose.
The W_INVENTORY_DAILY_BAL_F fact table is used to represent at a point in time information of all inventory balances and inventory values related to products whose inventory is maintained by the business organization, these would typically include all inbound (purchased from external entities) products as well as outbound (sold to external entities) products. The inventory balance information is trended by copying historical snapshot information from this table at periodic points in time into history table W_INVENTORY_MONTHLY_BAL_F.
The W_INVENTORY_MONTHLY_BAL_F table stores a snapshot of inventory balance.
There is one row for each product and product storage location whose point in time inventory quantity and value information is maintained. The storage location could represent a warehouse or further divisions within a warehouse. This aspect is configurable within the product. All the dimension key links to the other Oracle Business Analytics Warehouse dimension tables, such as W_DAY_D, W_BUSN_LOC_D, W_PRODUCT_D, W_INVENTORY_PRODUCT_D, and so on, represent information associations at that point in time for that product inventory information. The DATE_WID column represents the date on which the inventory balance information is valid.

These tables can also have Aggregate versions:
As mentioned in the description for the W_INVENTORY_DAILY_BAL_F table above, there is an aggregate version.  However, snapshot tables are not necessarily aggregated like transactional tables, because many times the measures are non-additive or semi-additive. For example, you would not take your account monthly balance in January and add it to your account monthly balance in February to determine how much money you have – that would be wrong.

The W_INVENTORY_MONTHLY_BAL_F fact table is used to represent the monthly information of all the inventory balances and the inventory values related to products whose inventory is maintained by the business organization. This information includes all inbound (purchased from external entities) products and outbound (sold to external entities) products. The aggregation period is configurable, and has a preconfigured value of Monthly.
There is one row for each product and product storage location whose point in time (as of a month) inventory quantity and value information is maintained. All the dimension key links to the other Oracle Business Analytics Warehouse dimension tables such as W_DAY_D, W_BUSN_LOC_D, W_PRODUCT_D, W_INVENTORY_PRODUCT_D, and so on, and represents information and associations at that point in time for that product inventory information. The PERIOD_START_DT_WID and PERIOD_END_DT_WID column represents the aggregation bucket start and end dates. The column INV_BALANCE_DT_WID represents the date within this aggregation period on which the inventory balance information is valid.

The Cycle Lines Fact Table store measurements for multiple related business events and are therefore typically derived from multiple fact tables. They typically store process cycle times or provide the ability to easily determine process cycle times.  These tables are also called Accumulating Snapshot Fact tables because they are snapshots of different events accumulated on each other.  An example of a Cycle Lines Fact table is W_PURCH_CYCLE_LINE_F.

Here is Oracle’s description of the table which should help clarify its purpose: W_PURCH_CYCLE_LINE_F table tracks the time duration of all events pertaining to the purchase process commencing with a requisition. Information in this table enables analysis of the direct spend process within an organization beginning with a purchase requisition, its approval, the creation of an approved purchase order, its submission to a supplier, the creation of a purchase schedule and ending with its receipt of the products. It can be used to calculate the time taken to receive products that have been ordered, the time between the first receipt and last receipt of products that have scheduled for delivery. The W_PURCH_CYCLE_LINE_F table contains all the various dates associated with the processes such as submission, approval, ordering and receiving as well as quantities and amounts. While Other spend related fact tables capture individual process such as requesting, ordering, scheduling this table combines all the in one place for ease of analysis and reporting.

These Cycle Lines tables can also have aggregate versions. For example: W_PURCH_CYCLE_LINE_A This is an aggregate table of W_PURCH_CYCLE_LINE_F at a higher level of dimensionality. The Product dimension is replaced by a Product type dimension to give a high level analysis of the sourcing data. It stores Purchase Cycle Line records aggregated over a preconfigured Monthly time period and product types.

State Transition Fact Tables store state-transition metrics based on business events, such as customer state – new, top, dormant, lost, etc – based on the customer order activity.  These tables store or allow you to easily derived counts of the various states.  State Transition Fact tables are derived from Transactional or Snapshot fact tables.

Below are two examples of State Transition Fact tables in OBIA (Marketing Analytics):

The Customer Status History Fact: W_CUSTOMER_STATUS_HIST_F
This is a fact table that tracks the status of customers based on the frequency of orders they place with the organization. Possible statuses are NEW, RECENT, DORMANT and LOST. The time duration for each status bucket is configurable, out of the box being a calendar year.
The grain of this table is at a Customer, Customer Status and the Status Start Date level. Other important columns in this table include the Sold to and the Ship to location for the customer. These are derived based on the status bucket start date against the Customer Locations dimension table.

The Loyalty Member Status History Fact: W_LOY_MEMBER_STATUS_HIST_F
W_LOY_MEMBER_STATUS_HIST_F Fact table stores status changes of Loyalty members. Grain: One record for each member status changed.

That’s it for OBIA fact tables.  Understanding the types of fact tables and their purpose helps us to make better design choices when we set out to build new fact tables to represent business events, and it also helps us to quicker recognize and better analyze the data in these tables.
I hope you found this information useful. If you have information about other fact table types, please share.

Business Intelligence Platform, Data Governance, Data Modeling

Oracle EBS Payables Data Flow and Data Model

This post gives a quick overview of Oracle Payables data flow and data model for some of the most used tables in the module. This is not a complete coverage of the topic but aims to give a general idea of how records are stored in and moves through the module.

OraclePayablesDataflowDataModel

In Oracle Payables, before an AP transaction can be generated, a supplier must exist in the system.  When suppliers are created, records are created in AP_SUPPLIERS and AP_SUPPLIER_SITES.  Invoices are saved in AP_INVOICES (invoice header records) and AP_INVOICE_LINES (invoice lines records).

Payments generate records in AP_INVOICE_PAYMENTS and AP_PAYMENTS_SCHEDULE.  These tables will have 1 record for an invoice if the invoice is paid using a single payment, but will have multiple records for an invoice if the invoice is paid in installments or with more than one payment.

When an invoice is approved and when it is paid, accounting transactions are generated.  These transactions make their way to the GL_INTERFACE table via Payables Transfer to GL process.  In addition to interfacing with GL, Payables also interfaces with many other Oracle modules, such as, Purchasing, Assets, Projects, Inventory, and others (but will not be covered in this post).

Invoices can be entered into Oracle Payables from external systems via the AP_INVOICE_INTERFACE and AP_INVOICES_LINES_INTERFACE tables.  When the Payables Open Interface Import program is run, the records are brought into the regular header and lines tables (AP_INVOICE and AP_INVOICE_LINES).

When payments are made, the information about the payment is recorded in AP_CHECKS.

These are some of the sources for the star-schemas (dimension and fact tables) used in Oracle Business Intelligence Applications (OBIA) Financials module, and in particular, the Payables dashboard and analyses.

Business Intelligence (BI) Development, Business Intelligence Platform, Data Modeling, Data Security

Oracle E-Business Suite (EBS) Foundation Tables

This post is simply a list of Oracle E-Business Suite (EBS) Foundation tables. The Foundation tables contain data that relate to the entire suite of applications – they are not specific to any one module.

Some foundation tables are used in Oracle BI Applications (OBIA), for example, the FND_USER, FND_USER_RESP_GROUPS, and FND_RESPONSIBILITY_VL tables are used in security-related Initialization Blocks.

In general, it’s good to be familiar with these tables when working on OBIEE projects or any BI platform with EBS as a source system.

Foundation Table Purpose
FND_APPLICATION Stores applications registered with Oracle Application Object Library.
FND_APPLICATION_TL Stores translated information about all the applications registered with Oracle Application Object Library.
FND_APP_SERVERS This table will track the servers used by the E-Business Suite system.
FND_ATTACHED_DOCUMENTS Stores information relating a document to an application entity.
FND_CONCURRENT_PROCESSES Stores information about concurrent managers.
FND_CONCURRENT_PROCESSORS Stores information about immediate (subroutine) concurrent program libraries.
FND_CONCURRENT_PROGRAMS Stores information about concurrent programs. Each row includes a name and description of the concurrent program.
FND_CONCURRENT_PROGRAMS_TL Stores translated information about concurrent programs in each of the installed languages.
FND_CONCURRENT_QUEUES Stores information about concurrent managers.
FND_CONCURRENT_QUEUE_SIZE Stores information about the number of requests a concurrent manager can process at once, according to its work shift.
FND_CONCURRENT_REQUESTS Stores information about individual concurrent requests.
FND_CONCURRENT_REQUEST_CLASS Stores information about concurrent request types.
FND_CONC_REQ_OUTPUTS This table stores output files created by Concurrent Request.
FND_CURRENCIES Stores information about currencies.
FND_DATABASES It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.
FND_DATABASE_INSTANCES Stores instance specific information. Every database has one or more instance.
FND_DESCRIPTIVE_FLEXS Stores setup information about descriptive flexfields.
FND_DESCRIPTIVE_FLEXS_TL Stores translated setup information about descriptive flexfields.
FND_DOCUMENTS Stores language-independent information about a document.
FND_EXECUTABLES Stores information about concurrent program executables.
FND_FLEX_VALUES Stores valid values for key and descriptive flexfield segments.
FND_FLEX_VALUE_SETS Stores information about the value sets used by both key and descriptive flexfields.
FND_LANGUAGES Stores information regarding languages and dialects.
FND_MENUS It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.
FND_MENUS_TL Stores translated information about the menus in FND_MENUS.
FND_MENU_ENTRIES Stores information about individual entries in the menus in FND_MENUS.
FND_PROFILE_OPTIONS Stores information about user profile options.
FND_REQUEST_GROUPS Stores information about report security groups.
FND_REQUEST_SETS Stores information about report sets.
FND_RESPONSIBILITY Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.
FND_RESPONSIBILITY_TL Stores translated information about responsibilities.
FND_RESP_FUNCTIONS Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.
FND_SECURITY_GROUPS Stores information about security groups used to partition data in a Service Bureau architecture.
FND_SEQUENCES Stores information about the registered sequences in your applications.
FND_TABLES Stores information about the registered tables in your applications.
FND_TERRITORIES Stores information for countries, alternatively known as territories.
FND_USER Stores information about application users.
FND_VIEWS Stores information about the registered views in your applications.
FND_USER_RESPONSIBILITY
FND_RESPONSIBILITY_VL
FND_ORACLE_USERID
FND_DATA_GROUP_UNITS
Business Intelligence (BI) Development, Business Intelligence Platform, Data Governance, Data Modeling, Reporting

Oracle E-Business Suite (EBS) List of Flexfields

This post is simply a table listing of the Key Flexfields (KFFs) in Oracle E-Business Suite (EBS) (aka Oracle Applications). The table contains the flexfields, along with their Code, Owning Application and base table. 

Flexfields are commonly used in OBIEE reporting for EBS source systems. Flexfields are also an integral part of the Oracle Business Analytics Warehouse (OBAW) data model. It is worthwhile to be familiar with them.

  Name Code Owning Application Table Name
1 Account Aliases MDSP Oracle Inventory MTL_GENERIC_DISPOSITIONS
2 Accounting Flexfield GL# Oracle General Ledger GL_CODE_COMBINATIONS
3 Activity Flexfield FEAC Enterprise Performance Foundation  
4 AHL Route AHLR Complex Maintenance, Repair, and Overhaul  
5 Asset Key Flexfield KEY# Oracle Assets FA_ASSET_KEYWORDS
6 Bank Details KeyFlexField BANK Oracle Payroll PAY_EXTERNAL_ACCOUNTS
7 CAGR Flexfield CAGR Oracle Human Resources  
8 Category Flexfield CAT# Oracle Assets FA_CATEGORIES
9 Competence Flexfield CMP Oracle Human Resources  
10 Cost Allocation Flexfield COST Oracle Payroll PAY_COST_ALLOCATION_KEYFLEX
11 Grade Flexfield GRD Oracle Human Resources PER_GRADE_DEFINITIONS
12 Item Catalogs MICG Oracle Inventory MTL_ITEM_CATALOG_GROUPS
13 Item Categories MCAT Oracle Inventory MTL_CATEGORIES
14 Job Flexfield JOB Oracle Human Resources PER_JOB_DEFINITIONS
15 Location Flexfield LOC# Oracle Assets FA_LOCATIONS
16 Oracle Service Item Flexfield SERV Oracle Inventory MTL_SYSTEM_ITEMS
17 People Group Flexfield GRP Oracle Payroll PAY_PEOPLE_GROUPS
18 Personal Analysis Flexfield PEA Oracle Human Resources PER_ANALYSIS_CRITERIA
19 Position Flexfield POS Oracle Human Resources PER_POSITION_DEFINITIONS
20 Public Sector Budgeting BPS Oracle Public Sector Budgeting  
21 Sales Orders MKTS Oracle Inventory SALES_ORDER_ID
22 Sales Tax Location Flexfield RLOC Oracle Receivables AR_LOCATION_COMBINATIONS
23 Soft Coded KeyFlexfield SCL Oracle Human Resources HR_SOFT_CODING_KEYFLEX
24 Stock Locators MTLL Oracle Inventory MTL_ITEM_LOCATIONS
25 System Items MSTK Oracle Inventory MTL_SYSTEM_ITEMS
26 Territory Flexfield CT# Oracle Receivables RA_TERRITORIES
27 Training Resources RES Oracle Learning Management