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


scn prepare_1.png


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


scn prepare_2.png


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


scn prepare_3.png


MERGE WITH OPTION LEFT OUTER JOIN


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


Please check the message


scn prepare_4.png


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).


scn prepare_5.png


Visualization Merge Left Outer Join


scn prepare_6.png


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.


scn prepare_7.png


Visualization Merge Inner Join


scn prepare_8.png


❗ 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”

scn prepare_9.png


Excel file generated


scn prepare_10.png


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


scn prepare_11.png


Visualization of the 3rd dataset created


scn prepare_12.png


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


scn prepare_13.png


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


scn prepare_14.png


Visualization of the Append process


scn prepare_15.png


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)




To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply