Microsoft Excel is the most common tool for business reporting and analytics. Despite its excellent capabilities in that role, the final objective of Microsoft Excel is not concerned with its Business Intelligence (BI) capabilities. There is a set of specialized platforms and tools for that purpose, and MicroStrategy is one of the best providers for BI.
Gartner defines BI as:
"Business intelligence (BI) is an umbrella term that includes the applications, infrastructure and tools, and best practices that enable access to and analysis of information to improve and optimize decisions and performance."
In this chapter, we will learn the reporting concepts for data manipulation and their implementation in the MicroStrategy platform in a simple and practical way. Additionally, we will learn how to use the MicroStrategy platform to its maximum strength. At the end, using MicroStrategy will be as easy as using Microsoft Excel. Please keep in mind that the ultimate goal is to produce BI reports in a do-it-yourself schema without the specialized support from an IT Guru or technical driven manuals and instructions.
Let's start with the key concepts that we will use in this chapter and that are needed to start building the BI reports.
The manner in which the information is represented in our reports is crucial in order to generate the desired impact on our BI report audiences. The amount of data, the business question that we need to solve will define the kind of visualization that we use. We will be using Visual Insight that is an integral part of MicroStrategy web application and Cloud platform service during our demonstrations and exercises.
The following figure shows a graphical representation of information:
By graphically representing the information in one single view, we can respond to business questions and detect trends and behavior of the information. In this case, we are looking at sales by region, where region 2 is the most profitable.
The following screenshot gives a detailed account of an object that is ideal for data analysis (store in this example) in order to detect specific behavior and review specific values in a grid representation:
We can use a combination of graphs and grids in one single view for a quick review of the data without the need to navigate to separate pages; this kind of single view is called a dashboard. So far, the concept is quite similar to Microsoft Excel; however, the main difference is the way in which to produce and design this visualization in MicroStrategy and how to exploit, analyze, and share information with key personnel responsible for making decisions based on the information analysis. But Excel and MicroStrategy are not separate alternatives; in fact, they are complementary and MicroStrategy offers a level of integration from and to the Excel data.
The MicroStrategy platform has the advantage of having more control of the data in a centralized and secure infrastructure instead of the end user's personal computer with tools like Excel. Another key advantage is the management of the official (institutional) formulas and calculations of Key Performance Indicators (KPI) for our reports instead of each user generating and defining their own in their local Excel files. More importantly, the ability to share the latest version to the key personnel through different channels such as e-mail, tablets, smart phones, and the Web and making changes in the data, reports, and KPI without affecting the model integrity and assuring all the people have the same version of the data is another feature of MicroStrategy.
In the following table, we will analyze a set of characteristics of Excel and MicroStrategy:
For any given requirement, assess if Excel is the best solution or the MicroStrategy platform is better. For example, MicroStrategy allows users to filter, sort, and interact with a report via a web browser and share it to mobile devices. Excel is ideally suited for joining data from multiple data sources for one-off analysis.
The first step is to define the business need that we need to address; we already defined it in Chapter 2, Mapping Typical Business Needs. The next step is to get familiarized with the MicroStrategy interface. After we log in to the MicroStrategy Cloud platform, we will see a single-page interface, and the main screen that we will use is explained in the following screenshot:
The following are the main options of our menu that we will discuss in this chapter, thus resolving business needs instead of explaining the options one by one:
- The area highlighted as 1 shows the activate or deactivate panel on the top of the window provides us with the Tools option
- The area highlighted as 2 shows the My Data panel shows the attributes and metrics loaded in our model; it also includes the option to add and modify new metrics
- The area highlighted as 3 shows the Filters panel allows us to add and configure filters for data selection, search, and slide
- The area highlighted as 4 shows the Grid panel allows us to add rows, columns, metrics, and format our data in a grid or graph view
- The area highlighted as 5 shows the Grid section shows the data that is to be analyzed and allows us to arrange it using panels
- The area highlighted as 6 shows the action panel at the bottom allows us to cancel and load data and save our model
Now, it is time to fulfill the first business need: identifying our top five/bottom five stores in terms of revenue (for all the options and menus that we use to design our report; later in this chapter, we will learn the details of the options during this exercise). Perform the following steps:
- Proceed with the MicroStrategy Cloud service; sign in and select the dataload model already loaded in the platform and the default Layout that is created, as shown in the following screenshot:
- Then, select the filters from the Filters panel by clicking on the drop-down button [
] and choose the Qrt1, Qrt2, Qrt3, Qrt4 options for Operative Income, Net Income, Profit, and Sales respectively, as shown in the following screenshot:
- Now, click on any header of the grid, navigate to Add to Grid, and select the same metrics already chosen in the prior step, as shown in the following screenshot:
We already have all the active filters in our main model, and the grid shows all the related information. Now, we need to calculate the total year profit, that is, the result of the sum of Qrt1 Profit, Qrt2 Profit, Qrt3 Profit, and Qrt4 Profit. MicroStrategy includes the option of calculated metrics that is needed in our case; the procedure to enable it is as follows:
-
In the My Data panel, select Insert New Metric… by clicking on the add button [
] and create a new metric, as shown in the following screenshot:
- An editor screen will appear; change the name of the metric to
Year Profitand from the Available objects panel, select Qrt1 Profit, Qrt2 Profit, Qrt3 Profit, and Qrt4 Profit as shown in the following screenshot:
- Click on Ok and the new metrics will appear at the end of the grid; select the Move Left option from the metric after the sales metric in the grid.
- In the Filters panel, add the Row Count filter; select the Rank highest option and the Qualification option in Display Style. Set it as Less than or Equal to and then type
5. Next in the grid, navigate to the Year Profit column and select the Sort Descending option; now, the grid will show the top five performers, as shown in the following screenshot:
Congratulations! We have already designed our first BI report in less than 10 minutes. This report shows the top five stores in terms of yearly profit; now, we need to format the report. The report is about profit; therefore, we need to add relevant data related to the profit.
-
In the first place, we need to show the profit by quarter; the best way to do so is via the drag-and-drop option on the Grid panel. Select the Profit metric by quarter and arrange it beneath the year profit metric. For each quarter profit metric, you can define the number format (click on the metric button [
] and select Format), select the currency for profit metrics as well as the need to arrange and format the sales, operative income, and net income metrics.
- The grid will now look like the following screenshot:
The report is almost ready; now, we need to add information related to the region (geographical zone) and the date when the store opens:
- In the Grid panel in the rows section, add the Region, Market Share (format to percentage), and Open Year objects.
- As the final step, rename Layout 1 to
Top 5 Report(click on the Layout 1 label at the top of the screen and select Rename) and save the report.
We already know the most profitable store: DG997 from Region 1, opened in 2001 with Market Share of 12%. In order to visualize the complete information, the next step is to generate a graph based on our first report. The steps are as follows:
- Copy the report to New Panel as shown in the following screenshot:
- Switch to New Panel and select Change Visualization….
- Select a bubble (metrics on y – x axis) as shown in the following screenshot:
- In the Graph Matrix panel in the Y Axis section, add a new formula: Total sales, that is, the sum of Qrt1 sales to Qrt4 sales.
-
Rename the panel (click on the drop-down button [
] on the top and select Rename) and type First Graphics. - Save the panel. The resulting graphic is similar to the following screenshot:
This the first graph that we create in the platform, but MicroStrategy offers wide options for data visualization for different purposes. Some graphs require specific data in order to work. When you work with graphics, a new panel appears in the main MicroStrategy interface, depending on the type of graphics we are using.
In Chapter 4, Scorecards and Dashboards – Information Visualization, in the visualization objects section, we will use all the graphics options and alternatives for dashboard creation. Please keep in mind that the MicroStrategy graphs option provides interactive visualization capabilities that enable decision makers to dynamically explore ideas, investigate patterns, uncover hidden facts, and share those insights across the enterprise for better decision making. An extensive suite of customizable graphical options presents information and insights that are not easily detected in grid formats.
The main features of MicroStrategy do-it-yourself schema are as follows:
- Provides self-service access to dynamic, visualization environments
- Reduces the overdependence on IT for ad-hoc requests
- Enables sharing of information via dashboards