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 design a table structure to ensure that certain users can see sales data for only
certain districts. Some users must be permitted to see sales data from multiple districts.
What should you do?
A.
Add a district column to the dimUser table.
B.
Partition the factSales table on the district column.
C.
Create a userDistrict table that contains primary key columns from the dimUser and
dimDistrict tables.
D.
For each district, create a view of the factSales table that includes a WHERE clause for
the district.
C
A table can only have one primary key column. It should be foreign keys. Answer cannot be C.
I am leaning towards B but I am not sure if you can assign permissions per partition.
It’s C. The provided answers refers to the fact that the userDistrict table will contain only two fields: the primary key field from dimUser table, and the primary field from dimDistrict table. The primary key for userDistrict table is formed by both fields, which obviously are foreign keys to their respective tables. Pay attention!!