Over the last few weeks, I have been spending some time working on model building in SAC(SAP Analytics Cloud). While the tool is extremely intuitive and easy to navigate, there are some areas where I had to spend some time, especially - model building with public dimensions. I wanted to share my findings with all of you & help you understand the difference between the different approaches to model building in SAC. Hopefully those that are starting to work on modeling in SAC will find this useful.
There are two ways in which Analytic Models can be created and deployed in SAC.
I’ll cover both the approaches in this blog and briefly discuss the pros and cons of each approach.
The first approach is rather simple, the model is created with the dimensions embedded in the source file. For e.g., if the file has the following columns:
Dimensions
Measures
Following steps were followed to upload the file into SAC:
Fig. 1
Once you select the model file you wish to load into SAC, you will see this.
Fig. 2
On creating the model, you will see this. The model is created based on the fields in the data source file.
Fig. 3
The dimensions are distinct data values contained in each of the dimension fields – e.g. MATERIAL_NUM
Fig.4
These dimensions can be extended to add additional attributes, e.g. MATERIAL_NAME or hierarchies.
The key is these dimensions are built based on the values in the model file (Inventory).
Pros
Cons
To alleviate the con in the prior approach of not having a reusable set of dimensions, the second approach can be followed.
Model Building with Public Dimensions
Creating the model using public dimensions in the second approach is an entirely different set of steps.
To create a model using the public dimensions, several steps need to be followed.
Fig. 5
Here’s an example of the Customer dimension. All public dimensions will mandatorily have an ID and description field. Additional attributes can be added. Hierarchy (more than one e.g. primary, alternative) can also be added.
Fig. 6
All required public dimensions need to be first created before setting out to create the model.
To leverage the public dimensions, it is necessary to have a Blank Model created first.
Fig. 7
When creating the blank model, two dimensions are defaulted:
Fig. 8
To do this, select the ‘+’ sign in the dimension pane and select one of the dimensions to be added.
Fig. 9
Once added to the model, these dimensions will show up with a Globe against its name on the dimension area.
Fig. 10
After all required dimensions are added to the blank model, data can be uploaded into the model using the data upload feature, with the current model selected in the Model Browser.
Fig. 11
Fig. 12
Scheduling Public Dimension Data Loads
With Public Dimensions, the data loads can be scheduled if it is being sourced from a File Server, or several other known data sources:
Fig. 13
Once data is imported from any of these data sources, you should see an option to see and set the refresh schedule.
Fig. 14
SAC also provides you with information on the daily scheduled refreshes, any data issues etc.
Fig. 15
When setting up schedules, the public dimensions should always run prior to the analytic models that utilize them. If this is not done, you could see orphaned data being rejected in the model data load.
Pros of using Public Dimensions:
Cons
In conclusion, while the use of public dimensions may seem like additional work and modeling, this approach can be beneficial overall.
Thanks for reading this blog. I welcome feedback and your thoughts on the above.
There are two ways in which Analytic Models can be created and deployed in SAC.
- Models are created with dimensions in the reporting source file.
- Models are created using Public Dimensions
I’ll cover both the approaches in this blog and briefly discuss the pros and cons of each approach.
The first approach is rather simple, the model is created with the dimensions embedded in the source file. For e.g., if the file has the following columns:
Dimensions
- MATERIAL_NUM
- PLANT_NUM
- FISCAL_YEAR
Measures
- ON_HAND_QTY
- ON_HAND_VALUE
- INVENTORY_VALUE
- PRODUCT_COGS
Following steps were followed to upload the file into SAC:
Fig. 1
Once you select the model file you wish to load into SAC, you will see this.
Fig. 2
On creating the model, you will see this. The model is created based on the fields in the data source file.
Fig. 3
The dimensions are distinct data values contained in each of the dimension fields – e.g. MATERIAL_NUM
Fig.4
These dimensions can be extended to add additional attributes, e.g. MATERIAL_NAME or hierarchies.
The key is these dimensions are built based on the values in the model file (Inventory).
Pros
- This approach allows for quick creation of the model and ingestion of data in SAC
- Dashboards can be created off this model as soon as the file is loaded
Cons
- The dimensions are not reusable
- Dimension refreshes cannot be scheduled independent of the analytic model
To alleviate the con in the prior approach of not having a reusable set of dimensions, the second approach can be followed.
Model Building with Public Dimensions
Creating the model using public dimensions in the second approach is an entirely different set of steps.
To create a model using the public dimensions, several steps need to be followed.
- Create the Public Dimensions
Fig. 5
Here’s an example of the Customer dimension. All public dimensions will mandatorily have an ID and description field. Additional attributes can be added. Hierarchy (more than one e.g. primary, alternative) can also be added.
Fig. 6
All required public dimensions need to be first created before setting out to create the model.
- Create a shell model using the public dimensions
To leverage the public dimensions, it is necessary to have a Blank Model created first.
Fig. 7
- Add default dimensions into the model
When creating the blank model, two dimensions are defaulted:
- Time – This dimension can be used for any date/time dimensions. Any date field in the source data will map to this dimension
- Account – This dimension will be used for any measures in the file. All numeric data fields will map to this dimension. The dimension should contain all measures that are expected it the file. This dimension can also be created as a public dimension or private one.
- E.g.
- Measures
- ON_HAND_QTY
- ON_HAND_VALUE
- INVENTORY_VALUE
- PRODUCT_COGS
- Measures
- E.g.
Fig. 8
- Add public/generic dimensions – Now we can add in the public dimension that have been pre-created using the Dimension creation option available in Public Dimensions.
To do this, select the ‘+’ sign in the dimension pane and select one of the dimensions to be added.
Fig. 9
Once added to the model, these dimensions will show up with a Globe against its name on the dimension area.
Fig. 10- Upload the data into the model
After all required dimensions are added to the blank model, data can be uploaded into the model using the data upload feature, with the current model selected in the Model Browser.
Fig. 11
- On uploading the model, SAC presents you with a mapping confirmation screen that looks like this. In this interface, mappings can be reviewed and corrected.
Fig. 12
- On the left side is the columns it the flat file being uploaded. On hovering over these columns, the mappings into the dimensions can be seen (e.g. MATERIAL_NUM is mapped to the DIM_MATERIAL)
- In the middle are the dimensions defined in the analytic model
- On the right are additional options and stats on the data uploaded. If there are any issues with the data – e.g. duplicate rows, orphans etc., it will be presented here. Refresh schedules can also be setup in this area.
Scheduling Public Dimension Data Loads
With Public Dimensions, the data loads can be scheduled if it is being sourced from a File Server, or several other known data sources:
Fig. 13
Once data is imported from any of these data sources, you should see an option to see and set the refresh schedule.
Fig. 14
SAC also provides you with information on the daily scheduled refreshes, any data issues etc.
Fig. 15
When setting up schedules, the public dimensions should always run prior to the analytic models that utilize them. If this is not done, you could see orphaned data being rejected in the model data load.
Pros of using Public Dimensions:
- The public dimensions can be created once, enhanced on an ongoing basis, and leveraged in multiple models
- Hierarchies and attributes can be modeled and uploaded independent of the model that is using it
- Dimension refreshes can be scheduled
- Ideal when working with non-EDW type of data sources where data is not been pre-processed and modeled
Cons
- All dimensions and hierarchies must be modeled and created in advance of creation of the model (additional upfront effort)
- Model creation should start with a blank model, source file cannot be used to create the initial model
- Type of data pre-processing for dimensions that can be done via the uploader tool is minimal.
In conclusion, while the use of public dimensions may seem like additional work and modeling, this approach can be beneficial overall.
Thanks for reading this blog. I welcome feedback and your thoughts on the above.
- SAP Managed Tags:
10 Comments
