Home > Articles > Data

Case Study of Building a Data Warehouse with Analysis Services (Part One)

  • Print
  • + Share This
In the first of a two-part series, Baya Pavliashvili, database administration expert, offers solutions to your business problem using a data warehouse.
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:

  1. Determine the reports that DW is supposed to support.
  2. Identify data sources.
  3. Extract data from their transactional sources.
  4. Populate the staging area with the data extracted from transactional sources.
  5. Build and populate a dimensional database.
  6. Build Extraction Transformation and Loading (ETL) routines to populate the dimensional database regularly.
  7. Build and populate Analysis Services cubes.
  8. 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.
  9. 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.
  • + Share This
  • 🔖 Save To Your Account