SAP Lumira Prepare room use case – How to blend data with Merge and Append features
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”
STEP BY STEP DESCRIPTION
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
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.
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.
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)
Perfect example Charlotte, thank you!
You helped me answer this thread immediately 🙂 Can we use more than 2 datasets as source in Lumira?
Thanks Charlotte for the example on merging and appending datasets in Lumira. It helped.
After having merged two datasets, how could you unmerge again, to go back to the inital situation ?
Related, how could you display merge definition ?
In my opinion this is not possible, even not in Lumira Discovery 2.0.
Thanks for sharing your ideas.