An Analytics Design Methodology

A data warehouse can be built top down or bottom up. To build a top-down warehouse, you need to form a complete picture or logical data model for the entire organization (or all the subsystems that are within the scope of the project, such as all financial systems). In contrast, building a warehouse from the bottom up takes a much more departmental or specific business-area focus (such as Sales Order system only). This breaks the task of modeling the data into more manageable chunks. Such a departmental approach produces data marts that are potentially subsets of the overall data warehouse. The bottom-up approach can simplify implementation. It helps get departmental or business-area information to the people who need it, makes it easier to protect sensitive data, and results in better query response times because data marts deal with less data. The potential risk in the data mart approach is that disparity in data-mart implementation can result in a logically disjointed enterprise data warehouse if efforts aren't carefully coordinated across the organization.

Before you embark on an OLAP database creation effort, the time you spend understanding the underlying requirements is the best time you can give your effort. If scope is set correctly, you will be able to achieve an industrial-strength OLAP design without much difficulty.

First, you need to take care of some ground work:

  1. Assess the scope of what you want to represent in the DSS environment carefully. Start small, as the bottom-up approach suggests. For instance, just tackle the Sales data facts.

  2. Coordinate your efforts with other related DSS efforts. Let people know that you are carving out a specific subject area or departmental data and, when you finish, publish your design to everyone.

  3. Seek out any shared dimensions that might have already been created for other cubes. You will want to leverage these as much as possible for data consistency and non-redundant processing sake.

  4. Understand your datasources. The OLAP cube you create will only be as good as the data you put into it. It's best to understand the dirty data issues of what you are about to touch long before you try to build an OLAP cube with it.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features