♫ Music ♫
One of the things that sets SAP Analytics Cloud apart is the fact that you can model
your data directly within the application.
Data preparation, also known as data wrangling, is the first stage of modeling.
It's when you’ll map, clean and transform your data in preparation for analysis.
Models can be created from file imports from your computer or google drive
Or from connected from on-premise and cloud data sources through import data connections.
Please note that data preparation and modeling within Analytics Cloud
is not possible, or necessary for data sources connected via live data connections.
This is because live data connections use the existing models within your source systems
that are updated with new data in real-time.
In this video, I’m going to take you through the process of preparing your data with the
Analytics Cloud Modeler.
I’ll show you how to check your data quality, how to define relationships within your data,
how to update your data using quick actions and transformations
and how to create geo locations.
To follow along, download the excel dataset linked below.
By the end of this video you’ll have prepared a dataset and created a model which you can
then use for analysis right away.
Our fictional company, Best Run Technology, is a large enterprise corporation with offices
and customers located across the globe.
Like many global organizations, Best Run Technology has KPIs attached to employee travel.
The dataset that you have downloaded was exported from SAP Concur, a travel and expense software.
To get started choose Create > Model from the main menu.
Because our dataset is an excel spreadsheet,
we’ll choose the “import a file from your computer” option.
Upload the excel file “Best Run Travel”, make sure to select the first sheet labeled
“Data Upload” and click "Import".
Due to the large number of records in this file, you’ll see that the data has been sampled.
Data sampling helps Analytics Cloud run faster during data preparation.
The changes you make to this sample will be
applied to the entire dataset once you create your model.
We’re now in the Data Integration workspace of the modeler.
You’ll notice that there is not an option to save in the toolbar.
This is because the process of preparing your data must take place in one session.
Once the data has been imported, you’ll see it organized in a familiar row and column format.
This view is called “table view”
To the right of your data, the details panel displays a data summary and model information.
You can use the panel to update your model information and select from some model options.
Because this model is for analysis only, we’ll leave the “Planning Enabled” option unchecked.
To get a good overview of your dataset you can switch to card view mode.
Card view makes it easier to navigate datasets with a lot of columns.
Each card represents a column of data, and displays some summary information.
When you select a card, detailed information about the column appears in the details panel.
You can also use this panel to make changes and enhancements to your data.
Let’s start by checking how the columns have been categorized.
Are the measures and dimensions defined correctly?
In this model, our categorization looks pretty good, but using the details panel
we can change the data types if necessary.
For example, the information in the count column represents
the number of travelers in each record.
Because each row accounts for a single traveler, the value is always one.
This means that the column is a measure, not a dimension, so let’s update the column type.
Miscategorization is unlikely to happen, since Analytics Cloud recognizes data patterns and
is typically able deduce whether or not a column of numeric values is a measure or dimension.
But, because of situations like this one, it’s best to double check.
The details panel is also where you can add dimension attributes like descriptions, properties,
and hierarchies to your existing dimensions.
Descriptions give context to dimension columns that are considered IDs.
For example, in an HR model, you may want to add employee name as the description to
the employee ID number dimension.
Descriptions will be displayed when you visualize dimensions in your stories,
or when you add them to tables.
Properties are made up of information that are related to a dimension.
So, in the same HR model, you could add employee email addresses and phone numbers
as properties of Employee ID.
These properties can then be optionally displayed in charts and tables within your stories.
Hierarchies are dimension attributes that create parent-child relationships.
These relationships will allow you to drill-down by different levels of detail within your charts.
Each type of dimension attribute can be created during data preparation, but they can also
be added later once the model has been created.
Ok, let’s add some description and properties to our model.
We’ll add a hierarchy later, once the model has been created.
Let’s start by sorting our cards from A-Z.
There are several ID columns that can be paired with corresponding description columns in
this dataset like these two, “Cabin Class” and “Cabin Class Description”.
Note, that the number of unique values on the ID card
should match the number of values on the description card.
4 IDs should have 4 corresponding descriptions.
Let’s create a description association between “Cabin_Class” and “Cabin Class Desc"
First, we’ll choose the dimension that is considered the ID, in this case “Cabin_Class”,
then using the details panel select “add attribute”, “description” and choose
“Cabin_Class_Desc”.
And that’s it!
Pause here and add the following descriptions:
Next, let’s add some properties.
The latitude and longitude coordinates in this dataset will be used to create geo locations,
but they are also considered properties of their respective location dimensions.
Let’s start with the Home_Office_ID dimension
and Home_Office_LAT and Home_Office_LONG as properties.
Pause here to repeat the process for Origin_Airport_Code and Destination_Airport_Code.
Another part of data preparation is cleansing your data and ensuring that only relevant
data is included in the model.
Selecting a measure card allows you to check the data quality and see the data distribution
in the details panel.
At this point, you can check for outliers in your data and make sure that all of the
values make sense.
Let’s select the “Ticket Price” card.
In the data distribution section of the details panel we can see that the minimum cost of
a ticket is $0.
Because we only want to include values greater than $0 in our analysis, we can omit these
records from our model.
To remove the $0 records, switch back to table view, select one of the cells with a $0 value,
and then select a quick action to delete the rows with a $0 amount.
During data preparation, you may also come across entire columns in a dataset that aren’t
necessary for analysis
For example, the dimension Home Office Code doesn’t add any value since we already have
an ID and a description.
To delete an entire column, select the card, switch back to table view,
and you’ll see that the column is still selected.
Now choose the “delete column” quick action from the column menu.
As you can see, there are a limited number of quick actions available,
but you can make even more changes with transformations.
When it comes to transforming your data, you can choose from the Smart Transformations
suggested by Analytics Cloud, or create your own using the transformation bar.
For example, in the details of the Carrier_Alliance dimension
we can see that there are two different versions of OneWorld.
This is because Analytics Cloud is case-sensitive.
To quickly update the values so that all of the instances of OneWorld are correct, we’ll
use a smart transformation.
Select a cell with the incorrect version of oneworld, then select the smart transformation
icon in the cell menu.
Here you can see the auto-generated transformations available.
When you hover over the selection that makes sense you’ll see the transformation formula
in the formula bar, and a preview of the outcome.
You can see that our City Pair name column contains both
the Origin and Destination city listed together.
So, another transformation that we can make is to split the City Pair Name column into two separate columns.
First select the column, then click into the transformation bar and choose split.
Then we’ll complete the formula by adding a dash as the delimiter.
Again, you’ll see a preview before you complete the transformation.
Let’s rename the new columns as Orgin_City and Destination_City.
For descriptions of all of the available quick actions and transformations,
visit the Preparing Data page of the SAP Analytics Cloud Help Library.
The last transformation we’re going to complete in the data preparation stage will allow us
to use the powerful location analytics capabilities in Analytics Cloud.
In order to visualize locations on a map they must first be geo-enriched during data preparation.
This transformation entails creating Geo Dimensions for each location group.
For example, in this model we’ll create Geo Dimension for our office locations, as
well as the origin and destination airports in our dataset.
We’ve already added our latitude and longitude coordinates as dimension properties, so to
create our Geo Dimensions we’ll choose the Geo by Coordinate option.
Let’s start with the Home Office Geo Location.
In the Location by coordinate window we’ll name the geo location “Home_Office_Location”.
Then select “Home_Office_ID” as the location ID
and choose "Home_office_Lat" and "Home Office_Long" for the coordinates.
Clicking create will make a new location.
Pause here to repeat this process for "Origin_Airport_Code" and "Destination_Airport_Code"
Okay, now that we’ve completed all of our data preparation we’re almost ready to create our model.
By clicking “Create Model”, Analytics Cloud applies
all of our changes to the full dataset and checks for errors.
It looks like there is an error in our Travel Date Column.
By clicking on the data quality bar, the error row is brought to the top of our column.
Now we can choose to replace the value, or to delete the rows with empty cells.
With this error resolved, we’re cleared to create our model.
Take the time now to check the details panel for information on your entire model,
because once you create it you won’t be able to update any of the settings.
When you’re ready, click “Create Model” and your master data will be imported into
the model that you have just created.
Once created, this model can already be used to create visualizations within your Analytics Cloud stories.
In the next video on modeling, I’ll show you some ways to make this model even better
by adding a hierarchy, formula, and other general enhancements.
See you then!
♫ Music ♫