PL 300: Power BI MS Data Analyst
You plan to create several datasets by using the Power BI service. You have the files configured as shown in the following table. Data 1 - TSV Data 2- XLSX Data 3 - XML Data 4 - CSV Data 5 - JPG You need to identify which files can be used as datasets. Which two files should you identify? Data 1 Data 2 Data 3 Data 4 Data 5
2 and 4 a) TSV is not supported via Power BI Service, and in question it clearly says "by using Power BI Service". b) XLSX 3 gb will not work for Pro licence, but it will work for Premium licence. In question there is no mention about licence type so this option will work for some customers, for some not. Partially correct. c) XML is not supported via Power BI Service, and in question it clearly says "by using Power BI Service". d) CSV, same as xlsx 3gb, it will work for Premium licence. Partially correct. e) JPG is not suported via Power BI Service. For XML Table (.xml) or text (.txt) files, you must load that data into an Excel or Power BI Desktop file first. You can then import the Excel or Power BI Desktop file into Power BI Service. There's a 1-GB limit for datasets stored in Shared capacities in the Power BI service. If you need larger datasets, you can use Power BI Premium
Which of the following sources can Power BI connect to? SQL Database Google Analytics R scripts All of the above
All of the above (Power BI can connect to virtually any type of data source, including (but not limited to) SQL databases, R scripts, and Google Analytics.)
You have two Microsoft SQL Server database servers named SQLProd and SQLDev. SQLDev contains the same tables as SQLProd, but only a subset of the data in SQLProd. You create a new Power BI Desktop model that uses 120 tables from SQLDev. You plan to publish the Power BI file to the Power BI service. You need to connect the model to the tables in SQLProd. The solution must minimize administrative effort. What should you do from Query Editor before you publish the model? Create a new connection to SQLProd, and then import the tables from SQLProd. Delete the existing queries, and then add new data sources. Configure the Data source settings. Edit the source of each table query.
Configure the Data source settings. (Query Editor >>Data Source setting as well as through Query editor >> Query Setting >> Source.So both option C and D are justified. But its required least amount of administrative effort so will go for option C)
You're creating a Power BI report with data from an Azure Analysis Services Cube. When the data refreshes in the cube, you would like to see it immediately in the Power BI report. How should you connect? Connect Live Import Direct Query
Connect Live
You have the following three versions of an Azure SQL database: - Test - Production - Development You have a dataset that uses the development database as a data source. You need to configure the dataset so that you can easily change the data source between the development, test, and production database servers from powerbi.com. Which should you do? Create a JSON file that contains the database server names. Import the JSON file to the dataset. Create a parameter and update the queries to use the parameter. Create a query for each database server and hide the development tables. Set the data source privacy level to Organizational and use the ReplaceValue Power Query M function.
Create a parameter and update the queries to use the parameter. (As you can't edit datasets data sources in Power BI service, we recommend using parameters to store connection details such as instance names and database names, instead of using a static connection string. This allows you to manage the connections through the Power BI service web portal, or using APIs, at a later stage.)
You plan to publish your SSAS Tabular (live connection) data model to Power BI Service. What must be used in order for this to be possible? Data Gateway Dual Storage Mode Parameters Admin Privileges
Data Gateway (Data Gateways are required for on-premises data sources, like SSAS Tabular live connections.)
What type of import error might leave a column blank? Keep errors Unpivot columns Data type error
Data type error
When should you use DirectQuery? The source data never changes Company policy states no data source restrictions Dataset is too large to be stored in-memory All of the above
Dataset is too large to be stored in-memory (Use DirectQuery when the dataset is too large to be stored in-memory, when the source data changes frequently & reports must show the most recent data, and when the company policy states data can only be accessed from the original source.)
Which storage mode leaves the data at the data source? Import Direct Query Dual
Direct Query
What can you do to improve performance when you are getting data in Power BI? Only pull data into the Power BI service, not Power BI Desktop Use the Select SQL statement in your SQL queries when you are pulling data from a relational database Combine date and time columns into a single column Do some calculations in the original data source
Do some calculations in the original data source
You have a Microsoft Excel spreadsheet that contains a table named Sales. You need to add the Sales table to a Power BI dashboard as a tile. How should you configure the tile? From the Power BI service, import the data from the Excel workbook. From Excel, publish the workbook to the Power BI service. From the Power BI tab in Excel, pin the table. From the Power BI service, upload the Excel workbook.
From the Power BI tab in Excel, pin the table.
When would you need to access the Data Source Settings? If you need to connect to a new data source If you need to edit and existing query If the file name or location changes All of the above
If the file name or location changes (Connections to local files reference the exact path, so if the file name or location changes, you will need to change the source via the Data Source Settings)
In which storage mode are tables solely stored in-memory and queries fulfilled by cached data? Import DirectQuery Dual Native
Import (In import storage mode, tables are stored in-memory, whereas in DirectQuery tables are connected directly to the source. Dual storage mode allows tables to come from in-memory data or by an on-demand query to the data source.)
Which of the following sources lets you connect your data to other business applications? Microsoft Dataverse Microsoft Dataplatform Microsoft Dataflows Microsoft Excel
Microsoft Dataverse (The Dataverse is a cloud-based storage options for your organization's data that you can connect to business applications like Power Apps, Power Automate, and Power Virtual Agents)
Which of the following sources lets users connect to a set of pre-wired connections? PBIDS Files JSON Files Dataflows SSAS Tabular
PBIDS Files (Power BI Data source files contain a single set of pre-wired data source connection settings allowing users to easily connect and speed up the "Get Data" process)
Which technology improves performance by generating a single query statement to retrieve and transform source data? Query folding Adding index columns Adding custom columns with complex logic
Query folding
You have an Azure SQL database that contains sales transactions. The database is updated frequently. You need to generate reports from the data to detect fraudulent transactions. The data must be visible within five minutes of an update. How should you configure the data connection? Add a SQL statement. Set Data Connectivity mode to DirectQuery. Set the Command timeout in minutes setting. Set Data Connectivity mode to Import.
Set Data Connectivity mode to DirectQuery. (DirectQuery: No data is imported or copied into Power BI Desktop. For relational sources, the selected tables and columns appear in the Fields list. For multi-dimensional sources like SAP Business Warehouse, the dimensions and measures of the selected cube appear in the Fields list. As you create or interact with a visualization, Power BI Desktop queries the underlying data source, so you're always viewing current data.)
Which query language do you use to extract data from Microsoft SQL Server? DAX T-SQL MDX
T-SQL
How can you use parameters when connecting to data? To connect to a JSON file To change data source values dynamically To create "What-If" scenarios To shape and transform data in the Query Editor
To change data source values dynamically (Parameters are a useful way to change data source values dynamically in Power Query. These parameters are different from the "What-If" parameters created in the Power BI Desktip front-end.)
What is the Query Editor used for? As a cloud-based storage option for your organization's data To shape & transform data, then load it into Power BI To create relationships between data and lookup tables To create measures & calculated columns using DAX
To shape & transform data, then load it into Power BI (Once you connect to data, the Query Editor lets you shape & transform the data to meet your needs, then load that model into Power BI Desktop)