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.
Are we able to grant privileges to users based on table Partition?
I bet B
yes, it should be B
What is the benefit to creating a userRegion table containing primary key columns of both dimUser and dimRegion dimensions?!
How’s this new table different (structurally) from the existing factSales fact table, which ordinarily consists of these same key columns?!
By partitioning factSales based on the region column, you can streamline user access by defining different levels of accessibility on the partitions.
B
B
B as the others won’t work
And, what says ‘B’ would work?! Tried it yet?
Yes, is a B.
Agree with above all. Ans. B.
B
I think its either B or D not sure needs more research.
It can’t be D because partitions are securables so even if it was nicely partitioned you can’t assign permissions to it
are securables
should read:
aren’t securables