Chapter 3. Preparing Data

Data is everywhere, and it has various formats and types. Sometimes it is structured, whereas sometimes it is not. Data discovery is a challenging process because, in most cases, we should work with massive volumes of raw data in order to find business insights. Business or data analysts have to work hard in order to prepare data and understand data patterns, highlight unique values, or just format and clean the data.

Once all this data has been collected, the data geek must prepare the data that has to be analyzed. Organizing the data correctly can save a lot of time and prevent mistakes. With SAP Lumira, they can format data to fit their needs in order to organize their data effectively. A good data geek enters all the data in the same format and in the same place because doing otherwise may lead to confusion and difficulty with statistical analysis later on. Once the data has been entered, it is crucial that the data geek checks the data for accuracy. This can...

Preparing a data tab

In the previous chapter, you learned how to extract data from various data sources. When we connected to data, we got a new window, called the Prepare tab, with our data in it. It has many features to clean, filter, and merge data. Let's look closely at the Prepare tab and the main bars. But, before this, we need to extract and acquire some data. Perform the following steps:

1. First, click on Acquire Data.

2. Then select Query with SQL.

3. Next, select the recently used connection that we created in the previous chapter; it offers us the opportunity to use our SQL query.

4. Then click on Preview and Create.

5. The Prepare tab will appear:

   

6. There are five main tabs on the Prepare window

   • The status bar (1): This is visible in all workspaces and displays details about the dataset, such as the name, the number of rows and columns, and the last refreshed date and time. We can also submit feedback about SAP Lumira from here to SAP.

   • The prepare workspace (2): This is where you edit and clean the...

Preparing data

Usually, there is raw data in the data source and we want to analyse it from another view. However, sometimes it is not formatted consistently. As a result, it is not easily interpreted by business users. Before creating reports and visualizations, it is often necessary to clean up the data so that it is presentable and understandable.

Cleaning and editing a dataset

Say we want to exclude Shipping City from our dataset in order to analyse the revenue and quantity of items by category. In addition, we want to reduce the dataset and improve performance. This can be done easily with the following steps:

1. Click on Data->Edit Data Source or press Ctrl+Shift+E; the Edit Data Source window will appear:

   

2. Uncheck Shipping City and click on OK.

   

We will get the new dataset with only three columns.

Let's continue to learn how to prepare our data on another SQL query that returns much more data. Perform the following steps:

1. Click on File->New. Select Query with SQL.

2. From the Recently Used...

Enriching data

SAP Lumira offers you various methods to enrich your dataset by adding measures, geography hierarchies, and time hierarchies. Measures allow you to easily manipulate calculations, and hierarchies allow you to use a natural grouping of related columns.

SAP Lumira detects columns that are potential measures, time hierarchies, and geography hierarchies when we acquire data.

The time hierarchy

By default, we have only Sales Date, but for a detailed historical analysis, we need to analyze various date dimensions, such as year, month, week, and so on.

Let's create the time hierarchy for Sales Date with the following steps:

1. Click on the option menu for the Sales Date dimension:

   

2. Then select Create a time hierarchy....

3. SAP Lumira will create new date dimensions as follows:

   

The geographical hierarchy

Modern analytics tools provide us with the opportunity to look at data from the geographical perspective. It gives us lots of advantages: for example, we can easily measure the region that is the...

Creating the calculated object

Finally, we will create new calculated measures or dimensions in our dataset using formulas that are based on the existing measures. In this example, we'll create a calculated measure for revenue based on the prognosis of sales analytics that the revenue in the next month will be 10 percent less than in both previous months together.

Perform the following steps:

1. Click on the options menu for measure and select Create Calculated Measure.

2. The New Calculated Measure window will appear.

3. Enter the new formula in the formula editor:

   

   SAP Lumira provides us with many functions that can help us calculate complex measures. You can learn more about these functions in the functions tab by choosing any function from the list in order to get detailed help about this function.

In addition, we can create new dimensions in the same way.

Summary

In this chapter, you have learned how to prepare data and why it is very important to clean data before starting to analyze it in detail. You have also looked at the rich functionalities that SAP Lumira offers to prepare and enrich data. We have discussed several techniques, such as merging and appending datasets, splitting column values, creating various hierarchies, creating new objects, and so on. These techniques will help you to prepare and produce better visualizations in the next chapter.