Add a Step to a Branch

You can add customization types such as "Extend a Dimension" as a step to an existing unapplied branch or a new branch that you create.

1. Sign in to your service.
2. In Oracle Fusion Data Intelligence Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
   You see the main and existing customization branches.
4. In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
5. On the Branch page, click Add Step.
6. In Add Step, select a customization type such as Extend a Dimension.
   You see the wizard sequence to add details for the selected customization type.

Add a Dimension

You can create a custom dimension, join it to the prebuilt or custom facts, and add the custom dimension to any subject area to meet your business requirements.

1. Sign in to your service.
2. In Oracle Fusion Data Intelligence Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
   You see the main and existing customization branches.
4. In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
5. On the Branch page, click Add Step.
6. In Add Step, select Add a Dimension.
   You see the wizard sequence to add a dimension.
7. In step 1 of the wizard, enter a name for your customization step, for example, Add Point of Sale Dimension and add a brief description.
8. In step 2 of the wizard, select the schema, and then select the dimension table in Object. For example, COST_CENTER_VIEW1.

   Note:

   If you don’t see the schema or table, then ensure that you have granted select permission to the OAX$OAC schema in the autonomous data warehouse. For example, grant select on <schema>.<table> to OAX$OAC. See Load Customization Data to the Autonomous Data Warehouse.

   You see the attributes available in the selected dimension table. You can use the Search and Filter fields to limit the attributes displayed for the dimension table.

9. Select the attributes that you want to use from the dimension table and indicate an attribute to be used as the key for joining with a fact table in the target subject area.
10. 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.
11. Optional: Click Create Column to add another column to your dimension 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.
12. In step 3 of the wizard, select, drag, and drop available data elements into the Selected Data elements pane to design a hierarchy for the dimension and then click Next. In the Selected Data Elements pane, click a level to update its primary key and set its display attribute in the Properties pane.
    You can add multiple levels in your hierarchy by right-clicking at a level and selecting Add Child or Add ‘n’ Child Levels. For example, your Region Hierarchy can have Region Total at Level 1, Region at Level 2, Country at Level 3, State at Level 4, and City at Level 5.
13. In step 4 of the wizard, select Skip Joins if you don’t want to join the selected dimension table to any facts. To join the selected dimension table to a fact, select the fact table, fact key, and join type. Click Content Level to specify the content level for your fact.
    You can join a single fact key column to multiple dimension keys.

    Note:

    Ensure that the data types of the join key pairs match. If your data types don't match but you want to proceed, then click Yes in the message. However, if the data types can't be absolutely matched, then the server-side validation rejects that join completely and you must change the data type of custom key column to match the factory data type.
14. Optional: Click Add Fact Table to select another fact table to link your dimension to and define the join.
15. Click Next.
16. Optional: In step 5 of the wizard, select the subject areas to include the new dimension and click Finish.

You see a message that your step is being applied to the customization branch. After it's applied, you see the new customization step in the customization branch. You can now apply the customization branch to the main branch or edit it to add more steps.

Note:

If you’ve created Add a Dimension steps using the previous functionality, you can still edit and reapply through the Edit option.

Add a Fact Table

Add a fact table to an existing subject area.

1. Sign in to your service.
2. In Oracle Fusion Data Intelligence Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
   You see the main and existing customization branches.
4. In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
5. On the Branch page, click Add Step.
6. In Add Step, select Add a Fact.
   You see the wizard sequence to add a fact.
7. In step 1 of the wizard, enter a name for your customization step, for example, Add Travel Expense and add a brief description.
8. Select a target subject area to which you want to add the fact. For example, Financials - AP Expense.
   You see the details of the selected subject area.
9. Click Next.
10. In step 2 of the 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.
11. 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.
12. 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.
13. 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.
14. 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.
15. Click Next.
16. In step 3 of the wizard, use the Diagram or Tabular tabs to specify the joins to link your new fact table to the dimensions in the selected subject area.
    Follow these instructions to specify the joins using the Diagram tab:

    1. Click Add Table.
    2. In Add Table, select the dimensions to add and click OK.
    3. Drag from the dimension's port (dark green circle) to the fact table's port (brown circle) to create a join link. You see the Join dialog.
    4. In the Join dialog, select the type of join, the fact table column, and the dimension key column. Click Add Join Condition to add multiple join conditions and then click Join. If you want to provide expressions as join conditions, then click Complex Join and in Create Joins, click Add Joins, select the target and source logical tables, enter the join condition as an expression, and click OK.

    Follow these instructions to specify the joins using the Tabular tab:

    1. Click Add Join.
    2. In Add Table, select the dimension to add and click OK. You see the Join dialog.
    3. In the Join dialog, select the type of join, the fact table column, and the dimension key column. Click Add Join Condition to add multiple join conditions and then click Join. If you want to provide expressions as join conditions, then click Complex Join and in Create Joins, click Add Joins, select the target and source logical tables, enter the join condition as an expression, and click OK.
17. Optional: Click Skip Joins if you don’t want to join a dimension now.
18. Click Next.
19. In step 4 of the wizard, select the aggregation rule for each fact column to set the aggregation behaviors.
20. Optional: 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.

    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.

21. Click Next.
22. Optional: Select additional subject areas to add the fact.
23. Click Finish.
    You see a message that your step is being applied to the customization branch. After it's applied, you see the new customization step in the customization branch. You can now apply the customization branch to the main branch or edit it to add more steps.

Add a Hierarchy

Add a hierarchy to a dimension table in an existing subject area.

1. Sign in to your service.
2. In Oracle Fusion Data Intelligence Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
   You see the main and existing customization branches.
4. In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
5. On the Branch page, click Add Step.
6. In Add Step, select Add a Hierarchy.
   You see the wizard sequence to add a hierarchy.
7. In step 1 of the wizard, enter a name for your customization step, for example, Add Region Hierarchy and add a brief description.
8. Select the subject area, the folder of the dimension table, and the dimension table to which you want to add a hierarchy.
   You see the existing hierarchies and the hierarchy levels in the selected dimension. If there aren’t any hierarchies, then you see a message informing you that there are no hierarchies in the selected dimension.
9. Click Next.
10. In step 2 of the wizard, assemble the product hierarchy using the attributes from this dimension table with these instructions:
    1. Enter a hierarchy name. For example, Region Hierarchy.
    2. Select, drag, and drop available data elements into the Selected Data elements pane to design a hierarchy that you want. You can add multiple levels in your hierarchy by right-clicking at a level and selecting Add Child or Add ‘n’ Child Levels. For example, your Region Hierarchy can have Region Total at Level 1, Region at Level 2, Country at Level 3, State at Level 4, and City at Level 5.
    3. In the Selected Data Elements pane, click a level to update its primary key and set its display attribute in the Properties pane.
11. Click Next.
12. Optional: In step 3 of the wizard, select additional subject areas to include the new hierarchy.
13. Click Finish.
    You see a message that your step is being applied to the customization branch. After it's applied, you see the new customization step in the customization branch. You can now apply the customization branch to the main branch or edit it to add more steps.

Add Session Variables

Add custom session variables that you can include in the analyses.

The SQL query that you define is executed by user OAX$OAC. If you're using another schema in the query, then you must mention the schema name as prefix. You must ensure to grant user OAX$OAC access to all the database objects used in the query.

1. Sign in to your service.
2. In Oracle Fusion Data Intelligence Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
   You see the main and existing customization branches.
4. In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
5. On the Branch page, click Add Step.
6. In Add Step, select Add Session Variables.
   You see the wizard sequence to add the session variables and a list of existing session variables.
7. In step 1 of the wizard, check if any of the existing session variables serve your purpose.
   If yes, then you can exit the wizard and use the applicable existing session variables in your analyses. If no, then continue with the next steps to create the session variables that you require.
8. Enter a name for your customization step, for example, Add a Session variable using Invoice Received Date and add a brief description. Click Row-wise Initialization to configure cache settings.
9. Click Next.
10. In step 2 of the wizard, define the SQL query and create the initialization block using these instructions:
    1. Enter a name and description for the initialization block.
    2. Select a preceding initialization block.
    3. Enter the SQL query that would be executed in the autonomous data warehouse and return a value that you can use in the reports. For example, if you want to get the Exchange Rate Type that's defined in Oracle Fusion Data Intelligence into a session variable, then you can use the following SQL script:

       SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE
        PARAMETER_CODE='PARAM_GLOBAL_EXCHANGE_RATE_TYPE' 

11. Click Next.
12. In step 3 of the wizard, create the session variables using the output of the initialization block created in step 2 of the wizard.
13. Click Finish.
    You see a message that your step is being applied to the customization branch. After it's applied, you see the new customization step in the customization branch. You can now apply the customization branch to the main branch or edit it to add more steps.

Extend a Dimension

Extend prebuilt dimensions with additional attributes from another data source.

1. Sign in to your service.
2. In Oracle Fusion Data Intelligence Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
   You see the main and existing customization branches.
4. In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
5. On the Branch page, click Add Step.
6. In Add Step, select Extend a Dimension.
   You see the wizard sequence to extend a dimension.
7. Enter a name for your customization step, for example, Add Cost Center Type.
8. Select a target subject area, for example, Financials GL Profitability.
9. In Folder, select a dimension that you want to extend, for example, Cost Center.
10. Select a logical table, for example, Dim – Cost Center.
    You see the available attributes in the table.
11. Click Next.
12. Select a schema and table from the database.
    You see the available attributes in the table.
13. Select the columns that you want to expose or use as a key for creating the join.
14. Click in the Display Name table field to enter a new name for the column or to edit an existing one and then click Enter to accept or click Esc to cancel.
15. 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.
16. Optional: Click Create Column to create a new column in the selected dimension table using these instructions:
    1. In Create a new column, enter a display name, for example, Cost Type.
    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 "case" to construct new expression-based columns. From the search results, double-click the applicable result to add it to the text pane.
    5. Click Validate, and then click Save.
17. Click Save.
18. Click in the Source Column table field to edit the column definition.
19. Click Save.
    You see the new column in the Data preview section in a highlighted color.
20. Click Next.
21. Select a join key to pair with the source column. If you want to provide expressions as join conditions, then click Complex Join and in Create Joins, click Add Joins, select the target and source logical tables, enter the join condition as an expression, and click OK.
22. Click Next.
23. Select the subject areas that should use this customization.

    Note:

    The Subject Area that you initially selected is selected by default and is read-only. By default, all additional subject areas are selected. Deselect the additional subject areas that shouldn’t use this customization.
24. Click Finish.
    You see the new customization step in the customization branch. You can now apply the customization branch to the main branch or edit it to add more steps.

Add Derived Columns

Add a derived column to an existing subject area.

1. Sign in to your service.
2. In Oracle Fusion Data Intelligence Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
   You see the main and existing customization branches.
4. In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
5. On the Branch page, click Add Step.
6. In Add Step, select Add a Column.
   You see the wizard sequence to add a column.
7. In step 1 of the wizard, enter a name for your customization step, for example, Regional Revenue and add a brief description.
8. Select a target subject area to which you want to add the column. For example, Profitability.
   You see the details of the selected subject area.
9. Select the presentation folder within the selected subject area and the logical table to which you want to add the column.
10. Click Next.
    You see the Create Column dialog in step 2 of the wizard.
11. In step 2 of the wizard, define your new column using these instructions:
    1. In Create Column, enter a display name.
    2. Under Data Elements, search for a data element from the subject area that you had selected previously.
    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. From the search results, double-click the applicable result to add it to the central text pane. For example, search for functions like "Filter" or "Avg" to construct expression-based columns. A sample expressions to derive the average supplier payment days is avg(ROUND(((CASE WHEN Invoice Received Date is not null THEN (Financials - AP Payments.Payment Date.Payment Date - Invoice Received Date) ELSE (Financials - AP Payments.Payment Date.Payment Date - Financials - AP Invoices.Invoice Date.Invoiced Date) END)/Financials - AP Payments.Facts - Analytics Currency.Total Payment Count),0)).
    5. Click Validate, and then click Save.
12. Optional: If you want the underlying measure of the column to be calculated to a specific level of a predefined dimensional hierarchy, then complete these steps:
    1. Click the Hierarchy Level-Based Aggregation icon.
    2. In the Hierarchy Level-Based Aggregation dialog, select the dimension, level, and then click OK.
    3. Click Add Dimension to add more dimensions.
13. Click Next.
14. Optional: Select additional subject areas to add the fact.
15. Click Finish.
    You see a message that your step is being applied to the customization branch. After it's applied, you see the new customization step in the customization branch. You can now apply the customization branch to the main branch or edit it to add more steps.

Create a Subject Area

You can create a subject area as a container and later add dimensions and facts to your new subject area or create a subject area based on an existing one.

1. Sign in to your service.
2. In Oracle Fusion Data Intelligence Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
   You see the main and existing customization branches.
4. In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
5. On the Branch page, click Add Step.
6. In Add Step, select Create a Subject Area.
   You see the wizard sequence to create a subject area.
7. In step 1 of the wizard, enter a name for your customization step, for example, Custom Profitability and add a brief description.
8. Create a subject area using one of the methods:
   1. Select Create a Subject Area to create a subject area container, provide a name without any leading or trailing white spaces, add a description, and then click Next. You see step 4 of the wizard. Click Finish to create the subject area.
   2. Select Create a Subject Area based on an existing one to create a subject area using an existing one in the system and provide these details:
      1. Select an existing subject area, name your subject area, and then click Next.
      2. In step 2 of the wizard, select the data elements that you want in your new subject area.
      3. Click Add Subject Area to select and add data elements from multiple subject areas.
      4. In step 3 of the wizard, organize and rename the data elements in your new subject area.
      5. Click Next.
      6. In step 4 of the wizard, review your new subject area and click Finish to create it.
   You see a message that your step is being applied to the customization branch. After it's applied, you see the new customization step in the customization branch. You can now merge the customization branch with the main branch or edit it to add more steps.

Modify a Subject Area

Modify a custom subject area using these instructions.

1. Sign in to your service.
2. In Oracle Fusion Data Intelligence Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
   You see the main and existing customization branches.
4. In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
5. On the Branch page, click Add Step.
6. In Add Step, select Modify a Subject Area.
   You see the wizard sequence to modify a subject area.
7. In step 1 of the wizard, enter a name for your customization step, for example, Custom AP Invoices and add a brief description.
8. Select a subject area that you had created using the instructions in Create a Subject Area.
   You see the details of the selected subject area.
9. Click Next.
10. In step 2 of the wizard, from the Available Data Elements pane, select or deselect the data elements that you want to use or don’t want in the selected subject area.
11. Optional: Click Add Subject Area to select and add data elements from multiple subject areas.
12. Click Next.
13. In step 3 of the wizard, organize and rename the data elements in your modified subject area.
14. Optional: Click the Advanced Properties icon next to the custom subject area to select an implicit fact that allows dimensions to be used for analytic queries even when not joined to a logical fact table.
15. Click Next.
16. In step 4 of the wizard, review your modified subject area and click Finish.