You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes the
dimUser and dimDistrict dimension tables and the factSales fact table. The dimUser table contains records for
each user permitted to run reports against the warehouse, and the dimDistrict table contains information about
sales districts. The system is accessed by users from certain districts, as well as by area supervisors and users
from the corporate headquarters. You need to designa table structure to ensure that certain users cansee
sales data for only certain districts. Some users must be permitted to see sales data from multiple districts.
What should you do?
A.
Create a userDistrict table that contains primarykey columns from the dimUser and dimDistrict tables.
B.
Partition the factSales table on the district column.
C.
Add a district column to the dimUser table.
D.
For each district, create a view of the factSalestable that includes a WHERE clause for the district.
Explanation:
Looks Correct
A.
Create a userDistrict table that contains primarykey columns from the dimUser and dimDistrict tables.
Of course, A suggests that there would be two primary keys to the table, which is wrong. It should be foreign key. C would only be the answer if there is only one district per user. If there’s more, A would be right with foreign key instead of primary key.
A suggests getting the primary key columns of the dimUser and
dimDistrict tables, not creating two primary keys for the userDistrict table. So A is the correct answer and is in the correct wording also.