Spreadsheet and Database Decision Support - PowerPoint PPT Presentation

1 / 32
About This Presentation
Title:

Spreadsheet and Database Decision Support

Description:

Models Data VBA = Decision support system. More Themes ... http://www.cait.wustl.edu/cait/papers/prism/vol1_no1/ Data is: 1. Subject Orientation ... – PowerPoint PPT presentation

Number of Views:137
Avg rating:3.0/5.0
Slides: 33
Provided by: marki152
Category:

less

Transcript and Presenter's Notes

Title: Spreadsheet and Database Decision Support


1
Spreadsheet and Database Decision Support
  • Power Excel and Access for Business Analysts

2
High Level Structure
Data Warehousing (Access)
Online Analytical Processing (OLAP) (Excel)
Data Cleaning and Transformation (Excel)
Session 1
Advanced Data Querying and Analysis (Excel and
Access)
Session 2
Models and Modeling (Excel)
Building DSS With VBA (Excel)
Take MIS 436
3
Module Overview and Session Themes
  • Data is good.
  • Data is often not enough, need models too.
  • ModelsDataVBA Decision support system

4
More Themes
  • Spreadsheets are de facto the most widely used
    platform for modeling and analysis in business
    today
  • Excel has rich set of modeling and analysis tools
  • End user DSS development
  • A wide open opportunity for stardom
  • "Things should be made as simple as possible, but
    not any simpler." Albert Einstein

5
(No Transcript)
6
Data Extraction, Transformation, and Loading
  • Playing in the Mud

7
The Many Roles of Knowledge Workers
Brilliant IS idea
8
The Gist of the Problem
  • Getting data out of some system to
  • Analyze it (e.g. Excel, Access, stats package)
  • Get it into another system (e.g. ERP or Data
    Warehouse)
  • Smart manipulation of electronic reports with
    embedded data
  • Dont want to do it manually (why?)

9
ACD Report Example
Report Header
Date
Split
Blank lines
Data!
Totals Lines
Next report
10
Why Talk About This?
  • Deloitte Touche Data Quality and Integrity
  • Beaumont Oracle ERP project
  • Huge amount of time wasted doing manual
    processing
  • Really useful spreadsheet and database skills
    (and mindset)
  • Its fun to play in the mud sometimes

11
OK, Lets start with a little toy data extraction
problem
  • Download PatientLocationLog.txt from the ATiB
    Downloads section (right click Save Target As)
  • Somehow, get it into Excel so you can analyze it
  • This is a table of entries of patients to various
    locations in a clinic. Note that each patient may
    visit multiple locations.

12
About the Data
A patient type code
Location ID
When patient entered and exited the location.
Unique Patient Arrival ID
13
Your Challenge
  • How many times did a patient with PatientType2
    enter Location 38?
  • If you get that, how might you quickly find how
    many patients of each type entered each location?
  • What date, time and day of the week did the very
    first patient arrive to location 31?
  • How much total time (in minutes) did the patient
    with ArrivalID16 spend in the clinic?

14
Data Warehousing and OLAP
  • Data is good

A Dimensional Modeling Manifesto An Overview of
Data Warehousing and OLAP Technology
15
What is a Data Warehouse?
Data is
  • Subject oriented
  • Integrated
  • Time-Variant
  • Nonvolatile
  • Data enters DW from operational environment,
    transaction processing systems (TPS).

Source Inmon, W.H., What is a Data Warehouse?,
PRISM, Vol 1. No. 1 http//www.cait.wustl.edu/cait
/papers/prism/vol1_no1/
16
1. Subject Orientation
Source Inmon, W.H., What is a Data Warehouse?,
PRISM, Vol 1. No. 1
  • 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

17
2. Integration
Source Inmon, W.H., What is a Data Warehouse?,
PRISM, Vol 1. No. 1
  • 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

18
3. Time Variancy
Source Inmon, W.H., What is a Data Warehouse?,
PRISM, Vol 1. No. 1
  • 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

19
4. Nonvolatility
Change
Replace
Insert
Insert
Load
Access
Delete
Change
Operational
Data warehouse
Source Inmon, W.H., What is a Data Warehouse?,
PRISM, Vol 1. No. 1
20
The DW and OLAP Value Chain
21
Multi-dimensional Data Modeling
  • Designed to facilitate analysis (not
    transactions)
  • Common in data warehousing
  • Intuitive concept of many dimensions or
    perspectives on business measures or facts
  • view sales from customer, product and time
    perspective
  • Conceptual model
  • Hypercube an n-sided cube

22
Data (Hyper) cubes
2-d to 3-d cube
Rotating the cube
23
A Call Center ExampleTech 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
  • Created simulation model to generate lots o data
  • Many call centers in many industries

24
Steps in Multi-dimensional ModelingCall Center
Example
  • 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

25
The Star SchemaA multi-dimensional data model
Non-normalized
Non-normalized
Non-normalized
Normalized
Non-normalized
26
A Sales Star
27
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

28
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.
29
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

30
Data Warehousing and OLAPWWW Resources
  • A Dimensional Modeling Manifesto Kimball, R.
    http//www.dbmsmag.com/9708d15.html
  • Kimball and Associates http//www.ralphkimball.com
    ./html/articles.html
  • DSS Resourceshttp//dssresources.com/
  • Data Warehousing Information Center
    http//www.dwinfocenter.org/
  • Intelligent Enterprise http//www.intelligententer
    prise.com/

31
Some Good Books
  • The Data Warehouse Toolkit Kimball, R.
  • Definitive
  • OLAP Solutions Thomsen, E.
  • Definitive
  • Unlocking OLAP with Microsoft SQL Server and
    Excel 2000 Freeze
  • For newbies
  • Microsoft OLAP Unleashed Peterson and Pinkelman
  • For techasauruses

32
Lets OLAP
  • Download and unzip the following from the ATiB
    page accessible from my home page

http//www.sba.oakland.edu/faculty/isken/isken.htm
http//www.sba.oakland.edu/faculty/isken/ATiB406_D
ownloads.htm
  • Lets look at Excel Pivot Tutorial
Write a Comment
User Comments (0)
About PowerShow.com