Power BI - Prepare the Data (Get Data From different data sources) Flashcards
How do you connect a data source
using the “Get Data” feature option in Power BI
How do you change data source settings
- In Power Query Editor, select the gear icon on a step (if you choose this option, you will have to do this for each query).
- In Power Query Editor on the Home tab select Data source settings
- In power BI Desktop on the home tab, select transform data, then select data source settings
What is a shared data set?
A dataset that has already been prepared and publised to Power BI Service
What are the benefits of a shared dataset?
- Ensures consistent data across multiple reports
- You aren’t copying data needlessly
- You can create a copy of an existing report and modify it (this takes less effort than starting from scratch)
What are the 3 types of storage modes?
Import, DirectQuery, and Dual
What is import mode?
The Import mode allows you to create a local Power BI copy of your datasets from your data source. You can use all Power BI service features with this storage mode, including Q&A and Quick Insights. However, data refreshes must be done manually. Import mode is the default for creating new Power BI reports.
What does DirectQuery allow for with respect to caching data and meeting security requirements?
The DirectQuery option is useful when you do not want to save local copies of your data because your data will not be cached. Instead, you can query the specific tables that you will need by using native Power BI queries, and the required data will be retrieved from the underlying data source. Using this model ensures that you are always viewing the most up-to-date data, and that all security requirements are satisfied.
How does DirectQuery help with large datasets?
This mode is suited for when you have large datasets to pull data from. Instead of slowing down performance by having to load large amounts of data into Power BI, you can use DirectQuery to create a connection to the source, solving data latency issues as well.
What does Dual Mode import allow you to do?
In Dual mode, you can identify some data to be directly imported and other data that must be queried. Any table that is brought into your report is a product of both Import and DirectQuery modes. Using the Dual mode allows Power BI to choose the most efficient form of data retrieval.
What is Connect Live?
Connect live is a new option in Azure Analysis Services. Azure Analysis Services uses the tabular model and DAX to build calculations, similar to Power BI. These models are compatible with one another. Using the Connect live option helps you keep the data and DAX calculations in their original location, without having to import them all into Power BI.
What is Azure Analysis Services?
Azure Analysis Services is an Azure product that allows you to ingest data from multiple data sources, build relationships between the data, and creates calculations on the data. The calculations are built using data analysis expressions (DAX). Azure Analysis Services is similar to the data modeling and storage technology in Power BI.
How getting data from Azure Analysis Services cubes similar to getting data from SQL Server?
Getting data from Azure Analysis Services cubes is similar to getting data from SQL Server, in that you can:
- Authenticate to the server.
- Pick the cube you want to use.
- Select which tables you need.
What are notable differences between Azure Analysis Services cubes and SQL Server?
Notable differences between Azure Analysis Services cubes and SQL Server are:
- Analysis Services cubes have calculations already in the cube, which will be discussed in more detail later.
- If you don’t need an entire table, you can query the data directly. Instead of using Transact-SQL (T-SQL) to query the data, like you would in SQL Server, you can use multi-dimensional expressions (MDX) or data analysis expressions (DAX).
How do you connect to data in Azure Analysis Services?
You use the Get data feature in Power BI Desktop. When you select Analysis Services, you are prompted for the server address and the database name with two options: Import and Connect live.
What does Azure Analysis Services allow you to do with respect to refresh schedules?
Azure Analysis Services can have a fast refresh schedule, which means that when data is refreshed in the service, Power BI reports will immediately be updated, without the need to initiate a Power BI refresh schedule. This process can improve the timeliness of the data in your report.
How is connect live similar to a relational database?
Similar to a relational database, you can choose the tables that you want to use. If you want to directly query the Azure Analysis Services model, you can use DAX or MDX.
What is an acceptable alternative with respect to importing the data that you want (from Excel, SQL Server, and so on) into the Azure Analysis Services model and using a live connection. What are the advantages of this alternative?
You will likely import the data directly into Power BI. An acceptable alternative is to import all other data that you want (from Excel, SQL Server, and so on) into the Azure Analysis Services model and then use a live connection. Using this approach, the data modeling and DAX measures are all performed in one place, and it’s a much simpler and easier way to maintain your solution.
What are your options in the Navigator Window?
- You now have the option to select the Load button to automatically load your data into the Power BI model
- Transform Data button to launch the Power Query Editor, where you can review and
clean your data before loading it into the Power BI model.
How do you connect Power BI to a relational database?
You can use the Get data feature in Power BI Desktop and select the applicable option for your relational database. You select the SQL Server option.
What are the first steps when connecting your Power BI desktop tool to a SQL Server database?
The first step is to enter your database server name and a database name in the SQL Server database window. The two options in data connectivity mode are: Import (selected by default, recommended) and DirectQuery. Mostly, you select Import. Other advanced options are also available in the SQL Server database window
(SQL Database) After you have added your server and database names, you will be prompted to sign in with a username and password. You will have three sign-in options. What are they?
- Windows: Use your Windows account (Azure Active Directory credentials).
- Database: Use your database credentials. For instance, SQL Server has its own sign-in and authentication system that is sometimes used. If the database administrator gave you a unique sign-in to the database, you might need to enter those credentials on the Database tab.
- Microsoft account: Use your Microsoft account credentials. This option is often used for Azure services.
What do the load and transform tools allow you to do?
- Load: Automatically load your data into a Power BI model in its current state.
- Transform Data: Open your data in Microsoft Power Query, where you can perform actions such as deleting unnecessary rows or columns, grouping your data, removing errors, and many other data quality tasks.
How can you use SQL query to import data?
Another way you can import data is to write an SQL query to specify only the tables and columns that you need.
To write your SQL query, on the SQL Server database window, enter your server and database names, and then select the arrow next to Advanced options to expand this section and view your options. In the SQL statement box, write your query statement, and then select OK.
What are additional features of using the SQL Query?
You can also join different tables, run specific calculations, create logical statements, and filter data in your SQL query.
When using an SQL query to import data, what approach should you take with respect to the wildcard character (*) in your query.
When using an SQL query to import data, try to avoid using the wildcard character () in your query. If you use the wildcard character () in your SELECT statement, you import all columns that you don’t need from the specified table.
Why is using the wildcard discouraged?
The wildcard is not recommended because it will lead to redundant data in your data model, which will cause performance issues and require additional steps to normalize your data for reporting.
What is a view?
A view is an object in a relational database, similar to a table. Views have rows and columns, and can contain almost every operator in
the SQL language.
What happens when Power BI uses a view?
If Power BI uses a view, when it retrieves data, it participates in query folding, a feature of Power Query.
What is a NoSQL database?
A NoSQL database (also referred to as non-SQL, not only SQL or non-relational) is a flexible type of database that does not use tables to store data.
How do you connect a NoSQL (Azure Cosmos DB) to Power BI?
You will use the Get data feature in Power BI Desktop. However, this time you will select the More… option to locate and connect to the type of database that you use. In this example, you will select the Azure category, select Azure Cosmos DB, and then select Connect.
What must you do to import a JSON type record?
JSON type records must be extracted and normalized before you can report on them, so you need to transform the data before loading it into Power BI Desktop.
How do you connect data in an application?
Select the “Get data” feature in Power BI Desktop. Then, select the option that you need from the Online Services category.
How to you connect your URL? (Sharepoint)
After you have selected Connect, you’ll be asked for your SharePoint URL. This URL is the one that you use to sign into your SharePoint site through a web browser. You do not need to enter your full URL file
path; you only need to load your site URL because, when you are connected, you can select the specific list that you want to load.
What do you need to after connecting your URL? (Shaepoint)
After you have entered your URL, select OK. Power BI needs to authorize the connection to SharePoint, so sign in with your Microsoft account and then select Connect.
How do you import datasets when they are too large or when you have security requirements?
Sometimes there may be security requirements around your data that make it impossible to directly import a copy. Or your datasets may simply be too large and would take too long to load into Power BI, and you want to avoid creating a performance bottleneck. Power BI solves these problems by using the DirectQuery storage mode, which allows you to query the data in the data source directly and not import a copy into Power BI.
Why is using DirectQuery useful?
DirectQuery is useful because it ensures you are always viewing the most recent version of the data.
What data model limitations are there with DirectQuery
- No built-in date tables
- Calculated columns are limited to two ways (can only use the current row of the table or related row in many-one relationships which rules out all aggregation functions. Can only use some functions that return scalar values
- Calculated tables aren’t supported
Parent-child functions such as PATH aren’t supported
Building clusters which rely on DAX is not supported
How do you identify and fix problems in Power BI?
Power BI provides the Performance Analyzer tool to help fix problems and streamline the process.