Create and Manage Aggregations Flashcards
1
Q
Why create an aggregated table?
A
It will be smaller and should increase the efficiency of your reports and visuals
2
Q
How is creating a new table off of an existing table using reference different than copying the table?
A
- Copying the table creates an exact copy with all of the applied steps
- Referencing a table to make a copy uses the table being referenced as a source. Only one step will come with, the step creating the source.
3
Q
How do you create a reference table?
A
Right click on the table you want to reference and select reference.
4
Q
How do you create an aggregate table in Power Query?
A
- Create a reference table
- Rename the new table
- Remove any unnecessary columns
- Use Transform->Group By to finish creating the aggregate table
- Create the correct relationship between the new aggregate table and the other desired tables
- Set the storage mode of the aggregated table as import
- The dialog box will offer to set the dimension tables to Dual. Allow this and then click ok. This will allow the dimension tables to act as imported when used in visuals with the aggregated table and DirectQuery when used in visuals with the DirectQuery Fact table
- Go back to Power Query and match the data types for the columns in the aggregate table to the data types of the source columns
- Select the Go to Model View->Select the aggregated table->Right Click->Select Manage Aggregations
- Make sure the configuration in Manage Aggregations matches what you created in the Group By in Power Query
5
Q
Where do you Manage Aggregations?
A
Model View
6
Q
Why are aggregation tables hidden in report view?
A
Because users cannot query aggregation tables
7
Q
How can you Manage Aggregations?
A
Model View->Select the aggregation table->Right Click->Select Manage Aggregations
8
Q
Can you create complex logic in DAX when using DirectQuery?
A
No
