Mastering Business Intelligence with MicroStrategy

Nowadays, we see lots of BI tools. Sometimes, we have to ask "What is the best BI tool?" In order to find the answer, we can look at reports from two main analytical agencies: Gartner and Forrester. Here is the Gartner Magic Quadrant for BI & Analytics Platforms for 2015:

This quadrant shows us the leaders in the market. But do not trust this rating, because it is a kind of marketing. The best way to understand the pros and cons of any BI tool is practically; just download and install a trial version and play with it. Most tools, especially the leading ones, have common functionality or similar features. A good example is the interface of Tableau. It is so good that other companies have tried to reproduce it in their own products.

Every organization uses BI. The question is, just how good are they? Even if the organization does not have BI at all, it at least uses spreadsheets to consolidate numbers. This is also a kind of BI. We can observe many levels of BI maturity. Let us try to demonstrate this, based on four phases shown here:

The first phase is Scattered Reporting, in other words, the organization doesn't have any BI solution and even doesn't try one. Various departments grab data from transaction systems and try to figure out what's going on. We see this situation especially in startups or small companies. It is the most inefficient way to measure business and make decisions.

The next phase is Centralized BI Solution. It is the most common approach nowadays, when organizations build corporate data warehouses, integrate data from internal sources and systems, deploy a BI solution, and try to force everyone to use a single point of truth. It is a long journey to leave this phase, and sometimes it is an endless process because business constantly changes.

The third phase is Big Data Analytics. Despite the fact that the term big data is almost gone, we still use it, because it is a very popular phrase that means lots of unstructured data such as social data, open source data, and so on. In other words, organizations try to enrich their internal data in order to find more insights. As a result, companies are forced to use new technologies such as Hadoop or NoSQL datastores. In addition, companies start to use machine learning technologies and try to make them part of their BI framework.

The last phase is Real-time Mobile Business Insight. This phase means that finally organizations can focus on the speed of delivery of business insights, their mobility, and predictive capabilities.

Of course, this maturity model is not standard, we just tried to structure BI maturity levels based on our experience. MicroStrategy can handle even the final phase, a real competitive advantage.

MicroStrategy 10 offers new training courses that can be conducted offline in a training center, or online at http://www.microstrategy.com/us/services/education. We believe that certification is a good thing on your journey. The following certifications now exist for version 10:

After passing all of these exams, you will become a MicroStrategy 10 Application Engineer. More details can be found here: http://www.microstrategy.com/Strategy/media/downloads/training-events/MicroStrategy-certification-matrix_v10.pdf.

Let us briefly look at the history of MicroStrategy, which began in 1991:

In order to master MicroStrategy and learn about some BI considerations, we need to download the all-important software, deploy it, and connect to a network. During the preparation of the training environment, we will cover the installation of MicroStrategy on a Linux operating system. This is very good practice, because many people work with Windows and are not familiar with Linux, so this chapter will provide additional knowledge of working with Linux, as well as installing MicroStrategy and a web server. Look at the training architecture:

There are three main components:

In the real world, we should use separate machines for every component, and sometimes several machines in order to run one component. This is called clustering. Let's create a virtual machine.

Let's create a virtual machine with Red Hat Linux, which will host our Intelligence Server:

Now we can create a virtual machine with RHEL 6.4. We have several options in order to choose the software for deploying virtual machine. In our case, we will use a VMware workstation.

Before starting to deploy a new VM, we should adjust the default settings, such as increasing RAM and HDD, and adding one more network card in order to connect the external environment with the MicroStrategy client and sample database. In addition, we should create a new network.

When the deployment of the RHEL virtual machine is complete, we should activate a subscription in order to install the required packages. Let us do this with one command in the terminal:

# subscription-manager register --username <username> --password 
    <password> --auto-attach

Performing prerequisites for MicroStrategy 10

According to the installation and configuration guide, we should deploy all necessary packages. In order to install them, we should execute them under the root:

# su
# yum install compat-libstdc++-33.i686
# yum install libXp.x86_64
# yum install elfutils-devel.x86_64
# yum install libstdc++-4.4.7-3.el6.i686
# yum install krb5-libs.i686
# yum install nss-pam-ldapd.i686
# yum install ksh.x86_64

Moreover, we should change the parameters of the kernel:

# nano/etc/sysctl.conf
kernel.sem=250 32000 32 2048
kernel.shmmni = 4096
vm.max_map_count=5242880

Then, save and reboot.

In order to download MicroStrategy software, we should already have an account on the MicroStrategy Resource Portal at https://resource.microstrategy.com, or we can create one using a corporate email address. Even if we do not have a license for MicroStrategy, we can obtain one for 30 days. That is enough to finish this book and learn the new features of MicroStrategy 10.

MicroStrategy 10.2 is available, and we are going to use this version in the book.

Let's download distributive 10.2.0008.0052_10.2_GA_Linux.tar and put it on the virtual machine with Linux 10.2.0008.0052_10.2_GA_Linux.tar.

Let's create a folder for MicroStrategy using the root user and change its rights in order to extract the archive into this folder:

# mkdir mstr10
# chmod 777 mstr10

Then we should extract the MicroStrategy distributive into our new folder. We can use the default Archive Manager.

After successful extraction, we should create four folders for MicroStrategy, in order to deploy the software and write the installation logs.

Using the root user, we can run the following commands:

# mkdir /mstr10/microstrategy
# mkdir /mstr10/microstrategy_home
# mkdir /mstr10/microstrategy_logs
# mkdir /mstr10/microstrategy_health_agent

# cd /mstr10/Installations/QueryReportingAnalysis_Linux
# ./setup.sh
user name Microstrategy
name masteringbi

After successful installation of MicroStrategy 10.2, we should set up a MicroStrategy repository and connect it to the Intelligence Server. In our case, we will use a PostgreSQL database on the RHEL virtual machine for the repository.

# yum install postgresql-server
# service postgresql initdb
# chkconfig postgresql on
# service postgresql start
# s
# psql

# psql
# CREATE DATABASE mstrmd;
# CREATE USER mstr PASSWORD 'happy2016';
# GRANT ALL ON DATABASE mstrmd TO mstr;

# \c mstrmd
# CREATE TRUSTED LANGUAGE plpgsql;

# cd /var/lib/pgsql/data
# nano pg_hba.conf

# service postgresql restart

Creating a metadata data source

Using the MicroStrategy connectivity wizard, we can create a new DSN. In order to open the wizard, we should run the following commands:

# cd /mstr10/microstrategy_home/bin
# ./mstrconnectwiz

Let's choose PostgreSQL ODBC driver and fill in the form with our credentials:

MicroStrategy has a configuration file that keeps all DSNs. We can create a new DSN directly in this file. We can look at our new DSN like this:

# vi /mstr10/microstrategy_home/odbc.ini

Here is our DSN metadata. In addition, we can configure the parameters of our connection in order to increase performance.

Creating metadata tables

In order to create metadata tables in the brand new database, we should run Configuration Wizard:

# cd /mstr10/microstrategy_home/bin
# ./mstrcfgwiz

During this process, wizard will automatically:

• Create metadata tables and history list tables

• Configure Intelligence Server

Tip

MicroStrategy metadata is a core piece of software. In other words, the number of tables in a database that contain a definition of all MicroStrategy objects.

In Configuration Wizard, we should create a new database and choose our DSN:

The next step is the configuration of Intelligence Server using fresh metadata tables. We can use masteringbi as a server definition. After successful configuration we can close wizard and deploy MicroStrategy Web.

We need a web server in order to deploy MicroStrategy Web and other components, such as Mobile Server and Operations Manager. For this book, we have chosen the Tomcat web server as our web application server.

# cp /mstr10/microstrategy/OperationsManager/MicroStrategyWeb.war 
      /usr/local/tomcat7/webapps
# cp /mstr10/microstrategy/OperationsManager/MicroStrategyOM.war 
      /usr/local/tomcat7/webapps

# /usr/local/tomcat7/bin/shutdown.sh
# /usr/local/tomcat7/bin/startup.sh

# nano /etc/sysconfig/iptables

-A INPUT -m state --state NEW -m tcp -p tcp --dport 8080 -j ACCEPT

-A INPUT -m state --state NEW -m tcp -p tcp --dport 34952 -j ACCEPT

# service iptables restart

According to our training architecture, we will use at least two machines. One is a Windows machine for client tools and the Oracle database, and a Linux machine for Intelligence Server and the PostgreSQL repository database. Let's look at how we can quickly create a network. This knowledge is very important for BI guys, because usually BI software has complex architecture and communicates with each other through the network. In our small example, the reader can learn basic considerations for building a network between several machines. Let's build a network using VMware:

Now we should change the network for Red Hat also. Let's do it:

As a result, the Linux and Windows machines now live on one network and can easy communicate with each other. Using this approach, we can connect other machines that run other software. For example, we could run Oracle on a separate machine. In addition, we can create a cluster of several machines running MicroStrategy.

Now we can install the MicroStrategy Client tools and connect Intelligence Server.

One of the simplest tasks is installing the client tools on the Windows machine. We should install the following software:

We should connect to our Intelligence Server, which is based on RHEL. Let's run Developer on port 34952 with the server name as the IP address 192.168.81.134. There are not any projects yet, but soon we will create one good project using our great expertise.

For our book, we decided to use Oracle 11g because it is the most popular RDBMS in the world. We can download Oracle from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html.

In our case, we will install it on the same machine as MicroStrategy, in order to save resources. In the real world, we should use a separate server. The installation process is pretty simple and straightforward. Just download the software and run setup.exe.

We want to share some important skills from experienced BI developers. These cover several areas:

There is no secret to how to master BI. There is only one way - constantly learn through reading and technical exercises. www.packtpub.com offers us plenty of technical books that can help us to solve any technical issues.

The most critical skill for a BI developer is to quickly find a solution using Google. It is impossible to store all relevant information in our head, but we can easily Google it and find the right solution. Do not hesitate to ask Google if you are struggling.

One hot topic is the happiness of business users. Usually they are not technical guys and often struggle with report development. Another difficulty is that there are many attributes and metrics in the data warehouse, and sometimes we can't use them in one query because it could produce cross joins and even crash the database. There is only one solution - constantly train them and give them the opportunity to learn the technical and business sides of BI solutions. As a result, the BI department should develop two streams of training - business and technical. Technical training relates to BI software. For example, our book is an advanced training for MicroStrategy. At the same time, business users should learn different business cases. For example, how they can build reports in order to compare revenue for 2014 and 2015 across all branches in Canada?

The main challenge of this training is to motivate business users. But there is a lack of time to learn. As a result, they make wrong decisions on inaccurate data. In order to involve them in the training process, we want to share one of our success stories.

One of the authors implemented BI Academy. It was an internal brand; users who successfully finish the training and pass the internal exam get a branded T-shirt:

In order to make the training process fun, the author used special software that offers the opportunity to create cartoons:

These guys were very popular because they helped to solve real business problems. Another piece of advice is to contact heads of department and encourage them to make this online training mandatory for employees. There are some training courses based on this approach on YouTube: https://www.youtube.com/channel/UCAGzL89m1L3c675hn7DE2Uw

Or you can see the MicroStrategy example here:

https://www.youtube.com/channel/UCLDoF-D_CH51CfGVEn8PBvQ

The final thing that is good to have in an organization is a BI portal. When companies run BI solutions, they do it for users. Usually, BI users are not technical. The best practice is to build BI and integrate it into an intranet website. For example, in the attachment to this chapter, there is a file called sweet home.html. It is not a BI portal, but it demonstrates the idea very well. It helps business users and developers to quickly get the latest news about BI changes and innovations. Moreover, it links to BI environments and training. For example, the company can use Microsoft SharePoint because it is easy to deploy, and we can easily integrate it with MicroStrategy and Office products.