♪ music ♪
In this video, I’ll take you through some ways to enhance your model
using the Analytics Cloud modeler after data preparation is complete.
We’ll add a formula, hierarchy, and create some general enhancements.
If you followed along during the previous video on data preparation,
you’ll have created a model called Best Run Travel.
You can access this model by choosing Browse > Models from the main menu.
When you find your model, click on the model’s name to open it in the modeler.
In the modeler, all of the different parts of your model are listed in the toolbar.
Listed first is the time dimension.
The time dimension will automatically include the timeframe that was imported with your master data.
If you want, you can extend the time frame so that you’ll be able to use the time dimension
to quickly forecast future values in your stories.
Next, is the account tab which contains a table of all of the measures in your model.
The first column lists the measures by name.
The rest of the columns perform functions that can further define each measure, and
alter how the values are displayed in your stories.
Whether or not you use these functions depends on the type of measures contained in your
model, and the purpose of the model itself.
For Analytical models, like this one, a Time Dimension and account dimension are the minimum
requirements for analysis.
Planning Models require Time and Categories, Account, and Organizational dimensions; and
they typically use more of the columns available in the account dimension.
For more information and help on planning models, check the Analytics Cloud help documentation.
Let’s start by taking a look at the Units & Currencies column in our account dimension.
This column is used to define the unit that is displayed for each measure.
By default, the unit for all measures is the US dollar currency, you can change the default
currency by accessing model preferences.
You can also change the unit for any measure by adding a label.
For example, mileage is measured in distance, not dollars, so you’ll want to add a “miles”
label here.
We also aren’t limited to the measures that are already listed in our account dimension.
It’s possible to create new measure directly in the model by adding a formula.
For example, it might be useful to know which flight paths offer the best value per mile
based on the ticket price.
To find this information out, we’ll create a new measure based on a formula.
In line 6 we’ll add a new measure ID "Ticket_Price_Per_Mile" and with the same description.
To create the formula, scroll over to the formula column and select the cell in row 6.
Now, you can enter the formula directly in the formula bar,
or you can open the advanced formula editor.
To calculate the ticket amount per mile we’ll want to divide the ticket price by mileage.
Using square brackets will call up all the available measures in our model.
So, let’s select the measure Ticket_Price, use the division operator,
and then select the measure Mileage.
You can click Format to make sure that the formula you’ve entered is valid.
Once you save your model, your new measure will be available to anyone that uses this model in their story.
Another great function of the modeler is that it gives you the ability to add hierarchies
within your dimensions.
An example of where you may want to create a hierarchy is in the case of regional information.
Our global business Best Run Technology has offices around the world.
So, for our analysis, we may want to see a global overview, or we may want to drill down
to regions, countries, or cities.
To make a hierarchy you’ll have to create parent/child relationships within your dimensions.
Let’s start by choosing the Home_Office dimension from the toolbar.
Next, we’ll click the plus sign to add in a column for the hierarchy.
This new column is the parent column.
In the parent column, we’ll want to add the ID’s of the parent locations.
It’s possible to do this manually, but we’ve included this hierarchy information within
the original spreadsheet that was used to create this model.
If you’ve misplaced the spreadsheet you can re-download it now, it’s available below.
The second tab of the spreadsheet is labeled “Region” and has our hierarchy information.
As you can see, the existing information in our model is notated in blue.
To create the hierarchies, copy the new entries for Parent (the ones in red) from the spreadsheet
and paste them in the parent column.
Because this hierarchy has more than one layer, we’ll add new countries and regions
as IDs and descriptions as well.
♫ music ♫
By opening the preview panel, we can see how the different levels of our hierarchy are organized.
If there’s a mistake in the hierarchy, you can easily make changes by dragging a dimension
member to a different position under a different parent.
The changes you make in the preview panel will be immediately reflected in the table.
You can also update the description information at any point while working on your model.
So let’s fix this typo in San Francisco.
Now, when you use this model in a story the new measure and hierarchy you’ve created
will be available for analysis.
♫ music ♫