Question: We're in the process of updating a data warehouse to include new information. IT is quite keen to understand what reports we want to produce from the data. I was always told by past "experts" that one shouldn't worry too much about the reports since, in reality, queries from users cannot be predicted. The data we are adding is relatively simple: sales and budget numbers, a product hierarchy, a salesperson reporting hierarchy and a time dimension.


I thought that the data warehouse could be designed to cover all possible queries on these dimensions that we could throw at it without providing a list of reports to IT. Was I right?


Sid’s Answer: First of all, some reports can be predicted. The reports users get today that will be satisfied by the data warehouse will still need to be run, perhaps somewhat differently and delivered differently, but they will still be needed and can be predicted. Some types of queries are predictable, but you will never be able to anticipate all the queries. Ideally, the results of one query will generate an idea, which will be the basis for the next unpredictable query.


There will always be some requirements for queries for which no data exists – so much for a design that covers all queries. There will be some queries that will run, but your initial design will result in terrible performance and so you will either live with the terrible performance or you’ll make some changes to your design.


So it would be useful for the folks in IT to have some idea of the reports the users are planning to run so they can design the data warehouse for what is known. They will have to be on their toes to monitor user activity and to be able to make the appropriate changes to deal with queries and reports that are very different and unanticipated.


Joe Oates’ Answer: Actually, a list of reports can be quite useful, but you are right in believing that a list of reports alone cannot tell you everything that needs to be in a data warehouse. The main thing that you need to do is to talk to a representative sample of users and management to understand what they need from the data warehouse.


Undoubtedly, each of these users is getting at least one report. What I have found to be effective is to set up interviews with each of these users, including management, and use these reports as a basis for making the interviewee feel at ease. I ask each person to tell me what reports they use to make key decisions and, specifically, what items on the reports are used to make a decision. Then, I ask them something like “What information do you need that you are not getting from your reports, like sales by region or what are my top stores and salesmen?” After they tell you, you should keep asking the question “Can you think of anything else?”


If you can talk to 10 or more people and get their responses, you should be able to make up a pretty comprehensive list of the kinds of queries that are needed. If you want more detailed information on interviewing for data warehouse requirements, The Data Warehouse Lifecycle Toolkit (First and Second Editions) by Ralph Kimball, et al. has a very good section on interviewing.


Chuck Kelley’s Answer: I would use the business communities “reports” as a basis of what data I get from the source. I also like to use the “touch it, take it” method of building my data warehouse. If I touch some data, I take it all. That way we can get more data than is required by the reports. And, while I am in the source system(s), I look around for interesting data that also might be available.


The problem is that sometimes the owners of the source system will not allow us access except to what we need. Then we have to show what we need and use the “touch it, take it” to get other data as well.

In short, you were correct, but sometimes practice doesn’t allow us to always do the correct thing.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access