Nagesh V. Anupindi

Feb 22nd
Text size
  • Increase font size
  • Default font size
  • Decrease font size
Home Publications Technology Articles How Oracle can meet Data Warehouse End-User Expectations

How Oracle can meet Data Warehouse End-User Expectations

E-mail Print PDF
User Rating: / 3
This paper presents the key steps and the responsibilities of a Data Warehouse or Data Mart Architect in developing a Data Warehouse or Data Mart project. The emphasis is placed on the process of gathering end-user requirements and methods to meet their expectations. Hence, the steps involved in setting the standards, providing the vision to a Data Warehouse or a Data Mart project, and documenting the specifications are stressed. These steps are essential whether the Architect is trying to implement a Data Warehouse or a Data Mart.

This paper is published in RMOUG Proceedings, January 28-29, 1998. The corresponding Power Point is presented during RMOUG Training Days 1998 in Denver, Colorado.

Though this paper is aimed for Data Warehouse or Data Mart Architects, following roles in a Data Warehouse or a Data Mart project can be benefited from this paper:

  • Data Warehouse or Data Mart Architects (Program Managers): Responsible for various components in the Warehouse, proposes technical solutions to IS Managers, and validates the end-user requirements of the Data Warehouse or the Data Mart project.
  • IS Managers: Responsible to provide integrated enterprise-wide Information System solutions and environment.
  • Product Manager(s): Leader(s) from end-user community and makes sure all the requirements are specified for each release. The Product Manager also negotiates these requirements with the Product Managers, the Data Warehouse Architect, and IS Manager(s).
  • Application Developers: Responsible to implement the solutions.
  • Business Analysts: Responsible to document the end-user requirements.

How do you scope your Data Warehouse or Data Mart project? Can you define a Vision statement for what you want to do in your project? Are you aware of the components that should be defined? What do you need to communicate with your IS Manager? This paper attempts to answer these questions so that you can have successful implementation and smooth roll-out of your Data Warehouse or Data Mart.

For most of the Warehouse architects, the ten thousand dollar question is “How do I convince my IS Manager that what I would be spending on the Warehouse project will actually be paying off by itself?”

Most of the times, an Architect can convince the higher authorities using Return-on-Investment (ROI) estimate. Now, the question is how do you calculate the ROI. Though there is no magical formula in which you can plug in some numbers to get the ROI, you can use the list of benefits described in “Business Objectives” sub-section of “Envisioning: Vision/Scope” section of this paper. When these benefits are quantified, you will be able to arrive at an estimate on returns of your Data Warehouse or Data Mart project. In addition, any evidence that supports the following objectives of your IS Manager would be helpful:

  • to build systems that support end-users
  • to improve quality of existing applications
  • to increase productivity of existing resources
  • to develop applications faster, and
  • to exploit new technologies for competitive advantage of the organization.

On the other hand, if you are having tough time selling the concept of Data Warehouse to your management, either you are looking at wrong solution or you are playing the wrong role in your organization. At this time, look back and see what you are trying to do. Prepare the laundry list of requirements from end-users and present it to the IS Manager(s). If Data Warehouse is the right solution, then this list should talk itself into the Data Warehouse or Data Mart.

Once the management agreed with the Data Warehouse initiative, your responsibility is to support these requirements and discuss how you would provide solutions with a suitable solution framework and the right set of tools.

What kind of data do you have to load into your Data Warehouse from internal/external sources? However academic it may sound, the right answer to this question is the data that helps you build the Warehouse that supports Strategic or Tactical decisions. The internal data in your organization will help the Warehouse to provide operational decisions while the external data helps you to disclose the relative position of your organization in the market place. In addition, to provide the Strategic or Tactical decisions, the data should also be summarized because the data to support the operational decisions is mostly at detailed level.

Hence, the first and foremost expectation from your end-user should be (if not, set it!) that their Data Warehouse should answer Tactical Business Questions because, profitability and ROI are closely tied to the ability to make decisions that correctly reflect changing business conditions.

What does a strategic/tactical decision means anyway and how does one get it from the Warehouse? Usually, the data from your customer provides you some Information which when analyzed becomes Knowledge. You use this Knowledge to figure your new marketing plans and strategies, which in turn provides you more data about your customer. In this whole life cycle of the use of the Warehouse data, you have seen both the ROI as well as some strategic/tactical decisions.

The reports your end-user needs/asks for are two types. The first type contains the strategic/historical summaries over long-range of periods. This type of reports can be obtained from the Warehouse. The second type of reports, though no text book explains you that this might be a possibility, are based on current volatility and correlation of the data. Even if you have built your Warehouse with loading frequencies as high as 12 hours, you would never be able to get the second type of reports from the Warehouse. How do you manage these type of expectations? The Operation Data Store (ODS), that provides dynamic data, gives you some insight into such scenarios. The ODS provides your end-users a consistent overview of consolidated operational information maintained in disparate operational systems. Though many architects may believe that building an ODS is a necessary step in building a Warehouse, ODS should not be built solely to pump the data into Warehouse. Build it because you have a need for reporting on the operational data. Note that the main difference between an ODS and a Warehouse is that the data in ODS is updateable while, Warehouse contains static data.

The major concern for your end-user would be “ease of use”. However, the more you concentrate on it, the worst will be the performance of your Warehouse. Keep an eye on those wishes.

Your responsibility to your user will be to make the right technological choices and build the right architecture. In addition, your responsibility to IS Manager is to provide supporting evidence for making such choices.

Set some ground rules to both your IS Manager as well as to your user community regarding how to resolve conflicts, who owns the data, and how to communicate and maintain relationships with the users. These rules will help you concentrate on the data issues than the political issues about data ownership.

Make your end-user understand the complete development process. This process is explained in the following sections with a four-phased solution framework. Also, let your end-users know that you will give them a post-project evaluation period in which they can feel free to state all their unanswered questions or unrealized wishes. However, it is your responsibility and to your advantage to conduct a post-project evaluation.

Though may not applicable to all cases, billing your customer based on the usage (charge-back methods) could be a major issue. Even if is not a must for financial reasons within your organization, it is definitely an issue to determine the usage of the Warehouse.

Do NOT push the security features into the next release because what your users see in the first release is not only what data you provide them but also how securely you are providing it. If you fail to attract the users to use the Warehouse in the first iteration, you may never have the second phase of the Warehouse!

Do you have right people on your team to help meet the end-user expectations? Technical issues, political issues, human performance issues such as compatibility play major roles in the development process of a Warehouse. If necessary, you need to change the processes and reorganize the structure. Also get the people who are more eager to learn and easy to change and train them if you need to.

Even if your enterprise Data Warehouse project is expected to take a long time such as three years, identify the short-term deliverables for the next three to six months period. These short-term deliverables should always address the solutions to immediate problems. Moreover, these solutions should fit within the corporate architecture you have planned. This is the best way to put a time-box around end-user expectations so that you can manage and have control on your product releases.

Also, the iterative phased approach will be able to overcome the dilemma of “architects need to know what users wants out of the Warehouse before the implementation of it while user will only discover what they want AFTER the architect delivers the Warehouse”.

For managing the deliverables and for smooth roll-out, the following sections describe various phases of a Data Warehouse or Data Mart project. These phases are based on Microsoft’s Solutions Development DisciplineTM (SDD) but expanded by Raymond James Consulting based on experiences in meeting its clients’ needs. The SDD framework introduces a milestone-driven process model to manage the risks inherent in a dynamic information solutions environment so that, project teams can concentrate on delivering value to the organization rather than getting trapped in political issues. This framework balances the schedule, resources/ budget, and the features/requirements in a project.

As depicted in the Figure 1, each release of a Warehouse or a Data Mart contains four phases, viz., Envisioning, Planning, Developing, and Stabilizing. Each of these phases has well defined milestones. Following sections describe the purpose of each phase and the milestones to be achieved.


The Envisioning phase contains the primary discovery process that determines the features in the Warehouse project. This phase also helps to determine the schedule, budget, and resources required for the next phase (Planning phase). The following components are essential in a Vision/Scope deliverable:

  • Executive Summary: Summary of the features that are in the scope of current release and the features that will be part of next release(s). The Executive Summary should not be more than one page.
  • Current Process Overview: List the processes that have been substituting the proposed Data Warehouse such as pulling Excel spreadsheets from Legacy systems.
  • Problem Statement: List the problems that exist in the Current Process. This section should also consist, for example, the problems such as inconsistencies and timeliness, which exist with the Excel spreadsheets from Legacy systems, etc.
  • Business Objectives: This section should consist the following sub-sections. This is where the benefits of Data Warehouse from various perspectives should be listed:
    • Overview
    • Customer-Oriented Benefits
    • Internal/Operational Benefits
    • Financial Benefits
    • Continuous Improvement Benefits
  • Vision Statement: Make one paragraph of what exactly you, as an Architect, are envisioning about the Data Warehouse or the Data Mart project.
  • Scope: List of features: The set of features listed here will drive the Functional Specifications document in the Planning phase of the Data Warehouse or the Data Mart project. This section should consist of the following sub-sections. While writing this section, make sure that you explicitly list all the features that will be excluded in the current release.
    • Overview
    • Inclusions
    • Exclusions
  • User/Customer Profile: This section identifies various types of Warehouse users, lists the profiles for each type such as their knowledge in using Graphical User Interface applications, the memory requirements of their Personal Computers (PCs), necessary software configurations, etc.
  • Solution Concept: This section describes what would make the project successful, what vendor tools will be used in Warehouse development, and what sign-off forms will be used at the end of each milestone.
    • Project Success Factors
    • Products and Services in the Solution
    • Operational Concept
    • Deliverables
    • Acceptance Criteria
  • Assumptions: This section lists all the assumptions considered in building this document. Specifically, any assumptions made about the responsibilities of Users or Customers.
  • Constraints and Dependencies: This section describes all the anticipated constraints and dependencies in for the success of the Warehouse development.
  • Risk Assessments: This section should list the risks, consequences, and mitigation plans when the dependencies described in the above section are not met.
  • Project Structure: This section should list the actual names of
    • the leader from end-user community (Product Manager),
    • the person who would be responsible to manage the resources and features (Program Manager),
    • development team
    • quality assurance team
    • logistics team
  • Future Features: The list of features in this section might overlap with the features in the “Exclusions” sub-section of the “Scope: List of Features” section. However, this section identifies the features that are important to your end-users in future releases as well as the features that might potentially become part of current release (through change management) due to the demand in your market.
    • Preferred Future Features
    • Market Demanding Future Features
  • Glossary: List and define all the terms that will be used in the Data Warehouse project. This list should aid the IS Managers as well as the end-users in understanding the terms in a Data Warehouse project. For example, define what exactly an Operational Data Store (ODS) means, and what role does it play in your project.

The Planning phase of a Data Warehouse or a Data Mart consists of expanding and documenting each feature from various perspectives. The list of features that you will be defining in this section should come straight from the “Scope: List of Features” section of your Vision/Scope document.

Each requirement/feature should match back to a business objective. Hence, providing a list of business services as a part of each feature ties back that feature to an objective.

Though both you and your end-users may agree on some of the requirements, these decisions should also be based on the source-data-driven so that you can actually deliver what you are promising. Hence, the functional specifications document should include all the data services that you are going to provide in the current release.

As an addendum to these specifications, also deliver the base data-model that supports the data services you promised in the functional specifications document.

In addition, if you are using preformatted reports out of the Warehouse or the Data Mart, your job becomes tougher. You need to define the contents of the report, placeholder for each field on the report, and the summarization requirements on the report (to get you the GROUP BY clause in your SQL). Also, you may need to define the parameter query windows for each report so that your development team can know the classes or objects that they need to be built.

To summarize, each feature should be specified in a great detail with the following sections:

  • Pictorial Depiction (if the feature is a Window or a report)
  • Description (two to three sentences in simple terms, mainly for end-users’ sake, of what this feature does)
  • Business Services (what are you automating and how does this feature enhance the business performance)
  • User Services (where can a user trigger this feature and what other features can be triggered from this feature)
  • Data Services (what data will be accessed or modified as a result of this feature).

On the implementation side, you as the Warehouse architect, will be responsible for managing the actual construction, providing necessary input such as test scripts to quality assurance team, and providing the schedules to logistics team for smooth roll-out of the product(s). It might be a good idea to set some standards to the delivery process so that your iterative short-term deliverables always provide the same quality and meet the predefined criteria.

Extraction Tools: Oracle Stored Procedures, Packages, and Gateways
Though the extraction tools may not meet all of your transformation or cleansing requirements, they can be extremely valuable for getting data out of disparate and non-relational systems, particularly if you don’t have knowledgeable developers available. However, most of the times, after transforming the data, the data from different systems need to be matched, merged, and consolidated for creating an appropriate business view. Most of the time tools are not available to solve these problems. In this situation, your development team might need to develop custom code using Oracle stored procedures and packages. In addition, the Oracle’s gateways can help you connect the Warehouse or ODS to your disparate systems.

Reporting Tools: Discover 3.0
To meet the reporting requirements of your Warehouse or Data Mart’s end-user, Discoverer 3.0 would be a great help. Features such as pivoting, drag and drop provide great insight into the data, which is a necessary requirement for strategic and tactical decision making process. In addition, the learning tools, online support, and the overall ease of use of Discoverer 3.0 can lift lot of help desk support and training responsibilities off of your (or IS department) shoulder. According to the American Institute for Research, the Discoverer 3.0 has been a preferred product by end-users over the leading reporting tools.

Providing easy access: Oracle Replication
If you face high volume of end-users trying to report off of your Warehouse, Data Mart, or Operational Data Store (ODS), it might be necessary to replicate the data. Especially, when you architect-ed the corporate ODS to be used as a reference but updateable database by operation database users at different locations, replication becomes optimal solution. Oracle’s symmetric replication technology provides solution to this problem. With this technology from Oracle Corporation, the data integrity of your ODS databases can be preserved by conflict detection and resolution. Good news is that the update resolution methods can be customized to your business rules. Moreover, with multiple sites having the same database, your ODS or Warehouse can never go down because you can redirect the end-user traffic to the server that is alive.

As a part of Program Manager role, you will be responsible to manage the Quality Assurance and Logistics team. For quality assurance team, you would be responsible to help them in developing the test scripts. Though these test scripts should be built by Quality Assurance team straight from your functional specifications document, practically you would need to put aside some time for helping this team. On the other hand, for smooth roll-out, you are responsible to provide the schedule so that the Logistics team can plan on their activities.

More to Watch-outs!

End-User Training
You should propose a query and reporting tool for end-users. However, training and spoon feeding them through using it is not a Warehouse architect’s job.

Who are your customers?
When it comes to your customers, both your IS manager and the end-user are your primary customers. Do not let each one bother you. Use them so that they are useful in solving each others’ problems. You are there to provide a solution to your organization. Not to fall into the political trap. Use one to solve the problems of the other. You are not supposed to control your end-user. Make sure you communicate that to your IS Manager. On the other hand, you have to tell your end-user that you can provide solutions only with the tools your IS Manager provides. If you don’t have the right tool, you just cannot promise a feature. Your responsibility is to identify and know-how-to-use the tool. Not to go and buy it when needed and become responsible for submitting the expense reports.

Scope Creep
Collect the list of “not this time” features. Make sure you start this list from the first day of requirements gathering. This will establish confidence in the relationship between you and your user.

During tool selection, be clear on the goals you want to meet with the tools before you hear market pitches. Your aim should not be to incorporate all the features provided by a tool. You aim should be to determine which tool provides all the features you have determined with your user and the IS Manager.

Sign-Off Process
Create user sign-off packages that will initiate sign-off meetings. At the same time, give instructions to the user what to look for before signing. Make sure your end-user and IS Manager recognize that this is a big occasion.

Meaningless Features
Maintain data integrity between different end-user communities of your Warehouse. Give them reasons why you cannot provide what they are asking for and how it violates the requirements from other users. If they need to resolve any issues with the other users, they should be going through the IS Manager with proper documentation on the cause and the possible alternatives for resolving the disintegrated data.

Overall Guidelines

Sponsorship Get top management support. Let end-users drive the requirements. It is their budget and their Warehouse.
Expectations Promise less and deliver more. Most Architects get excited in initiating a Warehouse and end-up promising more than they can deliver.
Architecture Limit scope to the first phase. Build it incrementally (only care about scalability of your architecture). Make sure both IS Manager(s) and end-users are being communicated continously in decision-making process / meetings
Integration Make sure the tools you buy can be integrated into your first phase architecture as well as the one you will need in second, third, etc., phases.
Metadata Get more information than end-users know and store it in metadata. Though users may think it is not necessary to store this information because they know about their system(s), you really need to have this information in metadata at least for positive confirmation. Even ifyou are not building a full-fledged Metadata, present this information in some form of on-line help.
Data Cleansing Make sure that the codes that exist in one system are understandable by other user. Expand them in metadata if you need to. Or, clean it before loading them into Warehouse
Size Do not load every bit of data into Warehouse. If they need details, they should refer to ODS. If they need details for last 10 years, then Data Warehouse is not the answer. It exists to show trend but not the complete history of every bits and pieces of the corporation.
Aggregates Give them highest level of summaries. For every other level of summary they request, make sure that you understand the actual need for it. Do NOT let your end-user tell you what should be in GROUP BY statements.
Tools Give them easy tools. You should not be highly involved in training end-users in these tools.
Roll Outs Make your roll-out cycle (time between the versions) no longer than six months. This way, you will always be delivering what end-user needs, not what they thought they need two years ago.

In summary, this paper discussed how to initiate Data Warehouse or Data Mart projects, and the key factors that an Architect need to understand about their project and their end-users. Also, a framework for building Warehouses or Data Marts that provide a high degree of end-user satisfaction is presented. This framework contains four major milestone-based deliverables for smoother roll-out and short-term deliverables.

Nagesh Anupindi is Oracle / Data Warehousing Group Practice Leader at Raymond James Consulting, a Denver-based consulting firm specializing in component-based analysis, design and development of Information Technology Solutions, Data Warehouses, and Data Marts. He received his Ph.D. in Computer Engineering from University of Rhode Island and has three years of experience as Senior Consultant in design and development of Client/Server systems and Warehouses using Oracle tools. He can be reached at:
Nagesh Anupindi, Raymond James Consulting, 4582 S. Ulster St. Parkway, Suite 100, Denver, CO 80237
Tel: (303) 770-7200, Fax: (303) 770-5452
(4563 Longs Court, Broomfield, CO 80023; cell: 303.246.6641).
Email: This e-mail address is being protected from spambots. You need JavaScript enabled to view it .