Data Warehousing and Online Analytical Processing OLAP - PowerPoint PPT Presentation


PPT – Data Warehousing and Online Analytical Processing OLAP PowerPoint presentation | free to view - id: 1386d8-YTU0Z


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation

Data Warehousing and Online Analytical Processing OLAP

Description: ... Microsoft battling Oracle and IBM (DB2) for dominance in the corporate database market ... – PowerPoint PPT presentation

Number of Views:2025
Avg rating:3.0/5.0
Slides: 48
Provided by: marki152


Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Data Warehousing and Online Analytical Processing OLAP

Data Warehousing and Online Analytical Processing
  • Data is good

BI Consumer Pool
Operations research / management science
Data Warehouse Design Considerations
Microsoft Examples
Excel, Access, Reporting Services, Data Analyzer,
Office, 3rd party tools
SQL Server, Access
Analysis Services (SQL Server
SQL Server, Access
What is a Data Warehouse?
Data warehouses are databases designed for
Data is
  • Subject oriented
  • Integrated
  • Time-Variant
  • Nonvolatile
  • Data enters DW from operational environment,
    transaction processing systems (TPS).

1. Subject Orientation
  • TPS organized around processes, functions
  • billing, banking, purchasing, payroll, etc.
  • DW organized around subjects
  • customers, vendors, encounters, sales
  • Transactions
  • TPS processes transactions
  • DW stores summary info related to transactions
  • TPS - keeps data needed for transaction
  • DW - keeps data needed for analysis

2. Integration
  • DW must integrate data from different apps
  • Create consistency across applications
  • naming conventions
  • measurement of variables (units)
  • data types
  • encoding
  • DSS analyst - use the data, not worry about
    credibility/consistency of data
  • often best person to find subtle data problems

3. Time Variancy
  • TPS accurate at moment of access
  • DW accurate as of some moment in time

Operational - current value data
Data warehouse - snapshot data
  • Time horizon 5-10 years
  • Key contains an element of time
  • Once snapshot made, data cannot be updated
  • Time horizon 60-90 days
  • Key may or may not have an element of time
  • Data can be updated

4. Nonvolatility
Data warehouse
Case for Data Warehousing
  • Can perform querying/reporting on servers/disks
    not associated with TPS - performance of both
  • Can use query/reporting optimization which would
    not be appropriate for TPS
  • Less technical expertise needed to create
  • Facilitates reporting across different TPSs
  • Facilitates storage of historical data
  • Prevents analysts from requiring access to live

Source Data Warehouse Information Center,
What is a Data Mart
  • Departmental based DSS database
  • Same architectural foundation as DW
  • Data mart is reconcilable with DW
  • Users are more likely business analysts than IT
    technicians (some overlap likely)
  • farmers (know what they want)
  • explorers (not sure what they want)
  • more farmers than explorers

Why Data Marts?
  • DW gets large, competition among users
  • Data becomes harder to customize in DW
  • Analysis tools for dealing with LARGE datasets
    may not be as elegant as tools designed for
    smaller datasets
  • Dept can customize data as it flows into data
    mart - no need to serve entire corporation
  • Dept can choose amount of historical data to
    include in data mart.
  • Less concern about impact on DW of
    computationally intensive analyses
  • Dept can choose own data mart DB software
  • Dept can choose specialized analytical software

Multi-dimensional Data Modeling
  • Designed to facilitate analysis (not
  • Extremely common in data warehousing
  • A de-facto standard
  • Concept has been around for a long time
  • Intuitive concept of many dimensions or
    perspectives on business measures or facts
  • view sales facts (such as total sales) from
    customer, product and time perspective
  • Multidimensional conceptual view of data for user
  • Rich dimensional structuring with hierarchical
  • Efficient specification of dimensions and
  • Separation of structure and representation
  • Underlying database technology will vary -
    relational, proprietary data cubes
  • Hypercube an n-sided cube
  • User can slice, dice, rotate, browse through
    multidimensional cubes of data

Data Models Relational vs. Multi-dimensional
  • Transaction focused
  • Focus on many linked, normalized tables
  • One big complex data model
  • Very little redundancy
  • Analysis focused
  • Normalized fact table joined to a few highly
    non-normalized dimension tables
  • Many simple, intuitive data models
  • Lots of redundancy

Data (Hyper) cubes
2-d to 3-d cube
Rotating the cube
A 6-D Multidimensional Type Structure Example
Data generating events happen at the intersection
of specific instances of each dimension. Total
Sales(Store5, 65 Males, Feb, Actual, Shirts)
OLAP Solutions by Thomsen
Bringing Facts and Dimensions Together The
few joins
Dimensions are highly non-normalized (i.e. lots
of redundancy
Atomic data
From 1st ed of Kimballs Data Warehouse
Toolkit, notice no transaction in the Sales
Fact table
What is OLAP?
  • Software tool providing multi-dimensional view of
    data for business analysis
  • Example of Decision Support or Business
    Intelligence tool
  • Fast data access and fast computations
  • Interactive, flexible user interface
  • Slice, dice, drill-down
  • Excel Pivot Table and Pivot Chart are examples of
    simple OLAP tools

  • Business logic and statistical analysis relevant
    to end user
  • Should not require programming for everything
  • Analysis can be via vendors tools or link to
    generic analytical platform such as spreadsheet
  • Examples include time series analysis, cost
    allocation, currency translation, goal seeking,
    ad-hoc multi-dimensional structural changes (cube
    building), non-procedural modeling, exception
    alerting, and data mining.
  • Capabilities vary widely by vendor and market

Common cube operations
  • Pivot or Rotate change which dimensions and/or
    levels within dimensions are shown on row and
    column axes
  • Roll-up aggregate or combine cells within a
    dimension according to some mathematical
  • Uses a hierarchy definition for the dimension
  • Commonly this is summation or count
  • Drill down examine data a greater level of
  • Add another row or column header which is further
    down the concept hierarchy
  • Slice select a subset of a cube by constraining
    the value of some dimension
  • Ex Select cells for month January in time
  • Dice select a subset of a cube by constraining
    two or more dimensions
  • Drill through access atomic level detail data

A Few Products
  • Microsoft Analysis Services
  • Part of SQL Server 2005
  • Create OLAP cubes, 10 data mining algorithms
  • Tableau
  • A new, pretty amazing pivoting tool
  • Cognos
  • Recently bought by IBM
  • Hyperion Essbase
  • Full suite of business intelligence developer and
    end user tools
  • Purchased by Oracle
  • Business Objects (Crystal)
  • Full suite of business intelligence developer and
    end user tools
  • Microstrategy
  • Oracle
  • Information Builders
  • Home of WebFocus, a web based OLAP tool
  • Pentaho
  • A new open source business intelligence project
  • http//

  • Download the following from the Downloads section
    of course web
  • Lets look at Excel Pivot Tutorial
  • Can even publish Pivot Tables to Web

A Call Center Example Tech Support for MS Office
  • Technology enabled business processes
  • Massive amount of data captured by ACD
  • Some data analysis done by ACD
  • Difficult operational questions related to
    staffing/scheduling impact on service level
  • Many call centers in many industries

Steps in Multi-dimensional Modeling Call Center
  • Choose business process
  • Servicing technical support calls
  • Choose grain of process
  • Individual phone calls
  • Choose dimensions
  • Customer, application, problem, time
  • Choose measured facts
  • time on hold, service time of call

The Star Schema A multi-dimensional data model
One E-R vs. Many Stars
Analysis focus
Transaction focus
One E-R model for all the business process.
One star per modeled business process.
What is SQL Server?
  • Microsofts industrial strength database
    management system
  • Intended for client/server architecture
  • Includes numerous tools for creating, modifying
    and working with SQL Server databases
  • It is NOT a front end development tool (i.e. no
    Forms, Reports, VB code modules as in Access)
  • Microsoft battling Oracle and IBM (DB2) for
    dominance in the corporate database market
  • SQL Server using price, performance, ease of use
    and Microsoft ubiquity as marketing levers
  • Comes in various flavors such as Enterprise,
    Developer and Personal
  • You can get Personal through the MSDNAA program
    here at OU
  • Personal edition (which is installed in this lab)
    has server and client running on same machine

MS Access Two tools in one
(1) Tools for creating Forms, Reports, Macros,
(2) Database engine (Jet) .mdb file is container
for both data tables as well as front end
MS SQL Server
Web applications
SQL Server client side tools such as Enterprise
Manager, Query Analyzer, and DTS
Windows apps created with VB, C, C, Java
Application software such as Excel or Access
Crystal Reports, Business Objects, etc.
SQL Server Databases (1) data file (2)
transaction log file
Transact-SQL or T-SQL is SQL Servers SQL
Main Components of SQL Server 2005
(2) Important Client Tools
(1) On the Server Side
  • SQL Server database engine
  • runs as a service on computer acting as the
    database server
  • server listens for requests from clients
  • Analysis Services
  • OLAP and data mining
  • Reporting Services
  • Integration Services
  • Management Studio primary tool for managing SQL
    Server databases, security and other objects
  • Query Builders tools for building and analyzing
    SQL statements
  • Business Intelligence Development Studio - build
    various BI applications
  • Analysis Services projects
  • Integration Services projects
  • Report Services projects

Understanding MS Analysis Services (AS)
Manage cubes with Analysis Manager
  • Included in SQL Server 2000
  • Was called OLAP Services in SQL Server 7
  • Includes cube building capabilities
  • Draws data from the data warehouse and constructs
    MD cubes for analysis
  • Cube builder is heavily wizardized
  • Includes a few data mining models
  • Runs as a service in Windows
  • AS includes various administrative tools as well
    as a simple data browsing tool
  • Its NOT meant to be an OLAP front end pivoting

Cubes get data from warehouse and make data
available to reports
Facilitates DSS app dev
Various dbs
AS got another huge facelift in SQL Server 2005
and is gaining strategic importance for Microsoft
Introducing Analysis Services Projects
  • Tools for managing OLAP cubes and data mining
    models in MS SQL Server AS
  • Uses special version of MS Visual Studio
  • SQL Server Books Online
  • A one stop shop for creating analytical
    applications using Microsoft technology

Analysis Services Databases
Cube Editor
Star Schema
Our Call Center Data Warehouse
Dimensions for analysis BY
Dimension Hierarchies
  • A conceptual relationship between related items
    in a dimension
  • Highest level in hierarchy usually has least
    number of distinct values
  • Hierarchies facilitate roll up of lower levels
    into higher levels as well as drill down from
    higher levels to lower levels
  • A dimension can have multiple hierarchies
  • MS AS restricts to one hierarchy per dimension
  • Other dimension attributes can be designated as
    Member Properties
  • MS AS 2005 will support multiple hierarchies
    within a dimension

Time Dimension
  • Almost all MD models have a time or date
  • Can build this in advance independent of the
  • Include many, many attributes that describe
    dates/times such as various time periods in
    calendar time as well as fiscal time, holiday
    indicators, weekday names, etc.
  • Lets explore the smallish time dimension in
    FoodMart and a slightly richer one in grocer.mdb
  • Some tools (including AS) will let you create a
    time dimension from any date/time field
  • Probably better to create your own fully seeded
    date table for the time dimension
  • Ensures no time gaps due to no facts
  • Kimball recommends creating separate date table
    and time table
  • Far less space required

Designing Cubes - Facts
  • Often cubes based on underlying data modeled as a
    star-schema (or one of its variants)
  • Identifying fact table and associated measures
  • Fact table also contains keys that join to the
    dimension tables
  • Dimensions often shared across multiple cubes

Here are the three measured facts in our call
center cube.
Cubes know about hierarchical relationships
within dimensions. This is how the cube can roll
up calculations between levels of the hierarchy.
Designing Cubes - Dimensions
  • We are picking a set of dimension tables as well
    as the fields within each table making up the
    levels of each dimension.
  • Dimension design involves picking dimension table
    and fields representing hierarchical levels
    within the dimension

Reporting Options for Analysis Services Cubes MS
  • Excel can link to OLAP cubes
  • Can build Excel Pivot Tables based on cubes
  • Need MS Query to connect to a cube
  • Differences between pivoting on data in Excel vs.
    data in OLAP cube
  • Row limits
  • Hierarchies
  • Some limitations in pivot table functionality
    with OLAP cubes (e.g. no calculated fields or
  • Can create a local cube for offline pivoting
  • From Pivot Table toolbar Offline OLAP… and then
    youre prompted to save a .cub file

Reference Library
BI Resources
  • The Data Warehousing Institute
  • Kimball and Associates http//
    ./html/articles.html A Dimensional Modeling
    Manifesto Kimball, R. http//
  • DSS Resources http//
  • Data Warehousing Information Center
  • Intelligent Enterprise http//www.intelligententer
  • DM Review http//
  • KDNuggets http//
  • IT Toolbox http//
    http// http/
  • OLAP Report http//
  • Some free stuff (nice history of OLAP and
    commentary on industry trends
  • Other stuff costs
  • http//
  • Awesome set of resources from the lead developer
    on MS SQL Server Analysis Server team

Some Good Books and Articles
  • The Data Warehouse Toolkit Kimball, R.
  • Definitive, Microsoft SQL Server 2005 based 3rd
    edition now out
  • OLAP Solutions Thomsen, E.
  • Definitive, abstract and dense, good
  • MDX Solutions With Microsoft SQL Server Analysis
    Services 2005 and Hyperion Essbase by George
  • MDX is the SQL for cubes
  • Data Mining with SQL Server 2005 (Paperback) by
    ZhaoHui Tang (Author), Jamie MacLennan
  • Data Warehouse Design Solutions Adamson and
  • Multi-D DW designs from lots of different
  • Very practical, uses realistic situations to
    reinforce the concepts
  • Summers Rubber Company designs its data warehouse
  • Gorla, Narasimhaiah Krehbiel, Steve
  • Interfaces Mar/Apr 1999 29, 2 ABI/INFORM

More AS Tutorials and Resources
  • http//
  • This is the granddaddy of MS SQL Server Analysis
    Services resoures. Mosha Pasumansky is the MS
    development lead on AS engine.
  • Site is gold mine of information and links
    regarding AS and related software
  • He participates in microsoft.public.sqlserver.olap
  • Great Blog at http//
  • Introduction to Analysis Services - by William
    Pearson (series of articles) http//www.databasejo
  • Very nice series of MS AS tutorials
  • Best practices for Business Intelligence using
    the Microsoft Data Warehousing Framework
  • A white paper from Microsoft

The various MS pivot tools
  • Pivot Table Service
  • Included as part of AS but also installed as part
    of MS Office
  • Client liason to AS
  • Can also create and communicate with local cubes
    that are not part of AS
  • Excel Pivot Table
  • A flexible crosstab type reporting tool which is
    part of Excel
  • Office Pivot Table List
  • Part of Office Web Components
  • Its an Active X control
  • Similar to Excel Pivot Table but with some
    differences in functionality

1. Select Business Process to Model
  • Some natural business activity supported by one
    or more information systems
  • Examples purchasing, orders, shipments,
    inventory, admissions, service calls,
    installations, bookings, production
  • We represent each activity as a table of facts
    with many related dimension tables
  • Designing and creating the fact and dimension
    tables is the critical part of creating a useful
    multidimensional data model the cube
  • This is a combination of business analysis and
    technical design

2. The Fact Table
  • Fact table contains
  • numeric measures describing some aspect of the
    business process
  • Foreign keys into dimension tables
  • Degenerate dimensions such as transaction
    numbers, invoice numbers, etc.
  • Each row is a measurement
  • Each row is at the same grain
  • Many useful facts are numeric and additive
  • Cubes tend to be quite sum and count centric
  • Well encounter additive, semi-additive and
    non-additive facts
  • Fact tables are usually deep (many rows) and
    narrow (not so many columns)
  • Three common fact table types
  • Transaction facts EX sales
  • Periodic snapshot facts EX daily inventory
  • Accumulating snapshot facts EX status of
    something as it moves through relatively well
    defined phases or stages such as a product as it
    moves from order to manufacture to shipment

2. Declare Grain of the Process
  • Specify exactly what a fact table row represents
  • An individual line item on a customers retail
    sales ticket as measured by a scanner device
  • A line item on a hospital bill
  • A single hotel reservation
  • Daily inventory snapshot for each product in a
  • Monthly snapshot of a 401k account
  • This is the lowest level of detail you are
    choosing to store in the multidimensional data
  • Table is sparse in sense that rows representing
    the fact that nothing happened (i.e. values of 0)
    are usually NOT stored
  • EXAMPLE Lets check the grain and measures of
    the fact tables in FoodMart 2000.
  • Sales Facts
  • Inventory Facts

4. Choose dimensions that apply to each fact
table row
  • How do people describe the data that results from
    the fact table rows?
  • Dimension richness is what really makes the data
  • Lots of them filled with descriptive business
  • The business perspectives BY which people
    describe their data
  • Time, customers, products, locations, suppliers,
    prospect type, problem, scenarios
  • Source of query constraints, groupings, report
  • Dimension tables are wide (many fields),
    denormalized (plenty of redundancy), with usually
    not too many rows
  • Use surrogate keys (meaningless long integers)
    instead of natural keys (actual meaningful
    primary keys from source systems such as customer
    s, invoice numbers, etc.)
  • Robust to changes in source systems
  • Can handle source system key reuse
  • Source keys may be complex strings (slow) with
    embedded info (hard to use)
  • So, is something a Measured fact or Dimension
  • Is it a measurement that takes on lots of values
    participates in calculations?
  • Or is it a relatively constant value that
    participates in constraints?