Business Intelligence Overview

-          Definition

-          Architecture

-          Source systems /OLTP

-          ETL process

-          Data Warehouses /OLAP

-          OLTP vs. OLAP

-          ODS and Data Marts

-          Data Warehouse Design Approaches

-          Dimensional Modeling

-          From Enterprise models to Dimensional models

-          Schema Types: Star, Snowflake, Fact Constellation

-          Conclusion

Definition
The term business intelligence (BI) refers to technologies, applications and practices for the collection, integration, analysis, and presentation of business information. The purpose of business intelligence is to support better business decision making. BI systems provide historical, current, and predictive views of business operations, most often using data that has been gathered into a data warehouse or a data mart and occasionally working from operational data.

BI enables enterprises

• Measure performance and trends
• To use analytic information strategically
• Unlock the value of its information
• Identify opportunities
• Improve efficiency
• Do competitive analysis..
• to find the Cause
• data Mining
• Etc.

Examples

• Cause & predictive analysis: Credit cart annual fee
• Performance and trends: Region total sales / our sales
• Competitive: Our sales / competitor sales in a particular region or a location, etc,
• Right timing: Bank customer accounts (pattern changes)
• Data mining:  market basket analysis

Architecture

Architecture cont…

Typical BI architecture has the following components:

•  A source system, also called Operational system—typically an online transaction processing (OLTP) system, but other systems or files that capture or hold data of interest are      also possible.

•  An extraction, transformation, and loading (ETL) process.

•  A data warehouse—typically an online analytical processing (OLAP) system.

•  A business intelligence platform such as Microstrategy.

Source Systems (OLTP)

An operational system is a term used in data warehousing to refer to a system that is used to process the day-to-day transactions of an organization. These systems are designed so processing of day-to-day transactions is performed efficiently and the integrity of the transactional data is preserved.

                Sometimes operational systems are referred to as operational databases, transaction processing systems, or on-line transaction processing systems (OLTP). In OLTP — online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity.

Source Systems examples

-          Account transactions in a Bank

-          Sales transactions in a Retail outlet.

-          Inventory management transactions in a warehouse

-          Workforce management transactions such as attendance, vacations, overtime tracking, etc.

-          Operational expenditure systems

-          External sources such as industry information like elasticity  or demand of a product from a third part sources in Retail domain.

-          Etc.

ETL – Extraction, Transformation and Loading

The Extraction, Transformation, and Loading (ETL) process represents all the steps necessary to move data from different source systems to an integrated data warehouse.

The ETL process involves the following steps:

-          Data is gathered from various source systems.

-      The data is transformed and prepared to be loaded into the data warehouse. Transformation procedures can include converting data types and names, eliminating unwanted data, correcting typographical errors, aggregating data, filling in incomplete data, and similar processes to standardize the format and structure of data.

-      The data is loaded into the data warehouse.

Data Warehouse (OLAP)

A Data Warehouse, in its simplest perception, is no more than a collection of the key pieces of information used to manage and direct the business for the most profitable outcome.  

- According to Bill Inmon, “a data warehouse is a

• subject-oriented,
• integrated,
• nonvolatile,
• time-variant

 collection of data in support of management decisions”.

- Ralph Kimball states that a data warehouse is “ a copy of transaction data specifically structured for Query and Analysis”.

OLAP

OLAP: a category of software tools that provides analysis of data stored in a database. OLAP tools enable users to analyze different dimensions of multidimensional data. For example, it provides time series and trend analysis views. OLAP often is used in data mining.

OLAP Analysis

Imagine an organization that manufactures and sells goods in several states of USA

During the OLAP analysis, the top executives may seek answers for the following:

-          Number of products manufactured.

-          Number of products manufactured in a location

-          Number of products manufactured on time basis within a location.

-          Number of products manufactured in the current year when compared to the previous year.

-          Sales Dollar value for a particular product.

-          Sales Dollar value for a product in a location.

-          Sales Dollar value for a product in a year within a location.

-          Sales Dollar value for a product in a year within a location sold or serviced by an employee.  

OLTP / OLAP

DW related: ODS and Data Marts

ODS (Operational Data Store) - This has a broad enterprise wide scope, but unlike the real enterprise data warehouse, data is refreshed in near real time and used for routine business activity.

Data Mart – is a subset of data warehouse and it supports a particular region, business unit or business function 

ODS and DW use case

In a pharmaceutical company

Customer ODS is used for:

                - sending new product details,

                - promotional activities,

                - and scheduling appointments.

DW is used to answer:

                - In a month, what is the total value of medicines prescribed by a Doctor?           

                - What is our company share

                - Is he missing any info from us.

Data Warehouse design approaches

Kimball - Let everybody build what they want when they want it, we'll integrate it all when and if we need to. (BOTTOM-UP APPROACH)

Pros:      fast to build, quick ROI, nimble

Cons:     harder to maintain as an enterprise resource, often redundant, often difficult to integrate data marts

Inmon - Don't do anything until you've designed everything. (TOP-DOWN APPROACH)

Pros:      easy to maintain, tightly integrated

Cons:     takes way too long to deliver first projects, rigid

Dimensional data modeling

•       Dimensional data modeling is

–      A logical design technique

                that seeks to

–      present the data in a standard frame work

                that is

–      intuitive and allows high-performance access.

•       A data model specifically for designing data warehouses

•       The method was developed based on observations of practice, and in particular, providing data in “user-friendly” form.

Step 1. Classify Entities

Transaction Entities

-          An event happened at a point of time

-          contains measurements or quantities

Component Entities :

-          directly related to a transaction entity

-          Component entities answer questions like “who”, “what”, “when”, “where”, “how” and “why” of a business event.

In a sales application transaction entities are:

                Customer: who made the purchase                                                       

   Product: what was sold

                Location: where it was sold

                Period: when it was sold

Classification Entities:

-          related to component entities by a chain of one-to-many relationships

-          represent hierarchies embedded in the data model

Step 2. Identify Hierarchies

•       A hierarchy in an Entity Relationship model is any sequence of entities joined together by one-to-many relationships, all aligned in the same direction.

Step 3. Produce Dimensional Models

Operators For Producing Dimensional Models

                Operator 1: Collapse Hierarchy

                Operator 2: Aggregation

There is a wide range of options for producing dimensional models from an Entity Relationship model.

These include:

                Star Schema

                Snowflake Schema

                Constellation / Integrated Schema

Star Schema

A star schema consists of one large central table called the fact table, and a number of smaller tables called dimension tables which radiate out from the central table

•       A fact table is formed for each transaction entity. The key of the table is the combination of the keys of its associated component entities.

•       A dimension table is formed for each component entity, by collapsing hierarchically related classification entities into it.

Sample Star Schema

Snowflake Schema

A snowflake schema is a star schema with all hierarchies explicitly shown.

Star vs. Snowflake

Fact constellation schema

The fact constellation architecture contains multiple fact tables that share many dimension tables

Constellation /Integrated Schema

A constellation schema consists of a set of star schemas with hierarchically linked fact tables.

Step 4. Evolution and Refinement

• Check if we can Combine any Fact Tables
• Check if we can Combine any Dimension Tables
• Handling Subtypes.

MicroStrategy Architecture and Product suite

• Overview and Architecture
• MicroStrategy Platform
• MicroStrategy Intelligent Server
• MicroStrategy Architect
• MicroStrategy Desktop
• MicroStrategy Web
• MicroStrategy Administrator
  • MicroStrategy Object Manager
  • MicroStrategy Command Manager
  • MicroStrategy Enterprise Manager
• MicroStrategy Distribution Services
• MicroStrategy Office
• MicroStrategy Mobile
• MicroStrategy OLAP Service
• MicroStrategy Report Services
• MicroStrategy Integrity Manager
• MicroStrategy SDK

Typical MicroStrategy Architecture

MicroStrategy Platform

Layers of MicroStrategy Platform

• Data layer consists of Metadata Repository, Relational Data warehouses, Non-Relational Data Stores.

        The Metadata repository contains information used by all the MicroStrategy products.

• Interactive reporting and Analysis layer allows users to perform on-demand reporting and data manipulation.
• Information Delivery and Alerting allows users to receive proactive information delivery services, delivered on a schedule or only when a certain condition is triggered.
• Design and Administration products allow speeding up project development and enabling fast, simple and centralized administration.

Intelligence Server
MicroStrategy Intelligence Server — architectural foundation and core component of the MicroStrategy system provides a powerful, comprehensive set of features necessary for a scalable, fault-tolerant, enterprise-wide business intelligence system; which provides the core analytical processing and  job management for all reporting, analysis and monitoring applications.
    It consists of :

• SQL Engine
• Query Engine
• Analytical Engine

Architect
MicroStrategy Architect is a rapid development tool that maps the physical structure of your database into a logical business model consisting of Schema objects. These mappings are stored in a centralized metadata repository. These schema objects are used to build reports and other objects.

Desktop

MicroStrategy Desktop is a Client/Server application used to create reports, documents, other reporting objects and manipulate result sets. It provides integrated querying and reporting, powerful analytics, decision support workflows and enables to perform complex types of online analysis of data.

    It is available in 2 versions:

• Desktop designer - Full featured version supporting complete functionality.
• Desktop analyst - Simplified version supporting basic interactive functionality.

Desktop Screenshot

Web
 MicroStrategy Web is a reporting interface for end users that offers interactive reporting and analysis through a web browser and provides access to extensive report generation, document creation, manipulation and formatting capabilities with data browsing and drilling.
    It is available in 3 versions:

• Web Professional - Full featured version that allows to create Intelligent cubes and reports.
• Web Analyst - Simplified version that allows ad hoc analysis from intelligent cubes.
• Web Reporter - Enterprise reporting version allows to view scheduled reports.

Web Screenshot

Administrator
MicroStrategy Administrator provides system administrators with a comprehensive set of tools to monitor manage and automates their BI Infrastructure by providing the centralized user and object management and performance monitoring that is critical for rolling out high performance, secure applications to small internal audiences or large Extranets.

    It consists of following components:

• Object Manager - It enables to manage distributed environments for Change management, Versioning, Internationalization from a single interface and provides Full object life cycle management from development and testing to production. Its four components are Manager, Project Merge Wizard, User Merge Wizard and Repository Translation Wizard.
• Enterprise Manager - It provides prebuilt reports and dashboards that enable to monitor MicroStrategy usage patterns and activities. It also helps to monitor system, user and group activity to tune performance and resource utilization.
• Command Manager - It is used to automate administrative tasks which enables to reduce workload and increase efficiency. It provides a fast and easy way to perform necessary and repetitive tasks.

Distribution Services

MicroStrategy Distribution Services - an add-on component available for MicroStrategy Intelligence Server that offers business performance monitoring and automated information distribution.

MicroStrategy platform also consists of the following products:

• MicroStrategy Office - this add-on enables power analysts and end users to run and analyze reports in Microsoft Excel,     Word, or PowerPoint.
• MicroStrategy Mobile - an interactive interface to the MicroStrategy BI platform that lets mobile business users run reports and dashboards directly from their BlackBerry mobile devices from Research in Motion.
• MicroStrategy OLAP Services - an add-on component available for MicroStrategy Intelligence Server that provides MicroStrategy Desktop, MicroStrategy Office, and MicroStrategy Web users with faster and more extensive analytics as part of In-Memory BI.
• MicroStrategy Report Services - an add-on component available for MicroStrategy Intelligence Server that enables business users to easily create and analyze Pixel-Perfect dynamic enterprise dashboards, scorecards, or production reports from multiple sources of data.
• MicroStrategy Integrity Manager - an application that automates the detection of inconsistencies and errors in your BI environment.
• MicroStrategy SDK - a collection of programming tools, utilities, documentation, and libraries of functions or classes that are designed to enable users to customize and extend MicroStrategy products and to integrate them within other applications.

MicroStrategy Architect and Project Design

• Overview of MicroStrategy Architect and Schema Objects
• Roles of MicroStrategy Architect and its importance
• Building Logical table design and Warehouse catalogue
• Architect Interface
• Creating Project Tables, Facts, Attributes and its relationships and hierarchies

Overview of MicroStrategy Architect and Schema Objects
MicroStrategy Architect provides a set of tools that are used to create new projects and modify the structure of existing projects.
It enables to perform the following tasks:

• Initially populate the metadata
• Create schema objects

The role of the metadata in generating report and document results

Any time you create, modify, or remove any type of project object, those changes are stored in the metadata.

Creating Schema Objects
The most fundamental objects stored in the metadata are schema objects. The basic schema objects are:

Roles of MicroStrategy Architect and its Importance
MicroStrategy Architect supports the following project functions :

• Reporting
• Drilling
• Browsing

MicroStrategy Architect and Reporting
Attributes and metrics are two of the most frequently used objects in designing reports. Metrics consist of facts, which are also schema objects.

MicroStrategy Architect and Drilling
The basic drilling options available for a report directly relate to the definition of following items in MicroStrategy Architect:

• Relationships between attributes
• Hierarchies and their drilling configuration

MicroStrategy Architect and Browsing
MicroStrategy Architect is used to create and define hierarchies, which determine the various paths for browsing attributes.

Building Logical table design and Warehouse catalogue

Logical Data Model
The first step in project design process is to design the logical data model. Logical Data Model is a diagram that shows what information is to be analyzed in a project and seen in reports and how that information is related. It depicts the flow of data in an organization but does not show the physical structure of how the information is stored in the data warehouse.
Simple Logical Data Model

Logical Data Model components
A logical data model includes the following three components :

• Facts
• Attributes
• Hierarchies

Facts
Facts are measures that are used to analyze  business. Fact data is typically numeric, and it is generally aggregated. In MicroStrategy projects, facts map to fact schema objects, which form the basis for all the metrics you use in reports. The other components of a logical data model provide context for the facts.

Attributes
Attributes are descriptive data that provide context for analyzing facts. Attributes provide levels for aggregating and qualifying fact data. They map to attribute schema objects, which describe the metrics on reports.

Attribute Relationships
They help to join data from different attributes and aggregating fact data to different levels. Attributes are related to each other in 2 ways :
•    Direct - A parent-child relationship exists between two or more attributes.
•    Indirect - Two or more attributes are related only through a fact or set of facts.

Types of Direct Relationships

Hierarchies
Hierarchies are groupings of directly related attributes ordered to reflect their relationships. Hierarchies contain only attributes that are directly related to each other. Attributes in one hierarchy are indirectly related to attributes in other hierarchies.
Structure of a Logical Data Model
When all components - facts, attributes and their relationships, and hierarchies are put together we have a logical data model.

Creating a Logical Data Model
Factors that influence the design of the logical data model :

• User reporting requirements
• Existing source data
• Technical and performance considerations

User reporting requirements
The Logical data model should take into account the reporting requirements of end users. It should include all information needed for reports and must not include any information captured  in source systems that is not needed for reports.

Existing Source Data
The Logical data model should take into account what source data is available for use. It should be ensured through an initial review of source systems that sufficient source data is present to support user reporting requirements.

Technical and Performance Considerations
Many technical and performance factors affect the design of logical data model, mostly with regard to its size and complexity. Technical factors are Robustness of database server and software, Network bandwidth and Volume of concurrent users. Complex user reporting requirements or source data structures pose greater challenges to delivering optimal performance.

Steps to Create a Logical Data Model
Factors that influence the design of the logical data model :

• List all the information from the source data needed to include in the logical data model
• Identify which items are facts
• Identify which items are attributes
• Determine the direct relationships between attributes
• Organize directly related attributes into hierarchies

Introduction to Physical Schema
The second step in the project design process is to design the data warehouse schema.
             A physical schema is a detailed, graphical representation of the physical structure of a database. The physical schema is designed  based on the organization of the logical data model. While the logical data model shows the facts and attributes, the physical schema shows how the underlying data for these objects is stored in the data warehouse.

Physical Schema Components
A physical schema includes the following two primary components:

• Columns
• Tables

Column Types
In a data warehouse, the columns in tables store fact or attribute data. The following are the three types of columns:

• ID Columns
• Description Columns
• Fact Columns

Table Keys
Every table has a primary key that consists of a unique value that identifies each distinct record (or row) in the table. There are two types of primary keys :

• Simple Key
• Compound Key

Lookup Tables
Lookup tables store information about attributes, including their IDs and any descriptions. They enable you to easily browse attribute data. Depending on the design of physical schema, a lookup table can store information for a single attribute or multiple related attributes.

Relationship Tables
Relationship tables store information about the relationship between two or more attributes. They enable you to join data for related attributes. To map the relationship between two or more attributes, their respective ID columns must exist together in a relationship table.

One-to-One Relationship
For one-to-one relationship, there is no separate relationship table and separate lookup table for the parent attribute. Instead, the parent is directly placed in the lookup table of the child attribute to map the relationship between the two attributes.

One-to-Many Relationship
For a one-to-many relationship, there is no need to have a separate relationship table. Instead, the parent-child relationship can be defined by including the ID column for the parent attribute in the lookup table of the child attribute.

Many-to-Many Relationship
For a many-to-many relationship, a separate relationship table with the IDs of parent and child attributes is created to map the parent-child relationship.
Fact Tables
Fact tables store fact data and attribute ID columns that describe the level at which the fact values are recorded. They enable to analyze fact data with regard to the business dimensions or hierarchies those attributes represent.

Base and Aggregate Fact Tables
Base fact tables are tables that store a fact or set of facts at the lowest possible level of detail. Aggregate fact tables are tables that store a fact or set of facts at a higher, or summarized, level of detail.
Schema Types
The type of schema design for the data warehouse depends on the nature of the data, how users want to query the data and other factors unique to the project and database environments. Commonly used schema designs :

• Completely normalized schema
• Moderately denormalized schema
• Completely denormalized schema

Normalized Versus Denormalized Schemas
Normalization occurs whenever there is a schema design that does not store data redundantly. Denormalization occurs whenever there is a schema design that stores at least some data multiple times, or redundantly.
Completely Normalized Schema
A completely normalized schema does not store any data redundantly.

Moderately Denormalized Schema
A moderately denormalized schema stores some data redundantly.

Completely Denormalized Schema
A completely denormalized schema stores the maximum amount of data redundantly.

Creating a Data Warehouse Schema
The following factors influence the design of the schema :

• User reporting requirements
• Query performance
• Data volume
• Database maintenance

Architect Interface

Introduction to Architect
Overview of Architect Components
The Architect graphical interface consists of several components that combine most of the functions of the Project Creation Assistant with most of the functions of the schema object editors.
The Architect graphical interface has the following components:

• Warehouse Tables pane
• Project Tables View tab
• Hierarchy View tab
• Properties pane
• Project objects pane
• Menu bar
• Toolbar

Architect Interface
Architect Graphical Interface

Warehouse Tables Pane
The Warehouse Tables pane enables to view database instances and their associated tables and select the tables to be included in a project. The Warehouse Tables pane also displays only database instances that have been associated to the project.

Project Tables View Tab
The Project Tables View tab displays images of the tables used in a project. It uses layers to display the tables. The All Project Tables layer enables to view all the tables used in a project. This layer exists by default when a project is created.

Hierarchy View Tab

The Hierarchy View tab displays all of the attributes that have been created in a project. This tab is used to create, modify, and remove relationships between attributes, which builds the system hierarchy. It can also be used to create, modify, and remove user hierarchies.

Properties Pane
The Properties pane enables to view and modify the properties for attributes, facts, and tables. It has three tabs-Attributes, Facts, and Tables.
Architect Interface

Project Objects Pane
The Project objects pane enables to view the number of attributes, facts, and tables that have been created in a project. It also shows the project name and the current user.

MicroStrategy Intelligence Server

 MicroStrategy Intelligence Server

• Setting up MicroStrategy Intelligence Server
• Intelligent server configuration, start up and connectivity
• Creating Metadata and Statistics Repositories
• Project Sources, connectivity and MicroStrategy Projects
• Defining and setting up the MicroStrategy reporting Environment

MicroStrategy Intelligence Server

MicroStrategy Intelligence Server provides the core analytical processing and  job management for all reporting, analysis and monitoring applications.

Setting up MicroStrategy Intelligence Server

Configuration Wizard

Metadata Connection

MicroStrategy Authentication

Server Definitions

Settings

Summary

Intelligent server configuration, start up and connectivity

Intelligent server configuration, start up and connectivity contd.

Creating Metadata and Statistics Repositories

Configuration Wizard

Repository Types

Metadata Tables

Summary

History List Tables

Summary

Statistics Tables

Project Sources, connectivity and MicroStrategy Projects

Configuration Wizard

Project Source Name

Metadata Location

Authentication

Summary

Defining and setting up the MicroStrategy reporting Environment

Configuring the MicroStrategy Reporting Suite – Connectivity Wizard

Driver Selection

Driver Details

DSN Created

MicroStrategy BI vs. Microsoft BI Compared

The overview of this document is to provide the capabilities of MicroStrategy and Microsoft BI (SSAS, SSRS and PPS), and guide appropriate tool for the business need based on business requirements, maintenance and support perspective.

Disclaimer: Information provided in this document is for reference use only. Information in this document prepared based on projects experience, Microsoft.com, MicroStrategy.com and web search, and information in this document does not guaranty full accuracy of the items explained. For any questions or concerns please contact info@netpeach.com

MicroStrategy BI vs. Microsoft BI Compared in details

Context	Microsoft BI Stack	MicroStrategy BI Stack	Remarks
Product Architecture (Tool set)	- Microsoft BI Stack combines various products to make complete BI: SQL Server 2008 R2, SharePoint 2010, Excel 2010 - Tool set: SSAS, SSRS, Report Builder, Performance Point Services (PPS) for Standard reporting and dashboards. - Microsoft version of Self Service BI: Power Pivot, Power view of Excel,	- MicroStrategy 9.2.1 - Tool Set: Intelligence Server, Web, Mobile, Report Services, OLAP Services, Distribution Services, etc.	- Microsoft BI requires disparate multiple products whereas MicroStrategy is a single unified platform for enterprise BI. - Microsoft has traditionally served smaller organizations and departmental applications. Microsoft BI remains unproven in the ability to cost effectively support large data and user scales while providing adequate performance.
Capabilities	- SSRS: Report Development and subscription - PPS: KPIs, Score Cards and Dash boards development - Excel BI: Connect to any ODBC /OLE DB Source and model the data for a specific analysis or self-service BI.	- Report development, querying, analysis, dash board development and subscription. - Drill anywhere: Users can seamlessly drill anywhere for boundary-free investigative analysis, regardless if the data is in one or multiple data sources. - Self-service BI using preconfigured report objects.	- Microsoft Reporting Services doesn’t have a unified semantic business layer across the data compared to an integrated unified architecture of MicroStrategy offering all styles of BI. With this limitation Microsoft end users cannot access report models and create reports from scratch using reusable metadata through Excel. - In Microsoft Reporting Services, each report references a specific datasource and doesn’t share any subcomponents of the report like attributes, metrics, filters etc. - Lack of Automatic Drill Anywhere: Microsoft does not have automatic drill anywhere capabilities across reports. All drill paths across cubes and across reports have to be manually pre-defined by IT or report developers.

MicroStrategy BI vs. Microsoft BI Compared in details Cnt

MicroStrategy BI vs. Microsoft BI Compared in details Cnt

MicroStrategy BI vs. Microsoft BI Compared in details Cnt

MicroStrategy BI vs. Microsoft BI Compared in details Cnt

MicroStrategy BI vs. Microsoft BI Compared in details Cnt

Microsoft BI vs. MicroStrategy BI Compared in details Cnt.

Intelligence Server:

MicroStrategy Intelligence Server provides the core analytical processing and  job management for all reporting, analysis and monitoring applications.

You can configure a 4-tier, 3-tier, or 2-tier setup for MicroStrategy. The following figure describes how to configure MicroStrategy to suit a 3-tier environment .

 A 4-tier setup involves configuring a Web server to communicate between Intelligence Server and MicroStrategy Web or Web Universal The figure below describes how to configure MicroStrategy to suit a

2-tier environment. It also shows how the various components of the MicroStrategy Configuration Wizard, the metadata repository and the project source interact with each other.

*It is not recommended to use a 2-tier setup for the production environment.

To configure MicroStrategy through the Configuration Wizard :

To configure MicroStrategy through the configuration wizard, configure these step by step

Setting up MicroStrategy Intelligence Server :