You are designing a data warehouse hosted on Windows Azure SQL Database. The data
warehouse currently includes the dimUser and dimRegion dimension tables and the
factSales fact table. The dimUser table contains records for each user permitted to run
reports against the warehouse, and the dimRegion table contains information about sales
regions.
The system is accessed by users from certain regions, 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 regions. Some users must be permitted to see sales data from multiple regions.
What should you do?
A.
For each region, create a view of the factSales table that includes a WHERE clause for
the region.
B.
Create a userRegion table that contains primary key columns from the dimUser and
dimRegion tables.
C.
Add a region column to the dimUser table.
D.
Partition the factSales table on the region column.
Right answer is B
1
0
D is actually correct; ‘B’ would be re-inventing the wheel on the factSales fact table. To achieve all the different access scenario in the question, just implement factTable partition (on the region column).
0
0
Partitioning would do nothing for the problem stated. I agree with Donk…B is correct
2
0
(B)
1
0