nerdexam
Microsoft

70-463 · Question #97

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…

The correct answer is C. Create a userDistrict table that contains primary key columns from the dimUser and. This question tests the ability to model a many-to-many relationship between users and districts in a data warehouse schema. A bridge table is the correct relational design for this scenario.

Design and implement a data warehouse

Question

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?

Options

  • AAdd a district column to the dimUser table.
  • BPartition the factSales table on the district column.
  • CCreate a userDistrict table that contains primary key columns from the dimUser and
  • DFor each district, create a view of the factSales table that includes a WHERE clause for the

How the community answered

(19 responses)
  • A
    5% (1)
  • B
    21% (4)
  • C
    42% (8)
  • D
    32% (6)

Why each option

This question tests the ability to model a many-to-many relationship between users and districts in a data warehouse schema. A bridge table is the correct relational design for this scenario.

AAdd a district column to the dimUser table.

Adding a single district column to dimUser limits each user to exactly one district, making it impossible to grant access to multiple districts without duplicating user rows.

BPartition the factSales table on the district column.

Partitioning factSales on the district column is a storage and query-performance technique and does not enforce or define which users are permitted to view which district data.

CCreate a userDistrict table that contains primary key columns from the dimUser andCorrect

A userDistrict bridge table containing the primary keys from both dimUser and dimDistrict correctly models a many-to-many relationship, allowing each user to be associated with one or more districts. This is standard dimensional modeling practice for handling security or access-control relationships where cardinality is variable. Reporting queries join through this table to filter factSales rows to only the districts a given user is permitted to see.

DFor each district, create a view of the factSales table that includes a WHERE clause for the

Creating one view per district is unscalable as district count grows, and a single user would still need to be mapped to multiple views, which does not solve the access-control design problem.

Concept tested: Many-to-many bridge table design in dimensional modeling

Source: https://learn.microsoft.com/en-us/azure/architecture/data-guide/relational-data/data-warehousing

Topics

#row-level security#bridge table#dimension design#SQL Azure

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-463 Practice