You design a Business Intelligence (BI) solution by using SQL Server 2008. The solution includes a SQL Server 2008 Analysis Services (SSAS) database. The database contains a cube named Financials. The cube contains objects as shown in the exhibit.
A calculated member named Gross Margin references both Sales Details and Product Costs. You need to ensure that the solution meets the following requirements:
-Managers must be able to view only their cost center’s percentage of the company’s gross margin.
-The impact on query performance is minimal.
What should you do?
A.
Add dimension-level security and enable the Visual Totals option.
B.
Add cell-level security that has read permissions on the Gross Margin measure
C.
Add cell-level security that has read contingent permissions on the Gross Margin measure.
D.
Change the permissions on the Managers dimension level from Read to Read/Write.
Explanation:
Tip: “view only …impact on query performance is minimal ” = “Visual Totals”http://msdn.microsoft.com/en-us/library/ms174927.aspx
User Access Security Architecture
Microsoft SQL Server Analysis Services relies on Microsoft Windows to authenticate users. By default, only authenticated users who have rights within Analysis Services can establish a connection to Analysis Services. After a user connects to Analysis Services, the permissions that user has within Analysis Services are determined by the rights that are assigned to the Analysis Services roles to which that user belongs, either directly or through membership in a Windows role.
Dimension-Level Security
A database role can specify whether its members have permission to view or update dimension members in specified database dimensions. Moreover, within each dimension to which a database role has been granted rights, the role can be granted permission to view or update specific dimension members only instead of all dimension members. If a database role is not granted permissions to view or update a particular dimension and some or all the dimension’s members, members of the database role have no permission to view the dimension or any of its members.
Note
Dimension permissions that are granted to a database role apply to the cube dimensions based on the database dimension, unless different permissions are explicitly granted within the cube that uses the database dimension.
Cube-Level Security
A database role can specify whether its members have read or read/write permission to one or more cubes in a database. If a database role is not granted permissions to read or read/write at least one cube, members of the database role have no permission to view any cubes in the database, despite any rights those members may have through the role to view dimension members.
Cell-Level Security
A database role can specify whether its members have read, read contingent, or read/write permissions on some or all cells within a cube. If a database role is not granted permissions on cells within a cube, members of the database role have no permission to view any cube data. If a database role is denied permission to view certain dimensions based on dimension security, cell-level security cannot expand the rights of the database role members to include cell members from that dimension. On the other hand, if a database role is granted permission to view members of a dimension, cell-level security can be used to limit the cell members from the dimension that the database role members can view.