Customize Oracle NetSuite Analytics Warehouse

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

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.

Oracle NetSuite Analytics Warehouse currently provides these methods for extending the semantic model:

If your instance has the semantic model extensions Sandbox framework, then you can use the information about extending the semantic model using the Sandbox framework. If your instance has the semantic model extensions Branch framework, then you can migrate to the semantic model extensions Sandbox framework because the Branch framework is planned to be deprecated in a future release. See Migrate to the Sandbox Framework for Semantic Model Extensions.

Here's a comparison of the capabilities of the Branch and Sandbox framework for semantic model extensions:

Branch Framework	Sandbox Framework
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.

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 extensions are available in this component.
Security Configurations: You can secure the objects of all the other components against the application roles in this component.

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 the extensions 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.

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.

Database Naming Standards for Autonomous Data Warehouse 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
- _V = Views
- _MV = Materialized View
- _DS = Data Augmentation Dataset
- _EXT = Data Augmentation Extension
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.

Deployment

Migrations must flow in a single direction only. Choose one environment to be the master Development environment. After user acceptance testing, generate and deploy a Semantic Extensions bundle to migrate changes to Production and other environments.
Don’t export the Semantic Extensions bundle separately and then do security promotion through test to production. Generate the Semantic Extensions bundle and include the extensions you want and then include security as well.

Data Augmentation Datasets and Flexfields

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.
You can reference synonyms from the data augmentation datasets in the semantic model extensions after the initial full load for the data augmentation has completed. Use the "Run Immediately" option in the data augmentation to execute the full load straightaway.

Extending

When joining facts to dimensions, ensure that the columns being joined are of compatible data types.
When extending DEGEN Dimensions ("Details" folders), always maintain the same level of granularity by joining on the Primary key(s) of the fact with a one to one [1:1] relationship. Don't define many to many [M:M] joins because it may cause performance degradation and data duplication.
When creating a custom dimension, you may unselect "Add hierarchy to Subject Area". However, it is still necessary to define a Hierarchy Primary Key and Display Attribute. Click on Selected Data Elements Detail folder, then the Properties edit icon to define the Hierarchy Primary Key and Display Attribute.
When extending a dimension (if the extension granularity is one to one [1:1] with the prebuilt dimension) combine multiple extensions for the same dimension in a single source (table/view/synonym) in Autonomous Data Warehouse. It is preferable to have one extension with many columns, rather than have multiple extensions per column.
If it's necessary to have multiple extensions on the same dimension due to varying one to many (1:M) relationships (such as multi-select), then be cautious of an index length limitation that may be hit. To avoid the constraint, name the table/view/synonym as short as feasible. For example, FDI_X_SZ_V (Size) and FDI_X_PR_V (Price).
When adding a custom fact, always set the content levels for the custom dimensions that are joining to the custom fact.
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 prebuilt 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.
When naming objects (dimensions, facts, and columns) remove all leading and trailing spaces. You can use underscores and spaces in names but avoid all other special characters.

Impact of Data Flows and Data Replication

If you’re using data flows and data replication capabilities in Oracle Analytics Cloud associated with your Oracle NetSuite Analytics Warehouse instance and creating semantic model extensions in Oracle NetSuite Analytics Warehouse, note that it may impact the performance of Oracle Analytics Cloud. You can schedule data flows outside of business hours because running complex data flows during business hours may have a negative impact on the overall system performance.

Extend the Semantic Model Using the Sandbox Framework

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

The Sandbox framework for the semantic model extensions 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 Sandbox capability has a logically organized flow for better usability with recommended practices enforced and better performance.

The Sandbox 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:

Create a sandbox.
Select Perform Action and then select Create or Manage a Star .
Make changes as required (the changes are done to the logical model).
Select Perform Action and then select Manage Subject Areas.
Incorporate logical changes in the desired subject areas.
Go back to Semantic Model Extensions page, select User Extensions, select Publish Model, and then select the sandbox to publish.
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

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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
In the User Extensions region, for Customization Sandboxes, click Create Sandbox to create your customizations.
In Create a Sandbox dialog, enter a Name having up to 80 characters or less, provide a Description, and click Done.
On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
On the selected sandbox Details page, click Perform Action, and select as applicable.
See:

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.

On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
On the selected sandbox Details page, click Perform Action, and then select Manage Subject Areas.
In Perform Action, select Create a Subject Area, and then click Next.
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

You can modify custom and prebuilt subject areas. Modify a custom subject area to change the previously selected data elements or add more data elements and modify a prebuilt subject area to add more data elements.

Ensure to create custom elements if you want to add them to the custom subject area and add additional columns to the prebuilt subject area prior to modifying either of them.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
On the selected sandbox Details page, click Perform Action, and then select Manage Subject Areas.
In Perform Action, select Modify a Subject Area, select a custon or prebuilt subject area that you want to modify, and then click Next.
In step 1 of the wizard, view the selected subject area details and click Next.
In step 2 of the wizard,
- If you're modifying a custom subject area, click Manage Elements and then click Manage New Customizations to select custom elements that you created.
- If you're modifying a prebuilt subject area, click Manage Elements and then click Manage Factory Customizations to select the prebuilt elements that you extended.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
On the selected sandbox Details page, click Perform Action, and then select Manage Logical Star.
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.

Navigate to the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
On the selected sandbox Details page, click Perform Action, select Manage Logical Star, and then select Create Logical Star.
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.
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.
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.
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.
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.
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.

Navigate to the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
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.
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.

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.
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.
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.
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.
In step 2 of the wizard, assemble the product hierarchy using the attributes from this dimension and click Finish. See Add Hierarchy.
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.
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.

On the Logical Star – Fact page, click Manage Dimension, and select Add Existing Dimension.
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.

In step 1 of the Extend a Dimension wizard, select a schema and table from the database.
Select the columns that you want to expose or use as a key for creating the join.
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.
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.
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.
Click Next.
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.
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.

In step 1 of the Add a Hierarchy wizard, name your hierarchy in Hierarchy Name.
Select, drag, and drop available data elements into the Selected Data elements pane to design a hierarchy for the dimension.
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.
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.

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.
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.

Navigate the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
On the selected sandbox Details page, click Perform Action, and then select Manage Logical Star.
In Perform Action, select Edit Logical Star'
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.
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.

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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
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.
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.
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.
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'
```
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.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
On the selected sandbox Details page, click Perform Action, and then select Manage Variables.
In Perform Action, select Modify Variable, select the variables that you want to modify, and then click Next.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click Merge to Main Sandbox.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
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.

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

Extend the Semantic Model Using the Branch Framework

You customize your semantic model in the non-production environments such as development or test by creating branches and adding steps to those branches.

Note:

Oracle recommends that you migrate to the semantic model extensions Sandbox framework because the Branch framework is planned to be deprecated in a future release.

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.

Don’t maintain 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 development, then merge to the main branch after user acceptance testing. Promote the main branch from Development to Production environments using the Semantic Extensions bundle. 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.

Topics:

Migrate to the Sandbox Framework for Semantic Model Extensions

Prior to switching over to the Sandbox framework for semantic model extensions, note the tasks and ensure to take appropriate action where required in the suggested sequence.

Begin by migrating the non-production instances to the Sandbox framework before migrating the production instance.

Topics:

Perform the Pre-Migration Tasks

Prior to the migration, note these and take appropriate action:

Ensure that you schedule or apply all the release updates prior to the migration to avoid not being able to rollback.
The option to revert to the Branch framework gets disabled after 60 days from the migration date or an Application update, if it happens first post migration to the Sandbox framework.
As a pre-clean up task, create a Semantic Model Extensions bundle of the published branch prior to making any semantic model extension changes.
Delete or enable disabled steps in the Main branch to be migrated because the migration process migrates only the enabled steps.
Delete or merge all unpublished branches because these won’t be migrated.
Merge the published branch with the main branch.
Publish the Main Branch, confirm that all steps are error free, and confirm that all the subject areas and security referenced in workbooks operate as expected.
As a post-clean up task, create a Semantic Model Extensions bundle of the published Main Branch.

Validate the Main Branch

On the Semantic Model Extensions page, when you see the banner to schedule the migration, don't click Schedule now until you've validated the main branch successfully with no errors.

Note these and take applicable action prior to initiating the validation process:

Don't perform any development or administrative activities that effect the semantic model extensions during validation or while the migration is in process. This includes modifying existing data augmentation or creating new data augmentation that have the semantic model extensions.
Remove leading and trailing spaces from the object names.
Remove special characters such as parenthesis from the object names.
Ensure all keys are present on the join columns.
Confirm all referenced source database objects and columns exist in Autonomous Data Warehouse.
Confirm grants are applied on all referenced source database objects using
```
GRANT SELECT ON "OAX_USER".“TABLE_VIEW" TO "OAX$OAC";
```
Resolve any duplicate object names.

On the Semantic Model Extensions page, click the Actions Menu for the Main Branch, and then click Validate to produce the Validation report.
Click the Activity tab on the Semantic Model Extensions page and wait for the validation of the main branch to complete. If the State is Failed, then don't proceed with the migration until you've resolved all of the errors.
Click the Actions Menu for the Main Branch, and then click Validation Report to download and review the validation report.

Note:
The validation report is enabled only if the validation fails with errors.
Resolve all errors in the validation report and repeat Validate and Validation Report steps until you've resolved all errors. For any errors unresolvable, contact Oracle Support before proceeding.
Click the Activity tab on the Semantic Model Extensions page and wait for the validation of the main branch to complete. If the State is Done and the report is error free, then proceed with scheduling the migration.

Note:
Don't proceed with the migration until all errors are resolved.

Schedule the Migration

You can initiate migration of your extensions to the Sandbox framework by submitting the request from the banner message on the Semantic Model Extensions page when you click User Extensions.

Set up the migration to happen automatically by scheduling it from the banner message. Ensure that you schedule the migration during a low usage period to avoid reporting outages.

Note:

You can't use the semantic model extensions capability during the migration process.

On the Semantic Model Extensions page, in the banner announcing the ability to schedule the migration, click Schedule now.
In the Migrate dialog, specify the date and time for the migration to begin.
Track the progress of the migration in the Activity tab on the Semantic Model Extensions page when the migration starts.

Perform the Post Migration Verifications

After the migration is complete, note these and take appropriate action:

The migration process converts the single Main Branch to a single Semantic Model Extensions Sandbox. It migrates the existing customizations to the new framework. The security configurations remain unchanged in the new framework, hence no action from you is required.

On the Semantic Model Extensions page, click the Activity tab and monitor the status of the migration.
Once the migration is complete, you receive a notification on the Console. If the migration fails, click the Failed state in the Activity tab for the applicable task and resolve the issues. For any unresolvable issues, reach out to Oracle Support.
After the migration is completed successfully, on the Semantic Model Extensions page, click Migration Details.
In Migrated Sandboxes, click the download icon to download the migration report.
This report shows which customizations are available for which object with the Main branch object to the Main sandbox object mapping. This enables you to see all your customizations that have been brought over. In most cases, operations like Add Dim, Add Fact, Add Variable, Extend Dimension, and Add Columns have 1:1 correspondence between the old and the new framework. The Main sandbox is published and ready for use. All your subject areas, visualizations, and reports remain unchanged.
Sign in and sign out of your service after the migration completes. Confirm that all the subject areas and security referenced in the workbooks operate as expected.

Revert to the Branch Framework for Semantic Model Extensions

Post migration, an option to revert to the Branch framework if customizations aren't as expected is available. The migration rollback option gets disabled after 60 days from the migration date or an Application update, if it happens first post migration to the Sandbox framework.

Note these:

Ensure to complete the reversal process within this period if you must revert. If you choose to revert, then the semantic model rolls back to the state prior to the migration being initiated. You can't rollback the migration if you've completed an Application update 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 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.

On the Semantic Model Extensions page, click the Activity tab and verify that the status of the scheduled migration task Upgraded to Revamped UI is Done.
On the Semantic Model Extensions page, click Migration Details.
In Migrated Sandboxes, click Revert to rollback the migration.

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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, for Customization Branches, click Create Branch to create a branch as an empty container.
In Create a Branch, enter a name for your branch, for example, Add Cost Center.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
In the User Extensions region, under Customization Branches, hover over a branch to viewActions.
You see the actions that are applicable to the branch.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
On the Branch page, click Add Step.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
On the Branch page, click Add Step.
In Add Step, select Add a Dimension.
You see the wizard sequence to add a dimension.
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.
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.
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.
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.
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.
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.
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.
Optional: Click Add Fact Table to select another fact table to link your dimension to and define the join.
Click Next.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
On the Branch page, click Add Step.
In Add Step, select Add a Fact.
You see the wizard sequence to add a fact.
In step 1 of the wizard, enter a name for your customization step, for example, Add Travel Expense and add a brief description.
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.
Click Next.
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.
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.
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.
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.
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.
Click Next.
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.
Optional: Click Skip Joins if you don’t want to join a dimension now.
Click Next.
In step 4 of the wizard, select the aggregation rule for each fact column to set the aggregation behaviors.
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.
Click Next.
Optional: Select additional subject areas to add the fact.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
On the Branch page, click Add Step.
In Add Step, select Add a Hierarchy.
You see the wizard sequence to add a hierarchy.
In step 1 of the wizard, enter a name for your customization step, for example, Add Region Hierarchy and add a brief description.
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.
Click Next.
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.
Click Next.
Optional: In step 3 of the wizard, select additional subject areas to include the new hierarchy.
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. After merging this step to the Main branch and publishing it, the custom session variables are available in the custom security configuration user interface only.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
On the Branch page, click Add Step.
In Add Step, select Add Session Variables.
You see the wizard sequence to add the session variables and a list of existing session variables.
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.
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.
Click Next.
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' 
```
Click Next.
In step 3 of the wizard, create the session variables using the output of the initialization block created in step 2 of the wizard.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
On the Branch page, click Add Step.
In Add Step, select Extend a Dimension.
You see the wizard sequence to extend a dimension.
Enter a name for your customization step, for example, Add Cost Center Type.
Select a target subject area, for example, Financials GL Profitability.
In Folder, select a dimension that you want to extend, for example, Cost Center.
Select a logical table, for example, Dim – Cost Center.
You see the available attributes in the table.
Click Next.
Select a schema and table from the database.
You see the available attributes in the table.
Select the columns that you want to expose or use as a key for creating the join.
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.
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.
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.
Click Save.
Click in the Source Column table field to edit the column definition.
Click Save.
You see the new column in the Data preview section in a highlighted color.
Click Next.
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.
Click Next.
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.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
On the Branch page, click Add Step.
In Add Step, select Add a Column.
You see the wizard sequence to add a column.
In step 1 of the wizard, enter a name for your customization step, for example, Regional Revenue and add a brief description.
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.
Select the presentation folder within the selected subject area and the logical table to which you want to add the column.
Click Next.
You see the Create Column dialog in step 2 of the wizard.
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.
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.
Click Next.
Optional: Select additional subject areas to add the fact.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
On the Branch page, click Add Step.
In Add Step, select Create a Subject Area.
You see the wizard sequence to create a subject area.
In step 1 of the wizard, enter a name for your customization step, for example, Custom Profitability and add a brief description.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
On the Branch page, click Add Step.
In Add Step, select Modify a Subject Area.
You see the wizard sequence to modify a subject area.
In step 1 of the wizard, enter a name for your customization step, for example, Custom AP Invoices and add a brief description.
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.
Click Next.
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.
Optional: Click Add Subject Area to select and add data elements from multiple subject areas.
Click Next.
In step 3 of the wizard, organize and rename the data elements in your modified subject area.
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.
Click Next.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
In the User extensions region, under Customization Branches, click a branch to display the steps.
Hover over a step to view Actions.
Click Edit and update the details.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, under Customization Branches, click a branch to display the steps.
Hover over a step to view Actions.
Click Reapply.

Disable and Enable the Disabled Steps

You can disable and enable the disabled steps in the main and customization branches in the development and production environments. This helps you in troubleshooting issues in the branches.

You can perform these actions on the steps in the User Extensions and Security Configurations regions. You can disable and enable individual steps or select multiple steps using the Manage Steps, Enable, and Disable buttons on the branch details page. You can’t disable a published step.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, click Main to view the details.
On the Main branch page, hover over a step to display the options.
Click Disable.
In Confirm Disable Steps, click Disable.
On the Main branch page, click Manage Steps, select the check box for the disabled steps, and then click Enable.
In Confirm Enable Steps, click Enable.
Perform these actions for steps in the customization branches.

Copy Steps from One Branch to Another

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

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User extensions region, under Customization Branches, click a branch whose steps you want to copy.
On the Branch page, click Manage Steps.
Select the check box for the steps that you want to copy and click Copy.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User extensions region, under Customization Branches, click the branch with the Failed icon to view the details.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, under Customization Branches, hover over a branch to view Actions.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
In the User Extensions region, under Customization Branches, click a branch to display the steps.
On the Branch page, click Manage Steps.
Select the check box for the steps that you want to reorder and click Reorder.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
In the User Extensions region, click Main to view the details.
On the Main Branch page, hover over a step to display the options.
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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
You see the main and existing customization branches.
To create a tag for a step in the Main branch, in the User Extensions region, click Create Tag.
In Create a Tag, enter a name for the tag and a description.
Select a merged step that you want to include in this tag.
Click Done.
To tag a step directly, hover over the Main branch to view Actions and then click View Details.
On the Main branch details page, hover over a step to view Actions and then click Tag.
In Create a Tag, enter a name for the tag and a description.
Click Done.
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.
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.

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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic model Extensions page, click Publish Model.
In Publish Model, select the user extensions and security configurations that you want to publish.
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.

Connect to the autonomous data warehouse corresponding to your Oracle NetSuite Analytics Warehouse instance.
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;
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) );
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;
Commit the changes to the autonomous data warehouse and disconnect.

Add Security Configurations

Add security configurations to secure the subject areas and data with prebuilt and custom duty and data type of application roles.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click Security Configurations.
In the Security Configurations region, click Add Configure Data Security to secure your data with the data type of application roles.
See Configure Data Security.
In the Security Configurations region, search for the prebuilt "Configure Object Permissions" to configure permissions for objects such as subjects areas and their elements with duty type of application roles.
See Configure Object Permissions.
Optional: In the Security Configurations region, click Reapply Steps to validate the security configuration-related steps against the current state of the model.

Configure Data Security

As a security administrator, provide users with access to data using the custom-created data type application roles.

You can add filters to data retrieved from logical or presentation objects based on the data roles assigned to users. If you specify functional groups, Oracle Fusion Data Intelligence combines all the filters in the same functional group using the OR operator and combines all sets of filters in different functional groups using the AND operator.

You can add one customization step for each data role. The elements that you can secure are from the Main branch of the semantic model. Hence, if you need a newly added object to be secured, then you must ensure that the customization branch containing the newly added object is merged with the Main branch before configuring the security. If any of the custom-created role is no longer available, then the security configuration for that role is removed from the "Configure Data Security" step.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click Security Configurations.
You see the existing security configurations, if any.
In the Security Configurations region, click Add Configure Data Security Step.
In step 1 of the wizard, enter a name for your step, select a data type application role, and then click Next.
In step 2 of the wizard, from the Available Objects drop-down list, click either Presentation Objects or Logical Objects to select the objects that you want to secure with the selected data type application role.
If you're viewing the presentation objects, then expand the subject area folders and double-click the objects. If you're viewing the logical objects, then double-click the logical table folders or expand the table folders and double-click the objects. You see the selected objects under Object to be secured in the right pane.
Optional: In Functional Group, select a prebuilt functional group or enter a unique functional group name in the text box to create a custom functional group.
For the Filter Argument, click the Function icon to define how the data filter gets applied.
Use the Expression Editor to enter the filter, based on the session variables that you had created previously. To view an example, see Custom Security in Fusion Data Intelligence.
Click Next.
Click Finish.
Optional: In the Security Configurations region, click Reapply Steps to validate the security configuration-related steps against the current state of the model.

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.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click Security Configurations.
You see your existing security configurations and the prebuilt object permissions-related step.
In the Security Configurations region, search for the prebuilt "Configure Object Permissions", and hover over it to view Actions, and then click Edit.
In step 1 of the wizard, click Next.
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.
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.

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

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 the user extensions or a user with the Modeler Administrator role, you can republish them if you’ve modified them after publishing them initially.

Sign in to your service.
In Oracle NetSuite Analytics Warehouse Console, click Semantic Model Extensions under Application Administration.
On the Semantic Model Extensions page, click User Extensions.
In the User Extensions region, you see a warning icon and the Republish button on the Semantic Model Extensions page.
Click Republish to publish your user extensions with the latest details.

Map the Custom Attributes

You can attach custom objects to transactions and transaction lines.

When you attach custom objects to transactions or transaction line types, you automatically create extension attributes that appear in Oracle Analytics Cloud reports and dashboards. The process is automated, so you don't need to create dimensions and extend entities.

Map Custom Attributes

Import custom attributes for transactions, transaction line, transaction accounting lines, and dimension entities using the Custom Attributes Mapper.

Determine the source attributes in NetSuite you want to import before starting this procedure to simplify the mapping process. For the source transaction types or entities that you want to import, NetSuite Analytics Warehouse displays the business names, IDs, data types, and size of the associated custom attributes.

Note:

Custom attributes that reference other transaction internal IDs aren't supported in the Custom Attributes Mapper. Use the Data Augmentation capability to bring these into NetSuite Analytics Warehouse.

The attributes that you can select have a default limit of 4000 characters for all data types. If the source transaction has attributes with more characters than the default limit, the system displays the unsupported attributes. If you want to use these unsupported attributes, you must either change the size in the data source or request the administrator to increase the character limit.

In NetSuite Analytics Warehouse Console, click Data Configuration.
On the Data Configuration page, under Applications, click Custom Attributes Mapper.
On the Custom Attribute Map List page, click Create.
Select the type of attribute you want to create, and the source transaction type or entity you want to import.
Select the attributes you want to associate with the source transaction type.
Click Save.
Click Publish to make the attribute map available to users.