The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

7.2. Designing the OLAP Structure

If you've followed our instructions to build a DW/BI system with surrogate keys, conformed dimensions, and well-managed dimension changes, then building an Analysis Services OLAP database is straightforward. There are several major steps:

1. Set up the design and development environment

2. Create a Data Source View

3. Create and fine-tune your dimensions

4. Run the Cube Wizard and edit the resulting cube

5. Deploy the database to your development server

6. Create calculations and other decorations

7. Iterate, iterate, iterate

Later in the chapter, we discuss physical storage issues, which are largely independent of the database's logical design.

NOTE

Analysis Services contains features to help you overcome flaws in the design of the transaction source database, like referential integrity violations, but we're not going to talk about those features. Instead, build your DW/BI system correctly, as described in this book.

7.2.1. Getting Started

There are several steps in the setup process. First, you need to install and configure one or more Analysis Services development servers. You need to ensure the correct software is installed on developers' desktops. You should have some clean data loaded into the data warehouse database, and you should have created a set of views on those database tables.

Setup

As the Analysis Services database developer, the only SQL Server components you must have on your desktop PC are the development tools, notably BI Studio and Management Studio. ...