By Krishna Sai Geetla, HEXstream data analytics engineer

Overview: The use case showcases support and the technical flow for external datasets to be combined with out-of-the-box data and build reports/visuals in OAC. All customizations are performed in Fusion Data Intelligence console’s semantic-model extensions. For the external data, a table is created in the target ADW database and is read in semantic model extensions of FDI.

GOAL: To verify if a new dimension with third-party data can be added to out-of-the-box subject areas' fact and verify data in visuals/reports when the data from custom dimension is selected.

Key points to consider:
→ Third-party data source for this use case is considered as dataset coming through an Excel sheet.

→ There must be a custom schema created in target ADW database with its own user-login details. 

→ The new table must have matching primary key data to join with the out-of-the-box Fact.

→ Both the primary key names for existing Fact and new dimension must not be same.

Flow of steps: 
→ Get the data set through an Excel sheet and import to target database.

→ Give necessary grants to OAX_USER and OAX$OAC users on the custom table.

→ In FDI platform, create a sandbox by modifying the existing logical star of the required subject area through the semantic-model extension feature.

→ Modify out-of-the-box subject area and apply changes.

→ For publishing the changes, this current flow used publishing of sandbox to directly verify in OAC. If user wants to make changes permanently, we can merge to main sandbox and publish main with new changes.

→ After publishing, check the newly added changes are reflected in the existing folders.

→ Final step is to verify the new dimensions data is available in OAC reports. Also, to verify if the new dimension columns yield data when combined with existing fact and dimensions.

Detailed steps:

·      Connect to the target database with custom user credentials.

·      To import the new data from Excel sheet, right click on “Tables” and then select “Import Data”.

·      Select the file with data.

·      Select format as Excel 95-2003(xls) if it is not automatically and click “Next”.

·      In next wizard, select import method as Insert.

·      Give the table name and click “Next”.

·      Choose the columns from excel sheet that needs to be attributes in new table in DB and click “Next”.

·      Change datatypes and size as per requirement or according to the columns in existing fact.

·      Both the primary key names for existing fact and new dimension must not be same.

·      The data type of primary keys of the two joining entities (custom & existing) must be the same.

·      Click on "finish" in the import summary wizard.

·      Click on “ok” on the import message.

·      For the “OAX_USER” to read/edit the data in new custom dimension in custom schema, there must be grants on the new table. 

·      In OAC “OAX$OAC” must have read access to the custom table.

·       Use the following grants:
GRANT SELECT ON CUSTOM_EWDIDEV2.CUSTOM_HOUSEHOLD_PAYMENT_BURDEN TO OAX$OAC; GRANT ALL ON CUSTOM_EWDIDEV2.CUSTOM_HOUSEHOLD_PAYMENT_BURDEN
TO OAX_USER;

·      Check the data in new table after importing.

·      In Admin console, go to Semantic Model Extensions.

·      In user extensions select create sandbox and enter a name.

·      Select “Manage logical star” in “perform action” dropdown.

·      Select the edit logical star->out of the box->Subject area name-> Fact name and click on
“Next”.

·      In the logical star structure click on “manage dimension” drop down and select “add dimension”.

·      Select the custom schema->Object (table name)->Dimension name is auto generated(use different if the name is already used).

·      Check on the column attributes that needs to be in the new dimension and click “Next”.

·      Uncheck hierarchy->give hierarchy name(mandatory)->select the detail primary key and display attribute.

·      Click on “Finish”.

·      Join the fact and custom dimension by dragging the connector from fact to dimension.

·      Select the appropriate join and fact-dimension joining keys.

·      Select the content level (mandatory) and click “Done”.

·      Validate and go back.

·      After going back click on perform action to modify subject area.

·      Select the required subject area and click “Next”.

·      Verify subject area folders and click “Next”.

·      In the second Wizard click manage elements dropdown and select Manage new customizations.

·      Click on the first check box to select all of them (we can select individual labels as per requirement). 

·      Click Add.

·      Verify all new custom changes are in folder and click Finish.

·      Go back to user extensions to apply sandbox changes.

·      Publish the model after confirming sandbox changes.

·      Go to OAC after publishing of model.

·      Select the subject area from Data page form dropdown.

·      Verify the new dimension folder shows up.

·      Select the columns from dimension to verify data.

·      Verify the connection of new dimension with existing fact/dimensions by adding columns from out of the box fact and dimensions.

CLICK HERE TO CONNECT WITH US ABOUT HOW EWDI CAN OPTIMIZE YOUR PROCESSES.