A hospital has a relational data warehouse, a SQL Server Analysis Services (SSAS) database, and a SQL Server Reporting Services (SSRS) instance. The SSAS database contains a cube named Pharmacy. Shared data sources exist in SSRS for the relational and SSAS databases. Each hospital department has its own report writers. Report writers in the Human Resources (HR) department want to create new reports by using Report Builder. Many reports will include data generated by a custom formula that references data stored either in a data warehouse table or in the Pharmacy cube. The custom formula will compare time periods across multiple products, categories, and employees. You have the following requirements:

Ensure that only the HR department report writers can access the

custom formula.

Implement only one dataset.

Ensure that the dataset references the data source that will provide

the fastest data retrieval. You need to meet the requirements to support the HR department report writers. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

Question

A hospital has a relational data warehouse, a SQL Server Analysis Services (SSAS) database, and a SQL Server Reporting Services (SSRS) instance. The SSAS database contains a cube named Pharmacy. Shared data sources exist in SSRS for the relational and SSAS databases. Each hospital department has its own report writers. Report writers in the Human Resources (HR) department want to create new reports by using Report Builder. Many reports will include data generated by a custom formula that references data stored either in a data warehouse table or in the Pharmacy cube. The custom formula will compare time periods across multiple products, categories, and employees. You have the following requirements:

Ensure that only the HR department report writers can access the

custom formula.

Implement only one dataset.
Ensure that the dataset references the data source that will provide

the fastest data retrieval. You need to meet the requirements to support the HR department report writers. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

Accepted Answer

B. Create a calculated member in the Pharmacy cube. SSAS calculated members are MDX expressions stored inside the cube that natively support time intelligence, cross-product, cross-category, and cross-employee comparisons - exactly the described use case. Access is controlled through SSAS database roles, allowing the calculated member to be exposed exclusively to HR department report writers without any changes to Report Builder or SSRS security.

Answer

A. Create, deploy, and secure a shared dataset that references the data warehouse shared A shared dataset referencing the data warehouse can be secured in SSRS, but it cannot natively perform multi-dimensional time-period comparisons across products, categories, and employees without complex SQL, and SSRS dataset-level security is coarser than cube-role security.

Answer

C. Create and secure in the data warehouse a stored procedure that implements the custom A stored procedure implements the formula at the relational layer, but granting execute permissions and surfacing it securely through Report Builder for only the HR team adds unnecessary complexity compared to cube roles.

Answer

D. Create, deploy, and secure a shared dataset that references the SSAS database shared data A shared dataset referencing the SSAS database exposes existing cube data but does not encapsulate a new custom formula - the formula logic itself must reside somewhere like a calculated member, not in a dataset definition.

A hospital has a relational data warehouse, a SQL Server Analysis Services (SSAS) database, and a SQL Server Reporting Services (SSRS) instance. The SSAS database contains a cube named Pharmacy. Share

Question

Options

How the community answered

Why each option

Topics

Community Discussion