This blog shows you how to extend the ready-to-use dimensions with additional attributes from another data source.
Here are the tasks that you must complete:
Create a Custom Schema on Autonomous Data Warehouse
Create a custom schema on autonomous data warehouse provisioned with your Fusion Analytics Warehouse. Create a table for storing additional job attributes that are populated from another data source (third-party data).
See Load Customization Data to the Autonomous Data Warehouse.
You can load the EXTENDED_JOB_DIM custom table that you created using Oracle Analytics Cloud data flows. Alternatively, you could also load this table using SQL client tools, such as SQLDeveloper.
Oracle Analytics Cloud Data Set – Upload Spreadsheet Data
Import the additional job dimension data in to a data set in Oracle Analytics Cloud.
1. Create a data set
2. Click Drop data file here or click to browse tile and select the excel file that contains the additional data for the job dimension.
3. Import the data.
4. Create an autonomous data warehouse connection object.
Note: The username is the custom schema created on the autonomous data warehouse instance in the earlier steps.
5. Create and run the Oracle Analytics Cloud data flow by following these steps:
a. Create a data flow; source object is the data set and target is the ADW custom schema.
b. Add the data set.
c. Select the autonomous data warehouse connection object that you created and enter EXTENDED_JOB_DIM as the table name.
d. Save and run the data flow.
e. Verify that the data flow was successfully completed from the Jobs page.
f. Connect to the custom autonomous data warehouse schema and verify that the data is loaded.
2. Create a branch of the main semantic model.
3. Add the Extend a Dimension step.
4. Select the dimension table that is being extended. For example, to extend the job dimension table, select HCM - Workforce Core subject area, Job folder, and Dim - Job logical table.
5. Select the custom schema and custom table created in step 1.
Note: The GRANT SELECT sql statement executed on CUSTOM_ADW_SCHEMA.Extended_Job_Dim table to OAX$OAC schema makes it appear in the drop-down list for the table. Select the attributes you'd like to add to the table and add a key column as well.
6. Select the join type and map the key column(s). This gets converted into a logical join between the tables Dim – Job and Dim – Extended_job_dim (added as part of semantic model extension).
7. Publish the branch and test the changes. Select the branch and click on Publish from the Actions menu.
8. Verify the progress from the Activity History tab.
Once successfully published, the branch name gets the word (Published) appended to it.
9. Verify the changes to the job dimension in the Oracle Analytics "projects" and confirm that you are able to see the additional job dimension attributes under the Job table (navigate to HCM – Workforce Core subject area, and then the Job presentation table).
10. After confirming the changes, merge the branch with the main branch and then version the model.
The versioned model is now ready for promoting to the Production instance.
11. To verify the merge with the main branch, click View Details for the Main Branch.
12. Promote the versioned model to the Production instance after thoroughly testing it in the Development instance. See Promote Your Customizations to the Production Environment.
13. Select the target production environment and select the appropriate version for promotion.