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.)
A.
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.
B.
Create a calculated member in the Pharmacy 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 SSAS database shared
data source and includes a calculated member for the custom formula.
I think B is not correct. Because of this sentences: “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.” So the custom logic cannot reside in the Cube.
0
0
Sorry, my bad. Of course B is correct. It’s EITHER the data warehouse OR the cube. Not both…
0
0
but maybe i don’t understand english well, but calculated member as I know reffers ONLY to Cubes? that means that for relational databases calculated member will be useles. Am I wrong?
So calculated member CAN’T be used because formula should be used also to relational source.
0
0
I am not sure i completely understand the question,
but looks like C is the correct answer.
You can use openrowset to query the SSAS instance
from relational DB (implemented myself several times).
By that you can to write a logic embeded in store procedure
the queries either source.
0
0
Maybe C can also work… But it’s definately a more difficult way than B.
0
0
I would go with B
0
0
Can anybody explain, why B is correct?
“Implement only one dataset”.
How is it possible to access the relational DB using this dataset that references the SSAS database?
0
0