4 Customize Oracle NetSuite Analytics Warehouse

You can customize the semantic model and groups.

As a modeler or modeler administrator, you can customize your semantic model. Customization enables you to make the data that you moved into the analytics warehouse more useful for reporting. As a security administrator, you can create, configure, and assign custom groups to users.

Topics:

• About Semantic Model Customization
• Create a Branch
• Edit a Branch
• Add a Step to a Branch
• Edit or Delete a Branch Step
• Reapply a Branch Step
• Copy Steps from One Branch to Another
• View Details of Failed Branch Steps
• Merge the Customization Branches with the Main Branch
• Reorder Steps of Customization Branches
• Delete a Main Branch Step
• Tag the Main Branch's Steps
• Configure Object Permissions
• View Activity History of Semantic Model Extensions
• Publish the Model
• Load Customization Data to the Autonomous Data Warehouse
• Move Your Customizations to Another Environment
• Republish Your Customizations

About Semantic Model Customization

You can customize the semantic model to extend it for your business requirements. You extend the semantic model to make the data that you moved into the warehouse more useful for reporting.

You customize your semantic model by creating branches and adding steps to those branches in your production environment. You use a branch or version to publish your changes to the model. You can apply the model from the branch to a local service instance for testing. When the changes are correct, you can merge that branch with the main branch. You can merge multiple branches with the main branch over time. When you have a set of changes finalized, you can version the main branch and use the Bundles functionality to move the customizations to another environment. See the information regarding the Semantic Extensions Bundle in Bundle Your Application Artifacts.

You can copy the steps from the main branch, edit them directly, and later merge them with the main branch. However, you must ensure not to perform two or multiple levels of copies. For example, you must not copy steps from the main branch to another branch, then copy the steps over to yet another branch, and then merge the second branch with the main branch.

You can customize the model by extending prebuilt dimensions with additional attributes from another data source, by adding a fact to an existing subject area, and by reorganizing the prebuilt subject areas to create a new subject area to name a few. If you've merged an external application, then note that both external semantic model and semantic model extensions can co exist. On the Publish Model page, select "Yes" if you want the external semantic model to be included.

The semantic model consists of these components:

• Oracle Content: This is the base model provided by Oracle. Your customizations are layered on this.
• System Extensions: Your descriptive flexfield and data augmentation changes are available in this component. See View Activity History of Semantic Model Extensions to know about the scenarios in which Oracle NetSuite Analytics Warehouse applies the system extensions.
• User Extensions: Your customization branches and versions are available in this component.
• Security Configurations: You can secure the objects of all the other components against the application roles in this component.

From release Platform 23.R4 onwards, Oracle NetSuite Analytics Warehouse periodically evaluates the customizations and sends notifications to the users with Modeler Administrator and Modeler privileges to correct errors and warnings in branches and steps as soon as possible to prevent errors during patching. If these errors aren't fixed and a patch is initiated (or a mandatory patch is auto-applied), then patching may encounter issues. In that case, customizations that haven't been merged to the main branch are removed and the factory semantic model is upgraded. You must reapply the applicable customizations after the patching is complete.

Learn About the New Semantic Model Extensibility Experience

A new set of wizards are available for you to create semantic model extensions. These wizards will replace the existing wizards in a future release. With the new wizards, you can create extensions with objects created using the merged external application. For this reason, the External Applications is the second layer to be applied and other options are applied subsequently. If you've already merged an external application, then you can create extensions with wizards as well. Though you may choose not to in order to keep all changes in the external semantic model. None of your customizations are lost as a result of this change in order and no reimplementation is necessary.

You can schedule when to switch to the new wizards after learning about it. When you switch to the new wizards, Oracle will migrate your current customizations to the new framework. See About Migrating to the Enhanced Semantic Model Extension Framework.

The new user interface provides a modeler-centric view of the data model. Here is a comparison:

Existing but soon to be replaced	New
Action centric view.	Model centric view.
Steps done one-by-one but are independent of each other.	Related steps are grouped together making it easier to complete a fully consistent unit of work.
Has the concept of branches that can contain steps that are unrelated.	Has the concept of a sandbox that can contain fully formed dimensional models or “stars”. All objects within a star are related.
No graphical view of the model.	Complete graphical view of each star that shows the ready-to-use and custom objects.
Publish branches to test. Merge to main when completed.	Publish sandbox to test, merge to main when completed.
Customization steps are disconnected. Different steps done on the same object can override each other in different branches.	All customizations done to an object are visible together. You see the result of all operations at any time.

As a modeler, there are generally three types of customizations you can do:

• Add or update objects, attributes, joins, and calculations.
• Change how objects are presented to the user such as change subject areas.
• Create variables to control behaviour of sessions and queries.

All other complexities and allowed operations are abstracted and done by the wizards. Operations of each type are possible with these new wizards and connected steps are done together. The key benefit is better usability and understanding of the model and customizations. Another key benefit is better performance and consistency of the semantic model. In the current wizards, each step is compiled and applied one at a time. In the new framework, the entire sandbox is compiled and applied, which results in a much faster time to have all changes available for reporting.

The new semantic model extensibility capabilities enable you to create customizations in a sandbox. For each sandbox, you can perform these actions:

• Manage Subject Areas - Create a new Subject Area or modify an existing one that you created earlier.
• Manage Logical Star - create a new or modify an existing logical star. You start by creating a fact, then add dimensions, and then joins. You modify an existing logical star by adding dimensions, attributes, and other tables. You can add dimensions to existing ready-to-use facts while creating a new logical star. You can create many logical stars.
• Manage variables - similar to adding session variables in the existing capability for the semantic model extension.

After you create your sandbox, you merge it with the main sandbox and then publish the merged main sandbox in the non-production environments such as development or test to ensure that there are no errors.

See Use the Enhanced Semantic Model Extensibility Capabilities.

About Migrating to the Enhanced Semantic Model Extension Framework

Prior to switching over to the new semantic model extensions framework, do note these and take appropriate action where required:

• Before migration, merge all your changes to the main branch and publish it because Oracle will migrate the current main branch, but not the custom branches.
• Security configurations will remain unchanged in the new framework, hence no action from you is required.
• Upgrade all non-production and production instances to the new framework before attempting any migration of semantic extensions from non-production to production instances using the Bundles functionality.

You can initiate migration of your extensions to the enhanced framework by submitting the request from the banner message on the Semantic Model Extensions page when you click User Extensions. You can initiate it automatically by scheduling the migration from the banner message. After initiating the migration:

• You can't use the semantic model extensions capability during the migration process.
• Oracle migrates the existing customizations to the new framework. Once the migration is complete, you receive a notification on the console. You can also monitor the status in the Activity tab for Semantic Model Extensions.
• Sign in and sign out of your service after migration completes.
• Once the migration is completed, you see a downloadable report that shows which customizations are available for which object with the Main branch object to Main sandbox object mapping (so that you can see all your customizations that have been brought over). In most cases, operations like Add Dim, Add Fact, Add Variable, Extend Dimension, Add Columns have 1:1 correspondence between the old and the new framework. The Main sandbox is published and ready for use. All of your subject areas, visualizations, and reports remain unchanged. An option to revert to the old framework if customizations aren't as expected is available and the migration rollback option gets disabled after 60 days from the migration date. Ensure to complete the reversal process within this period, if you must revert. If you choose to revert, then the semantic model reverts to the state prior to the migration being initiated. You can't rollback the migration if you've completed a content upgrade after completion of the migration process.
• Oracle maintains a backup of the existing semantic model extensions at the time the migration is initiated. If you encounter issues during or after migration, Oracle uses the backup to troubleshoot and enable required extensions on the new framework. Should you choose to revert back to the previous state, the semantic model is restored from this backup. If you've made further changes to the semantic model in the new framework before choosing to revert, these customizations can't be migrated to the previous semantic model extensions framework.

About Recommendations and Tips to Extend the Semantic Model

Before extending your semantic model, review the recommendations and tips to ensure that your extensions work as expected.

Recommended naming conventions: Ensure that you follow these naming conventions to avoid loss of customizations after an upgrade and to easily identify the custom objects:

• Prefix a custom object with X_ZZZ_ where ZZZ is an abbreviation of your organization.
• Suffix different objects as:
  • _A = Aggregate
  • _D = Dimension
  • _DH = Dimension Hierarchy
  • _F = Fact
  • _H = Helper
  • _M = Map Dimension
  • _MD = Mini Dimension
• Use unique names for objects in your semantic model extensions to avoid issues caused by duplicate names.

Recommendations applicable across the types of extensions:

• Perform all development activities in one environment that you consider as the master development environment. Subsequently, you must move the extensions from that environment to other non-production or production environments.
• When you need to create a star schema (custom dimensions and custom facts), always create the custom dimensions first followed by the custom facts.
• When you define joins in any customization wizard, ensure that the join columns are of compatible data types.
• Don’t extend a prebuilt fact to alter the structure. For example, don’t add Invoice Distribution ID into the Invoice Line fact table.
• Don’t keep changes in a local branch for long periods of time. Local branches can become out of synchrony when changes to the Oracle NetSuite Analytics Warehouse environment occur. Use a local branch for testing and merge to the main branch soon and frequently. Promote the main branch to Production using the Test to Production (T2P) process.
• Ensure that the changes in source are addressed in your Oracle NetSuite Analytics Warehouse instance. For example, if a descriptive flexfield used in a custom subject area has been disabled in the source, then you must replace or remove the applicable descriptive flexfield in Oracle NetSuite Analytics Warehouse else the applicable semantic model extension fails.
• Ensure that the table and column names don't have leading or trailing spaces in the semantic model extension steps.
• Ensure to check and correct the errors and warnings on the Semantic Model Extensions page regularly.
• If you want to use any dataset that has been brought in through data augmentation to build the joins or incorporate any object from the dataset into your semantic model, then you must ensure to run an incremental load before using the dataset.
• While reapplying any extension, ensure that you cleanup the view in schema OAX_CUSTOM_DATA, re-edit the extension in the Main branch, and re-apply it.
• Don’t create any table starting with "DW" in custom schemas and the OAX_User schema because this may result in conflict with the prebuilt object names. If you create tables starting with "DW", then these tables won't show as custom tables in the Semantic Model Extensions wizard.

Recommendations while adding a dimension:

• While defining the joins, you can choose to skip the joins and proceed to the next action. This results in the step ending up in a warning state but the process adds the custom facts. Oracle NetSuite Analytics Warehouse clears the warning when you define the joins later.
• Always specify the primary key and display attribute when you define the hierarchies for the custom dimension.

When extending a dimension, combine multiple extensions for the same dimension in the database. Same dimension extensions always join to the base dimension on the same columns. Hence, in the database, combine the extensions into one object and then extend all columns using that object.

Note:

Multiple steps defined for the same dimension eventually lead to a constraint causing the extensions to fail. For example, in step 1, you use table A and add a set of columns and then in step 5, you use the same table A, extend the same dimension, and add the same set of columns. This results in failure because in step 1, the columns are already added; hence, step 5 fails and the user extensions aren’t available. Combining the extensions into one database object and then defining all extensions using one step is highly recommended.

When adding a fact, always set the content levels for the custom dimensions that are joining to the custom fact.

Whenever a system extension run is replaying the master branch, you must not edit the main branch during this time as that might cause a conflict leading to failure of the semantic extensions.

Minimize the number of semantic extensions as much as possible. For examples, don’t create multiple extensions to modify the same subject area; instead, you must consolidate the modifications to the same subject area. If you’ve to modify the same dimension, then consolidate changes to a single logical table source.

When adding a custom hierarchy, avoid aiming to display the grand total levels in visualizations because custom hierarchies are exposed only from the first level. The ready-to-use hierarchies too don’t expose the total levels. The Grand Total level just gives the grand total amount; hence use it only when there is no join between a fact and dimension and the metric has to be set at a total level.

Don’t export the semantic model extensions bundle separately and then do security promotion through test to production. Generate the semantic model extensions bundle and include the extensions you want and then include security as well.

Create a Branch

To begin customizing your semantic model, create a branch of the main semantic model.

You add customizations to the production environment. After you have added and tested your customizations, you can publish them to the model in the production environment.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse 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, for Customization Branches, click Create Branch to create a branch as an empty container.
5. In Create a Branch, enter a name for your branch, for example, Add Cost Center.
6. Optional: Add a description and click Done.
   You see the Add Step dialog. You can continue to add the steps or you can add steps to the new branch container later using the Add Step button. See Add a Step to a Branch. You see the new branch on the Semantic Model Extensions page under Customization Branches.

Edit a Branch

Before you apply a branch to the main branch of your semantic model, you can edit the branch description to make it more meaningful.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
4. In the User Extensions region, under Customization Branches, hover over a branch to viewActions.
   You see the actions that are applicable to the branch.
5. From Actions, click Edit Description to update the branch description, and then click Done.

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 NetSuite Analytics Warehouse 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 NetSuite Analytics Warehouse 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 NetSuite Analytics Warehouse 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 NetSuite Analytics Warehouse 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 NetSuite Analytics Warehouse 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 NetSuite Analytics Warehouse 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.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse 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 NetSuite Analytics Warehouse 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 NetSuite Analytics Warehouse 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 NetSuite Analytics Warehouse 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 NetSuite Analytics Warehouse 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 NetSuite Analytics Warehouse 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.

Edit or Delete a Branch Step

As the owner of the branch or an user with the Modeler Administrator role, you can edit a step to modify the details or delete it if it's no longer required. You can delete multiple steps together.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
4. In the User extensions region, under Customization Branches, click a branch to display the steps.
5. Hover over a step to view Actions.
6. Click Edit and update the details.
7. Click Delete to remove it from the branch.

Reapply a Branch Step

As the owner of a branch or a user with the Modeler Administrator role, you can reapply a failed step after resolving any issues that might have occurred with tables or columns in the autonomous data warehouse.

You can reapply the steps from the Branch details page without opening the steps.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse 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 display the steps.
5. Hover over a step to view Actions.
6. Click Reapply.

Copy Steps from One Branch to Another

Use these instructions to copy steps from a customization branch to another customization branch.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse 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 whose steps you want to copy.
5. On the Branch page, click Manage Steps.
6. Select the check box for the steps that you want to copy and click Copy.
7. In Copy Steps, select the target branch to which you want to copy the selected steps and click OK.
   Alternately, you can create a branch using the "Create Branch" option available in the Copy Steps dialog and copy the steps into this new branch in a single action.
   You see a confirmation message that the steps have been successfully copied.

View Details of Failed Branch Steps

You can view the reasons why a branch step had failed and then correct the errors.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse 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 the branch with the Failed icon to view the details.
5. On the Branch page, click the Failed status for a step that has failed and view the error details.

Merge the Customization Branches with the Main Branch

Merge the customization branches with the main branch to use the customization steps as the sequence of steps.

When you edit a branch, the system automatically locks it to prevent another user from simultaneously editing the same branch. As you complete each step or reorder the steps, the system unlocks the branch, saves it, and notes the change in the change log tab.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse 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, hover over a branch to view Actions.
5. From Actions, click Merge to Main Branch, and then click Ok.
   When a branch gets merged into Main, the other branches go out of synchronization and you must resynchronize them with the Main branch.

Reorder Steps of Customization Branches

You can reorder the steps in a branch that has been applied or is yet to be applied to the main branch.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse 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 display the steps.
5. On the Branch page, click Manage Steps.
6. Select the check box for the steps that you want to reorder and click Reorder.
7. In Reorder Steps, use the drag handles to drag and drop the steps in the new order that you want, and then click Reorder.

Delete a Main Branch Step

As a modeler administrator, you can delete all steps of a main branch that have been either applied or have failed. With modeler permissions, you can delete only failed steps of a main branch.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
4. In the User Extensions region, click Main to view the details.
5. On the Main Branch page, hover over a step to display the options.
6. Click Delete to remove it from the main branch.

Tag the Main Branch's Steps

You can create tags on the "Applied" steps of the Main branch as a snapshot at a given point in time.

When you have a set of customizations ready for promotion and merged them with the Main branch, you can tag the Main branch's steps with “Applied” status using the Create Tag option for the Main branch or tag any of it's steps with “Applied” status directly on the Main branch detail page using the Tag option. When you tag a step directly on the Main branch details page or include a step while tagging using the Create Tag option for the Main branch, the steps prior to the selected step are included in the tag too. You can untag the tags that you create for the Main branch's steps using the Untag option.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse 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. To create a tag for a step in the Main branch, in the User Extensions region, click Create Tag.
5. In Create a Tag, enter a name for the tag and a description.
6. Select a merged step that you want to include in this tag.
7. Click Done.
8. To tag a step directly, hover over the Main branch to view Actions and then click View Details.
9. On the Main branch details page, hover over a step to view Actions and then click Tag.
10. In Create a Tag, enter a name for the tag and a description.
11. Click Done.
12. To untag a tag for the Main branch and the step included in the Tag, on the Main branch details page, hover over a step to view Actions and then click Untag.
13. In Confirm Untag, click Untag.

    Note:

    If there're multiple tags on the same step, then you can select the check boxes for the applicable tags in the Confirm Untag dialog.

Configure Object Permissions

Configure the permissions for objects such as subject areas and its elements with the ready-to-use or the custom-created duty roles.

You secure the subject areas and their elements using the Configure Object Permissions, a prebuilt single step. You edit this single step to specify the subject areas, their elements, and the duty roles to secure these with. The elements that you can secure are from the Main branch. Hence, if you need a newly added object to be secured, then you must ensure that the branch containing the newly added object is merged with the Main branch before configuring the security. If a custom-created role is no longer available, then the security configuration for that role is automatically updated in the existing Configure Object Permissions step.

For the front-end objects such as key metrics and workbooks, set the permissions individually for each object by adding the applicable duty role and the corresponding access.

By default, the list of permissions by duty role displays the explicit permissions set for the subject area or the elements of the selected subject area. If you want to add more permissions, then select the duty role from the list and set the required permission. Permission levels that you can set are:

• Default (inherited from the parent element).
• No Access (deny access to the respective subject area or its elements)
• Read-only (access to read the respective subject area or its elements).

Repeat the operation for all the subject areas or the subject area elements that you need to secure.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click Security Configurations.
   You see your existing security configurations and the prebuilt object permissions-related step.
4. In the Security Configurations region, search for the prebuilt "Configure Object Permissions", and hover over it to view Actions, and then click Edit.
5. In step 1 of the wizard, click Next.
6. In step 2 of the wizard, select the subject areas or elements and set the corresponding desired permission to the duty role selected from the list, and then click Next.
7. Review your changes and click Finish.

View Activity History of Semantic Model Extensions

View an audit of all activities performed on the semantic model.

Amongst the activities, you may see that the system steps have been applied. Oracle NetSuite Analytics Warehouse applies the system steps in the following scenarios:

• Any changes to augmentations and new augmentations. A new augmentation replays all the steps in the Main branch because of implicit dependencies.
• New module activations. Module activations can run augmentations related to that module, but the Main branch steps are always replayed.
• Any changes done to flexfields (like DFFs) in your Oracle Fusion Cloud Applications instance such as deletion or addition. It doesn’t matter where the DFF's are. If they are deleted in the source, then they get deleted in the prebuilt subject area. Hence, the custom subject area also must be modified to remove it. In this case, augmentation won't fail but the Main branch fails as it's custom and needs to be modified.
• Application update such as the Oracle NetSuite Analytics Warehouse version upgrade also runs the Apply System Steps.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click Activity History to view an audit of all activities performed on the semantic model.
4. Hover over an activity to view details of the activity.

Publish the Model

You can publish the versions on the main development branch and the other branches in the non-production environments such as development or test to ensure that there are no errors.

While publishing the data model, you can select the user extensions and security configurations that you added as part of customizing the semantic model. If you select the security configurations, then Oracle NetSuite Analytics Warehouse applies them on the user extensions that you selected. If the security configurations refer to elements in the model that aren't part of the user extensions, then Oracle NetSuite Analytics Warehouse excludes them at the time of publishing the model.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic model Extensions page, click Publish Model.
4. In Publish Model, select the user extensions and security configurations that you want to publish.
5. Click Publish.

Load Customization Data to the Autonomous Data Warehouse

You can load your customization data to the autonomous data warehouse provisioned with your Oracle NetSuite Analytics Warehouse instance.

You need the autonomous data warehouse wallet and credentials of the administrator.

1. Connect to the autonomous data warehouse corresponding to your Oracle NetSuite Analytics Warehouse instance.
2. Create a custom schema to store the customization data.

   Syntax: create user <custom_schema-name> identified by <custom_schema-password>;

   Example: create user example_schema identified by abcDEF123654;

3. Create one or more tables in the custom schema that you created.

   Syntax: CREATE TABLE <custom_schema-name>.<custom_extent_table_name> (<parameters>));

   Example: CREATE TABLE example_schema.ABC_EXTN ("DATE" DATE, "CATEGORY" VARCHAR2(1024 BYTE) , "MANAGER" VARCHAR2(1024 BYTE) );

4. Populate the required data and grant select permissions to the OAX$OAC schema in the autonomous data warehouse using this script:

   Syntax: grant select on <custom_schema-name> to OAX$OAC;

   Example: grant select on example_schema.ABC_EXTN to OAX$OAC;

5. Commit the changes to the autonomous data warehouse and disconnect.

Move Your Customizations to Another Environment

When you've a set of changes to data augmentations, custom attribute mapper, semantic model, security configurations, and customized KPIs that are ready for promotion, use the Bundles functionality to move the desired objects to other environments.

See Bundle Your Application Artifacts.

Republish Your Customizations

As the owner of a branch or a user with the Modeler Administrator role, you can republish your user extensions, security configurations, and tagged steps of the Main branch if you’ve modified them after publishing them initially.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse 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, you see a warning icon for a published branch that has been modified and the Republish button on the Semantic Model Extensions page.
5. Click Republish to publish the branch with the latest details.

Use the Enhanced Semantic Model Extension Capabilities

The enhanced semantic model extensibility provides better usability and understanding of the model and customizations along with better performance and consistency of the semantic model. The enhanced capabilities result in a much faster time to have all changes available for reporting.

The enhanced semantic model extensions capability adopts an object focused experience instead of a task focused experience in the existing capability. This enables you to see changes done to a specific objects rather than having to derive the changes from several scattered steps. The enhanced capability has a logically organized flow for better usability with recommended practices enforced and better performance.

The enhanced capability allows you to:

• Make changes within sandboxes, which are each user’s work area.
• Publish a sandbox to test and merge it to the Main once tested.
• Keep all changes for an object available in one place.
• Publish only one sandbox at a time. Publishing a sandbox removes any sandbox already published. If you've merged a sandbox, then the system preserves the changes if you publish any other sandbox.
• Zoom and focus on specific areas of the logical star using the Graphic tab on the Logical Star page.
• Rearrange the objects in the logical star using the Graphic tab on the Logical Star page.
• View all the joins in a tabular format using the Tabular tab on the Logical Star page.

A typical workflow to create extensions involves these:

1. Create a sandbox.
2. Select Perform Action and then select Create or Manage a Star.
3. Make changes as required (the changes are done to the logical model).
4. Select Perform Action and then select Manage Subject Areas.
5. Incorporate logical changes in the desired subject areas.
6. Go back to Semantic Model Extensions page, select User Extensions, select Publish Model, and then select the sandbox to publish.
7. In Oracle Analytics Cloud associated with your Oracle Fusion Data Intelligence instance, verify if the changes are reflected in the subject area.

Topics:

• Create Sandbox
• Manage Subject Areas
• Manage Logical Star
• Manage Variables
• Merge Customization Sandbox to Main Sandbox
• Apply Changes
• Publish Model

Create Sandbox

To begin customizing your semantic model, create a sandbox.

You add customizations to the production environment. After you have added and tested your customizations, you can publish them to the model in the production environment.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
4. In the User Extensions region, for Customization Sandboxes, click Create Sandbox to create your customizations.
   
   

   

   
   
5. In Create a Sandbox dialog, enter a Name having up to 80 characters or less, provide a Description, and click Done.
   
   

   

   
   
6. On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
   
   

   

   
   
7. On the selected sandbox Details page, click Perform Action, and select as applicable.
   
   

   

   
   

   See:

   • Manage Subject Areas
   • Manage Logical Star
   • Manage Variables

Manage Subject Areas

The Manage Subject Areas action enables you to organize all entities and attributes available for reporting in subject areas.

You can create business-friendly names and organize them in a desired order within folders to make it easier to find and include in the reports. The typical organization is to have each dimension organized in a folder with all its attributes within it, followed by folder for facts and calculations. You can rearrange columns based on your organizational preferences.

You can create a subject area or modify a subject area.

Create Subject Area

You can create a subject area as a container and later add facts and dimensions to your new subject area or create a subject area based on an existing one. The subject area enables you to organize all entities and attributes available for reporting.

1. On 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, and then select Manage Subject Areas.
   
   

   

   
   
3. In Perform Action, select Create a Subject Area, and then click Next.
   
   

   

   
   
4. In step 1 of the wizard, create a subject area using one of the methods:
   • Select Create a Subject Area to create a subject area container, and provide these details:
     1. Enter a name without any leading or trailing white spaces, add a description, and then click Next.
     2. In step 2 of the wizard, click Manage Elements, and then click either Manage New Customizations to select custom elements that you created or Manage Factory Customizations to select factory data elements to rearrange the subject area elements that are delivered by Oracle.
     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, and then click Next.
     5. In step 4 of the wizard, review your new subject area and click Finish to create it.
   • Select Create a Subject Area based on an existing one to create a subject area using an existing one in the system, select an existing subject area, name your subject area, and then click Next. Complete steps 2, 3, and 4 of the wizard.

Modify Subject Area

Modify a custom subject area to change the previously selected data elements or add more data elements.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
4. On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
5. On the selected sandbox Details page, click Perform Action, and then select Manage Subject Areas.
6. In Perform Action, select Modify a Subject Area, select the subject area that you want to modify, and then click Next.
   
   

   

   
   
7. Follow through the wizard to modify the subject area and in step 4 of the wizard, review your modified subject area and click Finish.

Manage Logical Star

A logical star is the basic complete unit of a dimensional model with a fact at the center and joined to the surrounding dimensions. Manage a logical star by adding and updating objects, attributes, joins, and calculations.

Facts contain elements that you can measure such as count, aggregate, and perform statistical operations on; while dimensions contain elements that provide context to those measurements. Each logical star has one fact and one or more dimensions. You can manage your own custom star or you can manage a prebuilt star by adding dimensions. You do these operations to extend the model to make use of custom data objects or elements that you've added to the warehouse or to create new calculations or joins to address your reporting needs.

Create Logical Star

Create a custom logical star to use custom data objects or elements that you have added to the warehouse or to create new calculations or joins to address your reporting needs.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
4. On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
5. On the selected sandbox Details page, click Perform Action, and then select Manage Logical Star.
6. In Perform Action, select Create Logical Star, and then click Next.
   
   

   

   
   

   You’re now ready to add facts, dimensions, hierarchy, and additional columns.

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.

Manage Dimensions

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.

You can add a new or an existing dimension.

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, select Edit Logical Star, select the applicable fact and then select Next.
3. On the Logical Star – Fact page, click Manage Dimension, and select either Add Dimension or Add Existing Dimension.

Add Dimension

Add dimensions to facts to complete a new star or to update existing stars. You can create joins from your custom dimension to a prebuilt fact.

1. In step 1 of the Add a Dimension wizard, select the schema, and then select the dimension table in Object. For example, COST_CENTER_VIEW1, and add a name in Dimension Name.

   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.

2. 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.
3. 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.
4. 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.
5. In step 2 of the wizard, assemble the product hierarchy using the attributes from this dimension and click Finish. See Add Hierarchy.
6. On the Logical Star: Fact page, in the Graphic tab, click on the prebuilt fact and drag drop on the custom dimension that you created to open the Join dialog.
7. In the Join dialog, select the join type, and then select the dimension keys to join them with the extended dimension keys. If you want to provide expressions as join conditions, then click Complex Join and in Create Joins, select applicable Content Level, click Add Joins, select the target and source logical tables, enter the join condition as an expression, and then click OK.

Add Existing Dimension

If you want to provide additional context to facts, you can create your own dimension and join to an existing available column in a fact.

For example, if you want to report on invoice categories, create a dimension called "Invoice Category" and join to a column in the fact that has that information. It is important to remember that one dimension record must join to one or more fact records; it should be a 1-many join. You shouldn't have many to one or many to many joins between a dimension and fact table.

1. On the Logical Star – Fact page, click Manage Dimension, and select Add Existing Dimension.
2. In Add Table, select the dimensions to add.

Manage Extensions

After adding the extension, you can extend the dimensions, add hierarchy, and add columns to ensure that your custom logical star meets your business requirements..

On the Logical Star – Fact page, right click on an extension, click Manage Extension, and select any of these:

• Extend Dim
• Add Hierarchy
• Add Column

Extend Dimension

Extend prebuilt dimensions with additional attributes from another data source. For example, you can create a category column that isn't available in the prebuilt dimensions.

1. In step 1 of the Extend a Dimension wizard, select a schema and table from the database.
2. Select the columns that you want to expose or use as a key for creating the join.
3. 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.
4. 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.
5. 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.
6. Click Next.
7. In step 2 of the wizard, select the join type, and then select the dimension keys to join them with the extended dimension keys. 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.
8. Click Finish.

Add Hierarchy

Assemble the product hierarchy using the attributes from a dimension table. Hierarchies enable you to define aggregations and drill downs. This makes it easier to report on summary level and drill into details easily and within the same visualization.

1. In step 1 of the Add a Hierarchy wizard, name your hierarchy in Hierarchy Name.
2. Select, drag, and drop available data elements into the Selected Data elements pane to design a hierarchy for the dimension.
3. 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.
4. Ensure Add hierarchy to Subject Area is selected and click Finish.

Add Columns

You can create columns to provide additional data elements or calculations.You can add derived and physical columns.

1. On the Add Column page, select Add Derived Column, and complete these steps:
   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.
2. On the Add Column page, select Add Physical Column, and complete these steps:
   1. In Select Physical Column, select the columns and click OK.
   2. On the Add Columns page, for the physical columns, select the Display check box to expose the columns, and click the Logical Level icon to set the required level.
   3. In Set Logical Level, select the dimension, select the level of the dimension hierarchy, and then click OK.

Edit Logical Star

Edit your logical star to modify any of the extensions that you had previously added or to add further extensions.

1. Navigate 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, and then select Manage Logical Star.
3. In Perform Action, select Edit Logical Star'
4. To select the prebuilt objects, select Out of the box, select a subject area and an applicable fact within the selected subject area, and then click Next. Select Custom to add custom objects to the logical star, select the applicable fact, and then click Next.
   
   

   

   
   
5. On the Logical Star – Fact page, click Manage Dimension and proceed with the steps discussed in Manage Dimensions.

Manage Variables

Use the Manage Variables action to control the behaviour of sessions and queries. You can create and modify the custom variables.

Create Variable

Create custom session variables that you can use in your semantic model.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
4. On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
5. On the selected sandbox Details page, click Perform Action, select Manage Variables, and then select Create Variable.
   
   

   

   
   

   You see the wizard sequence to add the session variables and a list of existing session variables.

6. 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.
7. In Initialization Block Name, enter a name such as Add a Session variable using Invoice Received Date, add a brief description, and select a preceding initialization block in Preceding Block.
8. In SQL Query, enter the SQL query that would be executed in the autonomous data warehouse and return a value that you can use in the reports and click Next. For example, if you want to get the Exchange Rate Type that's defined in the system into a session variable, then you can use the following SQL script:

   Copy
            SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE
            PARAMETER_CODE='PARAM_GLOBAL_EXCHANGE_RATE_TYPE'

9. In step 2 of the wizard, create the session variables using the output of the initialization block created in step 1 of the wizard. Select Row-wise Initialization to reset variable value for each row and Use caching check boxes to improve performance.
10. Click Finish.

Modify Variable

Modify a custom variable to update the SQL query that would be executed in the autonomous data warehouse and return a value that you can use in the reports.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
4. On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
5. On the selected sandbox Details page, click Perform Action, and then select Manage Variables.
6. In Perform Action, select Modify Variable, select the variables that you want to modify, and then click Next.
   
   

   

   
   
7. Follow through the wizard to modify the variable and click Finish.

Merge Customization Sandbox to Main Sandbox

After creating the semantic model extensions, you must merge the customization sandbox that contains your semantic model extensions into the main sandbox to make the extensions available for processing.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
4. On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click Merge to Main Sandbox.
5. In Confirm Merge with Main, review the message and click Merge.
   
   

   

   
   

Apply Changes

After merging your semantic model extensions with the main sandbox, you must apply the changes to your semantic model to use the extensions in your visualizations.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click User Extensions.
4. On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click Apply Changes.

Publish Model

You can publish the sandbox in the non-production environments such as development or test to ensure that there are no errors.

While publishing the data model, you can select the user extensions and security configurations that you added as part of customizing the semantic model. If you select the security configurations, then the system applies them on the user extensions that you selected. If the security configurations refer to elements in the model that aren't part of the user extensions, then the system excludes them at the time of publishing the model.

1. Sign in to your service.
2. In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
3. On the Semantic Model Extensions page, click Publish Model.
4. In Publish Model, select the user extensions and security configurations that you want to publish.
   
   

   

   
   
5. Click Publish.