In this article by Dmitry Anoshin, Himani Rana, and Ning Ma, the authors of the book, Mastering Business Intelligence with MicroStrategy, we are going to talk about MicroStrategy 10 which is one of the leading platforms on the market, can handle all data analytics demands, and offers a powerful solution. We will be discussing the different concepts of MicroStrategy such as its history, deployment, and so on.
(For more resources related to this topic, see here.)
Meet MicroStrategy 10
MicroStrategy is a market leader in Business Intelligence (BI) products. It has rich functionality in order to meet the requirements of modern businesses. In 2015, MicroStrategy provided a new release of MicroStrategy, version 10. It offers both agility and governance like no other BI product. In addition, it is easy to use and enterprise ready. At the same time, it is great for both IT and business. In other words, MicroStrategy 10 offers an analytics platform that combines an easy and empowering user experience, together with enterprise-grade performance, management, and security capabilities. It is true bimodal BI and moves seamlessly between styles:
- Data discovery and visualization
- Enterprise reporting and dashboards
- In-memory high performance BI
- Scales from departments to enterprises
- Administration and security
MicroStrategy 10 consists of three main products: MicroStrategy Desktop, MicroStrategy Mobile, and MicroStrategy Web.
MicroStrategy Desktop lets users start discovering and visualizing data instantly. It is available for Mac and PC. It allows users to connect, prepare, discover, and visualize data. In addition, we can easily promote to a MicroStrategy Server. Moreover, MicroStrategy Desktop has a brand new HTML5 interface and includes all connection drivers. It allows us to use data blending, data preparation, and data enrichment. Finally, it has powerful advanced analytics and can be integrated with R.
To cut a long story short, we want to notice main changes of new BI platform. All developers keep the same functionality, the looks as well as architect the same. All changes are about Web interface and Intelligence Server. Let’s look closer at what MicroStrategy 10 can show us.
MicroStrategy 10 expands the analytical ecosystem by using third-party toolkits such as:
- Data visualization libraries: We can easily plug in and use any visualization from the expanding range of Java libraries
- Statistical toolkits: R, SAS, SPSS, KXEN, and others
- Geolocation data visualization: Uses mapping capabilities to visualize and interact with location data
MicroStrategy 10 has more than 25 new data sources that we can connect to quickly and simply. In addition, it allows us build reports on top of other BI tools, such as SAP Business Objects, Cognos, and Oracle BI. It has a new connector to Hadoop, which uses the native connector. Moreover, it allows us to blend multiple data sources in-memory.
We want to notice that MicroStrategy 10 got reach functionality for work with data such as:
- Streamlined workflows to parse and prepare data
- Multi-table in-memory support from different sources
- Automatically parse and prepare data with every refresh
- 100+ inbuilt functions to profile and clean data
- Create custom groups on the fly without coding
In terms of connection to Hadoop, most BI products use Hive or Impala ODBC drivers in order to use SQL to get data from Hadoop. However, this method is bad in terms of performance. MicroStrategy 10 queries directly against Hadoop. As a result, it is up to 50 times faster than via ODBC.
Let’s look at some of the main technical changes that have significantly improved MicroStrategy. The platform is now faster than ever before, because it doesn’t have a two-billion-row limit on in-memory datasets and allows us to create analytical cubes up to 16 times bigger in size. It publishes cubes dramatically faster. Moreover, MicroStrategy 10 has higher data throughput and cubes can be loaded in parallel 4 times faster with multi-threaded parallel loading. In addition, the in-memory engine allows us to create cubes 80 times larger than before, and we can access data from cubes 50% faster, by using up to 8 parallel threads. Look at the following table, where we compare in-memory cube functionality in version 9 versus version 10:
Number of rows
Any schema, tabular or multiple sets
In order to make the administration of MicroStrategy more effective in the new version, MicroStrategy Operation Manager was released. It gives MicroStrategy administrators powerful development tools to monitor, automate, and control systems. Operations Manager gives us:
- Centralized management in a web browser
- Enterprise Manager Console within Tool
- Triggers and 24/7 alerts
- System health monitors
- Server management
- Multiple environment administration
MicroStrategy 10 education and certification
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:
- MicroStrategy 10 Certified Associated Analyst
- MicroStrategy 10 Certified Application Designer
- MicroStrategy 10 Certified Application Developer
- MicroStrategy 10 Certified Administrator
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:
- 1991: Released first BI product, which allowed users to create graphical views and analyses of information data
- 2000: Released MicroStrategy 7 with a web interface
- 2003: First to release a fully integrated reporting tool, combining list reports, BI-style dashboards, and interface analyses in a single module.
- 2005: Released MicroStrategy 8, including one-click actions and drag-and-drop dashboard creation
- 2009: Released MicroStrategy 9, delivering a seamless consolidated path from department to enterprise BI
- 2010: Unveiled new mobile BI capabilities for iPad and iPhone, and was featured on the iTunes Bestseller List
- 2011: Released MicroStrategy Cloud, the first SaaS offering from a major BI vendor
- 2012: Released Visual Data Discovery and groundbreaking new security platform, Usher
- 2013: Released expanded Analytics Platform and free Analytics Desktop client
- 2014: Announced availability of MicroStrategy Analytics via Amazon Web Services (AWS)
- 2015: MicroStrategy 10 was released, the first ever enterprise analytics solution for centralized and decentralized BI
We know only one way to master MicroStrategy, through practical exercises. Let’s start by downloading and deploying MicroStrategy 10.2.
Overview of training architecture
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:
Red Hat Linux 6.4: Used for deploying the web server and Intelligence Server.
Windows machine: Uses MicroStrategy Client and Oracle database.
Virtual machine with Hadoop: Ready virtual machine with Hadoop, which will connect to MicroStrategy using a brand new connection.
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.
Creating of Red Hat Linux virtual machine
Let’s create a virtual machine with Red Hat Linux, which will host our Intelligence Server:
- Go to http://www.redhat.com/ and create an account
- Go to the software download center: https://access.redhat.com/downloads
- Download RHEL: https://access.redhat.com/downloads/content/69/ver=/rhel—7/7.2/x86_64/product-software
- Choose Red Hat Enterprise Linux Server
- Download Red Hat Enterprise Linux 6.4 x86_64
- Choose Binary DVD
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 MicroStrategyClient 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
The project design process
Project design is not just about creating a project in MicroStrategy architect; it involves several steps and thorough analysis, such as how data is stored in the data warehouse, what reports the user wants based on the data, and so on. The following are the steps involved in our project design process:
Logical data model design
Once the user have business requirements documented, the user must create a fact qualifier matrix to identify the attributes, facts, and hierarchies, which are the building blocks of any logical data model.
An example of a fact qualifier is as follows:
A logical data model is created based on the source systems and designed before defining a data warehouse. So, it’s good for seeing which objects the users want and checking whether the objects are in the source systems. It represents the definition, characteristics, and relationships of the data. This graphical representation of information is easily understandable by business users too. A logical data model graphically represents the following concepts:
- Attributes: Provides a detailed description of the data
- Facts: Provide numerical information about the data
- Hierarchies: Provide relationships between data
Data warehouse schema design
Physical data warehouse design is based on the logical data model and represents the storage and retrieval of data from the data warehouse. Here, we determine the optimal schema design, which ensures reporting performance and maintenance. The key components of a physical data warehouse schema are columns and tables:
- Columns: These store attribute and fact data. The following are the three types of columns:
- ID column: Stores the ID for an attribute
- Description column: Stores text description of the attribute
- Fact column: Stores fact data
- Tables: Physical grouping of related data. Following are the types of tables:
- Lookup tables: Store information about attributes such as IDs and descriptions
- Relationship tables: Store information about relationship between two or more attributes
- Fact tables: Store factual data and the level of aggregation, which is defined based on the attributes of the fact table. They contain base fact columns or derived fact columns:
- Base fact: Stores the data at the lowest possible level of detail.
- Aggregate fact: Stores data at a higher or summarized level of detail.
Mobile server installation and configuration
While mobile client is easy to install, mobile server is not. Here we provide a step-by-step guide on how to install mobile server:
Download MicroStrategyMobile.war. Mobile server is packed in a WAR file, just like Operation Manager or Web:
Copy MicroStrategyMobile.war from <Microstrategy Installation folder>/Mobile/MobileServer to /usr/local/tomcat7/webapps. Then restart Tomcat, by issuing the ./shutdown.sh and ./startup.sh commands:
Connect to the mobile server. Go to http://192.168.81.134:8080/MicroStrategyMobile/servlet/mstrWebAdmin. Then add the server name localhost.localdomain and click connect:
Configure mobile server. You can configure (1) Authentication settings for the mobile server application; (2) Privileges and permissions; (3) SSL encryption; (4) Client authentication with a certificate server; (5) Destination folder for the photo uploader widget and signature capture input control.
Performing Pareto analysis
One good thing about data discovery tools is their agile approach to the data. We can connect any data source and easily slice and dice data. Let’s try to use the Pareto principle in order to answer the question: How are sales distributed among the different products?
The Pareto principle states that, for many events, roughly 80% of results come from 20% of the causes. For example, 80% of profits come from 20% of the products offered. This type of analysis is very popular in product analytics.
In MicroStrategy Desktop, we can use shortcut metrics in order to quickly make complex calculations such as running sums or a percent of the total.
Let’s build a visualization in order to see the 20% of products that bring us 80% of the money:
Choose Combo Chart.
Drag and drop Salesamount to the vertical and Englishproductname to the horizontal.
Add Orderdate to the filters and restrict to 60 days.
Right-click on Sales amountand choose Descending Sort.
Right-click on Salesamount | ShortcutMetrics | Percent Running Total.
Drag and drop Metric Names to the Color By.
Change the color of Salesamount and Percent Running Total.
Change the shape of Percent Running Total.
As a result, we get this chart:
From this chart we can quickly understand our top 20% of products which bring us 80% of revenue.
Splunk and MicroStrategy
MicroStrategy 10 has announced a new connection to Splunk. I suppose that Splunk is not very popular in the world of Business Intelligence. Most people who have heard about Splunk think that it is just a platform for processing logs. The answers is both true and false. Splunk was derived from the world of spelunking, because searching for root causes in logs is a kind of spelunking without light, and Splunk solves this problem by indexing machine data from a tremendous number of data sources, starting from applications, hardware, sensors, and so on.
What is Splunk
Splunk’s goal is making machine data accessible, usable, and valuable for everyone, and turning machine data into business value. It can:
- Collect data from anywhere
- Search and analyze everything
- Gain real-time Operational Intelligence
In the BI world, everyone knows what a data warehouse is.
Creating reports from Splunk
Now we are ready to build reports using MicroStrategy Desktop and Splunk. Let’s do it:
Go to MicroStrategy Desktop, click add data, and choose Splunk
Create a connection using the existing DNS based on Splunk ODBC:
Choose one of tables (Splunk reports):
Add other tables as new data sources.
Now we can build a dashboard using data from Splunk by dragging and dropping attributes and metrics:
In this article we looked at MicroStrategy 10 and its features. We learned about its history and deployment. We also learnt about the project design process, the Pareto analysis and about the connection of Splunk and MicroStrategy.
Resources for Article:
- Stacked Denoising Autoencoders [article]
- Creating external tables in your Oracle 10g/11g Database [article]
- Clustering Methods [article]