Data for Decision Making Chapter 4

0.0(0)
Studied by 3 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/28

encourage image

There's no tags or description

Looks like no tags are added yet.

29 Terms

1
New cards

Query

The identification and transformation of data to answer a question. Usually have parameters (constraints) that need to be meet. They use tables or other queries as their data source. PBI does not distinguish between tables and queries.

2
New cards

Businesses use queries to answer business questions and generate reports for decision-making. Data that is queried can be formatted for a report if it is at the appropriate level of aggregation.

How are queries used in organizations?

3
New cards

When translating a business question into query requirements which need to be incorporated into the query, it is important to dissect the key parameters.

How to determine query requirements?

4
New cards

Parameters

Important elements of the question that need to be part of the query. Ex: How many orders occurred each month in 2018?

5
New cards

Import tables → clean and transform → load to data model → create relationships → analyze

How can a data model be used to create a query

6
New cards

Single-table Queries

Use only one table as their data source. Select the existing table that contains the data needed to answer the business question. PK and FK have different meanings.

7
New cards

Aggregation

Using the Statistics menu in Power BI, an entire column of data can be aggregated (or summarized) into a single value: via sum, min, max, median, average, standard deviation, count values, count distinct values.

8
New cards

Sum

Returns total of the column.

9
New cards

Minimum

Returns lowest value in the column.

10
New cards

Maximum

Returns highest value in the column.

11
New cards

Median

Returns median of the column’s values (middle value).

12
New cards

Average

Returns average of the column’s values

13
New cards

Standard Deviation

Returns standard deviation of the column’s values.

14
New cards

Count Values

Returns the number of values in the column.

15
New cards

Count Distinct Values

Returns the number of different or unique values in a column.

16
New cards

Summarize data into a single scalar value.

When do you use aggregation functions in Power BI

17
New cards

Differences between count and count distinct values

If there were 5 Products. The Count of Product ID would be 5. For size ounces there are 5 sizes (18, 16, 12, 8, and 8)—> Count distinct would say 4.

18
New cards

Aggregating within Groups

Using the Group By menu, specify a column that contains the grouping column and what column should be aggregated and how. Any type of aggregation that can be performed on an entire column can be performed within groups. The query automatically determines how many groups exist by examining the grouping column’s distinct values. Example: Group By Category ID —> Operation: Average Column: Price.

19
New cards

Sorting

Arranges the rows in the query by examining the values in a specified column.

20
New cards

Sort Ascending

A to Z or lowest number to highest number.

21
New cards

Sort Descending

Z to A, highest number to lowest number

22
New cards

Filtering

Removing values that meet specified criteria.

23
New cards

Text Filtering

Can specify the entire cell contents (is), partial cell contents (contains), or starting with a specific character (begins with).

24
New cards

Number Filtering

Can specify operators: equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to.

25
New cards

Equals vs Contains

The equals means that Power BI will pick up only instances that look like what you are looking for. Ex: If something equals 1, it will only find the 1s. The contains means Power BI will look for a certain word in a string. Ex: The word Chocolate is in Chocolate Cake, Chocolate-chip cookie dough, Chocolate Ice Cream.

26
New cards

Multiple Filtering Criteria (AND, OR)

Multiple filters can be specified using the Advanced Menu under Filtering. Each criteria referred to as a clause. AND requires both tests to be true. OR requires only one test to be true.

27
New cards

Moving information into a single table so that single table queries can be applied.

Goals of multi-table queries.

28
New cards

Append

If multiple tables have the exact same columns and store similar information this is used to form a single table.

29
New cards

Merge

For queries that require data from multiple different tables, the table must first be this. This selects all rows from both participating tables or queries as long as there is a match between the specified columns.