Power BI - Prepare the Data (Get Data From different data sources) Flashcards

Question

When using an SQL query to import data, what approach should you take with respect to the wildcard character (*) in your query.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

If Power BI uses a view, when it retrieves data, it participates in query folding, a feature of Power Query.

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

Select the "Get data" feature in Power BI Desktop. Then, select the option that you need from the Online Services category.

Answer 9

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.

Answer 10

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.

Answer 11

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.

Answer 12

DirectQuery is useful because it ensures you are always viewing the most recent version of the data.

Answer 13

1. No built-in date tables 2. 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 3. Calculated tables aren't supported Parent-child functions such as PATH aren't supported Building clusters which rely on DAX is not supported

Answer 14

Power BI provides the Performance Analyzer tool to help fix problems and streamline the process.

Answer 15

The performance in Power Query depends on the performance at the data source level.

Answer 16

Good SQL Server performance tuning techniques includes index creation, hardware upgrades, execution plan tuning, and data compression.

Answer 17

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.

Answer 18

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.

Answer 19

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.

Answer 20

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.

Answer 21

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

Answer 22

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.

Answer 23

Another option is query diagnostics.

Answer 24

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.

Answer 25

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.

Answer 26

- 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.

Answer 27

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/.

Answer 28

A cloud-based storage space that organizations can use to store application data.

Answer 29

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.

Answer 30

Stored in tables

Answer 31

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.

Answer 32

Serves as a way to easily store and manage a value that can be reused

Answer 33

1. Switching between dev and production environments 2. Configuring incremental refreshes 3. Using Report templates 4. Creating custom functions

Answer 34

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.

Answer 35

Is a power BI Premium feature that allows you to get data from outside of Power BI.

Answer 36

Power Query Editor determines data anomalies by using the Column Distribution feature.

Answer 37

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.

Answer 38

``` # 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

Answer 39

Column statistics - Count, # of errors, # of empty, distinct, unique, min, max, average, even, odd

Answer 40

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.

Answer 41

If you have undesirable values | If you want to remove errors

Answer 42

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 2. 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

Answer 43

Special characters

Answer 44

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.

Answer 45

1. By selecting the Use First Row as Headers option on the Home tab 2. By selecting the drop-down button next to Column1 and then selecting Use First Row as Headers.

Answer 46

Incorrect data types will prevent you from creating certain calculations, deriving hierarchies, or creating proper relationships with other tables

Answer 47

``` Decimal Number Fixed Decimal Number Whole Number Percentage Date/Time Date Time Date/Time/Timezone Duration Text True/False Binary ```

Answer 48

The ability to combine queries is powerful because it allows you to append or merge different tables or queries together.

Answer 49

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.

Answer 50

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

Answer 51

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.

Answer 52

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.

Answer 53

the key in the table has a unique value for every row

Answer 54

The values in the key column are not necessarily unique for every row & may repeat (also known as Foreign Key)

Answer 55

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.

Answer 56

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.

Answer 57

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*

Answer 58

Unpivoting streamlines the process of creating DAX measures on the data later.

Answer 59

*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.

Answer 60

Useful when you have more than 2 steps you want to remove

Answer 61

Changes columns to rows and rows to columns

Answer 62

Summarize data and group data based on a selected column

Answer 63

Adds rows when you have the same columns

Answer 64

Adds columns when you have the same rows

Answer 65

- 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"

Answer 66

1. Advanced editor | 2. Formula bar

Answer 67

Right-click on the query & select/clear "enable load"

Answer 68

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.

Answer 69

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.

Answer 70

An error when Power BI incorrectly assigns a data type

Answer 71

Import and Connect Live