70-463 · Question #193
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…
The correct answer is B. Create a userRegion table that contains primary key columns from the dimUser and. Creating a userRegion bridge/junction table that contains the primary key columns from both dimUser and dimRegion implements a proper many-to-many relationship. This allows a single user to be associated with multiple regions and lets queries join factSales through dimRegion to…
Question
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?
Options
- AFor each region, create a view of the factSales table that includes a WHERE clause for the
- BCreate a userRegion table that contains primary key columns from the dimUser and
- CAdd a region column to the dimUser table.
- DPartition the factSales table on the region column.
How the community answered
(27 responses)- A11% (3)
- B56% (15)
- C26% (7)
- D7% (2)
Explanation
Creating a userRegion bridge/junction table that contains the primary key columns from both dimUser and dimRegion implements a proper many-to-many relationship. This allows a single user to be associated with multiple regions and lets queries join factSales through dimRegion to userRegion to enforce row-level visibility. Option C (adding a single region column to dimUser) only supports one region per user, breaking the multi-region requirement. Option A (one view per region) creates management overhead, does not handle multi-region users cleanly, and does not scale. Option D (partitioning factSales by region) is a performance/storage strategy, not a security mechanism - it provides no access control. The junction table is the standard relational pattern for flexible, scalable many-to-many access control.
Topics
Community Discussion
No community discussion yet for this question.