Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Business Intelligence with MicroStrategy Cookbook
  • Toc
  • feedback
Business Intelligence with MicroStrategy Cookbook

Business Intelligence with MicroStrategy Cookbook

By : Davide Moraschi
3.8 (11)
close
Business Intelligence with MicroStrategy Cookbook

Business Intelligence with MicroStrategy Cookbook

3.8 (11)
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.
Table of Contents (25 chapters)
close
close
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
chevron up
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

Filters on metrics

In Chapter 3, Schema Objects – Attributes, we learned how to build filters on attributes to restrict the data returned from the data warehouse using a list of elements. Similarly, we can create filters using values of metrics. The difference—as said—is how the SQL sentence will be generated when applying the filter: attribute filters appear in the WHERE clause, while metric filters appear in the HAVING clause. For those of you SQL superstars, I do not need to explain that WHERE restricts before aggregation, while HAVING restricts (of course…) after aggregation.

Getting ready

In the previous recipe we added a new fact table. Before we go on, we should specify the Product FK so that MicroStrategy can JOIN the FactResellerSales table with the Product dimensions. We see from the database ER diagram that FactResellerSales has a ProductKey column that points to the DimProduct table.

So, open the Product attribute (double-click) and modify the ID form to include FactResellerSales...

Unlock full access

Continue reading for free

A Packt free trial gives you instant online access to our library of over 7000 practical eBooks and videos, constantly updated with the latest in tech
Start a 7-day FREE trial
End of Section 7
Next Section
bookmark search playlist font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete