BI with SQL Server

1
BI with SQL Server Microsoft Data Analyzer

• BI Tools OLAP Examples

2
Data Analysis

• On-Line Analytical Processing. A category of
  applications and technologies for collecting,
  managing, processing and presenting
  multidimensional data for analysis and management
  purposes.
• Software
• Brio
• Cognos Powerplay
• Microsoft Data Analyzer

3
Business Intelligence System
Internal Source Systems
Data Warehouse Data Mart
Extract, Transformation and Load
4
Metrics

• A quantifiable measure of performance

• Allows for assessment and comparison

5
Dimensions

• Textual, qualitative descriptors of the
  business used to focus and
• aggregate the view of metrics

6
Data Analysis

• SQL Server
• Created Cube using FoodMart2000 database.
• Demonstrate use of MOLAP Cube (multi-dimensional
  OLAP)
• Microsoft Data Analyzer
• Created Airline Cube
• Demonstrate use of MDA to Drill Data.

7
SQL Server Analysis Services
Three cubes created Expense BudgetHRSales
Dimensions created in the Star Schema
8
Meta Data Sales Cube
Dimensions used in Sales Cube
Metrics
9
Data Sales Cube
10
Sales Cube Sales in US gt WA State gt City
11
Sales Cube Sales in US gt WA State gt City gt
Tacoma gt Employee
12
Dimensional Meta Data - Stores
13
Dimensional Meta Data - Products
14
Sales Cube Product Slice gt Beer and Wine gt Beer
15
Beer Sales in US by State
16
Sales Cube Time Dimension
Shows granularity of time dimension.
17
Sales Cube Beer Sales by US State by 4th Qtr
1998
18
MS Data Analyzer Airline Cube
19
Drill Down - Destination
20
Destination Detail By Location Revenue,
Profit, Passengers, Seats
21
Destinations Cube Percent of Revenue by Region
22
Region Type Aircraft
23
Shows Revenue from Commuter A/C by Region
Click on Commuter
24
Drill Down Central USA by Type of A/C
25
Drill Down San Antonio by Type of A/C
Click on San Antonio
26
Business Intelligence Tools -

• Improve consistency and accuracy of reporting
• Reduce stress on operational systems for
  reporting and analysis
• Faster access to information
• BI tools provide increased analytical
  capabilities
• Empowering the Business User
• Companies are realizing that data is a companys
  most underutilized asset