The Buyer’s Guide to the Data Warehouse
Monty Bieber Industry Consultant Bull Information Systems
Credit Where It’s Due Dept.
This presentation is based on a white paper titled “Questions You Should Ask Today or You’ll Wish You’d Asked Tomorrow” by Ron Fryer of NCR Corporation
Overview
v v v v How data warehouses differ General guidelines Questions to ask before you start Questions to ask references
Processing Environments
v Repetitive processing
0 Repeated execution of known functions 0 All factors known ahead of time 0 Predictable & manageable
v Iterative processing
0 Each answer raises new questions 0 Also called ad-hoc processing 0 Requires adaptability & flexibility
Size Counts
v Megabytes
0 Datamarts 0 Small systems
v < 50 Gigabytes
0 Datamart/departmental warehouse 0 UNIX database/SMP
v > 50 Gigabytes
0 Departmental/enterprise warehouse 0 UNIX database/MPP
Complexity
v v v v v v Number of tables Number of indexes Query complexity Query variability Update cycle & complexity Number of users
General Guidelines
v v v v v Never take vendor claims at face value Never assume a vendor reference is valid Always talk with references
0 Vendor should not be present
Always talk with MIS staff Always talk with users of the system
Warning Signs
v v v v v No references are available The reference doesn’t check out The vendor must attend the meeting The users cannot attend the meeting The only reference is very far away
Things to Ask Yourself
1 Can you tolerate risk?
0 What are the impacts of delays? 0 What if your product doesn’t work? 0 How is the vendor’s support? 0 Can you afford to be the first site?
2 Is your system mission critical?
0 If not now, when? 0 If never, why bother?
Things to Ask Yourself
3 What is your processing environment?
0 Will you need iterative processing? 0 Can you afford complex administration? 0 Do you want to manage all queries?
4 How much data do you have?
0 Not as simple as it sounds 0 Archive tapes are not a valid measure 0 Build a logical model first 0 Multiply your model by 10
Things to Ask Yourself
5 How many concurrent users will you have?
0 All systems can manage one user 0 Many benchmarks are based on only one user 0 Don’t buy the world’s most expensive PC
Checking References
1 How big is the system?
0 Number of processors 0 Amount of memory 0 Amount of disk storage 0 Number of processing nodes 0 What kind of environment is running
Real-Life Example
We’re interested in bringing up a 500GB data warehouse. A vendor has provided a 1.4 TB reference. The reference account has answered question 1 with “1 TB.” Sounds like no problem, it’s still way bigger than the data warehouse we need to implement.
Checking References
2 How much detail user data is on the system?
0 Few people take into account free space and blocking factors 0 Many people include mirrored data or indexes
v Real-life example: our reference says there’s 700GB of user data.
Checking References
3 How much index space is on the system?
0 This depends entirely on the DBMS and the nature of the database 0 Most references include index space in the answer to Question 2.
v Real-life example: Our reference says there’s 400GB of indexes (this is actually low for most products). That leaves us with 300 GB of data.
Checking References
4 How much summary data is on the system?
0 If you summarize for one question, you probably need to summarize for others. 0 Systems that rely on summaries quickly contain more summary data than detail data.
v Real-life example: Assume only 100GB of summary data - that leaves 200GB of detailed user data.
Checking References
5 Do some applications run against specific sets of tables?
0 Tables designed for specific applications are probably NOT part of the detail data base
v Real-life example: We have a very conservative 20GB of application-specific data; we now have 180GB of user data left.
Checking References
6 How much work space is on the system?
0 There are many kinds of work space
w Temporary DBMS work areas w Sort space
v Real-life example: Our users have 300GB of work space; we still have 180GB of data.
Checking References
7 Is the data mirrored?
0 Generally the answer is yes 0 Disaster recovery involving reloads is too time-consuming
v Real-life example: Our users say yes - their system actually houses 90GB of data, less than 20% of our 500GB need.
Checking References
8 Who are the users?
0 What departments are using the system? 0 Make sure their users are similar to your users 0 Make sure their users are the ones you will be talking to
9 Do these users have access to the detail data?
0 Do end users have direct access to the database? 0 Do they have to extract and/or download data?
Checking References
10 How many concurrent users are supported?
0 How many log on at one time? 0 How many queries run concurrently? 0 How many do iterative processing vs. repetitive? 0 Does parallelism have to be tuned manually?
11 What types of access are supported?
0 Is any iterative processing allowed at all? 0 Multi-dimensional analysis is not necessarily adhoc 0 Ad-hoc means you can ask anything 0 Changing parameters is not ad-hoc
System Administration Questions
1 What is the batch window?
0 How often do you add data? 0 How often do you delete data? 0 How long does it take?
2 How do you get data in?
0 What is the process for loading data? 0 Are utilities available? 0 How much prep work is required? 0 How much work is required to verify the loads? 0 Do you have to stage the data?
System Administration Questions
3 Do the DBAs tune all or most queries?
0 If yes, they’re in trouble
w Nobody has this much money w Nobody has this much time
4 How many DBAs support the system?
0 The biggest cost may be the DBAs 0 What if you
w Double the number of users? w Double the size of the database? w Double the number of queries?
System Administration Questions
5 Are you where you need to be today?
0 You’re never done building a data warehouse 0 Warehouses evolve with the business 0 Is all the data they need loaded and working today?
6 How long after the hardware arrived did you deliver your first application?
"Were there unexpected delays? "Did it take an unreasonably long time? "Are there ways you could have done it better?
7 If you started over today, what would you do differently?
User Questions
What did you expect the system to do? Does the system do what you expected? Are you where you need to be today? What else do you need? How much time passed between the contract and a system you could use? 6 If you started over today, what would you do differently? 1 2 3 4 5
Conclusion
v A lot of unsupportable claims are being made v Not all vendors can do what they say v A lot of warehouse project have failed v If it isn’t growing, it probably doesn’t work v A lot of references won’t talk v Verify everything!
For further information
Monty Bieber Bull Information Systems 231 W. Lake Lansing Rd., Suite 100 East Lansing, MI 48823 Phone: 517-333-4468 m.bieber@bull.com