Manipulating and enriching data with analytics
Self-service business intelligence is about interactive analytics and using a highly visualized way of getting insights. However it is also about preparing and enriching your data. The Digital Transformation brings data from everywhere in any possible structure or format. If end users want to analyze this data they need to be able to quickly access and integrate it with their existing data. Very probably they require to enrich the new data. Enrichment of data covers a wide variety of data crunching going from creating calculations and aggregation types, into hierarchies and custom groupings. Data enrichment is also about joining different data sets or – to a limited extend – cleansing data. It is about creating – custom – grouping or calculated dimensions ….. it Is about …………. well, I can talk a whole week about data enrichment since the verb covers so much: in short: data enrichment is anything you need to do to prepare your data and bring it in a condition that is sufficient for you to meet your analytical requirements in search for insights.
In this “Let Me Guide You series” we focus on data enrichment. I have chosen to pick following 4 examples of data enrichment since I experienced I use them basically in every situation:
Data Blending versus Joining
Isolating records that “did not make the join”
Use measures as a dimension
Custom grouping and hierarchies
All of the examples here are accompanied by instruction videos.
Data blending versus joining
If you need to link 2 or more data sets than tools like SAP Lumira offer the option to join the different datasets. This can be done via an Inner or Left Outer join. Respecting good old database modeling principles; these joins require the lookup dataset to have unique keys. But what if your datasets have different grains and thus no unique key available …… hmmmm, difficult ……. but ……….SAP Lumira now has Data Blending. Data Blending allows to “join” different datasets without requiring a unique key. In the below example we join a data set with sales figures on Continent-Hub-Country level to another set with budget numbers on only Continent-Hub level; so no unique key and this is where blending comes in. It is an absolute unique feature in SAP Lumira which makes this product very powerful.
a more sophisticated example can be found in following video:
Isolating records that "did not make the join"
The deep dive analysts reading this blog will love it: can I get an overview of all records that “did not make the join”; the records that do not have an accompanying record in the lookup data set. Yes, you can. In the below example you can learn how to use the exception or full outer join option in the blending facility to highlight records that did not make the join.
Tip: in case you do have records that “did not make the join”, you might want to put them on a dedicated visualization and export that visualization into a separate data set or even a csv/xls for further review.
Use measures as a dimension: when to use this functionality?
Sometimes you want you measures to “behave as a dimension” for example if you have multiple measures per dimension and you want to group the individual measures per dimension-member. The measures now act as normal or regular dimensions so you can also use them in color coding or trellis. See below video how to do so.
Creating custom groupings and hierarchies
Quite often we require to have dimensions grouped in another of alternative way. If this new grouping is permanent you might want to add it into your database model. However in self-service we often need to make the grouping instantly since our data comes in on the fly; maybe from external and initial data sources. In SAP Lumira’s prepare room you can simply click the column header of the applicable dimension and choose the Group-By-Selection option to define your own custom groups. In addition you could create a custom hierarchy and place your initial dimension below your new grouping to create a drill-path for your new groupings. Very useful and really easy to set-up.
Similar goes for grouped metrics where you can either choose the Group By Selection method (though not useful in case many records) or – even better – Group By Range. With the latter you define measure-groupings setting up variances for the measures to fit. See below instruction video.
subscribe to my Youtube channel
Being passionate about analytics, I maintain a YouTube channel where I demonstrate best practices with self-service business intelligence and managed dash boarding & reporting. All the posts are highly instructive and follow a step-by-step flow. Subscribe to my YouTube channel so you don’t have to miss anything.