Best Practices in Data Warehousing

1
Best Practices in Data Warehousing
A Shared Business and IT Accountability
Betty Kight Senior Data Warehousing
Consultant Strategic Warehousing Technologies
2
Topics

• Best Practices
• Focus on Business Value
• Strong Leadership
• Willing to Change Corporate Culture
• Implementation of Business Rules
• Value Driven Query Activities
• Timely Load Processing
• Business Driven Configuration/Version Changes
• Business Considerations within Operations and
  Infrastructure
• Well Thought Out Rollout Strategy
• Example

3
Opening Thought...
The business of Business is business. The
business of IT is supporting the Business in the
achievement of their goals. That is, ultimately,
how the success of Business AND IT will be
judged.
4

5
Leadership
Ensure effective data warehouse leadership is
positioned at the executive level within the
organization

• Data Warehouse Leader Characteristics
• Influential level
• Business knowledge
• Problem solver
• Communicator
• Functional Area Accountabilities
• Infrastructure
• Operations
• Application Development
• Data Warehouse Team
• The Business Community!

The balance of power between the groups is hard
to accomplish and underscores the need for strong
leadership in the data warehouse group.
6
LeadershipData Warehouse Governance Committees
Data Governance Team
DW Steering Committee
Develops Business Rules Enforces data policies
in terms of data validity, accuracy, ownership
User Forum
Business Advisory Team
Data Warehouse Team (Data Warehouse Advocacy)
End Users Tips Techniques Exchange
Successes Usability Feedback
Dept. Managers User Project Mgrs Define
Requirements Rollout Training
Build, Run DW Measure ROI
7
Focus on Business Value
Ensure accurate metrics are developed and
implemented to determine business value derived
from the data warehouse.

• Cost/Benefit Analysis (ROI)
• Measurements, Measurements, Measurements!!!
• Measure the Cost
• Cost of Acquisition
• Cost of Usage and Management
• Cost of Failure/Missed Opportunity
• Measure the Benefit
• Return on Action

8
Focus on Business ValueDetermining the R in
ROISix Key Business Questions
What are your key business issues and
opportunities?
Info Req.
What information do you need to support these
issues and opportunities?
Which information needs are being satisfied
today by the data warehouse?
What process change is/would be associated with
the issue and opportunity?
What is the value of that process change?
How will that value be measured?
9
Focus on Business ValueDetermining the I in
ROITotal Cost of Ownership
Cost of Acquisition
Software
Hardware
Operations
Applications
Components
Labor
Client
Cost of Usage and Management

Cost of failure and missed opportunities!
10
Cultural Change
Ensure corporate culture supports proactive,
predictive analysis and decision making, and
provides the tools with which to do so.

• Proactive vs. Reactive Analysis
• Look for things and not at things
• IT assists end users, not perform the work for
  them
• IT as stewards of data, not gatekeepers to
  analysis

• End User Tools
• Obtain only the information needed
• Automate reporting phase
• Buy vs. Build
• All 3rd party tools have shortcomings
• In-house developed applications
• Larger effort, greater return?

11
Iowa Department of Revenueand Finance

• Tax Gap Compliance Project
• An initiative to improve compliance through the
  implementation of technologies that would enable
  us to more effectively identify and understand
  the non-compliant population, then take the
  appropriate steps to education, communicate, and
  ensure voluntary compliance.

However, before we can begin to analyze and
understand our non-compliant population, we must
first find a way to accurately and efficiently
identify the non-compliant population.
12
Iowa Department of Revenueand Finance

• Challenges
• Disparate information systems
• Inability to fully utilize available internal and
  external data
• Limited management and information reports
• Labor intensive and paper producing audit
  processes
• Major Goals and Objectives
• Optimize revenue and promote voluntary compliance
• Optimize customer needs satisfaction
• Optimize decision-making techniques

Ultimately our goal was to bring together in a
single database, all the information available
about a single entity so that we could accurately
determine our tax gap, work smarter and become
more productive. We needed a solution that
would help us target the true tax gap and be less
intrusive to the compliant taxpayer. Rhonda
Kirkpatrick, Project Manager
13
Iowa Department of Revenueand Finance

• Data Warehouse Benefits to Date
• We have data! Data from 21 diverse source
  systems with 6 to 10 years of data
• Source system data supports multiple tax year
  program leads including individual income,
  corporate income, sales, retailers use, consumer
  use, and withholding taxes
• Users have direct access to the data to to
  perform ad-hoc as well as pre-defined queries
• Over 10,000,000 in revenues collected with
  expectation of reaching 19,000,000 by June 30,
  2002

Within four months of the contract start date,
we were generating audit leads and producing
positive results, by allowing us to match data
from diverse source systems to perform
sophisticated queries with drill-downs to analyze
and verify the data to identify and generate
potential tax gap leads. Never before has so
much data been available to the business users.
14
Query Performance
Ensure query performance optimization decisions
drive optimum business value.

• New Queries
• Understand anticipated Return On Action!
• Understand user run-time expectations!
• Document end-to-end execution!
• Existing Queries
• Understand whether run times are consistent or
  volatile and the frequency at which they run
• Implement optimizations you justify and explain
  to users why others are not.
• Document changes and capture explains for new
  queries

15
Query Performance
Understand the difference between performing ad
hoc and running amuck.

• Query Approval Process
• New repetitive queries should be reviewed for
  effectiveness and optimal performance
• Ensure queries are documented and provide
  developers with Teradata operational knowledge
• Ensure the data warehouse does not become unduly
  functionalized by one set of queries
• End User Education
• Users should understand the impact of their
  response time requests
• Understanding what data is available
• Manage response time expectations
• Teradata Certification

16
Load Processing
Implement effective, timely load processing
measured again Service Level Agreements.

• Service Level Agreements
• How much can you lift and how fast can you lift
  it?!?!
• Loading Methodology
• Investigate how the data is staged for loading
• Manage priority handing at the database level not
  at the host level
• Load Timeliness
• Enforce proper change control and operational
  rigor
• Implement service level agreements
• Dont wait until there is a problem!

17
Configuration/Version Changes
Ensure configuration and version changes are
analyzed to determine if, why, when and how they
should be implemented into the environment.

• Teradata Versions
• Determine the performance impact of new releases
• Analyze the implication to third-party tools

• Priority Scheduling
• Critical in Mixed Workload Environments
• Explore/Implement Priority Scheduler

18
Operations and Infrastructure
Ensure operational processes and the overall
corporate infrastructure support the data
warehouse environment.

• Change Management
• Moving new applications into production
• Ensure production environment is protected from
  unnecessary and uncontrolled change
• Schedule and have well documented back-out plans
• Realistic test environment
• Attention to application-specific changes
• MetaData Strategy
• Understand the benefit will result from having
  consistent data definitions and identifying and
  improving data quality.

19
Operations and Infrastructure

• Supportability of Business Process
• Make judgement calls about the criticality of a
  process in relation to the business need.
• Develop a comprehensive set of Service Level
  Agreements
• Capacity Planning
• Involve all stakeholders!
• Capacity planning cycle
• Growth of existing detail data
• User Concurrency
• Query Complexity
• Query response time/data availability
• Track Actual vs. Projected

20
Rollout StrategyLinkage of Information Models
Enterprise Information Model
Subject Area A
21
Rollout Strategy
Develop and implement an overall corporate
strategy for the inclusion of additional data in
the warehouse.

• Subject Area Requirements
• Fully exploit what you already have
• Concurrently, identify new subject areas
• Gain support of the business community
• Standards
• Becomes more critical as more subject areas are
  added
• More than standards for data elements
• change control
• application development
• security
• Compliment current practices, yet support the
  vision
• Leverage the data warehouse across the entire
  enterprise
• change in perspective
• understand your role in making the warehouse a
  success!
• ROI
• Capture it as it happens!

22
Rollout StrategyOpportunity Analysis
Develop Evaluation Criteria

• High ROI,
• Easy to implement,
• High impact on Decision Confidence

Identify Opportunities
High
Identify Benefits
Business Value
Easy
Ability to Implement
Prioritize Opportunities
Low
Difficult
High
Low
Develop Business Impact Report
Impact on Decision Confidence
23
Cost Benefit
100,000 200,000
24
Cost Benefit
100,000 200,000
200,000 400,000
25
Cost Benefit
100,000 200,000
200,000 400,000
100,000 200,000
26
Cost Benefit
100,000 200,000
200,000 400,000
100,000 200,000
100,000 200,000
27
Cost Benefit
100,000 200,000
200,000 400,000
100,000 200,000
100,000 200,000
100,000 200,000
28
and understanding the associated
costs/benefitsin
Cost Benefit
100,000 200,000
200,000 400,000
100,000 200,000
100,000 200,000
100,000 200,000
600,000 1,200,000
29
(No Transcript)
30
A Transportation Case Study Asset Management
...Horsepower Hours idling in rail yards
31
Key Success Indicator Velocity
What impact can reducing dwell time have on
increasing velocity?
32
Cycle Flow (Velocity) Metrics
Velocity
Miles
Miles per Segment
Segments
Hours
Hours per Segment
Segments
Arrive Intermediate Terminal
Dwell Time
Depart Intermediate Terminal
Improving Transit Times (Bottlenecks) 22 Million
33
Dwell-Time Business Rules

• Term (A noun or noun phrase with an agreed upon
  definition)
• Schedule
• Scheduled train departure
• Scheduled train arrival
• Dwell Time
• The difference between scheduled train arrival
  and scheduled train departure
• Excessive Dwell Time
• The difference between scheduled train
  arrival/departure and actual train
  arrival/departure
• Reason Code
• Reason for delay in train departure

34
Dwell-Time Business Rules

• Fact (Connects terms into sensible business
  relevant observations)
• Dwell Time tracks train terminal delays
• Excessive dwell time exceeds scheduled train
  arrival/departure
• Business Rules
• Dwell Time is deemed excessive if it exceeds the
  schedule by 15 minutes (mandatory)
• Excessive dwell-time must have a reason code
  (mandatory)
• If Dwell Time is excessive, then notify the
  dispatcher (action enabler)

35
Locomotive Dwell TimeBest Practices

• Understood the potential business value
• Able to Justify/Quantify It
• Leadership
• Supported enforcement of Business Rules
• Change Corporate Culture
• Put decision making in the field
• Value Driven Query Activities
• Understood the return expected on queries
• Timely Load Processing
• Ensured data supporting this application was
  there on time!
• Business Considerations within Operations and
  Infrastructure
• Recovery Process designed to support the
  criticality of this application

36
Business and IT share in this success story!
37
It always comes back to this!
knowledge
The keys to success in data warehousing are
pretty straightforward - Never lose sight of
the primary objective - achieving business
value! - Implement mechanisms to quantify that
value - Change things that inhibit the ability
to achieve that value
38
Thank You!
These customers have implemented the cultural,
organizational, functional, and operational
changes required to fully exploit their data
warehouseall based on driving optimum business
value. That is very good news indeed. The best
news, however, is that as you move your data
warehouse forward, the opportunity to learn and
benefit from their missteps and their successes
is at your fingertips.
betty.kight_at_ncr.com 636-273-5418