By Krishna Sai Geetla, HEXstream data analytics engineer

Let's explore how to modify existing subject areas by extending a new column that is brought via third-party data through SME (extending the OOTB Dimension).

Goal: To verify if an out-of-the-box entity can be extended with new columns with data that is brought via a third-party source. To verify if the changes are reflected in the OOTB subject area and validate the results in OAC reports.

Key points to consider

→ Third-party data source for this use case is considered as a 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.

Detailed steps

• Connect to the target database with custom-user credentials.

• To import the new data from the 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 the Excel sheet that need to be attributes in the 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 and existing) must be same.

• Click on finish in the import summary wizard.

• 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_OUDIDEV2. CUSTOM_PERSON_ENV_CLASS TO OAX$OAC;

GRANT ALL ON CUSTOM_OUDIDEV2.CUSTOM_PERSON_ENV_CLASS TO OAX_USER;

• In the 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”.

• Right click on the Required dimension for the columns to be added. Select “manage extension”.

• In the manage extension dropdown select extend dim.

• Select the Schema name and table name in object. Select the attributes that needs to be added in existing dimension from custom data and click on “Next”.

• Give appropriate join->select joining keys ->Click on Finish.

• Verify the new change and go back.

• Make changes in subject area by selecting manage SA from perform action dropdown.

• Verify Subject area folders and click “Next”.

• In 2nd Wizard click on manage elements dropdown and select Manage Factory Customizations.

• Select the columns by checking and click on add folders.

• Select the dimension folder to get the new changes there and click add folders.

• Verify the details of new added folder and click on “Finish”.

• Go back to user extensions and apply changes on sandbox.

• Check the status in activity and go to SME tab.

• Publish the new changes.

• After verifying publishing go to OAC page.

• Select the subject area in the data tab.

• Verify if new columns are added into existing dimension.

• Verify the data of new extended columns in report.

NEED MORE GUIDANCE? CLICK HERE TO CONNECT WITH US.