You open a query in Power Query Editor.
You need to identify the percentage of empty values in each column as quickly as possible.
Which Data Preview option should you select?
Case Study 4 - Contoso Ltd 2
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.
You need to update the Power BI model to ensure that the analysts can quickly build drill-downs from business unit to product in a visual.
What should you create?
You have the Azure SQL databases shown in the following table.
You plan to build a single PBIX file to meet the following requirements:
* Data must be consumed from the database that corresponds to each stage of the development lifecycle.
* Power Bl deployment pipelines must NOT be used.
* The solution must minimize administrative effort.
What should you do? To answer, select the appropriate options in the answer area.
You have a Power BI tenant.
You have reports that use financial datasets and are exported as PDF files.
You need to ensure that the reports are encrypted.
What should you implement?
You plan to create a Power Bl dataset to analyze attendance at a school. Data will come from two separate views named View1 and View? in an Azure SQL database. View1 contains the columns shown in the following table.
View2 contains the columns shown in the following table.
The views can be related based on the Class ID column.
Class ID is the unique identifier for the specified class, period, teacher, and school year. For example, the same class can be taught by the same teacher during two different periods, but the class will have a different class ID.
You need to design a star schema data model by using the data in both views. The solution must facilitate the following analysis:
* The count of classes that occur by period
* The count of students in attendance by period by day
* The average number of students attending a class each month
In which table should you include the Teacher First Name and Period Number fields? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
