1 of 27

Business Intelligence (BI)

Lecture 3

Prepared by: Dr. Nesma Mahmoud

Information Systems Program

0

Information Systems Program

2 of 27

Business Reporting

Learning Objectives

List multiple types of business reports and describe their shared building blocks
Summarize different questions business reports grapple with and the options for displaying the answers.
Learn the components and structure of the business reporting systems
Create various reports using MicroStrategy BI platform

1

Information Systems Program

3 of 27

Report

Report?

Any communication artifact prepared to convey specific information

Adopted from Sharda, R. Delen, D. and Turban, E. (2015), Business Intelligence and Analyti cs: Systems for Decision Support, 10 ED, Chapter 4

2

Information Systems Program

4 of 27

Business Reports

A report can fulfill many functions

To ensure proper departmental functioning
To provide information
To provide the results of an analysis
To persuade others to act
To create an organizational memory…

Adopted from Sharda, R. Delen, D. and Turban, E. (2015), Business Intelligence and Analyti cs: Systems for Decision Support, 10 ED, Chapter 4

3

Information Systems Program

5 of 27

Business Report

Business report is A written document that contains information regarding business matters.

Purpose: to improve managerial decisions
Source: data from inside and outside the organization (via the use of ETL)
Format: text + tables + graphs/charts
Distribution: in-print, email, portal/intranet
Types (in terms of content and format)

Informal – a single letter or a memo
Formal – 10-100 pages; cover + summary + text
Short report – periodic, informative, investigative

Source: Sharda, R. Delen, D. and Turban, E. (2015), Business Intelligence and Analyti cs: Systems for Decision Support, 10 ED, Chapter

4

Information Systems Program

6 of 27

Business Reports Building Blocks

Attributes

Metrics

Descriptive information providing business context and defining summarization levels for calculations

Quantitative business measures

5

Information Systems Program

7 of 27

Business Reports Building Blocks

Report Grid or Table

Basic building block for every business analytic application

6

Information Systems Program

8 of 27

Business Reports Building Blocks

Filters

Specify conditions the data must meet to be included on the report

Prompts

Allow users to dynamically select the information to be displayed in the report

Purchased

in the last

6 months

My Store Items

7

Information Systems Program

9 of 27

Types of Business Reports

Standard reports

Answer the questions: What happened? When did it happen? Example: Monthly or quarterly financial reports. We all know about these.

Ad hoc Reports

Answer the questions: How many? How often? Where? Example: Custom reports that describe the number of hospital patients for every diagnosis code for each day of the week.

Query drilldown (or OLAP)

Answers the questions: Where exactly is the problem? How do I find the answers? Example: Sort and explore data about different types of cell phone users and their calling behaviors.

8

From SHARDA, RAMESH; DELEN, DURSUN; TURBAN, EFRAIM, BUSINESS INTELLIGENCE AND ANALYTICS: SYSTEMS FOR DECISION SUPPORT, 10th Edition, © 2015. Used by permission of Pearson Education, Inc., New York, NY. All Rights Reserved.

8

Information Systems Program

10 of 27

Types of Business Reports

Dashboard-type Reports

present a range of performance indicators on one page, with both static/predefined elements and customizable widgets and views.

Balanced Scorecard-type Reports

present an integrated view of a company’s health and include financial, customer, business process, and learning/growth perspectives.

Metric Management Reports

involve outcome-oriented metrics based on service level agreements and/or key performance indicators. Can be used as part of business performance management

From SHARDA, RAMESH; DELEN, DURSUN; TURBAN, EFRAIM, BUSINESS INTELLIGENCE AND ANALYTICS: SYSTEMS FOR DECISION SUPPORT, 10th Edition, © 2015. Used by permission of Pearson Education, Inc., New York, NY. All Rights Reserved.

9

Information Systems Program

11 of 27

Business Reporting

From SHARDA, RAMESH; DELEN, DURSUN; TURBAN, EFRAIM, BUSINESS INTELLIGENCE AND ANALYTICS: SYSTEMS FOR DECISION SUPPORT, 10th Edition, © 2015. Used by permission of Pearson Education, Inc., New York, NY. All Rights Reserved.

10

Information Systems Program

12 of 27

Business Reporting

Business reporting is about good story-telling.

Think of your analysis as a story—use a story structure.

Be authentic—your story will flow.
Be visual—think of yourself as a film editor.
Make it easy for your audience and you. Invite and direct discussion.

From SHARDA, RAMESH; DELEN, DURSUN; TURBAN, EFRAIM, BUSINESS INTELLIGENCE AND ANALYTICS: SYSTEMS FOR DECISION SUPPORT, 10th Edition, © 2015. Used by permission of Pearson Education, Inc., New York, NY. All Rights Reserved.

11

Information Systems Program

13 of 27

Components of Business Reporting Systems

Adopted from Hill, G. (2008). “A Guide to Enterprise Reporting.”

12

Information Systems Program

14 of 27

Business Reporting Systems Components

According to Hill (2008) there are following components to a business reporting system:

OLTP (Online transaction processing)- A system that measures some aspect of the real world as events (e.g., transactions) and records them into enterprise databases.
Data supply- A system that takes recorded events/transactions and delivers them reliably to the reporting system.
ETL (extract, transform, and load)- This is the intermediate step where these recorded transactions/events are checked for quality, put into the appropriate format, and inserted into the desired data format.
Data storage- This is the storage area for the data and metadata. It could be a flat file or a spreadsheet, but it is usually a relational database management system (RDBMS) set up as a data mart, data warehouse, or operational data store (ODS)

Adopted from Hill, G. (2008). “A Guide to Enterprise Reporting.”

13

Information Systems Program

15 of 27

Business Reporting Systems Components- contd

Business logic- The explicit steps for how the recorded transactions/events are to be converted into metrics, scorecards, and dashboards.
Publication- The system that builds the various reports and hosts them (for users) or disseminates them (to users). These systems may also provide notification, annotation, collaboration, and other services.
Assurance- A good business reporting system is expected to offer a quality service to its users. This includes determining if and when the right information is to be delivered to the right people in the right way/format.

Adopted from Hill, G. (2008). “A Guide to Enterprise Reporting.”

14

Information Systems Program

16 of 27

BI OLAP Styles

Information Systems Program

15

Information Systems Program

17 of 27

BI OLAP

Learning Objectives

Compare and contrast different types of OLAP
Understand different applications of OLAP
Comprehend the differences between OLAP and OLTP

16

Information Systems Program

18 of 27

OLAP Applications

Marketing & Sales Analysis

Consumer Goods Industries, Retailers
Financial Services (Banks, Insurance etc.)

Clickstream Analysis & Web Analytics

Pure Play E-commerce Sites
Click-n’-Mortar Organizations

Database Marketing & CRM

Customer Segmentation
Customer Value Analysis

17

Information Systems Program

19 of 27

OLAP Applications – contd.

Budgeting & Financial Reporting

Requires multiple dimensions such as Time, Account, Organization, Product segment etc.

EIS, Balanced Scorecards

Management Reporting based on P&L Ratios
KPIs, CSFs

Other Applications

Profitability Analysis
Defect Analysis
Quality Analysis

Adopted from Teradata University Network presentation on OLAP.

18

Information Systems Program

20 of 27

OLAP Styles

ROLAP- Relational OLAP (using relational databases; a star schema is used)
MOLAP- Multidimensional OLAP (using multidimensional databases)
HOLAP (Hybrid Online Analytical Processing)
DOLAP- desktop OLAP

19

Information Systems Program

21 of 27

OLAP Analysis and Advanced ROLAP Analysis�MicroStrategy Satisfies All Analysis Needs

Total Revenue and Costs

In Jan 2011 and Jan 2012

At Top 10 Revenue Stores

Single-click OLAP Manipulations Allow People To Slice-and-Dice a Subset of Data To View It from Many Different Perspectives

Relational OLAP Architecture Allows People To ‘Drill Anywhere’ in The Entire Relational Database – Across All Dimensions and From Summary Level To Transactional-level Detail

Drill Anywhere with�Advanced Relational OLAP Analysis

Slice and Dice with�Basic OLAP Analysis

Geography

Products

Time

Revenue for Laptop Computers

In 2011

At All Stores

Revenue for All Electronics

In 2011 and Q1 2012

At Stores in the NE Region

20

Information Systems Program

Basic OLAP - Basic OLAP

With Basic OLAP technology, people perform these functions on a limited subset of data called a cube. With other BI technologies, these cubes are static physical databases, whereas with MicroStrategy, they are dynamically generated multidimensional caches. You can see in the diagram at left that users can view many different slices business performance:

In the top slice, the user is viewing Laptop computer revenue for 2010 and for all Stores

BUILD SLIDE - Relational OLAP Analysis

Another approach to OLAP is Relational OLAP. Relational OLAP doesn’t limit you to slicing and dicing within a cube. Instead you are able to access information anywhere within your data warehouse, from the highest levels of data summarization, down to the lowest layers of transactional detail.

Ideally, BI vendors would offer both approaches to OLAP analysis.

Differentiator – Enterprise Class ROLAP Engine

The Best In Class Approach Is:

ROLAP architecture with sophisticated caching.

Advanced ROLAP engine which allows people to drill anywhere.

ROLAP engine that is optimized for each relational database.

(++) Which Is Important Because:

Systems don’t fail or slow down even during peak usage.

Any question can be answered at any time.

◊ Other Alternatives In the Market Are:

Some other tools store data in proprietary data storage mechanisms.

Other ROLAP tools don’t have database-optimized ROLAP engines or caching engines.

Other tools only allow you to drill within pre-defined subsets of your data warehouse, or along pre-defined drill paths.

(--) Which Are Less Optimal Because:

Proprietary data storage mechanisms tend to not scale as well as relational databases.

Non-optimized ROLAP engines require more resources (CPU, memory, network).

Not all questions can be answered at any time by the business user.

Additional Notes for Reference:

The advantages of Relational OLAP are mostly obvious:

People can investigate the data freely… without being constrained to just the data contained within a cube

People can “surf” anywhere in the data warehouse… without the need to create new ad-hoc reports

Normal business people can serve themselves with data ... without requiring assistance from IT

Summary

Having heard this, it should probably make you wonder why other vendors’ technology is not also based on Relational OLAP. The simple reason is that Relational OLAP technology proved to be very difficult for software vendors to perfect. It took MicroStrategy a decade of concerted work with customers, theoretical work, and many patents to produce a highly scalable, high performance Relational OLAP technology. Other companies spent this past decade investing in the easier cube-centered technology instead, and are just now realizing that customers’ requirements are rapidly evolving to need Relational OLAP capabilities.

22 of 27

Example: Olap Usage of an Automobile Marketer

The Story

An automobile marketer wants to improve business activity. Therefore he wants to view sales figures from different perspectives.

A Question

What is the trend in sales volumes over a period of time for a specific model and color across a specific group of dealerships ?

The Data Needs

Sales by model
Sales by dealership
Sales by color
Sales over time
etc.

Adopted from Teradata University Network presentation on OLAP.

21

Information Systems Program

23 of 27

Example: The Multidimensional View of the Data

Sales Volumes

Blue

Red

White

Van

Coupe

Sedan

Miller

Clyde

Smith

COLOR

DEALERSHIP

MOD E L

Adopted from Teradata University Network presentation on OLAP.

22

Information Systems Program

24 of 27

OLAP Features: “Slicing and Dicing“ the Data

Sales Volumes

Blue

Red

White

Van

Coupe

Sedan

Miller

Clyde

Smith

COLOR

DEALERSHIP

MOD E L

Color: Blue and White
Model: Coupe only
Dealership: Clyde only

Clyde

Blue

White

Coupe

“Sliced and Diced“ Data

Choosing a range out of each dimension:

Adopted from Teradata University Network presentation on OLAP.

23

Information Systems Program

25 of 27

OLAP Features: Rotating the Data

Sales Volumes

Blue

Red

White

Van

Coupe

Sedan

COLOR

MOD E L

View of the Account Manager

Rotate the data cube by 90°

DEALERSHIP

Sales Volumes

Miller

Smith

Clyde

Van

Coupe

Sedan

MOD E L

View of the Product Manager

Different users will require different views of the multidimensional cube – OLAP allows easy rotation of data

Adopted from Teradata University Network presentation on OLAP.

24

Information Systems Program

26 of 27

OLAP Features: Drill-Down and Roll-Up

Data can be disaggregated and aggregated along a dimension according to their natural hierarchy

Drill-Down

Roll-Up

State

Region

Dealership

Miller

Smith

Clyde

Lucas

Gleason

Atlanta

Athens

Georgia

Sales Volumes by Organization Dimension�- three level hierarchy -

Adopted from Teradata University Network presentation on OLAP.

25

Information Systems Program

27 of 27

OLTP versus OLAP

Clerk, IT Professional
Day-to-day Operations
Application-oriented �(E-R based)
Current, Isolated
Detailed, Flat Relational
Structured, Repetitive
Short, Simple Transaction
Read / Write
Index/Hash on Prim. Key
Tens
Thousands
100s MB-GB
Transaction Throughput

Knowledge Worker
Decision Support
Subject-oriented �(Star, Snowflake)
Historical, Consolidated
Summarized, Multidimensional
Ad-Hoc
Complex Query
Read Mostly
Lots of Scans
Millions
Hundreds
100s GB-TB
Query Throughput, Response

User

Function

Database Design

Data

View

Usage

Unit of Work

Access

Operations

# Records Accessed

# Users

Database Size

Performance Metric

OLTP

OLAP

Adopted from Teradata University Network presentation on OLAP.

26

Information Systems Program