Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
tldr
aggregate tables are not being used when time slice is added to query
Want to be able to slice NetSales by day, restaurant, mealperiod, hour, 15min interval and I cannot get the aggregate table to be used when I add an attribute from TIME. It seems like I'm on the cusp, and am hoping someone out there can point out my misstep. I understand why the things I've tried do not work (they should not) but am uncertain where to go from here.
The TIME dimension is
each of the AGG tables are configured in PBI Desktop on top of SalesLabor_BASE. I've confirmed query folding is happening for each. The Agg tables use incremental refresh.
the following relationships are in the model
tried making TIME the base for an aggregate table TimeMealPeriod - no dice
this is the measure I am testing with
MEASURE _Sales[NetSales] = sum(SalesLabor_BASE[NetSales])
from optimizer I can see that the aggregate table is used when the measure, or the measure sliced by date, is called
EVALUATE SUMMARIZECOLUMNS("NetSales", [NetSales])
{
"table": "SalesLabor_BASE",
"matchingResult": "matchFound",
"mapping": {
"table": "SalesLaborDay_AGG"
},
"dataRequest": [
{
"aggregation": "Sum",
"table": "SalesLabor_BASE",
"column": "NetSales",
"mapping": {
"table": "SalesLaborDay_AGG",
"column": "NetSales"
}
}
]
}
EVALUATE SUMMARIZECOLUMNS('DATE'[Date], "NetSales", [NetSales])
{
"table": "SalesLabor_BASE",
"matchingResult": "matchFound",
"mapping": {
"table": "SalesLaborDay_AGG"
},
"dataRequest": [
{
"aggregation": "Sum",
"table": "SalesLabor_BASE",
"column": "NetSales",
"mapping": {
"table": "SalesLaborDay_AGG",
"column": "NetSales"
}
},
{
"table": "SalesLabor_BASE",
"column": "SmartDateKey",
"mapping": {
"table": "SalesLaborDay_AGG",
"column": "SmartDateKey"
}
}
]
}
Here's where the behavior is not as intended
EVALUATE SUMMARIZECOLUMNS('DATE'[Date], 'TIME'[MealPeriod], "NetSales", [NetSales])
{
"table": "SalesLabor_BASE",
"matchingResult": "attemptFailed",
"failureReasons": [
{
"reason": "semijoin not compatible with Import",
"datasource": "Import"
},
{
"alternateSource": "SalesLaborDay_AGG",
"reason": "data source mapping not found",
"datasource": "Import"
},
{
"alternateSource": "SalesLaborMealPeriod_AGG",
"reason": "data source mapping not found",
"datasource": "Import"
},
{
"alternateSource": "SalesLaborHour_AGG",
"reason": "data source mapping not found",
"datasource": "Import"
},
{
"alternateSource": "SalesLabor15_AGG",
"reason": "data source mapping not found",
"datasource": "Import"
}
],
"dataRequest": [
{
"aggregation": "Sum",
"table": "SalesLabor_BASE",
"column": "NetSales"
},
{
"table": "SalesLabor_BASE",
"column": "SmartDateKey"
},
{
"table": "SalesLabor_BASE",
"column": "OrderTimeKey"
}
]
}
Solved! Go to Solution.
I created bridge tables for the TIME grains, and now the aggregate tables are leveraged as I expected. However, my UX is less than ideal (IMHO) in that I have a HOUR dimension and MEALPERIOD dimension rather attributes on TIME.
I created bridge tables for the TIME grains, and now the aggregate tables are leveraged as I expected. However, my UX is less than ideal (IMHO) in that I have a HOUR dimension and MEALPERIOD dimension rather attributes on TIME.
Can you please double check that your aggregation tables are wired exactly the same way as their Direct Query fact tables?
I think that's the beginning of my issue, I cannot relate the AGG tables to TIME without an error (which makes sense, M:M between SalesLaborMealPeriod_AGG.MealPeriodKey and TIME.MealPeriodKey)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |