cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NoodleCat
Regular 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
Regular 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
Regular 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

lbendlin
Super User III
Super User III

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors