SAP Lumira Prepare room use case – How to blend data with Merge and Append features

INTRODUCTION

The purpose of this document is to illustrate how to blend data and how to use merge and append features of SAP LUMIRA.

This document is delivered with excel, lums files and some methodological recommendations, allowing practice and enablement for end users.

In order to demo these different options, I created with the excel files attached SAP Lumira datasets in following this workflow:

1 – Merge the file 2011 Financial kpis with the file 2011 Quality kpis

These files have 3 common columns: Year, Month, Project and one difference, in the file 2011 Quality kpis, the data for the month 12  (December) are missing.

The 2 merge options are illustrated:

(a) Merge with Left Outer Join

(b) Merge with Inner Join

  2 – Append the merge dataset 2011 Financial + Quality kpis with the dataset 2012 Financial + Quality kpis.

For SAP Lumira beginners, please first refer to the video of my colleague Adrian Paul “How to merge datasets”

https://www.youtube.com/watch?v=sU1xNWqa9tA

STEP BY STEP DESCRIPTION

DATASET CREATION

Creation of the 1st dataset with the file 2011 Financial kpis

Creation of the 2nd dataset with the file 2011 Qualiy kpis when merging (with the add new dataset function)

When merging, please do a first test in selecting only one key, for instance “project”, and check the message

MERGE WITH OPTION LEFT OUTER JOIN

Merge on the 3 common keys in selecting the option Left Outer Join.

Please check the message

Outcome of the merge with the option Left Outer Join (union of the data)

Please note that the month 12, which for 2011 Quality data were lacking, is included in the 2nd dataset (see blanck cells in the sceenshot below).

Visualization Merge Left Outer Join

MERGE WITH OPTION INNER JOIN

Merge on the 3 common keys criterias, select the option Inner Join.

Check the message, it is identical to the one generated with the previous option but the outcomes are different.

Outcomes of the merge with the option Inner Join (intersection of the data)

Please note that data are merged and that the blanck cells, referring to the month 12 (December) where data were misssing, have disappeared in this use case.

Visualization Merge Inner Join

❗ Month 12 is not available

APPEND

From a methodological perspective, if you want to add next year the same type of data and built historical visualizations, I suggest exporting the obtained merged dataset to a file and use the first row as a pattern for the file to be appended.

Why ? Because the Append process needs to have the same colummns between the both files.

Export file

Process for exporting the file: go to the share room, select the merged dataset and launch “Export as file”

Excel file generated

Use the first row to build the excel file pattern that’ll be used to collect the data of 2012 and append with the existing dataset with data from 2011.

Creation of the 3rd dataset with the file 2012 Financial_Quality kpis

Visualization of the 3rd dataset created

As both files have the same columns, you can add this dataset 2012 Financial_Quality kpis to the previous dataset 2011 Financial kpis merged with 2011 Quality kpis.

Append process

Outcome of the Append feature : merge of the data of 2011 and 2012

Visualization of the Append process

Please note that in this example the Append feature was used with the first Merge option (Left Outer Join without Quality data in month 12)