The first two fields Exist in the FactInternetSales, but not the third one. Go to Power Query Editor, and select the FactInternetSales table, the aggregated table that we are going to create in this example, is going to be on three fields OrderDateKey, CustomerKey, and ProductSubCategoryKey. The aggregated table can be created in the data source with T-SQL queries, or in Power Query, or anywhere else that you can create a grouped table. Since we are doing everything with Power BI here, I’ll create the aggregated table with Power Query. You can create it in all other data transformation tools and query languages possible. You can create the aggregated table with T-SQL statements from SQL Server.
The aggregated table can be created by all possible methods. In our case, Aggregated table, would be a grouped table by specific fields from the FactInternetSales table. The aggregated table is a table in Power BI aggregated by one or more fields from the DirectQuery source table. Tables loaded into Power BI, will have relationships together, I just limited the relationship between DimDate and FactInternetSales table to one active relationship based on DateKey (in DimDate), and OrderDateKey (in the FactInternetSales). With the DirectQuery option, select these tables:įactInternetSales, DimCustomer, DimDate, DimProduct, DimProductCategory, DimProductSubCategory, DimPromotion, and DimGeography. Create a Power BI report with the DirectQuery connection to the SQL Server In this sample model, we will be analyzing the data of FactInternetSales (which we assume is our big fact table). files/adventureworksdw2012.bak Sample model
#Power bi report builder tutorial download#
You can download the database link from here: I made some changes in my dataset to be a bit bigger for the size of its fact table, so I can show you the functionality of aggregations. If you want to follow the example scenario here, you would need a SQL Server database named AdventureWorksDW. If you want to learn more about Power BI, read the Power BI book, from Rookie to Rock Star. In this blog post, I’ll explain how that step can be done. For the aggregation set up, your first step is to create an aggregated table. Aggregations are part of the Composite model in the Power BI. In the previous blog post, I explained what is an aggregation, and why it is an important part of a Power BI implementation. Aggregation tables are the fast performing solution for huge DirectQuery tables in Power BI.