With Inputs from
Krithika Raghavan
Senior Director
Introduction
This article explains how to merge external applications for customizing Fusion Data Intelligence (FDI) using the Semantic Model Sandbox framework.
Advantages for Using External Applications
- Reuse and avoid reconfiguring existing semantic models such as those from non-FDI Oracle Analytics Cloud (OAC), Oracle Analytics Server (OAS), and Oracle Business Intelligence Enterprise Edition (BIEE)
- Similar development process for previous users of Oracle BI Administration Tool
- Reduced development time
Supported External Applications Use Cases
| Use Case |
Action/Result |
Supported |
Details |
| [1] Extend a prebuilt conformed dimension |
Add attribute columns to a dimension folder such as Customer, Item Category, and Time. |
Yes –Limited |
Supported for conformed dimensions available in the template |
| [2] Extend a prebuilt degenerate dimension |
There’s no support to add attribute columns to a transaction details folder such as Dim – OM Sales Order Details. Folder names mostly end in “Details”. |
No |
No support for |
| [3] Add a custom dimension to a prebuilt fact |
There’s no support to create a new folder containing dimensional attribute columns. |
No |
Not supported (no workaround available) |
| [4] Add a custom fact and join to prebuilt dimensions |
Create a new folder containing the fact measure columns. |
Yes |
Fully supported for conformed dimensions available in the template |
| [5] Add a custom fact containing both attributes and measures (not supported for prebuilt facts or degenerate dimensions) |
Create two folders; one containing fact measure columns and another containing dimension attribute columns. |
Yes |
Model fact attributes in a separate degenerate dimension |
| [6] Add derived columns |
Add a calculated column to a folder in a conformed dimension. |
Yes – Limited |
Supported for conformed dimensions only (available in the template) |
| [7] Create custom dimension with hierarchy |
Add workbook, with expand and collapse for custom dimension attributes. |
Yes – Limited |
Only supported for custom dimensions (not supported for prebuilt dimensions) |
| [8] Create a new custom subject area including custom fact and custom dimensions |
Create an empty custom subject area with all custom elements such as Salesforce, QuickBooks, and Google Analytics subject areas. |
Yes |
Supported for custom facts joined to custom or conformed dimensions |
| [9] Write-back from the OAC Classic dashboard to an ADW table |
Not supported for OAC data visualizations. |
Yes |
OAC Write-back |
| [10] Create a custom variable |
Define a variable in the semantic model and reference it in OAC. |
Yes |
Create session and request variables for OAC calculations and parameters. |
| [11] Set security on factory objects |
Not supported |
No |
Not Supported (no workaround available) |
| [12] Set security on custom objects |
Supported |
Yes |
If the external semantic model references application roles and groups, then you must create them manually. The merge capability imports only the mappings and doesn’t import the roles and groups definitions. Create the applicable groups in your identity provider and create application roles using the Security page in FDI. |
High Level Steps to Merge an External Application in FDI
1. Create a custom database schema in the Oracle ADW associated with your Oracle FDI instance. Follow the instructions here.
2. Migrate or create database objects such as tables, views, and synonyms in the new schema. Import or migrate your external database objects such as tables, views, and synonyms into the new database schema using the Oracle database utilities or any ETL tool.
3. Grant semantic model access to the database objects. Follow the instructions here.
4. Configure Oracle Analytics Client Tools (Oracle BI Administration Tool). Follow the instructions here. Detailed steps showing how to Install Oracle Analytics Client Tools can be found here.
5. Export the semantic model template. Follow the instructions here.
6. Import the custom database objects or merge your semantic model metadata into the Semantic Model Template. Follow the instructions here.
If not complete, see the documentation, Export the Semantic Model Template.
-
To import:
-
Temporarily change the data source name and login credentials to import the custom database objects into the physical layer of the Sematic Model Template.
-
Don’t modify any other setting except for the data source name, username and password.
-
Always revert to original template configuration before importing or merging.
-
-
Before: Data source name: DSN and Username and Password: Blank
-
After: Data source name: ADW tns – be sure to use _low.adb.oraclecloud.com and User name: RPD_MERGE_USER and Password
-
Right-click Oracle_Data_Warehouse_Connection_Pool and select Import Metadata.
-
Select Metadata Types to import. Views are used in these examples.
-
Revert Oracle_Data_Warehouse_Connection_Pool back to the original state.
*** It’s very important that the database connections are not modified in any way prior to merging. The Data source name and User name must be reverted to the factory settings as shown below. ***
- Additionally, don’t leave any custom database connections in the semantic model. Only the three prebuilt database connections should remain before importing.
-
The custom schema should remain and point to the factory database connection (which only support FDI ADW).
-
Alternatively, merge from your existing semantic model.
-
If merging, don’t leave any custom database connections in the semantic model. Only the three prebuilt database connections should remain.
7. Model the custom semantic model objects.
-
Copy or configure the semantic model physical, business, and presentation layer mappings as required.
8. Merge the external semantic model with the semantic model of your FDI instance. Follow the instructions here.
- Check Global Consistency of the semantic model to confirm it’s error-free before merging.
*** Errors must = 0. Review warnings. Resolve any warnings that occurred because of custom modifications that could potentially negatively impact the semantic model. Note: The template contains many warnings prior to changes that may be ignored. ***
- Save the semantic model with a unique name to represent the changes made.
*** If an existing external application already exists, it’s preferrable to fully replace the semantic model by deleting the existing imported application prior to merging the new. If the prior external application isn’t removed, a merge will be performed between existing and new and may cause conflicts. ***
- Under External Application, click the ellipse, and select Remove Application.
-
Review the Activity tab to confirm that the External Application Delete is complete.
-
Click Actions and select Import Application.
-
Browse for the semantic model, provide Name, Password, Prefix/Postfix, and select Expose custom columns in the prebuilt subject areas.
*** “Expose custom columns in prebult subject areas” should only be selected when necessary and not by default as it has performance impacts. Only select this option if you modified the prebuilt business model. ***
| Process |
Check/Select “Expose custom columns in prebuilt subject areas” |
| Extend a prebuilt conformed dimension |
YES |
| Add a custom fact – join to prebuilt dimensions |
NO |
| Add a custom fact – containing both attributes (degenerate dimensions) and measures |
NO |
| Add derived columns to a prebuilt conformed dimension |
YES |
| Create a custom dimension with a hierarchy |
NO |
| Create new custom subject area – add custom fact and custom dimension |
NO |
-
Click Validate.
-
For Post Validation Successful, left-click the ellipse and select Merge Application.
-
Monitor the Activity Tab, refresh it to see the current status, and wait for In Progress to switch to Done.
Summary
This article explained how to merge the external applications for customizing FDI using the Semantic Model Sandbox framework.
References
For additional information, see the following articles:
- About Migrating to the Enhanced Semantic Model Extension Framework
- Use the Enhanced Semantic Model Extension Capabilities
- About Merging External Applications
Call to Action
For more information, refer to Merge Your External Applications and docs.oracle.com/pls/topic/lookup?ctx=faw-latest&id=FAWAG-GUID-584A64B8-F4B9-4398-86CF-8E43F1260336″>About Semantic Model Customization to learn more about extending the semantic layer using the Semantic Model Sandbox framework.
Now that you’ve read this article, try it yourself and let us know your results in the Oracle Analytics Community, where you can also ask questions and post ideas.
Contribution
Many thanks to Padma Rao Padala and Suzanne Gill for reviewing and all the useful feedback!