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.)
A. Create, deploy, and secure a shared dataset that references the SSAS database shared data source and includes a calculated member for the custom formula.
B. Create a calculated member in the Sales cube. Create, deploy, and secure a shared dataset that references the SSAS database shared data source and includes the calculated member from the cube.
C. Create and secure in the data warehouse a stored procedure that implements the custom formula. Create and deploy a shared dataset that references the data warehouse shared data source.
D. Create, deploy, and secure a shared dataset that references the data warehouse shared data source and includes custom Transact-SQL (T-SQL) code for the custom formula.
