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.
What does performance in Power Query depend on?
The performance in Power Query depends on the performance at the data source level.
What do good SQL Server performance tuning techniques include?
Good SQL Server performance tuning techniques includes index creation, hardware upgrades, execution plan tuning, and data compression.
What is query folding and what does it help?
The query folding within Power Query Editor helps you increase the performance of your Power BI reports. Query folding is the process by which the transformations and edits that you make in Power Query Editor are simultaneously tracked as native queries, or simple Select SQL statements, while you are actively making transformations.
How can you use Power Query to load and transform data in Power BI?
You can use Power Query to load data into Power BI. Using Power Query Editor you can then make further transformations to your data, such as renaming or deleting columns, appending, parsing, filtering, or grouping your data.
What are the benefits to query folding?
The benefits to query folding include:
- More efficiency in data refreshes and incremental refreshes. When you import data tables by using query folding, Power BI is better able to allocate resources and refresh the data faster because Power BI does not have to run through each transformation locally.
- Automatic compatibility with DirectQuery and Dual storage modes. All DirectQuery and Dual storage mode data sources must have the back-end server processing abilities to create a direct connection, which means that query folding is an automatic capability that you can use. If all transformations can be reduced to a single Select statement, then query folding can occur.
What do you do if the View Native Query option is not available?
If the View Native Query option is not available (not displayed in bold type), that query folding is not possible for this step, and you will have to work backward in the Applied Steps area until you reach the step in which View Native Query is available (displays in bold type). This process will reveal the native query that is used to transform the dataset.
Native queries are not possible for which transformations?
Native queries are not possible for the following transformations: -Adding an index column
- Merging and appending columns of different tables with two different sources
- Changing the data type of a column -Running complex DAX functions
What is the guideline to know if you can use query folding?
A good guideline to remember is that if you can translate a transformation into a Select SQL statement, which includes operators and clauses such as GROUP BY, SORT BY, WHERE, UNION ALL, and JOIN, you can use query folding.
While query folding is one option to optimize performance when retrieving,
importing, and preparing data, what is
another option?
Another option is query diagnostics.
What does query diagnostics allow for?
This feature allows you to determine what bottlenecks (if any) exist while loading and transforming your data, refreshing your data in Power Query, running SQL statements in Query Editor, and so on.
How do you access query diagnostics?
Selecting Diagnose Step shows you the length of time that it takes to run that step. This selection can tell you if a step takes longer to complete than others, which then serves as a starting point for further investigation.
What are other ways to optimize query performance?
- Process as much data as possible in the original data source. Power Query and Power Query Editor allow you to process the data; however, the processing power that is required to complete this task might lower performance in other areas of your reports. Generally, a good practice is to process, as much as possible, in the native data source.
- Use native SQL queries. When using DirectQuery for SQL databases, such as the case for our scenario, make sure that you are not pulling data from stored procedures or common table expressions (CTEs).
- Separate date and time, if bound together. If any of your tables have columns that combine date and time, make sure that you separate them into distinct columns before importing them into Power BI. This approach will increase compression abilities.
What does incremental refresh do?
When working with large datasets, it may be a good idea to implement incremental refresh which keeps the bulk of your data static and only refreshes the most recent data that has changed/.
What is Microsoft Dataverse?
A cloud-based storage space that organizations can use to store application data.
What does Diagnose Step help you to see?
Selecting Diagnose Step shows you the length of time that it takes to run that step. This selection can tell you if a step takes longer to complete than others, which then serves as a starting point for further investigation.
How is data in dataverse stored?
Stored in tables
What is a PBIDS file?
A file that is designed with the “get data” process for future report developers. It allows users to skip the “get data” process (queries are already loaded in the file). The file does not contain credentials.
What’s the purpose of parameters?
Serves as a way to easily store and manage a value that can be reused
What are some examples of when you might use parameters?
- Switching between dev and production environments
- Configuring incremental refreshes
- Using Report templates
- Creating custom functions
What are dataflows?
Dataflows are stored in a centralized location (Power BI Service) and are useful when you want your power query queries to be used across your organizations. They can be used in multiple files.
What does XMLA endpoint do?
Is a power BI Premium feature that allows you to get data from outside of Power BI.
How does Power BI determine data anomalies?
Power Query Editor determines data anomalies by using the Column Distribution feature.
How many rows does Power Query examine by default? How do you increase?
By default, Power Query examines the first 1000 rows of your data set. To change this, select the profiling status in the status bar and select Column profiling based on entire data set.
What does Column Distribution show?
# of distinct and unique values - distinct # referes to how many different values are in the column
-unique # referes to the values that occur only once
Distinct and unique values will be the same if all values are unique
What does Column Profile show?
Column statistics - Count, # of errors, # of empty, distinct, unique, min, max, average, even, odd
When would you want to null values?
Occasionally, you might find that your data sources contain null values. For example, a freight amount on a sales order might have a null value if it’s synonymous with zero. If the value stays null, the averages will not calculate correctly. One solution would be to change the nulls to zero, which will produce the more accurate freight average. In this instance, using the same steps that you followed previously will help you replace the null values with zero.
Why would you need to replace values?
If you have undesirable values
If you want to remove errors
What two advanced options are there with replace values?
Match entire cell contents - if this option is checked, Power Query won’t’ replace values where the “replace with” value is only part of the value to find value
- IReplace using special characters - this is used to insert special character codes, such as carriage return or nonbreaking space, in the replace with or value to find box
What are null values considered as in Power Query?
Special characters
Why would you want to remove rows and how do you remove rows?
If you have errors in a column you can remove the rows entirely. (right-click the column header and select remove errors). This removes rows for the selected column.
How do you promote headers?
- By selecting the Use First Row as Headers option on the Home tab
- By selecting the drop-down button next to Column1 and then selecting Use First Row as Headers.
What will incorrect data types prevent you from doing what?
Incorrect data types will prevent you from creating certain calculations, deriving hierarchies, or creating proper relationships with other tables
What data types does Power Query Support?
Decimal Number Fixed Decimal Number Whole Number Percentage Date/Time Date Time Date/Time/Timezone Duration Text True/False Binary
Why is the ability to combine queries is powerful?
The ability to combine queries is powerful because it allows you to append or merge different tables or queries together.
What does Power BI do to detect data type?
When you import a table from any data source, Power BI Desktop automatically starts scanning the first 1,000 rows (default setting) and tries to detect the type of data in the columns.
Under what circumstances can you combine tables into a single table?
You can combine tables into a single table in the following circumstances:
- Too many tables exist, making it difficult to navigate an overly- complicated data model.
- Several tables have a similar role.
- A table has only a column or two that can fit into a different table. -You want to use several columns from different tables in a custom column
How do you combine queries?
After you have finished reformatting, you can combine the queries. On the Home tab on the Power Query Editor ribbon, select the drop-down list for Append Queries. You can select Append Queries as New, which means that the output of appending will result in a new query or table, or you can select Append Queries, which will add the rows from an existing table into another.
How is merging queries similar to with respect to SQL?
When you merge queries, you are combining the data from multiple tables into one based on a column that is common between the tables. This process is similar to the JOIN clause in SQL.
What does it mean when a table is on the one side of a relationship?
the key in the table has a unique value for every row
What does it mean when a table is on the many side of a relationship?
The values in the key column are not necessarily unique for every row & may repeat (also known as Foreign Key)
What are the steps to merge tables?
Go to Home on the Power Query Editor ribbon and select the Merge Queries drop-down menu, where you can select Merge Queries as New. This selection will open a new window, where you can choose the tables that you want to merge from the drop-down list, and then select the column that is matching between the tables.
What are the “join kinds” you can choose from when merging?
You can also choose how to join the two tables together, a process that is also similar to JOIN statements in SQL. These join options include:
- Left Outer - Displays all rows from the first table (left) and only the matching rows from the second.
- Full Outer - Displays all rows from both tables.
- Inner - Displays the matched rows between the two tables.
When would you use unpivoting?
Unpivoting is a useful feature of Power BI. You can use this feature with data from any data source, but you would most often use it when importing data from Excel. The following example shows a sample Excel document with sales data.
Change columns into rows
What does unpivoting do with respect to creating DAX measures?
Unpivoting streamlines the process of creating DAX measures on the data later.
How do you use the Pivot Column feature?
Change rows into columns
If the data that you are shaping is flat (in other words, it has lot of detail but is not organized or grouped in any way), the lack of structure can complicate your ability to identify patterns in the data.
You can use the Pivot Column feature to convert your flat data into a table that contains an aggregate value for each unique value in a column. For example, you might want to use this feature to summarize data by using different math functions such as Count, Minimum, Maximum, Median, Average, or Sum.
When would you use the delete until end option?
Useful when you have more than 2 steps you want to remove
What does the transpose feature do?
Changes columns to rows and rows to columns
What does the group by feature do?
Summarize data and group data based on a selected column
What does append do?
Adds rows when you have the same columns
What does merge do?
Adds columns when you have the same rows
How can you leverage the Advanced Editor to modify Power Query M Code?
- you can do more by writing M queries yourself
- M is case sensitive language
- you can view & edit full M queries in the advanced editor
- the query starts with “let” & ends with “in”
Where can you see the steps in a query?
- Advanced editor
2. Formula bar
How do you stop a query from loading?
Right-click on the query & select/clear “enable load”
While importing data into Power BI, you may encounter errors resulting from factors such as?
While importing data into Power BI, you may encounter errors resulting from factors such as:
- Power BI imports from numerous data sources.
- Each data source might have dozens (and sometimes hundreds) of different error messages.
- Other components can cause errors, such as hard drives, networks, software services, and operating systems.
- Data can often not comply with any specific schema.
What is the purpose of the query timeout?
Relational source systems often have many people who are concurrently using the same data in the same database. Some relational systems and their administrators seek to limit a user from monopolizing all hardware resources by setting a query timeout. These timeouts can be configured for any timespan, from as little as five seconds to as much as 30 minutes or more.
What is a cell-level error?
An error when Power BI incorrectly assigns a data type
What storage mode options are available for when connecting to Azure Analysis Services?
Import and Connect Live
