Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Microsoft Data Modeling in Power BI > Optimization > Flashcards

Optimization Flashcards

1
Q

What does optimization refer to in regards to Power BI?

A

Optimizing performance of
1. Data models
2. Reports
3. Dashboards

By
1. Modifying
2. Tuning
3. Streamlining

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What might performance issues look like?

A
  1. Slow Loading Time
  2. Slow Dashboard Interaction Response
  3. Timeouts and Errors
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What are some reasons for poor performance?

A
  1. Inefficient Data Models
  2. Complex DAX Calculations
  3. Inappropriate Visuals
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Benefits of optimization

A
  1. Enhanced speed and efficiency
  2. Faster downloading and processing of large data sets
  3. Timely Informed Decision Making
  4. Improved user experience
  5. Timely report generation
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is optimization?

A
  1. Transforming
  2. Cleaning
  3. Organizing

For best performance

Just writing a really long sentence to left justify

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Optimization Techniques

A
  1. Sorting
  2. Filtering
  3. Indexing
  4. Data Transformation
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What can you do to reduce the size of the model?

A
  • Correct Column Data Types
  • Delete Unnecessary Rows and Columns
  • Avoid Repeated Values
  • Replace Numeric Columns with Measures
  • Reduce Cardinalities
  • Analyze Model Metadata
  • Summarize data where possible
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What type of relationship can cause performance issues?

A

Many-to-Many

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What factors contribute to performance issues when working with Many-to-Many relationships?

A
  1. Bi-Directional Filters
  2. Data Model Complexity
  3. Large Volume of Data
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is high cardinality?

A

A column with a high number of distinct values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How does high cardinality impact performance?

A

It slows data models

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Can Power BI handle high volumes of data?

A

Yes
Power BI can handle high volumes of data but sometimes high cardinality will cause performance issues

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How can you reduce high cardinality?

A
  1. Reduce by summarizing the data during the transformation steps
  2. Change decimal columns to fixed decimals. Precision values increase cardinality
    3.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How can you summarize data in Power Query?

A
  1. Select the column you want to group by
  2. Transform Tab->Group By
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How do you change a column to a Fixed Decimal Number in Power Query?

A

Transform Tab->Data Type->Fixed Decimal Number

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is metadata?

A

Data about data

For example, table names, column names, relationship, data types, etc.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

How do you check column quality?

A

Transform Data->View Tab->Column Quality

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What does enabling column quality do?

A

At the top of each column you will see the percentage of values that are
- Valid
- Error
- Empty

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What does a percentage of valid less than 100% indicate?

A

You need to attend to empty cells or errors

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

How do you check column distribution?

A

Transform Data->View Tab->Column Distribution

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What does column distribution show you?

A

A bar chart appears at the top of each column. It indicates the frequency of values within your column.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

How do you check the column profile?

A

Transform Data->View Tab->Column Profile

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What does Column Profile tell you?

A

Based on the column you select, at the bottom of the window you will see a range of statistical measures and a distribution chart giving you a detailed profile of your column.

24
Q

Column and Distribution example

A
25
Q

Column Profile Example

A
26
Q

Close up of column stats available using Column Profile

A
27
Q

What is one way to minimize dataset size?

A

Delete columns of data that you don’t need

28
Q

How does categorizing columns help optimize your model?

A
  • It helps Power BI better understand your data and the relevant features and insights.
  • For example, if a column is given a category of City then Power BI knows this column can be used in geographical analysis.
29
Q

How can you change the Data Category for you Column?

A

Click on desired column->Column Tools->Data Category

30
Q

How can ensuring each column has the right data type help optimize your model?

A

Some data types take up more memory than necessary. For example, the Text data type. If applicable, change the column to a different data type.

31
Q

How does the Auto Date/Time feature work?

A
  • It automatically generates Date/Time tables for time-intelligence calculations.
32
Q

How many Date/Time tables does Power BI create?

A

One for each Date/Time column in your data

33
Q

How could the Auto Generated Date/Time tables cause performance issues?

A

If there are too many then it can overwhelm the system and cause a slowdown

34
Q

What columns does the auto generated Date/Time table include?

A
  • Year
  • Quarter
  • Month
  • Day
  • Year-To-Date
  • Quarter-To-Date
  • Month-To-Date
  • and more
35
Q

How can you disable the auto Date/Time feature?

A

File->Options and Settings->Options->Current File Section->Data Load->Uncheck Auto Date/Time->Click OK

This only disables auto Date/Time for the current file

36
Q

Benefits of disabling Auto Date/Time

A
  1. Reduced Data Model Size
  2. Improved Query Performance
  3. Greater Flexibility and Control
  4. Eliminate Redundancy
  5. Reduce Resource Usage
37
Q

What is Direct Query?

A

A direct connection to a data source without importing data into Power BI

38
Q

What is best practice for data used in Power BI?

A

Import the data to Power BI whenever possible. This is preferable over Direct Query

39
Q

Direct Query benefits

A
  1. Real-Time Updates
  2. Reduced Memory Usage
  3. Data Security
40
Q

What are some important things to know about Direct Query

A
  • Only the schema is brought into Power BI
  • Reports and Visuals send queries to the underlying database to receive the necessary data
41
Q

How do you display the latest data for a Direct Query?

A

You must refresh the data

42
Q

What is a potential downside to Direct Query

A

Poor Performance
It is ALWAYS faster to use the data that is in memory, i.e. imported to Power BI

43
Q

What factors contribute to poor performance when using Direct Query

A
  • Size of Data
  • Database Server Specs
  • Network Connection Speed
  • Optimization to the Data Source
  • Every change to the data using a filter or slicer sends a query to the underlying database
44
Q

How does Power BI handle transformations when working with Direct Query?

A
  • Not all transformation options are available
    The source you connect to will determine what transformation options are available.
  • Data Model is limited
  • DAX is limited
45
Q

How do you optimize Direct Query?

A

All layers of the solution need to be optimized
- Start at the data source. Tune the source database.
- Avoid complex calculated columns
- Review Indexes
- Create new Indexes if necessary
- Create Aggregations
- Optimize the Data Model
- Optimize Reports (Reduce visuals or fields used in visuals)

46
Q

What are storage modes?

A

They determine where data is stored in Power BI and how queries will be sent to the data sources

47
Q

What are the storage modes available in Power BI?

A
  1. Import Mode
  2. Direct Query Mode
  3. Dual Mode
48
Q

Where is data from Import Mode stored?

A

In-Memory in Power BI

49
Q

Where is data from DirectQuery Mode stored?

A

In the data source outside of Power BI

50
Q

What is Dual Mode?

A

Some in-memory tables in Power BI and some tables in the data source outside of Power BI

51
Q

What does storage mode do?

A

Lets you control whether Power BI desktop catches table data in memory for reports

52
Q

Benefits of Storage Mode

A
  • Better Query Performance
  • Cache Larger Tables
  • Data Refresh Optimization
53
Q

Where can you change the storage mode for a table?

A
  1. Go to the Data Model area
  2. Click on the table you are interested in
  3. Go to Properties
  4. Scroll down to the Storage Mode
54
Q

How can you optimize Direct Query by doing query reductions?

A

File->Options and Settings->Options->Query Reduction

55
Q

What does Reduce Number of Queries Sent by Disable Cross Highlighting/Filtering by Default?

A
  • Disables highliting or filtering on other visuals when you when you select one visual
  • This technique would only be useful in a report that has visuals that do not interact with each other
56
Q

Where can you change when slicers are applied?

A

File->Options and Settings->Options->Query Reduction->Filters section

57
Q

What options are available for when filters are applied?

A
  1. Instantly apply basic filter changes
  2. Add Apply button to all basic filters to apply changes when you are ready adds an Apply button to all basic filters
  3. Add a single Apply button to the filter pane to apply changes at once adds a single Apply button to the entire Power BI filter pane. Any changes you make will not execute until you select the Apply button.

Number three is the preferred method.

Microsoft Data Modeling in Power BI (14 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions