Book Image

Business Intelligence with MicroStrategy Cookbook

By : Davide Moraschi
Book Image

Business Intelligence with MicroStrategy Cookbook

By: Davide Moraschi

Overview of this book

Business intelligence is becoming more important by the day, with cloud offerings and mobile devices gaining wider acceptance and achieving better market penetration. MicroStrategy Reporting Suite is a complete business intelligence platform that covers all the data analysis needs of an enterprise. Scorecards, dashboards, and reports can be explored and delivered on desktop, the Web, mobile devices, and the Cloud. With the latest Visual Insight tool, MicroStrategy brings the power of BI to the business users, allowing them to discover information without the help of IT personnel. Business Intelligence with MicroStrategy Cookbook covers the full cycle of a BI project with the MicroStrategy platform, from setting up the software to using dashboards in the cloud and on mobile devices. This book uses step-by-step instructions to teach you everything from the very basics to the more advanced topics. We will start by downloading and installing the software and a well-known sample SQL Server database. Then, one brick at a time, we will construct a fully-featured BI solution with a web interface, mobile reporting, and agile analytics. The chapters are ordered by increasing difficulty, and each one builds on top of the preceding chapter so that the learning process is progressive. The examples given in this book are practical, and you will be able to see the immediate result of your efforts. We will first cover setting up the platform, including the creation of the metadata and the different objects that are part of a BI project: tables, attributes, and metrics. Then, we take a look at how to create and analyze reports, charts, documents, and dashboards, as well as how to manipulate data with the desktop application, the web Interface, and an iPad device. The last part of the book is dedicated to advanced topics like the new agile analytics technology from MicroStrategy, where we cover both Visual Insight and MicroStrategy Cloud Express. Whether you are a database developer, data analyst, or a business user, Business Intelligence with MicroStrategy Cookbook will get you up to speed with one of the most powerful BI platforms on the market with the smallest possible investment of time and money.

Related Content you might be interested in

Current Title:

Small book image
Business Intelligence with MicroStrategy Cookbook
Davide Moraschi
Oct, 2013 | 356 pages
No titles found
Table of Contents (25 chapters)
Business Intelligence with MicroStrategy Cookbook
Business Intelligence with MicroStrategy Cookbook
Credits
Credits
Foreword
Foreword
About the Author
About the Author
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Getting Started with MicroStrategy
Getting Started with MicroStrategy
Introduction
Installing SQL Server 2012 Express LocalDB
Installing SQL Server Native Client 11.0
Installing SQL Server 2012 Command Line Utilities
Setting up the AdventureWorks DW sample database
Installing the .NET Framework 4.0 and the 4.0.2 update
Checking whether IIS is enabled and working
Installing MicroStrategy Suite
Registering the MicroStrategy License
Metadata and data warehouse
Creating ODBC DSN for metadata and data warehouse
Modifying the logon account for the Intelligence Server
Creating the metadata and configuring the Intelligence Server
Opening the MicroStrategy Desktop application
2
The First Steps in a MicroStrategy Project
The First Steps in a MicroStrategy Project
Introduction
Creating an empty project
Setting up a data warehouse connection and selecting tables
Modifying a table structure
Using logical tables to create custom views
Generating constants with SELECT statements
3
Schema Objects – Attributes
Schema Objects – Attributes
Introduction
Attribute forms – ID and DESC
Using functions in an attribute form
Parent-child relationship I
Parent-child relationship II
Other attribute forms
Selecting which forms are displayed
Building Data Explorer Hierarchies
Creating an attribute-only report
Parent-child relationship in a report
Filters on attributes
4
Objects – Facts and Metrics
Objects – Facts and Metrics
Introduction
Creating a simple counter fact and metric
Using SQL View to inspect SELECT statements
Understanding the GROUP BY clause
Adding more facts
Filters on metrics
Creating ranking metrics
Grouping at a different level (level metrics)
Embedding filters inside metrics
Using Metric Join Type in reports
Creating a previous month metric (transformation)
5
Data Display and Manipulation – Reports
Data Display and Manipulation – Reports
Introduction
Going deeper into data with drill down
Manipulating grids – Pivot and page-by
Dynamically adding and removing objects in reports
The bottom line – customizing subtotals
Avoiding missteps – NULL values in facts
How to sort data in grids
Emphasizing numbers with conditional formatting
Printing and exporting reports
Restricting results with view filters
6
Data Analysis and Visualization – Graphs
Data Analysis and Visualization – Graphs
Introduction
Displaying both grid and graph in the same view
Drag-and-drop objects using drop zones
Beautify your chart
Display multiple metrics with dual axis charts
Conditional formatting with thresholds
7
Analysis on the Web – Documents and Dashboards
Analysis on the Web – Documents and Dashboards
Introduction
Setting up MicroStrategy Web
Creating your first "Hello World" document
Adding data to a document – the dataset
Modifying the grouping in a document
Stepping up to dashboards
Using the editable mode to fine-tune the design
Adding interactivity with panels and selectors
Embedding images, HTML, and links
Switching to Adobe Flash mode
8
Dynamic Selection with Filters and Prompts
Dynamic Selection with Filters and Prompts
Introduction
Restricting rows – compare an attribute to another attribute
Restricting rows – evaluate on moving dates
Changing the level of a metric filter
Adding interactivity to filters using prompts
Using prompts as object selectors
9
Mobile BI for Developers
Mobile BI for Developers
Introduction
Setting up the Mobile Server
Setting up a mobile connection
Installing and configuring the mobile client
Adapting content to the iPad screen
Using mobile views in documents
Adding panels to mobile documents
Creating panel selectors
Adding details with pop-up windows
Linking from one document to another
10
Mobile BI for Users
Mobile BI for Users
Introduction
Answering prompts in reports or documents
Sorting, drilling, and paging
Adding notes and sharing
When things go wrong
11
Consolidations, Custom Groups, and Transformations
Consolidations, Custom Groups, and Transformations
Introduction
Creating virtual attributes with consolidations
Aggregating results with custom groups
Creating bands on metrics (custom groups banding)
Month-to-date and year-to-date transformations
12
In-Memory Cubes and Visual Insight
In-Memory Cubes and Visual Insight
Introduction
Creating a cube from an existing report
Building a new cube
Scheduling data refresh
Creating reports and documents based on a cube
Exploring data with Visual Insight
Using the network visualization
13
MicroStrategy Express
MicroStrategy Express
Introduction
Creating an Express account
Importing Excel data
Using filters
Importing cross-tabbed data
Connecting to a RDBMS
Refreshing dashboard data
Sharing a dashboard by e-mail
Solution to Exercises
Solution to Exercises
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Exercise 7
Exercise 8
Exercise 9
Exercise 10
Exercise 11
Exercise 12
Exercise 13
Exercise 14
Exercise 15
Exercise 16
Exercise 17
Exercise 18
Exercise 19
Exercise 20
Exercise 21
Exercise 22
Exercise 23
Exercise 24
Exercise 25
Exercise 26
Exercise 27
Exercise 28
Exercise 29
Exercise 30
Exercise 31
Exercise 32
Exercise 33
Exercise 34
Exercise 35
Where to Look for Information
Where to Look for Information
Online resources
Blogs
Books
Cloudera Hadoop
Cloudera Hadoop
Introduction
Connecting to a Hadoop database
HP Vertica
HP Vertica
Introduction
Connecting to a Vertica database
Index
Index

Creating ODBC DSN for metadata and data warehouse

Now that we have two databases, we need to tell MicroStrategy how to find and how to connect to them. Being in a Windows world, all the connections are done through ODBC, 32-bit ODBC to be correct.

Tip

You should be aware of this if you happen to install MicroStrategy on 64-bit Windows—the connectivity tier runs on 32 bits, the ODBC drivers are 32-bit drivers, and you won't find them in the default 64-bit ODBC Administrator (hint: look in the SysWOW64 folder).

Getting ready

You need to have completed the previous recipe to continue.

How to do it...

We will create the two DSN:

  1. Click on Start | Administrative Tools | Data Sources (ODBC) and select the System DSN tab.

  2. You will find some DSNs already created for you by the setup; we need to create two more (yes, you're right: one for the metadata and one for the data warehouse).

  3. Click on the Add… button, scroll down the list to select SQL Server Native Client 11.0 and click on Finish.

  4. In the Create a New Data Source to SQL Server window, type these values:

    • Name: metadata

    • Server: (localdb)\v11.0

  5. Click on Next and then on Next again.

  6. Click the checkbox labeled Change the default database to and in the drop-down list underneath, select metadata. Leave all the remaining default values and click on Next and on Finish.

  7. Click on Test Data Source… and look at the message: if it says TESTS COMPLETED SUCCESSFULLY!, you're good to go. Click on OK and then on OK again.

  8. Repeat all the steps to create another DSN to the data warehouse, this time with the following values:

    • Name: datawarehouse

    • Server: (localdb)\v11.0

    • Change the default database to: AdventureWorksDW2008R2

How it works...

Every interaction with the relational DBMS is done through ODBC. MicroStrategy ships with a list of out-of-the-box drivers for the most common databases. Nevertheless, you may use other ODBC drivers if so you wish, provided that they work in 32 bits.

There's more...

In our recipes, we will connect to a SQL Server instance, so we need to use the Microsoft provided Native ODBC. If, for example, you use Oracle in your BI environment, you will find the provided Oracle Wire driver being very convenient and light-weight. The company behind the shipped drivers is DataDirect.

Note

You can watch a screencast of this operation at:

Previous Section
End of Section 12
Next Section