Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NoodleCat
Frequent Visitor

aggregate table not used in query

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

  • dual
  • TimeKey int 0-1440 for all mins in a day
  • Interval15MinKey int 1-96; 15 timekey per 1 interval15minkey
  • HourKey int 0-23; 60 timekey per hourkey; 4 interval15minkey per hourkey
  • MealPeriodKey int 1-3; many hour key per 1 mealperiodkey

 

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.

  1. SalesLabor_BASE: DirectQuery; SmartDateKey, RestaurantKey, OrderTimeKey, MealPeriodKey, HourKey, Interval15MinKey
  2. SalesLaborDay_AGG: Import; SmartDateKey, RestaurantKey
  3. SalesLaborMealPeriod_AGG: Import; SmartDateKey, RestaurantKey, MealPeriodKey
  4. SalesLaborHour_AGG: Import; SmartDateKey, RestaurantKey, HourKey
  5. SalesLaborHour_AGG: Import; SmartDateKey, RestaurantKey, Interval15MinKey

 

Agg1.jpg

Agg2.jpg

 

the following relationships are in the model

ModelRelationships.jpg

 

MPAgg_Time.jpg

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"
}
]
}

 

1 ACCEPTED SOLUTION
NoodleCat
Frequent Visitor

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. 

ERD.jpg

View solution in original post

3 REPLIES 3
NoodleCat
Frequent Visitor

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. 

ERD.jpg

lbendlin
Super User
Super User

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)

Agg R.jpg

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.