Summary

In this chapter, we built an Analysis Services database using the data warehouse as the source. The key goals were to support flexible analysis of the profitability of various customer groups and products, and to support the company's On-Time Delivery improvement initiative. The Analysis Services database can be directly accessed by the users using tools such as Excel, reducing some of the pressure on IT to deliver standard reports.

We extended the fact table views in the data warehouse to provide the information in the best format for Analysis Services, such as transforming logic about which shipments were on time into an additive OnTime column with a 0 or 1 value. We also used the various dates in the fact table to calculate quantities such as the number of days a shipment is late.

In the Analysis Services project in BI Development Studio, we created a data source view and defined all the relationships between the fact and dimension tables, and then used the Cube Wizard to produce a first pass at the cube and dimension design. For the Time dimension attributes such as Year and Quarter, we used the unique columns that we defined in the dimension table rather than the descriptions, because this is more efficient and avoids problems with duplicate members in hierarchies.

We changed some of the attributes to use descriptive names such as Plant Name rather than the surrogate key and defined some hierarchies. One of the most important steps in the design process was to set up the attribute relationships for every dimension, because the relationships between attributes can have a big impact on the performance of the cube. We also added some calculations to the cube, such as On-Time Percent, and picked suitable formats for all the measures.

We used the Analysis Services Deployment Wizard to create a script that was executed on the production server to create and process the database. The operations team is now also managing the processing of the Analysis Services database, and the backups (which include both the metadata definitions and the data that has been loaded).