MicroStrategy Tutorial

This MicroStrategy tutorial is designed for readers who want to learn the fundamentals of Microstrategy such as how to read, create, write, and modify BI Reports and dashboards using MicroStrategy reporting tools. In addition, it will also be helpful for those readers who want to pursue a career as a Data Scientist or Data Analyst.

MicroStrategy Tutorial - Table of Content

What is MicroStrategy? Features of MicroStrategy Installation And Initial Setup Importing Data Transforming Data Using Data Wrangler First Report Data Discovery Sharing My Reports

What is MicroStrategy?

MicroStrategy is a Business Intelligence software that can be connected with any data including data files, big data, social media data, and so on to create and modify highly detailed reports and dashboards. It is a very popular BI tool since it allows organizations to analyze their data in-depth and make better decisions.

Do you want to get certified and build your career in MicroStrategy? Then enroll in "MicroStrategy Online Training" this course will help you to achieve excellence in this domain.

Features of MicroStrategy

• Ease of use and self-service
• Highest report scalability
• Highest data scalability
• Plug and play components
• Offers highest user scalability
• Automated report maintainability
• Allows you to prompt parameterized reporting
• Conditional formatting of data and visual indicators
• Helps you to format tabular grid reports and convert them into graphs and charts
• Drill down granular level data using Microstrategy
• Export in MS-Excel, HTML, or text formats

1. Installation And Initial Setup

The first thing to know about this Microstrategy version  10 is that it contains an essential piece called Desktop.
For those coming from previous software generations (7, 8, or 9) MicroStrategy Desktop was the tool that was used to create the metadata and reports on a Windows (PC only) environment. From version 9.3 Desktop was renamed, Developer. So don’t be confused with the new name.
The Desktop version in 10 is Multi-Platform, that is, it remains a desktop application but now with the ability to be run on both Windows and MAC OSX but there is no difference in functionality between both versions.

1.1 Download

The first step is of course to download our version if we don’t have it yet.

Note: If you have corporate access, you should ask your IT department to provide you with a copy of the software and the license.

Otherwise, you could use HTTP://WWW.MICROSTRATEGY.COM/US/ANALYTICS/TRIAL to enter your information and download the trial version. Once inside you must choose your version for Windows or Mac.

1.2 Installation 

Once you have downloaded the corresponding installer we’ll proceed with its installation on our local computer.
After installation, we’ll proceed to run Desktop and this will be the first glimpse we’ll get:

 

1.3 Setting Up Your Data Connection

In 10, we have several options to connect to our data sources, one of the main ones is the ability to connect to our MicroStrategy corporate server. That is, we can run Desktop in “solitary” to use data from different sources such as spreadsheet files, corporate databases, or connect our desktop directly to the project we have assigned to upload our new data and then create our reports in this project.

1.4 Desktop Connection To Microstrategy Server

To connect our local Desktop to the corporate Intelligence server, we must have the IP address or hostname of the server. If you don’t know it, please contact the MicroStrategy administrator within your company who surely has all the information needed.
The second step is to use the user name and password to connect the project to which you have been previously assigned.
To do this connection on Desktop, you should go to the Main Menu MicroStrategy> Preferences. Within this menu select the Server tab.
The following window should then appear:

 

Once connected to the Intelligence server, you will be able to navigate through the folder structure of the project and select attributes and metrics to add to your report on Desktop.
Note: To connect to a corporate MicroStrategy server, this server must be version 10.

1.5 Connecting To “Live” Data

One of the new possibilities allowed by version 10 is to connect to a data source and maintain that connection live, that is, import the initial data but then automatically refresh it from the source.
There is a long list of data sources connections available to choose from but you can select from traditional databases, such as Oracle, SQL Server, MySQL, Teradata to the newest ones such as Hadoop, Cloudera, Impala, Hive.
This is especially useful when, for example, you are looking to analyze real-time data and your report must be constantly updated.

2. Importing Data

Let’s import our data. We will use a sample file which to make the process easier for you.
Import options in MicroStrategy 10 are varied, ranging from the classic Excel file to the possibility of extracting information directly from Facebook or Twitter.
In this tutorial, we will review the most relevant options for importing more commonly sources, such as importing from a spreadsheet (using multiple sheets in a book), importing from a file stored in Dropbox, importing data from an URL, and finally data import using social networks like Facebook and Twitter.

 

2.1 Importing A Spreadsheet On A Local Disk

The first option when we talk to business analysts who want to perform Self-Service BI is the ability and flexibility to import data they keep in their spreadsheet, in their local computer, directly to MicroStrategy. For this reason, it will be the first import choice we will review here, in order to show the step by step of this process and also the improvements brought by this version as opposed to its predecessor: 9.4 One of the major improvements that have been added in 10, is the ability to import several sheets from the same book at the same time.

Let’s recall that in version 9, it was only possible to import one of the pages of the book at a time, which made the process cumbersome since a data cube had to be created for each sheet.
To make the process even simpler, We have prepared a sample worksheet with the “World Population updated to 2014”.
You can download the resource below.

Once you’ve downloaded the example excel sheet, we will proceed to explain step by step how to import our file.

1. Click in “Add new data”

2. Select the “File from Disk” option

3. Click the “Select File” option

4. After loading, MicroStrategy will present a window like this. If we want to, we could add another file to our selection

 

5. If the excel file contains only one sheet, by clicking on “Prepare Data”, this will be the only available Table. In this example, we are using a file containing 2 sheets, which is why when moving to the next option MicroStrategy will display the following:

y ]

 

As you can see, we have two sheets to select “Population per Country” and “Birth rate by country.” We will select both to show how MicroStrategy is now able to perform this task compared to previous versions where it was possible to import only one sheet at a time.

6. The next thing is to select both pages of the book and click on “Select”. MicroStrategy will then show us 2 tables available to import. By default, the first sheet of the book will be pre-selected.
7. Important Note: From here, the steps are the same for all other options of importing data. Therefore, if we learn to manipulate and transform data for this import option, we would be doing it for all the others.
8. The most important part of this step is to properly separate those columns that are Attributes (Dimensions) from those that are Metrics (Measures).

 

9. To change the “Rank” column, for example, which is being interpreted as a metric, we must right-click on the name of the column and select “Convert to Attribute”. Otherwise, it is the same procedure except we only need to select “Convert to Metric”.

 

10. We continue with the preparation of data for sheet 2, in this case, “Birth Rate per Country.” However, as we can see in the picture below, the data appears to be unsorted and in a difficult format to work with. This is because the birth rate metrics are separated by year in columns and not by row, we also have a header line that MicroStrategy interprets as an attribute.

 

To change the way MicroStrategy interprets this table, we must do the following:

11.1 Click on the name of the table (birth rate per country) and choose “Parse” in the options

11.2. Then we convert the table from Tabular to Crosstab as pictured.

 

 

11.3. This option will allow us to define the values of the year as elements of the dimension. While here we can’t exclude the first line which in this case is the title of our sheet, we can do so in the next step. We click on “Apply” to continue to the next option.
11.4. After applying the changes, we will see the result differently. Now we have 3 attributes and only one Metric.

 

12. The last step here will exclude the column title of the sheet (the second column named Birth rate by country…”. To do this simply right-click on the name column and select ” Do not import“.

 

13. Now we can see the end result where the record that refers to the title of the page does not appear as an attribute
Before selecting ” Finish“, we can see that MicroStrategy has created a link between the two tables (clicking on Country) following semantic techniques, in this case using the “Country” column. If required, this relationship can be modified by right-clicking on a column and selecting “Unmap”.

14. Moving to the next screen, we will see the final result of our imports. Since we have chosen to unite the two tables using the ” Country” column, we see only one (1) data set which contains all the attributes and metrics of both excel book sheets.
We’ll save this report as “Population Metrics by Country” which we will use later. From this point, we are in a position to start building our analysis and discovery of new data, which we will review in detail later in this Microstrategy Tutorial.

2.2 Importing Directly From Dropbox

Importing data from a file stored in Dropbox or any other file storage system, such as GoogleDrive, SkyDrive, SugarSync, among others, is very similar. Basically, you need to know the URL (physical address of the file) which is commonly accessed by a username and password, except in cases where the source file is hosted in public service or the user has determined that the file is in the public use, in which case, no authentication is required.

Now step by step to import the data and create our first report.

1. Open MicroStrategy 10 and select “Add New Data”.
2. In the Connect with your data window, we select “Dropbox”.
3. MicroStrategy will show as a window to enter the username and password associated with the Dropbox account you want to use:

 

4. If this is the first time we connect our Dropbox account, an additional window will be displayed with the following message, we should click on “Allow”:

 

5. Once we have achieved the connection with our Dropbox account, which can be personal or corporate, MicroStrategy will show us the structure of folders from our own account which we can navigate to find the desired file.

6. The following is simply adding the source files we want to our import process.
7. Click on ” Prepare Data“.
8. From here the process is the same as the one made for importing data from a local file, so to continue to proceed from step 5 “Importing a spreadsheet on a local disk.”

2.3 Importing Data Using Url

In the case of wanting to import data using the URL option (Uniform Resource Locator), what is required is to have a valid Web address that contains the information you want to import in the form of tables or lists. In this Microstrategy, we will use one of the most recurrent options when wanting to import public data directly from the web: Wikipedia.

This site offers relatively consistent information, with validated sources and which will even be updated with the passage of time. Other data sources typically used are those that provide information about the value of the Shares, the ones related to the Weather Forecast as well as those that generate global statistics such as demographics, number of computers produced, number of downloaded mobile applications among others that can be found, the list is huge and increasingly detailed.

To import data directly from a URL (or web page), there are two options available:

1. Using the Import ” File from URL“
2. Using the option Import ” Public Data“.

 

The option to choose depends entirely if what we’re trying to import is exactly a file, such as a plain text or CSV file, in which case we’ll use the first option; or whether on the contrary and as we’ll do here, we want to extract data directly from the website considering that the information is in one table or list.
We will then extract the information related to the “List of Countries by Number of Internet Users”. Data which is available on Wikipedia in the following link: HTTPS://EN.WIKIPEDIA.ORG/WIKI/LIST_OF_COUNTRIES_BY_NUMBER_OF_INTERNET_USERS

The step by step is what follows below:

1.Choose the website from which you wish to extract information.

2.Open MicroStrategy Desktop 10.

3.Click ” Add new data“

4. Click on the ” File from URL” and enter your URL in the dialog box as shown in the image below:

 

5. Click on ” Add“.
6. Click on ” Prepare Data“.
7. The next step is to select which one of the available Tables on that webpage are we going to use. In this particular case, we are going to use the first of the list “Country or area”. Check that option and click on “Select”.

8. In the next window, we must change the ” Rank” column to be an attribute and the ” Internet Users” and ” Percentage” columns to metrics (measures) and exclude the “Rank 2” column from this process.

 

9. The next step will be renaming the first columns from “Country or Area” to “Country”, the second column to “Internet Users” and Percentage 3 to “Percentage”.
10. In this particular case, in addition to changing object type and names, we’ll change the data type of two (2) columns (Internet Users and Percentage). This is done because MicroStrategy recognizes them as text and not as numeric data. The first “Internet Users” should be changed to Integer while “Percentage” should be assigned as a Float value.
11. To continue, we will assign the Country attribute to the Geographic – Country type, allowing us to create interactive maps later on. The preview should look like the following image:

 

12. Finally, we will rename our data set as ” Global Internet Use” before saving the template with the same name.

 

2.4 Importing From Social Networks

This is perhaps the most popular choice today among all Business Intelligence tools. The ability to extract information from social networks like Facebook and Twitter is not new and is something that advanced users increasingly demand.
The justification for this type of work is varied and can range from simply getting feedback that social network users have of a given subject to the possibility of analyzing the progress of a particular advertising campaign to launch a new product or service.
In this chapter, we will make a couple of examples of search for common words using Twitter.

To use Twitter, we should have a valid account previously created, in my case, I will use my account @DataVizWarrior

1. Open MicroStrategy 10.
2. Click on ” Add New Data“
3. Click on ” Twitter“. 
4. Fill in the screen below with your own account information.

 

5. The main thing here is your username and password. For the user, we can use @name_of_account or the associated email.
6. Once you have entered the authentication data, click on “Authorize App”
7. Once we have authenticated our account, it will display a screen where we can add keywords or phrases that we want to search on Twitter.

 

8. Remember that all results are in real-time.
9. To give continuity to our previous example, we will perform a search on World
10. An important tip to remember is the option to use Operators in our search in order to improve the results.

 

The most useful operators in my opinion are:

10.1 The AND operator is by default, for example, if we look for cheap tickets it will return results where both words are present.

10.2 Using the “OR” allows us to choose more than one search option, for example, Funny OR Boring, will return results that meet both conditions or only one of them.

10.3 The use of # (hashtag) and @ (user-specified)

10.4 Filtering by positive or negative attitudes, using the typical emotions smiley or sad.

11. Once you have entered the desired search, click ” Search” and after a few seconds, it will return the results in a format like the one shown below:

 

12. All searches on Twitter will return the same number of columns, so we need to know which of them are really relevant to the question we are trying to solve, in some cases you may want to know the specific source of a tweet, for which we’ll use the source_link_url column while in other situations we just want to know the number of responses related to this particular issue, for which we will count the ID column.
13. Among the most used columns, we can highlight:
13.1 ID
13.2 Source_link_url
13.3 Created_At (Date and hour of creation)
13.4 Username y UserID
13.5 is_liked (It indicates whether this tweet has been bookmarked by other people )
13.6 iso_language_code (en for English)
13.7 User_followers_count (number of followers the person that created the tweet has)
13.8 Place.Country_code (Country code)
14. The next thing to do is click on “Prepare Data” and choose those columns that we will use in our subsequent analysis:

 

15. For purposes of this example, we’ll leave all columns and click on ” Finish“.
16. MicroStrategy will return a template like the one shown below, where we can see for example the number of tweets (Retweet Count) related to our search (QueryKey).

 

17. Lastly, we’ll save this template for future use as ” Twitter Research“.

To import data from Facebook, the principle is basically the same: A valid user is required and uses keywords or strings to search.

3. Transforming Data Using Data Wrangler

MicroStrategy has introduced a new, although expected for some time, data transformation functionality. For the more advanced users, it’s a reduced version of an ETL tool, with some functions such as separation of columns, eliminate data duplicates, among others.

The name of this new module within MicroStrategy 10 is Data Wrangler. Here we look at this functionality along with the most relevant aspects when transforming data, along with some of the best practices that will be of great use when performing Data Blending.

To demonstrate the functionality of data transformation, we will return to our first report in which we import data from a local Excel file “Population Metrics by Country”. Using Data Wrangler, we will implement some of the most commonly used transformations.

3.1 NEW INTERFACE

1. Open your project in MicroStrategy.
2. Click on the data set “Population by country” and select “Edit Data Set”:

 

3. We will see the “Preview” screen with the difference that we will click now on ” Wrangle“. Before continuing, please be sure that “Population per Country” is selected.

 

4. MicroStrategy is going to ask us to select the file again. We can use this screen to replace the original file if needed. After the file is been selected, click on “Refresh”.

 
5. The screen will now be somewhat different (see image below). This is where we apply different data processing rules such as filter, Replace, remove duplicates, separate a text string in two or more columns, among others.

 

The most important points to highlight about this new interface are:

1. In the upper left box, we have two selectors, the first for the column on which we want to apply a transformation function, while on the second selector, we have the potential functions for that column (as in the image below).
2. In the upper right frame, we will see the change history that we will implement, with the option to go back more steps as needed.
3. In the central area, are located all the columns with their data, which will be affected each time we apply a transformation function.
4. Finally, at the bottom of the screen, we have something similar to a selector or values filter, particularly useful when we are implementing some new facet to text or numeric data. 

3.2 QUICK FUNCTIONS

Next, we’ll apply some quick functions to transform the data set “Birth Rate by Country”.
1. Select the corresponding data source (Birth Rate by Country)
2. Click Transform
3. We should see this screen:

 

The first thing we will do is eliminate those rows where the “birth rate” column is empty. Why? Because we cannot use the null data as a valid statistic in this particular case.

4. Click on the “Birth Rate” column

 

5. Click on the “Remove Row(s) where the cell is empty [Birth Rate]”
The next step, remove the column associated with the sheet’s title (Birth rate by Country per 1,000 population).
6. Select the “birth rate per country every 1000 inhabitants” column,
7. Click on the icon close to the column name to display the options available and click on “Delete”.

 

8. At the end of both actions, we should see the following result:

 

9. As we can see, now we have 2 scripts running on script History (superior right corner).
If we wanted to we could undo these changes simply by using the navigation icons forward / backward.
10. Finally, we’ll click on “OK” to apply changes.

3.3 Other Transformation Functions

Most transformation functions are fairly simple and self-descriptive. One of the most commonly used is one that will allow us to split a column of data in two or more columns, for example, when we receive a file that contains name and last name in the same column, we must apply the ” Split” option in order to separate them in 2 columns, one for name and the other for last name.

4. First Report

When creating our first report, it is always desirable to have clearly identified the goal we are pursuing and above all, the question we are trying to answer. If we achieve clarity about the question we’re trying to solve, it will be much simpler to build a dashboard or report based on the data we are working on. Having said that, there are four key factors taken into account when building a new report:

• What questions are we trying to answer
• The quality of the data we are working with
• How we will present the results; tables, graphs, or both
• Who are the receivers and how are we going to distribute our report

Let's then create our first report using the previously generated example with the template stored under the name “Population Metrics by Country “.

4.1 Sorting And Basic Formats

We’ll start by answering a simple question: ” Which is the country with the largest world population? “
1. Open our previously saved report.
2. Add ” Population (2014)” into the Metrics section.
3. Add ” Country” in the Row.

Your report should be similar to the following image:

We can see that by default, MicroStrategy has organized by the name Country according to the alphabet order. What we really want to know is what the most populous country is, therefore, we will change the order using now the metric.

4. Click on the visualization and select “Sort Descending”
5. What we see after applying this simple ordering, is that China is the first country with the largest number of inhabitants, but the default format is not very friendly, right?
6. The following will be to give a different format to those numbers. For this, we click again in the metric name and select Number Format > Fixed and we will check by using the thousands separator.

 
7. Click on OK to apply those changes. We can see that after applying a different format, the figures can be understood much better.
8. In the end we are able to tell which the most populous country is and also if we wanted one with the lowest population; to achieve it is just enough to change the order of the metric to ascending.
In the visualization Properties view that we are creating, there are several other options to apply new formats, both to titles as to metric and attributes elements, such as:

• Sources
• Font types
• Fill Color
• Vertical and horizontal alignment
• Line and Line colors

 
We even have advanced options (click on More Options), where we can define properties such as:

• Combine and block rows and columns headers,
• Show or hide null values,
• Filtering behavior,
• Type of join behavior for attributes and metrics.

 

4.2 Defining Thresholds

Something important before setting thresholds or traffic lights is to be clear about the ranges and colors that will apply to each segment.
I recommend you to talk about it and define it with the business user who will use these ranges daily.
For our example, we will define a high population as deep green, while the smaller population will have a light green.
1. Right-click on the selected Metric column to display the properties
2. Click on “Thresholds”
3. Select the next combination to achieve the expected: Color= Green, Based on= Population (2014), Highest %, and click on Reversed.

 

4. Once the threshold has been applied, we see that when values of the metric are in a black color they can’t be distinguished clearly, to improve this, we will change the font color of the data to yellow.
5. Right-click on any column value and select Format

 

6. Here you can choose the color of your preference, in this case, we’ll use yellow.

 

Our Population metric now shows a traffic light-colored in green scale with colors decreasing in intensity proportional to the decreasing amounts on the metric. You can clear all the Threshold over a column at any time by selecting “Clear Thresholds” from the properties list. MicroStrategy offers a great opportunity to create different thresholds, even using pictures instead of colors.

The process is the same but instead of using a Color-based threshold, you must use “Image-based” thresholds.

 

I invite you to try some of them and become familiar with the operation of these traffic lights. MicroStrategy has some really interesting options such as arrows, Pins, bubbles, and even Trucks.

 

 

4.3 FILTERS

One of the main elements of any report is filters. These filters help us to be more accurate when creating our reports and, of course, to limit the number of results.
MicroStrategy 10 gives us the possibility to filter all or only one visualization at a time. The first thing we will do is to add a new visualization to our existing report, in this case, we’ll use a map to delineate the countries.

1. We open our report “Population Metrics by country”
2. On the top icons bar, we click “Insert Visualization”

 

3. By default it will show just on the right side of our chart. We’ll add the Country attribute in Rows and the “Population (2014)” measure in Metrics.
4. Now we’ll select in the view bar on the right side, the Map icon, and will click on it.
5. To finish, we’ll move this new visualization right under our table and we’ll rename it Colors map. To do this, just drag the entire visualization and drop it under the table.
The report should look as below:

 

We will begin reviewing the option on how to filter the complete report:

1. Using the same report. First, we must add Rank Attribute into our grid visualization. Place it on the left of the Country.
2. On the toolbar, select View> Filter Panel or click on the Filter icon next to visualization properties.

 

3. Add the Country attribute into the filter panel.
4. Right-Click on the filter name and select Display Style > Radio Buttons
The report should look as shown below, where if I select one of the options available on the list, all the visualizations in our sheet should be affected. For example, I’m selecting Australia and both, the grid and the map are being filtered by this Country only.

 

Upon completion of the desired changes, we click Save. How to filter just one visualization without affecting the others.
1. Instead of using the Filter Panel, we will use a dynamic filter. In order to do that, we must first remove the previous country filter created using the filter panel.
2. Next, click on the icon bar, specifically on the ‘+’ icon

 

3. Select the first option on the list which is ” Filter“
MicroStrategy will add a new section on the top of our visualizations with the text ” Drag objects here“.
4. We drag the ” Country” attribute
5. On the properties panel, we select Style> Drop-Down.

 

6. Click on “Select Target” and choose both the grid at the top and the map visualization at the bottom. At this point, we have a similar filter than when we use Filter Panel, if we select any country from the list this selection will be applied to all the elements of the report, whether these are tables or graphs.
7. Change the name of the top grid to “Details by Country” and the map visualization to “Color Map” and save the report.
8. To filter only the top visualization, called “Detail by Country”, right-click on any part of the filter and “Select Targets”
9. Uncheck “Color Map”
10. Click on Apply and then on

 

Now, this filter will only change the top view but not the map at the bottom.
We can add as many filters as we need on top following exactly the same procedure explained above.

4.4 Calculations

Calculations are a very useful tool when we are building reports. They allow us to derive results from other metrics in our data set or even derive them from one.
Here we’ll take a look at a couple of examples to create basic “runtime” measures (living in the report and not being part of the original data set).
The first thing we need to do is create a percentage of the total metric for our population column. To accomplish this, we can right-click directly on the metrics already presented in our visualization or on the metric name in the “Metrics” section.
1. Right-click on “Population (2014)” and select the option Shortcut Metric> Percent to Total> Grand Total

 

What MicroStrategy will do for us, is to calculate the overall total population for all countries first and apply a percentage share for each country on the aggregate. The result will be displayed as a new metric and, in this case, as a percentage format.
Most of the time, analysts prefer to work with percentages as that gives them a much clearer trend over time than when working only with numbers. If we want, we can rename this new metric by right-clicking on it and selecting the “Rename” (% of Total) option. The result should look like the following.

Note: I recommend doing it directly in the Dataset objects area, otherwise, it is going to apply the new name only for that visualization.

Do not forget to save your progress.

 

The following example will be to create a metric that will show us the Population density per square kilometer.
As you may have noticed already, this metric exists in our dataset. Still, we’ll do the exercise manually to teach you how to create calculated metrics.
1. First, add the Area (km2) metric to our grid. This metric shows the total area of each country. To add it we double click on the metric or drag it from the left panel (Data Set Panel) to the right of our newly created percentage metric.
2. Next, we will change the number format to fixed with a thousand separators.
3. We will right-click on “Population (2014)” and select the “Create Metric” option.

 

We will stop here to explain, in general terms, what is the screen that we are seeing. This metrics editor allows us to write our own formulas based on simple mathematical functions, such as add, subtract, divide, multiply or use a variety of analytical functions (also called OLAP) such as:

• Average
• Count
• First
• Last
• Max
• Min, among many others.

Here you can choose from basic functions, going through string functions to statistical and financial functions. Each function has its own rules regarding the type of data required and the number of metrics to be used to achieve the desired result, but that’s beyond the scope of this tutorial.
Right now, we will focus on creating a simple metric for population density and the formula for calculating it is: [Population (2014)]/[Area (Km²)]

4. Drag and drop “Population (2014)” first, then click on the division icon and finalized adding “Area (Km2)” at the end.
5. Click on Validate.

 

6. Click on Save.
7. Lastly we will change the Name to Population Density (P/km2) and that’s how we have created our first metric manually.
8. If you want to validate the result, we can add the base measure ” Density (P/Km2)” to our grid and both values should be idem.

 

5. Data Discovery

In this chapter, we will focus on the concept of “Data Discovery”. According to Wikipedia, the definition of Data Discovery is:
“Data Discovery is a Business Intelligence architecture aimed at interactive reports and searchable data from multiple sources. According to Gartner Consulting, data discovery has become one of the mainstreams of BI architecture of our time”

Gartner says that Data Discovery has become very popular in recent years, and perhaps the fastest growing branch in the BI world. Along with this new trend, new concepts and professional profiles have appeared as the case of Data Scientists.
In my personal opinion, Data Discovery has to do with finding valuable information where it is not apparent, that is, when we do not have the precise data but we can derive it from the analysis that we ourselves have on the subject at hand.
I’ll try to give some clear practical examples on how to perform data discovery that are not evident using, of course, MicroStrategy 10. Even with all this, if one of your objectives is to perform detailed data analysis looking for patterns or other findings that are relevant to the organization in which they are located, I always recommend looking for literature in line with these concepts and new techniques. For purposes of this tutorial, we will perform a simple analysis trying to find a relationship between the world’s population and the Birth Rate.

The questions we will try to answer here will be quite simple, but they will serve as an example to the concept of discovery data, these are:

• Is there a direct relationship between the number of inhabitants and birth rates by country?
• What is the relationship between population and Country area?
• What is the relationship between population density and birth rate?
• What are the countries with the largest elderly population (over 60 years)?

As you can see, we can make an unlimited number of questions from just 2 datasets that we are dealing with, then we will see if we can answer any or all of these questions.

5.1 Mixing Data From Different Sources

As I mentioned before, this new version of MicroStrategy will allow us to mix data (Data blending) from various sources as never before. Not only to use structured data sources such as databases, excel files, or flat files; but also those un-structured sources or even those called Big Data.
In this example, we are using two different data sources but that can work together since we have a common concept which is Country.
The first thing we will do is to create a table containing the main metrics with which to work.

1. Once again, we open our report “Population Metrics by Country”
2. First, add the ” Birth Rate” metric from the second set of data, to the “Details by Country” grid visualization.

Something important before proceeding: if Birth Rate data is repeating for each row in the grid, it’s because the data set are not linked between them. To make this possible, we must right-click in Country, at the data set panel, and select the ” Map Attribute” option to select, of course, the second set of data.

3. We must remember that we are mixing the Birth rate per country, which has several years of history, with the Population per Country which only has the year 2014, so the following is to apply a filter to the entire report, containing only 2014 for the birth rate.

 

With this last step, we have created our first report successfully combining two completely different data sets, using the Country attribute as leveling.

5.2 Selecting Type Of Visualization

At the time of having to select which visualization to use, keep in mind a couple of concepts. First, what kind of data we are delivering in terms of size and importance, and second, the target audience.
Often as a consultant, I have had to face highly complex designs using maps or high-end graphs, that ultimately are not required since the target audience of that report does not require such a high level of sophistication.
The concept of “less is more” is key to achieving a report or dashboard that is clear and concise but at the same time effective. In MicroStrategy 10 we have 10 basic visualizations to fulfill our tasks and although it is possible to drastically increase this number using java libraries (d3 for example) it will not be the objective of this book to go into this concept and we will only cover the use of basic options.
In our current report, we already have two visualizations, one is a Grid; while the other is a color map. What we will do is to add a couple more according to the analysis we are doing.

1. First, add a new visualization and place it on the right side of the map
2. We will allocate the Bubble chart as visualization.
3. In the Vertical section will use the metric “Population (2014)”
4. In the Horizontal section we will use the metric “Birth Rate”
5. In the Color By section use the metric “Population (2014)”
6. Finally, in the Break By section, place our Country
7. Rename the visualization to “Birth Rate as “Birth Rate related to Total Population”
8. To add the country labels, we must right-click on any bubble and select Data Labels > Only Text (if we want to display the Country name) OR select Only Value to display both Population 2014 and Birth Rate.

 

9. Click on Save.

The question we are trying to answer here is:
Are countries with the largest populations also the ones with the highest birth rate?
Apparently, the answer is “No, they are not”, in the X-axis we have the birth rate while on the Y-axis we use the number of inhabitants or population in 2014.

As we can see here both India and China soar in population, yet they are on a rather low scale in terms of birth rate, while the countries Niger, Mali, and Chad occupy the top 3 positions in the index even though their population is very small compared to the rest.

5.3 Using Interactive Maps

Using maps to highlight our data points is not new, the geo-reference has been vital in many industries for a few decades, however, we see that the use of these technologies has expanded in recent years, reaching more and more people eager to better understand their data and publish it in an elegant and friendly way.
In this section, we will review how we can enrich the use of maps making them work together with other views present in our report.
1. Click on Color Map, we’ll find some properties if we click on the button next to the title.

 

2. Select the” Use as Filter…” option.
3. Let’s say that this map takes control only on the last graph ” Birth Rate Related to Total Population”. Change the data on the selection combo box from Filter to Highlight.

 

4. Click on OK to accept the changes.
5. It’s possible to choose between select one or many countries using the toolbar at the top of our map.

 

6. To select a single country, click on the Pin icon, then select Australia and finally click on Done.
7. The result should see as follow. Because we choose to Highlight the results in the destiny visualization, we can wee the Australian Bubble highlighted.

 

8. We can expand this behavior by selecting multiple countries at the same time. Again, we can use the Pin selector or we can use the “Rectangular Selection Tool” (First icon from left to right).
9. Zoom in to South America, and click on the “Rectangular selection tool” to select the entire area.

 

10. As a consequence, we can see now all the points related to the selected countries, are highlighted results in the bubble chart.
11. Click on Save.
The function of serving as a selector or filter for other displays can be used in the full range of graphics available.
Like any other metric, within the maps, it is also possible to use thresholds or traffic lights.
Let’s set up a simple traffic light using a scale of Red-Orange -Green colors which will show us the most populous countries.

1. First, we need to select the visualization you want to edit, in this case, the color
2. Right-click on the metric that we are using ” Population (2014)” in the Color By section.
3. Select “Thresholds”.
4. Set it up as shown in the following image:

 

5. Click on accept and we now have a much more colorful map with extended data ranges.
6. As a result, Countries with the highest population are colored green.
7. Click on Save.

 

5.4 Answering Our Questions

To end this chapter, we’ll try to answer some other questions we have still pending. We will try to use as many graphs as possible in order to give you a wide range of options to use.
Of course, learning this and other tools is not limited to this book but to the very search for new ways to make reports always adding a share of wit and love for what one does. The first thing we will do is to add a new sheet to our report where we will create new visualizations.
Click on the bottom bar to add a new sheet, Sheet 2 in this case.

 

The next question we try to answer is what is the relationship between Country areas (Km2) and their population? Are those countries with more people also the most extensive in the area?

1. Click on the new sheet 2 and add a new visualization, we’ll use a Bubble Chart.
2. In the Vertical axis we will use “Population (2014)”.
3. In the Horizontal axis we will use the metric “Area (km2)”.
4. We add Color By
In this example, we must add some Average Reference Lines for both metrics.
5. Right-click on “Population (2014)”.
6. Select Add Reference Line> Average.

 

7. Repeat the process for metric “Area (km2)”.
8. To finish, add Data Labels > Only text to display the Country name for each bubble.
9. Rename the visualization to “Population vs Area KM2”
10. Click on

Our new Bubble Chart visualization should look like the following image:

 

Result Analysis:

As we can see, in the X-axis we have Area (km2) per Country while on the Y-axis we have the Population 2014 in millions. Each country colored independently and our two axes with the average lines as references.
The country in yellow is China which stands out immediately fulfilling both conditions, a high Population, more than 1,300 million people, and also high Area km2, 9.5 Million Km2. However, to our surprise, it is not the largest country in terms of land area, if they look in the lower right corner, Russia appears as the largest country with more than 17 Million Km2 but with a much smaller population than China, only 142 million people. Therefore, we cannot answer yes to the claim that ALWAYS the country with the largest territorial extension is also the one with the largest number of inhabitants.
The following answer we will try to discover is Which are the countries with the oldest population (60+ years)?

For this scenario, we will use a simple, but always effective Bar Chart.

1. Add new visualization to our sheet and next to our Bubble Chart.
2. Select Bar Chart on the Visualization Gallery.
3. Double click on the” Country” attribute to add it and double click on the” Age 60+”

 

4. By default, the chart orientation is horizontal, i.e., with Countries as the x-axis.
5. To change this, we must click on the display advanced options icon and select the ” swap” option OR in the Visualization EDITOR click on the swap icon  

 

6. Now we see that the axis has moved to the left.
7. To order the bars from highest to lowest, use the ” Sort Descending” option by right-clicking on the metric that we are using directly on the visualization or, again on the visualization Editor.
8. Change the “Aged 60+” metric number format to Percentage with no-decimal points.
9. To improve it, we can add a color code adding Country to the Color by
10. Rename the visualization as “Top Countries with Aged 60+”
11. Click on Save.

Our preview result shows that Japan is the first country of the list with 33% of the population being on average of 60 years old or more. The list is extensive and we want to focus only on those countries with the most elderly average age, something like the Top countries.
To accomplish this, we must create a rank over the “Aged 60+” metric which will then allow us to filter only those between 1 and 10.

1. On the “Aged 60+” metric, right-click and select ” Create Metric…” to open the Metric Editor.
2. Use the function search box to find the Rank function.
3. Once you have found it, click on the button next to the name of the function EDIT.

4. The next screen will allow us to select the based metric to calculate the Rank, which will be “Aged 60+” and also define the Rank Order among other options.
5. Select Descending as the Rank Order.

 

6. Click on Add
7. Rename the new Metric as ” Rank Aged 60+” and click Save.
In order to test our new metric, add a new visualization and use a Grid.
8. Add Country, “Aged 60+” and “Rank Aged 60+” to the grid.

 

9. You should see that Japan with 33% of the population with “Aged 60+” is at the top of our ranking.
10. The following is to assign the new metric to the filter area and make it have an impact only on the “Top Countries with Aged 60+” visualization chart.
11. In the filter area, change the display style to ” Qualification” and select ” Less than or equal” and add the value 5.
12. Press Enter to apply the changes to the filter.
13. Now Bar Chart has been reduced significantly leaving only the Top 5 countries.
14. Finally, we will remove the Grid visualization at the right.
15. To be more precise with the information provided, we add a Value Label to the bars. To do this, right-click on any of them and select Data labels> Only Value.
16. Click on Save.
17. As we can see, this new filter has an impact only on the right Bar Chart even if we change the value to Top 10 or Top 35, the result will be the same.

 

As we can infer, 3 countries on the list are sharing the same result of 27% of their population with Ages of 60 or more. Our last analysis it will answer if there a Direct relationship between Population and Fertility Ratio by Country.
To do this, we must add a new visualization to our sheet and use Combo Chart.

1. Click on Insert Visualization
2. Select Combo Chart from the Visualization Gallery.
3. Add Country attribute into the Horizontal
4. “Fertility Rate” and “Population (2014)” metrics into the Vertical section.

 

5. Rename the new visualization as “Population vs Fertility Rate per country”.
6. Click on Save.
7. On the visualization properties, select Shapes and Data labels from the first combo box and “Population (2014)” on the Shape formatting combo box.
8. Change the shape color for Population (2014) to orange or some similar color.

 

9. Change the selection to Reference Lines and select Fertility Rate.
10. Click on Add Average and select black color for this line.
11. Click on Add Maximum and select a green color for this line.
12. Click on Add Minimum and select red color for this line.

 

13. In the visualization Editor Panel, right-click on “Population (2014)” and select Change Shape> Bar
14. Right-click on “Population (2014)” and select Sort Descending
15. Position the new visualization at the bottom area of our sheet.
16. Add Data labels > Only Value.
17. Click on Save.

 

Result Analysis:

Analyzing this last chart, we can realize that there is no direct relationship between the countries with the highest population and their fertility rate. Something interesting is the most populous countries, such as China and India, have a significantly lower average birth rate, below the average line.

On the contrary, the country with the highest fertility rate which is Congo (11 points) has only 73 million people. It is interesting to work in the process of creating new knowledge, new data that is not obvious at first sight, of course, these examples are simple ones and we’re not discovering anything new, but what I’ve been trying to achieve here though is for the reader to understand the process of Data Discovery and be able to apply it in the future.

The most important thing is to ask those questions that no one does, those that will make the difference between what we believe we know and what is actually true using data.

The MicroStrategy file we have created for these examples is available for download in the following link:
HTTP://BIT.LY/1LDQEWR

6. Sharing My Reports

One of the basic features of any business intelligence tool is to have the ability to export and share the reports created. The ability to distribute our results will perhaps enable us to meet one of the requirements of the standard when working on corporate projects, exposing our findings to those making decisions within the organization. In MicroStrategy 10, the options to distribute reports are varied and the options we will use will depend on our particular needs.

6.1 Sharing Reports With Other Microstrategy Users

The first thing to check is the option to share our report with other MicroStrategy 10 users.
For this, we only need to keep our report in a location on our computer, selecting the ” Save As” option.
This new file will have the “.mstr” extension and should simply be sent to other users using MicroStrategy desktop for them to be able to use/modify it at will.

 

6.2 Sharing Using Pdf Or Images

The next option will allow us to export the sheet that is active at the moment of the action, to a PDF file or a PNG image.
1. Open your report
2. Go to the menu bar and select File> Export

 

3. Select PDF or Image
4. Assign a name to your file.
5. Browse to the location where you want to save the new file and press Save.

6.3 Sharing Individual Visualizations

This option will allow us to export a particular view to 3 different formats:

• Excel
• PDF
• Data (CSV)

1. Click on the top right of the visualization icon you wish to export.

 

2. Click on Export choosing one of the options (as an example we will export to excel)
3. Select the preferred name and location of the new file
4. Click Save
Finally, locate the file and make sure the data has been successfully created.

 

Note: At this point, with the first version of MicroStrategy 10 it’s not possible to export the sheets of a file at the same time nor can export all displays to a single file.