Besides the grid and graphical objects, a key component of the MicroStrategy platform is the ability to analyze and drill the information in order to resolve business issues, detect trends, opportunities or risks, as well as to define different views of data for different audiences. In this section, we will learn about these capabilities and use them.
MicroStrategy has the capability to add your own metrics (columns used for calculations, cost, revenue, and so on) or attributes (columns that organize and group metrics) besides the data loaded in the model. For advanced analysis, attributes and metrics can leverage mathematical and financial formulas; for example, it is possible to calculate average, standard deviation, sum, and variance as a new attribute, and aggregation and advanced mathematical formulas as the new metrics.
Let's start with some definitions and platform usability. The main interface includes three panels for metrics' and attributes' definition: My Data where the attributes button [
] and metrics button [
] are managed, Filters where the attributes and metrics can be used for data filtering, and Grid where we can adjust the grid-managing rows, columns, and metrics of our data. These three main panels are managed by clicking on the drop-down button [
] for options.
In order to start using this option, please proceed to generate a new model with the same raw data provided and used in Chapter 2, Mapping Typical Business Needs, and leave it with the default options. But mark the Employees field as a metric, select the Grid option, and name the model dataload-metrics. The model will be similar to the following screenshot:
Generate New Metric named Total Employees from the My Data panel option and edit the formula with the sum of Qrt1 Employees to Qrt4 Employees, as shown in the following screenshot:
In the Grid panel in the metrics section, add the Total Employees metric that we already created and remove the others metrics. The Grid panel will show only the Total Employees metric by region. Repeat the procedure; but now, add a metric for Total Sales and Total Profit. The Grid panel is now similar to the following screenshot:
Now, let's calculate the revenue and profit by employee. This is a new calculated metric that is the result of dividing the total sales/total employees and the total profit by the total employees. We need to create two new calculated metrics from the My Data panel by selecting the total employees, total sales, and total profits metrics that we created earlier to create new ones as shown in the following screenshot. Also, in the Grid panel in the metrics section, add the new metrics that are already created.
MicroStrategy offers a broad scope of formulas for data analysis as well as the Row Count metric for data aggregation.
Add a new metric using the row count formula and rename it as Total Stores; use the same procedure that we used for the generation of the previous metrics.
This kind of metric is very useful to identify how many rows (in our case, Store) are aggregated in the attribute (in our case, Region). Now, the Grid panel shows the results similar to the following screenshot:
We already transformed our raw data in the BI report in a very simple way without technical complexity.
We were able to determine that Region 2 is more profitable with fewer employees than Region 3 with the same number of stores. This kind of information triggers business actions to improve results such as improve sales clerk training and customer centric approach or avoid the rotation of employees in Region 3.
The MicroStrategy platform includes threshold functionality for the metrics in our models in order to detect the relevant data with a visual indicator based on the business rules that we define. For example, let's define the following business rule for the Profit by Employee metric:
- If the profit by employee is higher than 10,800, the status is green
- If the profit by employee is higher than 10,600 but lower than 10,799, the status is yellow
- If the profit by employee is lower than 10,600, the status is red
In order to activate the threshold, perform the following steps:
- In the Grid panel, select the Profit by Employee metric in the metric section.
- Select the Threshold option and eliminate the two intermediate ranges, as shown in the following screenshot:
- Select the first range from the left, double-click and select the color on the left, and move the slide to the value 10,600.
- Select the second range from the left, double-click and select the color on the right, and move the slide to the value 10,799.
- Click on the OK button.
- The threshold is similar to the following screenshot:
The Profit by Employee metric in the Grid panel changes color according to the business rule already set in the threshold, as shown in the following screenshot:
Threshold functionality is a powerful characteristic of MicroStrategy. It helps managers and directors to visualize what is good or bad in one shot and focalize the analysis according to their business needs.
In the previous example, Region 2 is the top performer in terms of profit by employee and Region 1 and Region 3 are the lower ones; in particular, Region 1 due the low sales by employee in that region.
We already created a model with calculated metrics, and we detected some information that was relevant for the business at high level. Now it is time to analyze and manipulate data leverage of the MicroStrategy platform in order to discover what is behind the data. Let's start with the drilling capability.
First, we need a common definition of what it means to drill. Drilling is a capability that takes the user from a general view of data to a specific one. For example, our report that shows the sales revenue by region allows the user to select a store, click on it, and see what the market share of this store is. He can click on it again and see when the store opens. It is called drill down because it is a feature that allows you to go deeper into the more specific layers of data or information that is being analyzed. The level of the drill depends on the definition of attributes in our model; in our case, Region, Store, Market Share, and Open Year are the attributes.
The benefits of drill down are as follows:
- Gain instant knowledge of different depths of data: In mere seconds, drill down answers questions such as which regions of my national sales figure are performing better, which stores are underperforming, and which store is driving revenue within each region.
- See data from different points of view: Drill down allows us to analyze the same data through different reports, analyze it with different features, and even display it through different visualization methods.
- Keep reporting load light and enhance reporting performance: By presenting only one layer of data at a time, features such as drill down lighten the load on the platform at query time and greatly enhance the reporting performance.
The best place to start to drill down our model is in the main grid view. Click on the drop-down button [
] in the Region 1 row and select Store. The Grid panel now shows the stores that belong to Region 1 with the metrics that we already defined in the grid. With the threshold activated in the Profit by Employee metric, another click on Store DG1 to drill Open Year will show the year of opening of the store, as shown in the following screenshot:
Another alternative to drill the information is to add a new row in the grid. This avoids one drill step (other query to the model), but the grid information is loaded with more data and the response time will be compromised.
In order to activate this alternative in the Grid panel, add a new row in the Rows section and select Open Year. Now, grid the Open Year group of the store by Region, as shown in the following screenshot:
Filters and slices are a key functionality of BI. Filters are used in reporting and analysis, for example, to restrict data to a certain region of stores, certain product groups, or certain time periods.
Another example is that you want to reevaluate the transaction data in your model by a factor of 10 percent. However, you only want to perform the reevaluation for certain groups of customers. In order to do this, you create a filter that contains the group of stores for which you want to reevaluate the data. MicroStrategy, offers the filters and slices functionality using attributes and metrics. This functionality can be enabled in the Filters panel of the main screen of MicroStrategy and you can add more filters whenever you need them. Add a new filter by clicking on the drop-down button [
] in the Filters panel using the Total Employees metric, and be sure that the region filter is enabled and remove the other filters.
For each filter, it is possible to set different properties depending on whether the filter is based on an attribute or metric:
The Display Style option allows you to select a Slider or Qualification option (in case of metrics) and Check boxes, Search box, Slider, Radio buttons, or a Drop-down list (in case of attributes). For the Region filter that has already been created, select the drop-down menu, and for the Total Employees filter, select the Qualification option. Now, let's assume that we need to analyze Region 3 in detail. It consists only of stores with more than 50 employees in order to detect issues in the Profit by Employee column according to the threshold defined previously. In the Region filter, select Region 3 from the list, and select Greater than or Equal to and type 50 in the Total Employees filter.
The Grid and Filters panels looks like the following screenshot:
Now, in order to detect why only few stores are in a particular color in the Profit by Employee metric, perform the following steps:
- Add a new filter by selecting the Profit by Employee metric.
- Change Display Style to Qualification and type
10,800(this is our value for the green status in that metric); the grid now shows only seven stores.
This information is relevant from the business perspective because Store is the benchmark in Region 3, which that is the lowest performing region in terms of Profit by Employee. The resulting grid is the following screenshot:
If we need to find a specific value in our data, it is possible to do that with the Region attribute or the Total Sales metric. Assume that we need to find the performance of three specific stores with some issues: DG626, DG828, and DG212.
In order to do so, please add a new filter in the Filters panel using the store metric, and select Search box in the Display Style option. Then, type the store's ID: DG626, DG828, and DG212. Now, the Grid panel will show the specific data of those stores, as shown in the following screenshot:
For calculated metrics similar to Total Employees or Total Profit, it is not possible to search for direct values, but MicroStrategy offers the following specific operators in order to search for specific data:
- Equals
- Does not equal
- Greater than
- Greater than or equal to
- Less than
- Less than or equal to
- Between
- Not between
- Is null
- Is not null
- In
- Not in
If we select equals and the specific data in the options, the query will work like a direct search alternative. For example, let's search for stores with a total of 50 employees only. In the Total Employees filter, select the Display Type option as Qualification, select Equals, and type 50 (please be sure to clear other filters, and select the Store attribute in the Rows section in the Grid panel); the results are shown in the following screenshot:
The grid shows all the stores with Total Employees equal to 50; in fact you can combine several filters with search type in order to solve more complex queries. The other benefit is that the entire Filters panel works together, regardless of the type of filter; for example, based on the previous result, search for stores with Total Employees equal to 50 only from Region 3. The Region filter is already activated; just select the store from the list in Region 3. Now, the grid shows only the stores with 50 employees within Region 3, and we can continue to combine filters. We already selected Region and Total Employees; now, select the Greater than option in Profit by Employee and type 10,800.
The grid now shows only the stores from Region 3 with 50 employees and profit by employee greater than 10,800, as shown in the following screenshot:
Each filter has the option to include and exclude data, depending on the filter (only works for filters based in metrics) and the display style. The Include option in the filter considers the information in the filter for the query and the Exclude option does not. It is the best way to search for a specific group of data that belong or do not belong to a specific criteria; for example, if we want to search for stores that were opened in the year 2000 and then search for stores that weren't open in the year 2000 (for benchmark purposes), we need to perform the following procedure:
- Add a new metric filter,
Open Year. - Change Display Style to Search box.
- Make sure that the Include option is selected.
- Type
2000in the filter that has already been created. - Be sure that the Rows section is added in the Grid option panel and the Store metrics. The configuration is shown in the following screenshot:
Now, we need to execute the same query but with the stores whose open date consists of all the years except 2000. In the Open Year filter option select the Exclude option; the Grid panel now shows all the stores except those whose open date is in the year 2000. This functionality allows us to execute reports with information filters in a very simple and straightforward process without the complexity of defining database queries or other IT programming languages. Remember, the approach is a do-it-yourself schema.
Now, you might be wondering how to totalize the results of your filters and queries in the result grid. In order to have the facts and results of your reports, MicroStrategy offers the possibility to show the totals of your metrics with different formulas such as Total, Average, Maximum, Minimum, Count, Geometric Mean, Median, Mode, Product, Standard Deviation, and Variance. This functionality is enabled in the results Grid panel by clicking on the drop-down button [
] of the first metric header ,in this case Region, in the results Grid panel and selecting the Show Totals option that you need for your report, as shown in the following screenshot:
Select the Total and Average options and run the filter of Open Year that contains the value 2000 with the Include and Exclude options again. At the end of the Grid panel (scroll down) you will see the total. The following screenshot shows a total of 43 stores opened in year 2000:
We already filtered and totalized information in our grid. What if we need to sort the stores by Profit by Employee and Total Employees in order to detect the top ten? MicroStrategy offers the functionality to sort the grid results. In order to enable it, click on the drop-down button [
] in the required attribute or metric section to sort the results. In our case, select Profit by Employee and sort by clicking on the Descending option; the grid will arrange the results sorted by Profit by Employee.
It is possible to combine various metrics or attributes in the sorting procedure. In order to enable it, select Advance Sort and the required columns and rows to be combined. In our case, select the Descending option for both Profit by Employee and Total Employees as shown in the following screenshot:
The results in the grid are sorted by the defined rule as shown in the following screenshot:
Ranking allows you to choose the rank level at which to return the results of the report. For example, our report contains the Total Market Share attribute and the Profit by Employee metric that you want to filter so that you can see only the top or bottom 10 stores of Profit by Employee.
Instead of generating a filter, sort the results and look for the data as we previously did. The ranking option allows us to generate the data in simple steps to filter it, based on metric value, rank, or rank percentage:
- In the Filters panel options, reset all the filters selected in order to start a new query.
- Select the Profit by Employee filter.
-
Click on the drop-down button [
] and select Rank highest for top performers.
- In the filters section, type
10and in the lists section, select the Less than or Equal to option. - Apply the filter. Now, you will see the top 10 stores in a single data grid in a single step, as shown in the following screenshot:
