How can I copy dimension members between private dimensions in 2 separate models

[andrewbarlow1]

Hi,

I have 2 models and in each model I have an identical dimension called region.

Model 1 is a raw model with a lot of data and model 2 is a heavily aggregated model used for the reporting.

I am using a data action to copy data from model 1 to model 2.

The problem I have is that if a new region is created in Model 1 the data action will not send that data to the target model because the region is missing and I can't see a way of automating the creation of the region in the target model? I can copy members using a manual method using a story but the member synch needs to happen before the data action fires automatically.

I can't use a public dimension for this for technical reasons to do with the load unfortunately.

Is there a simple way to align these two dimensions automatically?

[Ivan_C]

Hi Andrew,

I haven't tried this exactly, but we are doing something similar, and I think you could adapt it to suit your purpose. What I would suggest is the following:

1. Create an export datasource off the first model. This is done in the 'Data Management' section of the model, where you look for the 'API Subscriptions' tab. You can click the '+' which will generate the OData endpoint for the model.

Select 'Fact Data' during creation of the endpoint, and you can also specify any selections that might be useful. Alternatively, you can configure a delta, which might also be useful for your situation, but I haven't tried that.

Once the endpoint is created, you can click 'View Details' and then you are able to copy the link which would look something like: https://xxx.ap10.hcs.cloud.sap/api/v1/dataexport/providers/sac/Cf7p31xvLdq8nZytgWbcm4ruo9/FactData?d...

Now in admin for SAC, you need to configure a new OAuth Client in the 'App Integration' section of the platform Admin. This needs to be for API Access and enabling the 'Data Export Service'.

This will give you a client ID and secret. You can use these along with the Authorization URL, Token URL to create a new connection in SAC that points to your export service for the model:

You can now use this as a data source to create an Import Job to load data into your other model. Because you aren't using public dimensions, you can't load them directly, but you could load the facts into the model (using a specific Version that you don't allow access to) and then in the Job Settings for the import job, enable 'Update Local Dimensions with New Members'. This should create the new members for you in the private dimensions.

I hope this helps.

Cheers, Ivan. 

[andrewbarlow1]

Thanks Ivan - this seems like a great option - thank you for your support on this.

[Ivan_C]

I hope it works for your application. Out of interest, the data export service exposes multiple structures, including master data for all the dimensions, so there is a good chance you could just use the master data for your dimension as the source, create a dummy measure in the transformation, and then load the data to a dummy version. Cheers, Ivan.

[Ivan_C]

Out of interest, I am using a similar approach to load a public dimension onto itself. So using the data export service, I have set up a data load job to the public dimension, and I use this to clear a property flag which is set elsewhere in a planning story. The load job itself is added as a step in a multi action which can be added to a story and triggered via scripting. Cheers, Ivan.