Chapter 14

Managing a SQL Server Data Warehouse

Review Questions

1. You are designing a data warehousing system for a retail grocery chain. The company wants to be able to track changes to products over time. For example, the company would like to answer questions such as "What was the impact on sales of changing six-pack cola can size from 12 ounces to 11.5 ounces?" How should you design dimension records, and why?

You should write additional dimension records each time that a change occurs to an attribute for a product. Using this approach, you will be able to track sales over time for each variation of the product. Other approaches would not be appropriate. Using the option to overwrite the dimension record would not keep history of the product attributes, and storing additional attribute values would limit the number of times that product attributes could change. You will need to modify the design of any OLAP cubes.

2. What type of solution for slowly changing dimensions is most commonly found in data warehouse implementations?

Type2: Writing another dimension record.

3. You manage a data warehouse and large OLAP cubes for a manufacturing company. Two groups of users use the system and query different parts of the system in different ways. The manufacturing operations group performs detailed analysis on work-in-process inventory on a daily basis to optimize manufacturing efficiency. The sales group is concerned only with summary information, such as how many units of a particular product sold in a given week. Would it be effective to use partitions in the design of your cube?

Yes, using partitions in the cube design would allow you to use different storage methods and different optimizations for each partition. The partition for the sales group could contain just those aggregations needed for the most queried data, and the partition for the manufacturing operations group could have a greater number of aggregations to support their needs.

Another solution is to create two cubes with different dimensions for the groups. You can use a virtual cube to join them on a common dimension. In many cases, this would be a better solution.

4. You have a cube that contains information that is used by all users in your company as well as sensitive information that must be available to a small number of users. How should you implement security to control access to the sensitive information?

Install SQL Server OLAP Services Service Pack 1. Use OLAP Manager to create a role in the cube for the users that must have access to the sensitive information, and assign the Windows NT accounts or groups for the users to the role in the cube. Write DSO code to create an MDX expression that limits access to the sensitive information at the cell level.

5. When you designed a cube for your data warehousing system, you selected a high level of optimization (50 percent). This resulted in a large cube, and processing is now taking longer than your processing window. What should you do to identify and correct the problem?

You should use the Usage-Based Optimization wizard to help you reduce the number of aggregations and processing time. You should also use the Usage Analysis wizard to see information about the queries that are being submitted.