Working with SAP Analytics Cloud (SAC) Public Dimensions
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.
- 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:
Following steps were followed to upload the file into SAC:
Once you select the model file you wish to load into SAC, you will see this.
On creating the model, you will see this. The model is created based on the fields in the data source file.
The dimensions are distinct data values contained in each of the dimension fields – e.g. MATERIAL_NUM
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).
- 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
- 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
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.
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.
- 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.
- 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.
Once added to the model, these dimensions will show up with a Globe against its name on the dimension area.
- 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.
- 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.
- 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:
Once data is imported from any of these data sources, you should see an option to see and set the refresh schedule.
SAC also provides you with information on the daily scheduled refreshes, any data issues etc.
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
- 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.
The blog is very clear and well explained. Thank you for taking the time to write this up. I had an additional question about the Public dimension which I am not able to get my head wrapped around. How does the data in the public dimension, maps to the data in the files.
In your example, how does the data from the incoming file know, which Customers to map the Inventory data to.
Great blog, very nice clear guidance and subject demystified nicely! cheers, H
One additional con to using public dimensions is that not all standard datasources are supported for scheduling refresh. For example it is not possible to schedule based on a SAP ERP (ECC) connection.
In that case you will need to use a private dimension and use transactionnal data load based on Infoset or SAP Queries which does also create missing members and their attributes. At least one measure is required but it can be created on the fly by duplicating a column and filling it with zeroes. Sames goes for other dimensions which you can fill automatically with # member or by creating another dummy column.
Nice article 🙂
Is it possible to add number columns in the public dimensions? If I have a product dimension, and like to have a column with product price, and use that in a calculation. It's seems that I maybe have 3 options:
a) Add the number to a number column in the Public dimension product, and create calculations in the storys.
b) Add the price column in a account dimension the model as a fact.
c) Create a public account dimension and "link" that thogheter with the public product dimention.
Any tips will be appreciated 🙂
BR Petter Huseby
Thank you..quite informative blog...
Thanks for the step by step guide, however, I want to ask two questions -
I am currently searching for the same answer. Want to add the global dimension and I am asked to select a "standard element" (german, en: default member).
Rahul Ravi Gupta and Venu Nair : Could you provide some insight here?
Ah sorry... just figured it out.... The default member ist used in case the dimension of a particular set has no matching value loaded. So in my case I've choosen the usual "#" indicating not assigned / unassigned elements.
In the dialog, it is shown at the end of the value help list. Alternatively you may directly enter the "#" as the defaults element ID.
default member selection for public dimension
Thanks for the information.
May I ask a question regards what will happen if I use public dimension in different models and I need to update the dimension structure in one model? Can I do that? Will the change applies to all other models using this same public dimension?