SQL Server by ert554898


									BI with SQL Server &
Microsoft Data Analyzer

BI Tools & OLAP Examples
  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.
  Cognos Powerplay
  Microsoft Data Analyzer
                  Business Intelligence System

Internal Source

                   Extract, Transformation and Load
                                                      Data Warehouse
                                                         Data Mart

  External Data

                                                      Business Intelligence /
                                                      Executive Information System

   A quantifiable measure of performance

   Allows for assessment and comparison


Revenue                            Overhead Percent
Profit                             Customer Churn
Expenses                           Employee Turnover
Number of Customer Calls           Return on Investment
Number of Customer Complaints      Income per Capita
Product Defects                    Capacity
Gross Margin Percent               Utilization
Accidents per Mile Driven          Average Bill Rate
Cost of Sales                      Management Ratio

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


Customer                                Division / Department
Geographic Area                         Business Sector
Time                                    Sales Channel
 (Year, Quarter, Month, etc.)           SIC Code
Sales Person                            Special Customer Group
Sales Organization                      Brand
Business Unit                           Market Segment
Competitors                             Product Color
Season                                  Marketing Campaign
  Data Analysis

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

                           Three cubes created:
                           Expense Budget

                  Dimensions created
                  in the Star Schema
Meta Data – Sales Cube

                         Dimensions used
                         in Sales Cube

Data – Sales Cube
Sales Cube – Sales in US > WA State > City
Sales Cube – Sales in US > WA State > City > Tacoma > Employee
Dimensional Meta Data - Stores
Dimensional Meta Data - Products
Sales Cube – Product Slice > Beer and Wine > Beer
Beer Sales in US by State
Sales Cube – Time Dimension

                              Shows granularity of
                              time dimension.
Sales Cube – Beer Sales by US State by 4th Qtr 1998
MS Data Analyzer – Airline Cube
Drill Down - Destination
Destination Detail – By Location: Revenue, Profit, Passengers, Seats
Destinations Cube – Percent of Revenue by Region
Region & Type Aircraft
Shows Revenue from Commuter A/C by Region

                                 Click on Commuter
Drill Down – Central USA by Type of A/C
Drill Down – San Antonio by Type of A/C

Click on San Antonio
      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
  company’s most underutilized asset

To top