SQL Server by ert554898

VIEWS: 11 PAGES: 26

									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.
Software
  Brio
  Cognos Powerplay
  Microsoft Data Analyzer
                  Business Intelligence System


Internal Source
    Systems




                   Extract, Transformation and Load
                                                      Data Warehouse
                                                         Data Mart




  External Data
    Sources



                                                      Business Intelligence /
                                                      Executive Information System
 Metrics


   A quantifiable measure of performance

   Allows for assessment and comparison



                        Examples

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
 Dimensions


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



                           Examples

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
  database.
  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
                           HR
                           Sales




                  Dimensions created
                  in the Star Schema
Meta Data – Sales Cube


                         Dimensions used
                         in Sales Cube




                                   Metrics
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