Introduction
Oracle Fusion Data Intelligence (FDI) is the next generation of Oracle Fusion Analytics Warehouse, designed specifically for Oracle Fusion Cloud Applications. It brings together business data, ready-to-use analytics, and prebuilt AI and machine learning (ML) models.
FDI introduces a powerful new feature, dynamic aggregate tables. This feature revolutionizes reporting and data analysis by providing a streamlined and efficient approach to data aggregation and query optimization.
Key Capabilities and Benefits
- Custom Aggregate Table Creation
- You can easily create tailored aggregate tables and significantly reduce data volume at least 80% by selecting specific dimensions from the base table.
- Flexible Dimension Selection
- The feature gives you precise control over the dimensions used for aggregation, enabling the creation of tables that perfectly match your reporting needs.
- Automated Data Management
- Seamless integration with the daily FDI pipeline loads automates the population and refresh processes, ensuring data consistency and freshness.
- Accelerated Report Performance
- By leveraging pre-aggregated data, report execution times are dramatically reduced, providing you with faster insights.
- Enhanced Data Compression
- Data compression, tailored to the selected dimensions, further optimizes storage efficiency.
- Simplified Development with Wizards
- Intuitive wizards streamline the creation and deployment of aggregate tables, reducing development time and complexity.
- Intelligent Query Routing
- The system intelligently routes queries between aggregate tables and base tables based on the report’s data granularity, ensuring optimal performance and data detail. When data is requested at a high level, the aggregate tables are used. When a low level of detail is requested, the base tables are used. This is true mostly in an ideal ue case, but there’s an exception i.e., SCD. In the case of exceptions, a new subject area will be created for dynamic aggregate table.
In essence, this feature empowers you to:
- Create highly efficient and performant reporting solutions
- Accelerate the delivery of business insights
- Reduce the workload associated with managing large datasets
Dynamic Aggregate tables significantly improves the overall efficiency and effectiveness of data analysis in FDI.
Note: This capability is currently available for the “Financials – GL Balance Sheet” subject area with support for certain dimensions.
The Approach: Create Dynamic Aggregate Tables
1. Sign into your service.
2. Navigate to Oracle Fusion Data Intelligence Console and Reporting Configuration under Application Administration.
3. On the Reporting Configuration page, on the Advanced tab, select Dynamic Aggregation, and then click Create.
4. In Create a Dynamic Aggregation, select the subject area and base fact.
Mandatory list dimensions based on subject are automatically pre-selected by default.
5. Toggle the Show Cardinality option to On to view the cardinality of each dimension with respect to the fact, and then the applicable dimensions.
The cardinality value can help you to decide whether to select the dimension or not. But the driving factor for selecting dimensions should usage in reports.
5. Click Next.
6. Review your aggregate base fact and click Save.
7. In the Dynamic Aggregation section, on the Advanced tab on the Reporting Configurations page, click Actions for your aggregate base fact, and then select Publish to complete the dynamic aggregation configuration.
The data in the aggregated table is compressed based on the selected dimensions. The default threshold value for the compression is 20. For example, you can select up to 20% or lesser of the base fact volume/rows depending on cardinality of various dimensions. The compression value is the number of compressed rows divided by the number of total base fact rows. If you exceed the 20% value, the system displays a validation error that you must correct to proceed.
Common Errors and Troubleshooting
If the status changes to Validation Failed, it’s most likely becuase a compression value is higher than threshold of 20% as shown below.
- Click edit and modify list of selected dimensions so to achieve compression values less than 20.
- Submit the job again for validation.
Once validation is successful, the job status changes to “Publishing”.
After initial data population pipeline completes, the status changes to Published.
Note: Depending on many factors, like other process scheduled or running, there can be a significant delay, sometimes 24 hours, before the aggregate is published.
Data Population Guidelines
- During the publishing stage, patience is required as the initial data population can take anywhere from few minutes to few hours based on data in base fact tables.
- If the job completion status updates to Published, this tells you that initial data population of dynamic aggregate table is complete.
- Daily Incremental updates to dynamic aggregate tables will be performed by daily FDI pipeline runs automatically.
To Validate Query Routing to Dynamic Aggregate Tables
- Create a query on GL- Balance SA before creation of dynamic aggregate tables into FDI.
Observe that this query hits base fact table DW_GL_BALANCE_CA.
Note: DW_GL_BALANCE_CA is a prebuilt aggregate table & DW_GL_BALANCE_CF is base fact table but considering report cardinality used in this example assume DW_GL_BALANCE_CA is a base fact table.
Note: Prebuilt aggregate tables in FDI and dynamic aggregate tables are different tables.
- Create dynamic aggregate tables into FDI.
- Validate that same query on top of GL – Balance SA hits the newly created dynamic aggregate table DW_FA_X_CONTENT_GL_BAL_CA.
Note: Ensure that the query only includes dimensions selected in the aggregate definition.
Call to Action
For more information, see the FDI documentation.
Now that you’ve read this article, try it yourself and let us know your results in the Oracle Analytics Community, where you can also ask questions and post ideas. It’s free and easy to join!