Data modeling in SAP Analytics Cloud is a way to enrich your data and prepare it for analysis. In SAP Analytics Cloud, you can model your data through data wrangling, the process of cleaning, structuring, and enriching your raw data. The data wrangling process includes defining dimensions and measures, filtering data, resolving data quality issues, setting hierarchical relationships, establishing currency conversions, creating custom formulas, and geo enriching your data.
How to Model Using the Modeler
Now that you have a basic understanding of data modeling in SAP Analytics Cloud, let’s walk through how you can create a model using the Modeler.
In SAP Analytics Cloud, you can create a model through a story or the Modeler. In this article, we will focus on how to create a model through the Modeler.
The Modeler is an advanced modeling tool that allows you to create, maintain, and load data into models. In contrast to creating a model through a story, the Modeler allows you to save your data and use your model for various stories. This tool is best suited for advanced modeling users as the Modeler contains a variety of functions that allows you to dive deeper into data modeling.
Step 1: Import your Data
To access the Modeler, you will either have to:
- Create a model from scratch
- Open an existing model from the Files list
- Create a live data connection where you can connect to external data sources such as SAP Concur, SAP SuccessFactors, and SAP S/4 HANA to create a new model
- Import an external file, such as an Excel spreadsheet or comma-separated-values files.
In this article, we will focus on how to access the Modeler by importing a file from your computer. However, click on the links above to learn how to access the Modeler from connecting to other data sources.
To import your data from an external file, click the main menu tab and select Create > Model.
Once you have imported your data, you will be directed to the Data Integration workspace of the Modeler. Here, your data will be organized in a familiar row and column format referred to as Grid View. The other view you can switch to is Card View. Card View displays all your columns as separate cards, enabling you to easily analyze your data types and spot data quality issues. As shown in the GIF below, in the toolbar, you can use the Mode to switch between Grid View to Card View.
Now that we’ve imported our data into the Modeler, let’s take a look at how we can prepare our data model, which involves the process of data wrangling.
Step 2: Define your Measures and Dimensions
When you select a card or a column, you will see the Details Panel located on the right side of the screen. Here, you can define your data as a dimension or measure, update your model information, and access different modeling options.
Using machine learning technology, SAP Analytics Cloud automatically identifies whether a value is a Measure or Dimension. However, it is always good practice to review the data to ensure the measures and dimensions are correctly identified.
To change a measure or dimension, select the column in the table and select Type in the Details Panel. Here, you can change the column to a measure or dimension.
Step 3: Cleanse and Prepare your Data
One way you can prepare your data is through resolving data quality issues. If you see a cell that is highlighted red, this means there is an issue with the cell value. To learn what is impacting the cell value, click on the highlighted cell and look at the issue that is listed under the Data Quality bar in the Details Panel. To learn how to resolve data quality issues, watch the Resolve Data Quality Issues video.
Another way you can prepare your data is through Smart Transformations, which uses machine learning technology to automatically suggests appropriate tools to transform your data based on the context of your select columns. You can choose to update values, and sort, delete, combine, and split columns to better prepare your data for enhanced visualizations. To access Smart Transformation features, click on a column and click Smart Transformation.
Step 4: Define Attributes for the Dimensions
After you have defined your measures and dimensions and resolved any data quality issues, the next step is to define Dimension Attributes. Within Dimension Attributes, you can add descriptions, properties, and hierarchies to your existing dimensions.
Descriptions provide context to dimension columns that are considered IDs. Using the retail clothing example shown in the GIF below, a dimension of the “Product_ID” could be the “Product_Item.” To add “Product_Item” as a dimension of the “Product_ID,” click Add Dimension Attributes > Description and then select the correct variable for the description. Descriptions can be displayed when you visualize dimensions in your stories or when you add them to tables.
Properties comprise of information that is related to a dimension. Using the same example previously, the column “Material” doesn’t represent a separate dimension but instead represents an attribute of the “Product_Item” column. As shown in the GIF below, to define the column as an attribute you need to select the dimension column and then click Add Dimension Attributes > Property in the Details Panel. Using the Property heading, select the column that is a property. Notice in the GIF below, under the “Property” heading, there is a suggested area where the system recommends likely columns that may be properties of the dimension. Properties can be optionally displayed in charts and tables within your stories.
Hierarchies are used to establish parent-child relationships within your data. In our example, we’ll define a parent-child hierarchy for the clothing items. To do this, follow these steps:
- Select the desiredcolumn, in our case, the “Product_Item” column. Select Add Dimension Attributes > Parent-Child Hierarchy (Parent).
- Under the Parent-Child Hierarchy (Parent) heading, select the parent dimension, in our example, we will choose “Product_Line.” Now, you’ll see that the “Product_Line” column is defined as the parent of the “Product_Item” dimension.
- Select Create Model and then save your model.
Once in story mode, your data-enriched charts will allow you to drill down to the different layers. To see this in action, check out our post all about hierarchies.