Excel Upload and Data Wrangling

Excel Upload and Data Wrangling

Contents

  • Import Data from a File
  • Data Manipulation

The HR department has handed you an Excel file that they want you to incorporate into a new dashboard. Thus, you need to upload the file to a story and use a series of transformations to clean up and format the data.

Key Features:

  • Learn how to import data from a file

  • Understand data manipulation (Grid View)

  • Learn the quick transformations available within data manipulation (i.e. Concatenate, Extract, Transpose)

  • Create a level based hierarchy to convert multiple columns into a single dimension

  • Enrich your data with geographical information

Import Data from a File

When you create a story using a file as the data source, you automatically create an embedded dataset.

On the Home Page of SAP Analytics Cloud, users can explore a sample story, create a new story, learn more in the help center, view recent stories, or use natural language to JustAsk.

Download the Excel file AIN360_HR_Employee_Salary.

  1. From the side Navigation Bar select Files.

  2. Follow the location path My Files/Public/Experience Academy BI/Datasets and select the AIN360_HR_Employee_Salary.xlsx file to download.

  3. Select Home from the side Navigation Bar.

  4. On the home screen, select Create Your First Story.

Under the Stories tab, users can choose the story type, or choose a template for the new story.

  1. Select the Canvas tile.

  2. Select the Optimized Design Experience, and choose Create.

We are interested in accessing and exploring data that we bring in from an Excel File.

  1. Under the Tools menu in the Top Toolbar, select the Add New Data dropdown.

    Note

    Resize your browser if you don’t see the Tools menu.

  2. Select the Data Uploaded from a File icon.

  3. Choose Select Source File.

  4. Select AIN360_HR_Employee_Salary.xlsx from where you saved the file, and choose Open.

  5. Select Import.

  6. Wait for the data to import into SAP Analytics Cloud.

    imb10

Data Manipulation

Data Manipulation provides the content creator the ability to configure which columns are measures and dimensions. It empowers them to correct any mistakes that may exist within their dataset.

The upper section of Data Manipulation provides the content creator a set of tools such as creating a hierarchy, creating a geographical dimension with latitude and longitude information, and more.

Below the toolbar, a transformation bar is provided to help the content creator when it comes to modifying data within a column. Whether it is concatenating two columns, or extracting information, the transformation bar eases the process.

The right section of Data Manipulation contains a Details Panel to help the content creator better understand the data within a column. It highlights any errors and displays the data distribution.

Combine the employee First Name and Last Name into a single column, it will help when it comes to creating visualizations.

  1. Select the column, First Name.

  2. Also select the column, Last Name while pressing Ctrl / Command key.

    Both the First Name and Last Name columns are selected.

  3. Select Transformation -> Concatenate using " ".

  4. View a confirmation that the two columns concatenated successfully.

Set the Employee Name as a description of the Employee ID column, and merge them into one dimension.

  1. Select column Employee, then in the side panel click the drop-down under Description.

  2. Expand Description, and choose the First Name_Last Name dimension.

There are multiple columns related to an employee's location. However, they're currently represented as individual dimensions (Office, Office State, Office Country, and Office Region). We want to create a Level Based Hierarchy to represent this information as a single dimension.

  1. From the Actions menu, under More, select Level Based Hierarchy.

Within the Hierarchy Builder we need to select the columns that are part of our Office Hierarchy. Start with the lowest level of the hierarchy.

Note

If the

  1. Expand Select Dimension.

  2. Choose Office.

  3. Expand Select Dimension again.

  4. Choose Office State.

Repeat the steps for Office Country and Office Region.

  1. Expand Select Dimension.

  2. Choose Office Country.

  3. Expand Select Dimension.

  4. Choose Office Region.

Give the hierarchy a name.

  1. Select the New Hierarchy text box under Name and enter “Office”.

Quality Check Does your Level Based Hierarchy look like this?

  1. Select OK.

We've completed all the required transformations to ensure that we have the right data to create visualizations within SAP Analytics Cloud. Now we need to save the changes we've made.

  1. Under File menu in the Top Toolbar, choose the Save icon.

  2. Select Keep Data Sources.

Save your story with your data model in your Private Area, My Files.

  1. Select My Files.

  2. Name your story with your username in front of the story name: “###_Employee_Salaries” and select Save.

  3. [Optional] Update the story and create your own HR dashboard with the embedded dataset.

Congratulations!! You have now completed the Excel Upload and Data Wrangling unit.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn