Spreadsheet Based Decision Support

1
Spreadsheet Based Decision Support

• Power Excel for Business Analysts

2
High Level Structure
Data Warehousing (Access)
Online Analytical Processing (OLAP) (Excel)
Session 1
Dealing with Muddy Data (Excel)
Session 2
Decision Modeling (Excel)
Building DSS With VBA (Excel)
MIS 446
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
Data Warehousing and OLAP

• Data is good

6
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/
7
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

8
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

9
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

10
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
11
Case for Data Warehousing

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

Source Data Warehouse Information Center,
http//pwp.starnetinc.com/larryg/casefor.html
12
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

13
Data (Hyper) cubes
2-d to 3-d cube
Rotating the cube
14
(No Transcript)
15
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

16
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

17
The Star SchemaA multi-dimensional data model
Non-normalized
Non-normalized
Non-normalized
Normalized
Non-normalized
18
A Sales Star
19
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

20
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.
21
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

22
Defining OLAP - ANALYSIS

• 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

23
OLAP - MULTIDIMENSIONAL

• The key requirement for OLAP
• Multidimensional conceptual view of data for user
• Underlying database technology will vary -
  relational, proprietary data cubes
• User can slice, dice, rotate, browse through
  multidimensional cubes of data

24
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/

25
Some Good Books and Articles

• The Data Warehouse Toolkit Kimball, R.
• Definitive
• OLAP Solutions Thomsen, E.
• Definitive, abstract and dense, good
• Unlocking OLAP with Microsoft SQL Server and
  Excel 2000 Freeze
• For newbies
• Summers Rubber Company designs its data warehouse
• Available from MIS 446 web (Schedule)
• Tons more, see some of the web sites on previous
  slides or search Amazon.com

26
Lets OLAP

• Download and unzip the following from the
  Downloads section of course web

• Lets look at Excel Pivot Tutorial
• Can even publish Pivot Tables to Web