70-019 Designing and Implementing Data Warehouse with Microsoft SQL server 7.0 Training Course

Demo Question 7.

You have OLAP cube named Sales. This cube is used by users in the sales department and the marketing department. The Sales cube's fact table is named SalesFact. The Sales cube contains a measure named Commission that represents a salesperson's commission payment. This measure is confidential data. The source column for the Commission measure is the Sales Commission column of the SalesFact Table. You need to prohibit access to the Commission measure by users in the marketing department, and you need to permit access by users in the sales department. What should you do?

A. Create a new role named Marketing for the database of the Sales cube's data source and include users in the marketing department as members of this new role. Restrict the Marketing role access to the Sales Commission column
B. Create a new database view, named Marketing View, based on the Sales cube's fact table. Exclude the Commission measure from this view. Change the Sales cube's fact table to Marketing View. Create a new role named Marketing for the database of the source and include users in the marketing department as members of this new role. Grant the Marketing role access to the Marketing View view. Restrict the Marketing role access to the SalesFact table.
C. Create a new cube named Marketing Sales with the same data source as the Sales cube. Exclude the Commission measure from the new cube. Create a new role named Marketing for the database of the data source and include users in the marketing department as members of this new role. Grant the marketing department access to the Marketing Sales cube. Restrict the Marketing role access to the SalesFact table.
D. Create a new virtual cube named Marketing Sales based on the Sales cube. Exclude the Commission measure from this Virtual cube. Create a new role named Marketing for the Marketing Sales cube and include users in the marketing department as members of this new role. Restrict the Marketing role access to the Sales cube.


Display Answer

Answer: D

Explanation: We create a virtual cube based on the original cube and exclude the Commission measure. We then allow different sets of users to use the two different cubes. Note: Virtual cubes can be compared to views. Views are virtual tables that are build by joining tables. Virtual cubes are build by joining cubes. Reference: SQL Server 7.0 Data Warehousing Training Kit (), Cube-Level and Database-level security, Page 467 SQL Server 7.0 Data Warehousing Training Kit (), Virtual Cube, Page 261 Incorrect