A technical deep-dive into SAC modelling

Other blogs in this series can be found here: Part 1, Part 2, Part 4, Part 5

Modelling

In our last blog, we looked at the steps to get data from your Business Warehouse (BW) system connected to SAC. Now that the connection is set up, we can model the data – enhancing it with semantic and derived information and using built-in functionality to make analysis and planning easier.

Drafting a model

All reporting objects in SAC – datasets, models, stories, applications, Digital Boardrooms and external content such as images – are stored in the ‘Files’ area. It is here that, in conjunction with teams, access and folder/object-level security is managed. A root-level public folder is provided by default, accessible by all users, but an early set-up job should be planning a suitable security matrix.

Models can be created from files imported from your system or a file server and you can also create a blank model, which can be built to specification before data is loaded. We, however, want SAC to create our basic model for us based on a BW query, so we choose ‘get data from datasource,’ select the previously defined BW connection and find the desired query.

This will bring up the query window, where we can choose the dimensions and measures required, as well as applying filters, selecting key/text view and enabling hierarchies. If your query contains variables, they will be prompted here, and customer exits are compatible.

Once satisfied, hit ‘Create’ and your data will be uploaded into a draft. Drafts are where semantic definitions and data transformations are performed – it is a key step, as many settings decided here cannot be changed once the model is created.

The draft will show a sample of your data in a table, but this can be changed to a card overview of dimensions. On the right, the Details panel will give you key information, such as validation errors, and allow you to choose the default currency for your model as well as enable it for planning. We will not cover planning models here, but it is an incredibly powerful feature.

Enhancing and transforming data

Helpfully, SAC provides a transform log in the right-hand panel, so everything that is done to the draft can be undone and errors pinpointed. In the grid view, duplicate rows can be removed, as well as data, which is not required, and there is a powerful transformation editor. Advanced users can write transforms in the Excel-like formula bar, but SAC offers value-help for tasks such as text replacement and concatenation.

Once your data is validated, define the semantics of your dimensions in the right-hand panel. Most dimensions will be Generic, but special dimensions are set up to allow SAC functionality.  Dates can be set as Date dimensions, allowing date formatting and date-based calculations and output, whilst Organisation dimensions provide for departmental/BU analysis.

Versions can be created for planning and for budget/forecast comparison and Timestamps can be used for iterative analysis. With Measures, you can set the number format, scaling and decimals. The Dimension settings panel also gives basic value analysis, such as value count and, in measures, quartiles and outliers, to help with manual validation.

If your model has geographic data such as places or coordinates, these can be turned on to enable plotting on maps. You can also create level-based hierarchies. For instance, if your data has columns for Country and City, SAC can use geographic enrichment and you can create a Country-City hierarchy for drill analysis.

Saving your model

Once you are happy with your semantics, create your model. This will validate your data once more and allow you to save it to the folder structure, before uploading from your source. You will then be taken to the Data Management screen of the model.

Here, you can see the sources of your model, in our case the data import job that we just processed. Once your model is more mature, you will also see draft data when new data is to be imported, as well as any data exports you set up to, for instance, SAP Business Planning and Consolidation (BPC).

Imports and exports can be scheduled here, allowing regular updates based on recurrence or a calendar event. Rules for data imports can be set, for example to append or drop and replace data.

The second screen is the Model area, where you can see your dimensions grouped into types. You will notice that measures have been placed into an Account dimension, which is a single column for all measures, allowing a row-based model.

Click the wrench to be taken to the model preferences. Here, you can define model and dimension security, as well as fiscal time. For instance, if your business runs May-April, this can be set up against your date dimensions. Planning capabilities are set here, as well as currency conversion. In SAC currency conversion tables are managed independently of models, and dimensions with currency attributes (e.g. countries) can be used to automate currency conversion in models, hence the definition of default currency in the draft stage.

Individual dimension settings can be altered in the right-hand panel, such as creating hierarchies and controlling access rights, which are defined in the security matrix. Public dimensions can be added to the model too. These act like master data and can be set up as standalone dimensions to be included in a model and mapped to, much like creation of a BW cube. Dimension members can also be added or deleted in the dimension management screen, accessed by clicking on a dimension name.

Now your model is set up, enriched and has data imported, you’re ready to report. Create a story or analytic application on top of your model and you can quickly and responsively analyse your data.

To quick-start reporting in SAC, SAP delivers a huge range of business content – dimensions, models, stories and applications – on hundreds of common line-of-business and industry scenarios. In the next part of this series, we’ll look at how you can rapidly set up your reporting with the SAC Content Framework’s pre-built packages.

Chris Bradshaw

Senior consultant in SAP BI

Chris Bradshaw is a senior consultant in SAP BI in the UK I&D team. A graduate of the University of Oxford, he has 10 years’ experience providing analytic insight to clients in a range of industries.