In the previous blog, we saw the basic transformations of Data Modelling in SAP Analytics Cloud to enhance your data and prepare it for analysis.
Sample Data considered is a pivoted table with columns Region, State, City, Store, Postal Code, Latitude, Product Category and Product showing the monthly sales information.
In this blog let us see how to enrich your data with Hierarchies, Geo Locations, and Formulas.
Though Hierarchy is not needed for creating a data model, it helps the user to systematically organize the data and maintain integrity. Here it is good to have a hierarchy on Region -> State -> City -> Store.
You can also have multiple hierarchies in SAP Analytics Cloud Data Model.
Even though there are Latitude and Longitude information in the data model, you cannot directly map them in Geo Map. You need to create a geolocation dimension from the Latitude and Longitude available in the data.
If your data doesn’t have Latitude and Longitude information you can create a geolocation dimension based on Area. Region and Sub-Region columns (state, city, county, etc.) can be mapped.
Another enhancement that can improve the data model is the usage of Formulas. You can create calculations using the predefined formula in the formula editor. For example, consider that the tax amount of 20% must be included in the Sales Amount available in the data model. You can create a simple calculated column as shown below.
While creating a formula you can make use of conditions, operators, and functions (substring, replace, concat, etc.).
Apart from Formulas, Geo Locations and Hierarchies, you can also make use of combine data options. It allows you to combine data from another excel file or model. To learn more visit the blog here.
As you continue modeling your data, the transformations you make are tracked in your transformation log. It can be specific to a column as well. You can utilize the log to change a transformation that you made if there are no dependencies based on the transformation.
Currently, the transform log supports Unpivot, Smart transformations (split, concatenate, etc.), Formula and Combine Data.
To learn more about SAP Analytics Cloud, check out the series of blogs here.