Spreadsheet and database decision support
1 / 32

- PowerPoint PPT Presentation

  • Updated On :

Spreadsheet and Database Decision Support. Power Excel and Access for Business Analysts. High Level Structure. Data Warehousing (Access). Data Cleaning and Transformation (Excel). Online Analytical Processing (OLAP) (Excel). Session 1. Advanced Data Querying and Analysis

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about '' - Sharon_Dale

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Spreadsheet and database decision support l.jpg

Spreadsheet and Database Decision Support

Power Excel and Access for Business Analysts

High level structure l.jpg
High Level Structure

Data Warehousing


Data Cleaning and Transformation


Online Analytical

Processing (OLAP)


Session 1

Advanced Data Querying and Analysis

(Excel and Access)

Session 2

Models and



Building DSS

With VBA


Take MIS 436

Module overview and session themes l.jpg
Module Overview and Session Themes

  • Data is good.

  • Data is often not enough, need models too.

  • Models+Data+VBA = Decision support system

More themes l.jpg
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

The gist of the problem l.jpg
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

  • Don’t want to do it manually (why?)

Slide9 l.jpg

ACD Report Example

Report Header



Blank lines


Totals Lines



Why talk about this l.jpg
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)

  • It’s fun to play in the mud sometimes

Ok let s start with a little toy data extraction problem l.jpg
OK, Let’s 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.

About the data l.jpg
About the Data

A patient type code

Location ID

When patient entered and exited

the location.

Unique Patient

Arrival ID

Your challenge l.jpg
Your Challenge

  • How many times did a patient with PatientType=2 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 ArrivalID=16 spend in the clinic?

Data warehousing and olap l.jpg

Data Warehousing and OLAP

Data is good

A Dimensional Modeling Manifesto

An Overview of Data Warehousing and OLAP Technology

What is a data warehouse l.jpg
What is a Data Warehouse?

  • Subject oriented

  • Integrated

  • Time-Variant

  • Nonvolatile

  • Data enters DW from operational environment, transaction processing systems (TPS).

Data is:

Source: Inmon, W.H., “What is a Data Warehouse?”, PRISM, Vol 1. No. 1


1 subject orientation l.jpg

Source: Inmon, W.H., “What is a Data Warehouse?”, PRISM, Vol 1. No. 1

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 l.jpg

Source: Inmon, W.H., “What is a Data Warehouse?”, PRISM, Vol 1. No. 1

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 l.jpg

Source: Inmon, W.H., “What is a Data Warehouse?”, PRISM, Vol 1. No. 1

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 l.jpg
4. Nonvolatility Vol 1. No. 1










Data warehouse

Source: Inmon, W.H., “What is a Data Warehouse?”, PRISM, Vol 1. No. 1

Multi dimensional data modeling l.jpg
Multi-dimensional Data Modeling Vol 1. No. 1

  • 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”

Data hyper cubes l.jpg
Data (Hyper) cubes Vol 1. No. 1

2-d to 3-d cube

Rotating the cube

A call center example tech support for ms office l.jpg
A Call Center Example Vol 1. No. 1Tech 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

Steps in multi dimensional modeling call center example l.jpg
Steps in Multi-dimensional Modeling Vol 1. No. 1Call 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

  • The star schema a multi dimensional data model l.jpg

    Time dimension Vol 1. No. 1

    Customer dimension

    Fact table

    Problem dimension

    Application dimension

    The Star SchemaA multi-dimensional data model






    A sales star l.jpg
    A Sales Star Vol 1. No. 1

    Data models relational vs multi dimensional l.jpg

    Transaction Vol 1. No. 1 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 Models Relational vs. Multi-dimensional

    One e r vs many stars l.jpg
    One E-R vs. Many Stars Vol 1. No. 1





    One E-R model for all the business process.

    One star per modeled business process.

    What is olap l.jpg
    What is OLAP? Vol 1. No. 1

    • 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

    Data warehousing and olap www resources l.jpg
    Data Warehousing and OLAP Vol 1. No. 1WWW 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.intelligententerprise.com/

    Some good books l.jpg
    Some Good Books Vol 1. No. 1

    • 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

    Let s olap l.jpg

    CallCenter_Scenario01_DW.zip Vol 1. No. 1


    Let’s OLAP

    • Download and unzip the following from the ATiB page accessible from my home page:



    • Let’s look at Excel Pivot Tutorial