1
Q
Dashboards
A
A single-page collection of visuals built from a report (created in Power BI Service!)
2
Q
Tiles
A
Snapshots of your data on a dashboard
3
Q
Buildings Blocks of Power BI
A
Visualisations, Datasets, Reports, Dashboards, Tiles
4
Q
Where to update dataset for an app or other data that you use in Power BI
A
Power BI Service
5
Q
Flat file
A
This ia type of file that has only one data table and every row of data is in the same structure. The file doesn’t contain hierarchies. Examples are csv files, txt files.
6
Q
Cons of Importing Data by SQL Query
A
- It is best practice to avoid doing this directly in Power BI. Instead, consider writing a query like this in 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.
- If Power BI uses a view, when it retrieves data, it participates in query folding, a feature of Poewr Query.
7
Q
A NoSQL database (also referred to as non-SQL, not only SQL ornon-relational) is a ________
A
flexible type of database that doesn’t use tables to store data.
8
Q
If you’re working with data stored in JSON format, it’s often necessary to
A
extract and normalize the data first. This is because JSON data is often stored in a nested or unstructured format, which makes it difficult to analyze or report on directly.
9
Q
The three different types of storage modes you can choose from:
A
- Import: 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. Data refreshes can be scheduled or no-demand. Import mode is the default for creating new Power BI reports
- DirectQuery: useful when you don’t want to save local copies of your data because your data won’t be cached. Instead, you can query the specific tables that you’ll need by using Power BI queries, and the required data will be retrieved from the underlying data source. Essentially, you’re creating a direct connection to the data source. Using this model ensures that you’re always viewing the most up to date data, and that all security requirements are satisfied. This mode is suited for when you have large datasets to pull data rom. 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.
- Dual (Composite): identify some data to be directly imported and other data that must be queries. Any table that is brought in to 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.
10
Q
As previously mentioned, you use theGet datafeature in Power BI Desktop. When you selectAnalysis Services, you’re prompted for the server address and the database name with two options:
A
Import andConnect live.
Connect liveis an option for 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.
11
Q
The performance in Power Query depends on
A
the performance at the data source level.
12
Q
The query folding within Power Query Editor helps you ______
A
increase the performance of your Power BI reports. The reason for implementing this process is to ensure that these transformations can take place in the original data source server and don’t overwhelm Power BI computing resources
13
Q
Benefits to Query Folding
A
- More efficiency in data refreshes and incremental refreshes.
Automatic compatibility with DirectQuery and Dual storage modes.
14
Q
Another tool that you can use to study query performance is
A
query diagnostics. You can determine what bottlenecks may exist while loading and transforming your data, refreshing your data in Power Query, running SQL statements in Query Editor, and so on.
15
Q
Other ways to optimize query performance in Power BI include:
A
-Process as much data as possible in the original data source.
-Use native SQL queries.When using DirectQuery for SQL databases, such as the case for our scenario, make sure that you aren’t pulling data from stored procedures or common table expressions (CTEs).
- Separate date and time, if bound together.
16
Q
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. This error indicates that you’ve pulled too much data according to your organisation’s policies. What will Power BI show?
A
Query Timeout Expired
17
Q
Solution to Query Timeout Expired
A
You can resolve this error by pulling fewer columns or rows from a single table. While you’re writing SQL statements, it might be a common practice to include groupings and aggregations. You can also join multiple tables in a single SQL statement. If you need the rows, columns, and complexity, consider taking small chunks of data and then bringing them back together by using Power Query.
18
Q
Couldn’t find file
A
Usually this error is caused by the file moving locations or the permissions to the file changing. If the cause is the former, you need to find the file and change the source settings.
19
Q
Sometimes, when you import data into Power BI, the columns appear blank. This situation happens because of an error in interpreting the data type in Power BI. The resolution to this error is unique to the data source.
A
Data type errors
20
Q
Use the Pivot Column feature to convert your flat data into _____
A
a table that contains an aggregate value for each unique value in a column.
21
Q
When you import a table from any data source, Power BI Desktop automatically starts scanning the first __________
A
1000 rows and tries to detect the type of data in the columns. Solution to this is to change the profiling status in Power Query Editor
22
Q
Implication of Incorrect Data Types
A
- It will prevent you from creating certain calculations, deriving hierarchies or creating proper relationships with other tables
- Another issue with having an incorrect data type applied on a date field is the inability to create a date hierarchy, which would allow you to analyze your data on a yearly, monthly, or weekly basis.
23
Q
Benefits of Combine Multiple Tables into a Single Table
A
- allows you to append or merge different tables or queries together
24
Q
When to combine multiple tables into a single table
A
- 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 tabels in a custom column
You can combine the tables in 2 different ways: merging and appending
25
Power Query Editor determines data anomalies by using the
Column Distribution feature.
26
To understand data anomalies and statistics, select the
Column Distribution, Column Quality, and Column Profile options. The following figure shows the statistics that appear.
27
Column quality shows you the
percentages of data that is valid, in error, and empty. In an ideal situation, you want 100 percent of the data to be valid.
28
Column distribution shows you the
distribution of the data within the column and the counts of distinct and unique values, both of which can tell you details about the data counts.
29
Distinct vs Unique
distinct in this table tells you the total count of how many values are present, while unique tells you how many of those values only appear once.
30
Column Profile
gives you a more in-depth look into the statistics within the columns for the first 1,000 rows of data. This column provides several different values, including the count of rows, which is important when verifying whether the importing of your data was successful.
31
Value distribution graph tells you the ___________
counts for each distinct value in that specific column.
32
Column Statistics will also include
how many zeroes and null values exist, along with the average value in the column, the standard deviation of the values in the column, and how many even and odd values are in the column. These statistics give you an idea of the distribution of data within the column, and are important because they summarize the data in the column and serve as a starting point to determine what the outliers are.
33
In a star schema, each table in your dataset is defined as
a dimension or a fact table
34
Fact tables contain
observational or event data vaalues and can contain several repeated values
35
Dimension tables contain
the details about the data in fact tables
36
Mark as Date Table command
Power BI performs validations to ensure that the data contains zero null values, is unique and contains continuous date values over a period. It will remove autogenerated hierarchies from the Date field in the table that you marked as a date table.
37
PATH() and PATHITEM()
Use PATH() , a simple DAX function that returns a text version of the managerial path for each employee and PATHITEM() to separate this path into each level of managerial hierarchy. This is part of the flatten parent-child hierarchy
38
Define Data Granularity
Very important topic for all data analysts
- the details that is represented within your data, meaning that the more granularity your data has, the greater the level of detail within your data
- Defining the correct data granularity can have a big impact on the performance and usability of your Power BI reports and visuals
- can also have an impact when you're building relationships between tables in Power BI
39
directionality
plays an important role in filtering data between multiple tables. When you load data, Power BI automatically looks for relationships that exist within the data by matching column names. You can also use Manage Relationships t oedit these options manually
40
Many-to-one (*:1) or one-to-many (1: *) relationship
Describes a relationship in which you have many instances of a value in one column that are related to only one unique corresponding instance in another column.
41
With a single cross-filter direction:
Only one table in a relationship can be used to filter the data. For instance, Table 1 can be filtered by Table 2, but Table 2 cannot be filtered by Table
For a one-to-many or many-to-one relationship, the cross-filter direction will be from the "one" side, meaning that the filtering will occur in the table that has many values.
42
With both cross-filter directions or bi-directional cross-filtering:
- One table in a relationship can be used to filter the other. For instance, a dimension table can be filtered through the fact table, and the fact tables can be filtered through the dimension table.
- You might have lower performance when using bi-directional cross-filtering with many-to-many relationships.
43
Hierarchies are useful because
they allow you to drill down into the specifics of your data instead of only seeing the data at a high level.`
44
What is relationship cardinality
it is the measure of unique values in a table.
45
Having too much calculated columns will
slow performance and will cause you to reach the maximum Power BI file size sooner.
46
One way to avoid using a calculated column is to use
X functions, such as the SUMX, COUNTX, MINX, and so on. These are the iterator functions as they iverate over the individual rows to do their calcation. An X function will perform better and use less disk space than a calculated column
47
Each calculated column will
increase the space that is used in that file and potentially increase the refresh time
48
Performance analyser will help ....
identify the elements that are contributing to your performance issues, which can be useful during troubleshooting.
49
Before you run Performance analyzer, to ensure you get the most accurate results in your analysis (test), make sure that you start with
a clear visual cache and a clear data engine cache.
50
Auto Date/Time Feature
The Auto date/time option allows you to work with time intelligence when filtering, grouping, and drilling down through calendar time periods. We recommend that you keep the Auto date/time option enabled only when you work with calendar time periods and when you have simplistic model requirements in relation to time.
If your data source already defines a date dimension table, that table should be used to consistently define time within your organization, and you should disable the global Auto date/time option. Disabling this option can lower the size of your data model and reduce the refresh time.
51
Benefits of using variables
Improve performance, readability, simplified debugging and reducing complexity
Variables can make measures more efficient because they remove the need for Power BI to evaluate the same expression multiple times. You can achieve the same results in a query in about half the original processing time.
52
Reduce Cardinality
It is a term that is used to describe the uniqueness of the values in a column
53
Limitations of DirectQuery connections
- Overall user experience depends heavily on the performance of the underlying data source
- Security Implications
- Data transformations limitations
- Limited modeling
- Quick Insights and Q&A features are not supported
54
When aggregating data, you summarize that data and present it in at a
higher grain (level).
55
The aggregation process ______
reduces the table sizes in the data model, allowing you to focus on important data and helping to improve the query performance.
Aggregated data is cached and uses a fraction of the resources that are required for detailed data
56
Using tooltips is a clever way of providing
more contextual information and detail to data points on a visual.
57
the default tooltip displays
the data point's value and category, but you can customize this information to suit your needs.
58
Tab order
To help keyboard users navigate your report in an order that matches the way that visual users would, you can set the tab order.
59
Using Navigation
- To specify the logical path that your report users should take. In other words, you determine the order in which users view each page.
- To tell a data-driven story. For example, you could use it to give your employees a message that is backed up by the data. This method could be useful to help drive change, such as increase sales.
- To create a reporting portal where users can navigate to a set of reports.
60
Cross-report drillthrough
The Cross-report drillthrough feature allows you to contextually jump from one report to another report in the same Power BI service workspace or app. This feature allows you to connect two or more reports that have related content. You can also pass filter context along with that cross-report connection.
61
Pin a tile to a dashboard
The pinning process pulls visuals from your report and "pins" them to a dashboard for easy viewing.
62
The Q&A visual consists of 4 main elements:
- **Question box** - In this element, the user can enter their question about the data. An example of a question could be: What was the average sales amount by category? Entering this question will trigger Power BI's natural-language analysis engine to parse and determine the appropriate data to display.
- **Pre-populated suggestion tiles** - This element contains tiles with pre-populated suggestions for questions that the user can consider asking. When the user selects one of these tiles, they will be shown analysis. For example, if you select the **top product category names by march sales** tile, you would get the following visual that is converted from the Q&A visual.
- Convert Icon: Select this icon to convert the Q&A visual into a standard visual
- Gear Icon: allows designers to configure the underlying natural language engine
63
Paginated reports
allow report developers to create Power BI artifacts that have tightly controlled rendering requirements. Paginated reports are ideal for creating sales invoices, receipts, purchase orders, and tabular data.
64
When to use paginated reports
You can use paginated reports for operational reports with tables of details and optional headers and footers.
65
Difference between grouping and binning
Grouping is used for categories of data. Binning is similar to grouping but it is used for grouping continuous fields such as numbers and dates.
66
What-if parameters
- use to run scenarios and scenario-type analysis on your data (what-if analysis)
- enable you to look at historical data to analyse potential outcomes if a different scenario had occured
67
Review Quick Insights
The Quick insights feature in Power BI uses machine learning algorithms to go over your entire dataset and produce insights (results) for you quickly. This feature is a great way to build dashboards when you don't know where to start. It also helps you find insights you might have missed when building your reports. From the insights that Power BI discovers, you can generate appealing, interactive visualizations.
68
Apply AI Insights
The AI Insights feature allows you to connect to a collection of pretrained machine learning models that you can apply to your data to enhance your data preparation efforts.
69
Key influencers visual helps you
understand the factors that are affecting a specific metric. It analyzes your data for you, ranks the factors that matter, and then displays those factors as key influencers.
70
Decomposition Tree
rill down into your dimensions so that you can view your data across multiple dimensions. Because Decomposition Tree is an AI visual, you can use it for improvised exploration and conducting root cause analysis.
71
Development life cycle
The development process is iterative; it typically requires building an initial solution, testing the solution in a different environment, returning to make necessary revisions, and eventually releasing a final product.
72
Deployment pipeline
The deployment pipeline feature in Power BI manages content in dashboards, reports, and datasets between different environments in the development life cycle. With this feature, you can develop and test Power BI content in one centralized location and streamline the process before deploying the final content to your users. This Power BI Premium feature requires you to be a Capacity admin.
73
Advantages of using the deployment pipeline
- **Increased productivity** - Through this feature, you can reuse previous deployment pipelines, ensuring that efforts aren't duplicated.
- **Faster delivery of content** - Report development becomes more streamlined, meaning that it takes less time to get to production.
- **Lower human intervention required** - Having the ability to reuse deployment pipelines means a decreased chance of error associated with moving content from one environment to another.
Basically, Development -> Test -> Production
74
Lineage view feature in Power BI allows you to
quickly refresh datasets and see the relationships between the artifacts in a workspace and their external dependencies.
75
Data lineage
Data lineage refers to the path that data takes from the data source to the destination.
76
The Lineage view feature in Power BI is crucial because it:
- Simplifies the troubleshooting process because you can see the path that the data takes from source to destination and determine pain points and bottlenecks.
- Allows you to manage your workspaces and observe the impact of a single change in one dataset to reports and dashboards.
- Saves time by simplifying your task of identifying reports and dashboards that haven't been refreshed.
77
The Lineage view is only accessible to
Admin, Contributor, and Member roles.
78
Sensitivity labels
Sensitivity labels specify which data can be exported. These labels are configured externally to Power BI, and Power BI allows you to quickly use them in your reports and dashboards.
79
Scope Report Design Requirements for Dashboard
- Goal: interpret the story as quickly as possible
- User interactions are limited by insights that are highly curated toward the audience
80
Scope Report Design Requirements for Analytical reports
- Goal: help report consumers discover answers to a broad array of questions by interacting with the report and its visuals while providing a structured space for analysis
- Often have manby slicers to filter report data and often contain complex visuals that expose in depth detail of the data
81
Scope Report Design Requirements for Operational reports
- give the report consumer the ability to monitor current or real-time data, make decisions and act on those decisions.
- Include buttons that allow the report consumer to navigate within the report and also beyond the report to perform actions in external systems
82
Educational report scope
- assumes that the report consumer is unfamiliar with the data or context
- Report must provide clear narrative detail and guidance to help with understanding
83
Power BI : Table vs Matrix
**Table**
As the name itself suggests, this is a basic 2 dimensional visual to represent tabular data. Its a primitive 2-D grid representation of the data.Basically, a flat structure of the data.
**Matrix**
To be blunt, Matrix is just a fancy name, given to excel’s pivot table in the world of Power BI. Just like pivot table in excel it becomes easier to represent the data across multiple dimensions. In Matrix visual, you have the option to add rows, columns, and values unlike Table.
84
Filtering can occur at 5 different levels of Microsoft Power BI report:
- Dataset (RLS)
- Report
- Page
- Visual
- Measure
85
Persistent filters
Persistent filters is a feature that saves report consumer's slicer and filter settings.
86
Filters result in
faster report rendering because no visual rendering is required.
87
Disadvantages of slicers
- **Slicers can have an impact on report render performance due to the visual render time.**
- **They occupy space on the page that could be used by data visuals.**
88
Purpose of bookmark
Bookmarks are a powerful way to turn a Power BI report into a guided analytical experience. Bookmarks can also help maximize available page space and provide user-friendly interactions.
89
When visual headers are disabled they will
still be visible when editing the report, but they will be hidden for report consumers (in reading view).
90
Two types of on-premises gateways are:
- **Organization mode** - Allows multiple users to connect to multiple on-premises data sources and is suitable for complex scenarios.
- **Personal mode** - Allows one user to connect to data sources. This type of gateway can be used only with Power BI and it can't be shared with other users, so it is suitable in situations where you're the only one in your organization who creates reports. You will install the gateway on your local computer, which needs to stay online for the gateway to work.
91
Power BI provides two ways to endorse your datasets:
- **Promotion** - Promote your datasets when they're ready for broad usage.
- **Certification** - Request certification for a promoted dataset. Certification can be a highly selective process, so only the truly reliable and authoritative datasets are used across the organization.
92
Before you can connect to your on-premises data source,
you need to install the on-premises data gateway, and then configure it to suit your organizational needs.
93
What services do not require a gateway
Cloud services, such as SharePoint, do not require a gateway because the data is already in the cloud. You only need to provide your authorization credentials to set up a data source connection.
94
RLS involves several configuration steps, which should be completed in the following order:
1. Create a report in Microsoft Power BI Desktop.
1. Import the data.
2. Confirm the data model between both tables.
3. Create the report visuals.
2. Create RLS roles in Power BI Desktop by using DAX.
3. Test the roles in Power BI Desktop.
4. Deploy the report to Microsoft Power BI service.
5. Add members to the role in Power BI service.
6. Test the roles in Power BI service.
95
userprincipalname()
function will compare the email address from the Employees table with the email that the user entered when signing in to Power BI service. If Russel King uses the email address russel@tailwindtraders.com to sign in to Power BI service, the system will compare that value to the email address in the Employees table. Assuming that a relationship has been created between Employees and Sales, Russel will only see his four sales.
PL-300
flashcards
Decks in class (4)
# Cards
