Case Study of Building a Data Warehouse with Analysis Services (Part One)
- Data Warehouse Lifecycle
- The Sample Scenario
- Dimensional Modeling
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Data warehousing has been around for decades. Yet, many businesspeople and quite a few technical folks don’t know what it takes to build a warehouse. Most people think that a warehouse is a data store that contains all data within the enterprise, is built within a couple of weeks, and thousands of people can use it for years to come without any additional effort or expense. Unfortunately, this view is incorrect.
In this series of articles, I give you a very simplistic example scenario and show you how you can go about resolving a business problem using a data warehouse. I also describe the efforts involved in building a warehouse for technical as well as non-technical individuals.
The first of this two-part article gives you an overview of steps involved in building a data warehouse and introduces the example scenario. It also teaches you how to create and populate a dimensional model. The second article goes into detail about Analysis Services, MDX, and analytical views that are generated from the data warehouse.
Data Warehouse Lifecycle
A Data Warehouse (DW) lifecycle can be summarized as follows:
- Determine the reports that DW is supposed to support.
- Identify data sources.
- Extract data from their transactional sources.
- Populate the staging area with the data extracted from transactional sources.
- Build and populate a dimensional database.
- Build Extraction Transformation and Loading (ETL) routines to populate the dimensional database regularly.
- Build and populate Analysis Services cubes.
- Build reports and analytical views by:
- Using a third-party application.
- Creating a custom analytical application and writing Multi-Dimensional eXpressions (MDX) queries against cubes.
- Maintain the warehouse by adding/changing supported features and reports.
Reading through these points, you should be assured that:
- A DW is never "finished." It is an entity that keeps growing along with the organization’s reporting needs.
- There is no such thing as a free lunch. If you want additional reports to come out of the warehouse, you need to spend additional time (and money) to extend the warehouse.
- Notwithstanding, what I said thus far, you can have DW functioning and available for use with a limited set of features as you build it.