You are on the right path. You took the first important step in realizing it is not a good architectural solution to fire reports against your online application. Onlines are designed and tuned to facilitate quick and effective transactional processing, not analytical-oriented reporting.
The data warehousing architecture is a proven best practice for serving your organization's need for information access, be that access in the form of canned reports, dynamic queries, data extracts and downloads, multi-dimensional cubes, etc.
My recommendation for getting started with your understanding of how to go about implementing data warehousing is from a few sources. Bill Inmon's Web site has a wealth of information available for download. Check out his books on building the data warehouse. Also Claudia Imhoff and Bill Inmon's books on the Corporate Information Factory and Ralph Kimball's book, the Data Warehouse Toolkit. Also tap into industry associations such as The Data Warehouse Institute and DAMA. If you have the budget, several of us data warehouse consulting firms can help you establish a roadmap and get you on your way. As you digest this wealth of information, keep in mind that the data warehouse architecture is multi-facted and holistic. It is important you take time to draft an overall framework and lay out a deliberate program of projects to incrementally install components of that framework AND deliver value to the business community.
You will find that data extraction is one of the more important architectural considerations, but it is certainly not the only one. You will also find there is no single answer to your data extraction questions. Why? Because how to extract what data from which originating operational support system (OSS) depends on the requirements of your business (what data do they need and how current does it need to be) and on how each of your OSS applications are designed. You will want to seriously consider acquiring an (Extract Transformation Load) ETL tool. There are several on the market that fit all budgets and breadth of functionality needs.
I hope this helps. Keep in touch, I am interested in the progress you make.
For More Information
- Dozens more answers to tough data warehousing questions from Mike Lampa are available here.
- The Best Data Warehousing and Business Intelligence Web Links: tips, tutorials, scripts, and more.
- Have an DW tip to offer your fellow administrators and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical data warehousing questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle business intelligence and analytics
Related Q&A from Mike Lampa
When trying to design a data warehouse, we often try to model the database on the operational data model. Are there any guidelines in trying to ... Continue Reading
What is a surrogate key in a table? Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.