Add Fact

Add elements that you can measure such as count and aggregate, and perform statistical operations to your custom logical star using the Add Fact option.

While selecting an aggregation rule for each fact column to set the aggregation behaviour, use a time-balanced aggregation when the added measure mustn't be "aggregated" by default across a time dimension. Oracle Fusion Data Intelligence supports non-aggregation types like "Last" or "First" in place of the "SUM" aggregation type when required. Use a level-based aggregation when the underlying measure must always be calculated to a specific level of a predefined dimensional hierarchy. For example, in a product hierarchy that has the Product Total, Product Category, Product Sub-Category, and Product Details levels, you add a new measure called "Revenue" and need this "Product Category Revenue" measure to be aggregated to Product Category, then you must use the level-based aggregation and choose the right level of the Product Dimension. This setting enables Oracle Fusion Data Intelligence to always aggregate and show the value of the measure at the Product Category level. This is useful when you need to calculate Product Revenue as a % of Category Revenue.

1. Navigate to the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
2. On the selected sandbox Details page, click Perform Action, select Manage Logical Star, and then select Create Logical Star.
3. On the Logical Star page, click Add Fact, and in step 1 of the Add a Fact wizard, select the schema, and then select a view or table or synonym as the object. For example, FCT_CALC_Extensions.
   You see the fact table for the selected object.
4. In the details of the fact table for the selected source table, click the Select Fact and Use for Key check boxes for the source columns that you want to add to your new fact table in the target subject area.
5. Optional: In the details of the fact table for the selected source table, under Select Degen Attribute, click the check boxes for the attributes for which you need the degenerate dimension to be created.
6. If any of the selected attributes have been removed or modified in the source table since the last refresh, then you see such columns highlighted and a message asking whether you want to update the table. Select OK in the message to reload the source columns. If you want to review the changes to the source columns, then click Cancel in the message, and later click Refresh to reload the source columns.
   If any of the attributes that you haven’t selected have been removed or modified in the source table, then you see the refreshed list of source columns. If any of the custom columns fail validation during the refresh, then you see a message asking you to resolve the cause of failure and revalidate.
7. Optional: Click Create Column to add a new column to your new fact table in the target subject area using these instructions:
   1. In Create Column, enter a display name.
   2. Under Data Elements, search for a data element from the physical table of the selected dimension table.
   3. From the search results, double-click the data element to place it in the text pane.
   4. Under Functions, search for a function to construct a column using expressions. For example, search for functions like "substring" or "concatenate" to construct new expression-based columns. From the search results, double-click the applicable result to add it to the central text pane.
   5. Click Validate, and then click Save.
8. Select the aggregation rule for each fact column to set the aggregation behaviour. You can set the time-balanced aggregation rule for a time dimension and hierarchy level-based aggregation rule for a dimension using these steps:
   1. For a fact column, click the Time-Balanced Aggregation icon.
   2. In the Time-Balanced Aggregation dialog, click Add Time Dimension, adjust the aggregation rule, and then click OK.
   3. For a fact column, click the Hierarchy Level-Based Aggregation icon, select the dimension and level. Click Add Dimension to add more dimensions. Click OK.