70-467 · Question #170
A company 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 Sales. Shared…
The correct answer is B. Create a calculated member in the Sales cube. A calculated member in the SSAS Sales cube centralizes the custom formula logic and supports cube-level role security to restrict access to the Marketing department only.
Question
A company 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 Sales. Shared data sources exist in SSRS for the relational and SSAS databases. Each company department has its own report writers. Report writers in the Marketing 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 Sales cube. The custom formula will compare time periods across multiple products, categories, and regions. You have the following requirements:
- Ensure that only Marketing 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 Marketing department report writers. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Options
- ACreate, deploy, and secure a shared dataset that references the SSAS database shared
- BCreate a calculated member in the Sales cube.
- CCreate and secure in the data warehouse a stored procedure that implements the custom
- DCreate, deploy, and secure a shared dataset that references the data warehouse shared data
How the community answered
(44 responses)- A2% (1)
- B84% (37)
- C5% (2)
- D9% (4)
Why each option
A calculated member in the SSAS Sales cube centralizes the custom formula logic and supports cube-level role security to restrict access to the Marketing department only.
A shared dataset deployed to SSRS cannot be restricted to a single department using built-in item-level security alone, and it relocates the formula outside the SSAS layer where cross-dimensional time comparisons are most efficient.
Calculated members defined inside the SSAS cube are secured using SSAS database roles, allowing access to be granted exclusively to Marketing department users without affecting other consumers. The cube already models time periods, products, categories, and regions as dimensions, making it the natural and most efficient location for a formula that compares these attributes - and all consumers share the existing SSAS data source, satisfying the single-dataset requirement.
A stored procedure in the relational data warehouse cannot directly reference data stored in the SSAS Sales cube, so it cannot implement a formula that spans cube dimensions such as products, categories, and regions.
A shared dataset referencing only the relational data warehouse cannot access SSAS cube data, making it impossible to implement the required formula that compares time periods across cube dimensions.
Concept tested: SSAS calculated member security and centralized formula design
Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/calculated-members-in-mdx-queries
Topics
Community Discussion
No community discussion yet for this question.