Load master data within SAP Analytics Cloud | Example to synchronize Local Dimension Members

[ThomasK]

Introduction

SAP Analytics Cloud allows to create models analytics and planning stored in SAC. Models are based on measures and dimensions. Dimensions can be public or private.

In case of private dimensions you may have the requirement to synchonize dimension members between local dimension. E.g. in case you have a backup model members of local dimensions must be synched with the source data model to ensure all data can be backed up.

This blog shows what´s neccessary for it. However, this technique can be used for other scenarios, e.g. like loading master data from SAC productive system to SAC test system.

Step-by-Step Guide

1. Create OAuth Client
2. Determine Data Service URL
3. Create a connection
4. Prepare version in target data model
5. Create Import Job

Create OAuth Client

1. Go to System / Administration / App Integration
2. In section OAuth Clients click "+ Add a new OAutClient"
3. Dialogue "New OAuth Client"
   Purpose: API Access
   Access: Data Export Service
   Authorization Grant: Client Credentials
   Click "Add"
4. Make sure to copy and collect generated OAuth Client ID and Secret (by clicking "Copy secret")
5. Copy and collect the Token URL in section "OAuthClients" for later
   https://<yoursystem>.hana.ondemand.com/oauth/token

 

Determine Data Service URL

1. Open the source data model with the local dimension where you want to load members from
2. Remember string after last / in the URL. It´s the unique data model ID.
3. The Data Service URL is 
   https://<your system>/api/v1/dataexport/providers/sac/<data model ID>/
4. You can enter the Data Service URL in the browser which should show a list of dimensions of data model in JSON format

Create a connection

1. Go to Connections
2. Click + to add a new connection
3. Select "OData Services" from the list
4. In dialogue "New OData Services Connection"
   Don´t check "Connect to an SAP Odata service" (yes, it´s strange but when checking it, it didn´t work)
   Enter Data Service URL from above
   Authentication Type: OAuth 2.0 Client Credentials
   Enter collected information: OAuth Client ID, Secret, Token URL
5. Click Create

Prepare version in target data model

It´s not possible to configure an import job for a local dimension. Because of this we import fact data into the prepared version of the target data model and update local dimension members in the target model. 

1. Open the target data model
2. Open dimension Version
3. Create a new version e.g. "LocalDim"

Create Import Job

1. Open target data model with local dimension with dimension members you want to synchronize
2. Switch to Data Management of the data model
3. Create a new Import Job
4. Select "OData Services" as data source
5. Select Connection created above
6. Select the local dimension where you want to import data from
   Remark: You also can load master data from public dimensions or fact data from the source model.
7. Select ID, Description and attributes you want to load
   Remark: It´s not possible to load hierarchy! Only attributes can be loaded.
8. Setup your import
   1. Prepare Data
      Create new Custom Expression with formula like [Value]=1
      
   2. Map to Facts
      Map Version to prepared version (e.g. "LocalDim")
      Map Value to a measure or your choice
      Map ID to Local Dimension
      Map all other dimensions to fixed value # or fixed date
      

      

   3. Job Settings 
      Open Job Settings (wrench icon) and make sure option "Update Local Dimension with New Members" is checked
   4. Map Properties
      Assign attributes from data source to attributes of target dimension
      
   5. Validate Import
   6. Run Import

After running the import you should find all local dimension members from the source model in the target model (deletion of dimension members is not supported).

Conclusion

Data can be exposed by the Data Export Service and loaded into same or other SAP Analytics Cloud system by creating an OData connection and an import based on it.