Overview -
Contoso, Ltd. is a manufacturing company that produces sports equipment. Contoso holds quarterly board meetings for which financial analysts manually prepare
Microsoft Excel reports, including balance sheets and profit and loss statements for each of the company's four business units.
Existing Environment -
Data and Sources -
Data for the reports comes from the sources shown in the following table.
 
The balance sheet data is unrelated to the profit and loss results other than they both relate to dates.
Balance Sheet Data -
The balance sheet data is imported and includes the final monthly balances of each account in the format shown in the following table.
 
The balance sheet data always includes a row for each account for each month.
Product Catalog Data -
The product catalog shows how products roll up to product categories, which roll up to the business units. The product list is provided in the format shown in the following table.
 
Revenue data is provided at the date and product level. Expense data is provided at the date and department level.
Business Issues -
Historically, it has taken two analysts a week to prepare the reports for the quarterly board meetings. Also, there is usually at least one issue each quarter where a value in a report is wrong because of a bad cell reference in an Excel formula. On occasion, there are conflicting results in the reports because the products and departments that roll up to each business unit are not defined consistently.
Requirements -
Planned Changes -
Contoso plans to automate and standardize the quarterly reporting process by using Power BI. The company wants to reduce how long it takes to populate the reports to less than two days. The company wants to create common logic for the business units, products, and departments. The logic will be used across all reports, including but not limited to the quarterly reporting for the board.
Technical Requirements -
Contoso wants the reports and datasets refreshed with minimum manual effort.
The company wants to provide the board with a single package of reports that will contain custom navigation and links to supplementary information.
Maintenance, including manually updating data and access, must be minimized as much as possible.
Security Requirements -
The reports must be made available to the board from powerbi.com. An Azure Active Directory (Azure AD) group will be used to share information with the board.
Contoso identifies the following security requirements for analyst access:
Analysts must be able to access all balance sheet and product catalog data.
Analysts must be able to access only the profit and loss data of their respective business unit.
Analysts must be able to create new reports from the dataset that contains the profit and loss data, but the reports built by the analysts must NOT be included in the quarterly reports for the board.
Analysts must NOT be able to share the quarterly reports with anyone.
Analysts must NOT be able to make new reports by using the balance sheet data.
Report Requirements -
You plan to relate the balance sheet table to a date table in Power BI in a many-to-one relationship based on the last day of the month. At least one of the balance sheet reports in the quarterly reporting package must show the ending balances for the quarter, as well as for the previous quarter.
The date table will contain the columns shown in the following table.
 
The definitions and attributes for the products, departments, and business units must be consistent across all the reports.
The board must be able to get the following information from the quarterly reports:
Revenue trends over time
The ending balances of each account
Changes in long-term liabilities from the previous quarter
The percent of total revenue contributed by each product category
A comparison of quarterly revenue versus the same quarter from the previous year
 
The reports must be updated with the latest data by 5 AM each day.
What is the minimum number of Power BI datasets needed to support the reports?

A. a single imported dataset

B. two imported datasets

C. two DirectQuery datasets

D. a single DirectQuery dataset








 

Suggested Answer: B

Note:
Analysts must be able to create new reports from the dataset that contains the profit and loss data, but the reports built by the analysts must NOT be included in the quarterly reports for the board.
Analysts must NOT be able to make new reports by using the balance sheet data.
Two datasets are required.
Need DAX for: A comparison of quarterly revenue versus the same quarter from the previous year. Also see other questions in this Case study which uses DAX expressions.
Incorrect:
Not DirectQuery: DirectQuery Limited Transformations.
You are not able to use all of the normal Power Query transformation features. Particular DAX functions are not available in this method as well. So if your data is poorly structured or needing lots of transformation, sometimes Direct Query is not a viable option.
Reference:
https://www.tessellationtech.io/import-vs-direct-query-power-bi/

This question is in PL-300 Exam
For getting Microsoft Power BI Data Analyst Associate Certificate