Hi,
I try to get "online" reports on large volumes of SQLserver order data.
I have two underlaying fact tables of orders; "order_until_yesterday" and "order_today". My orders are "posted" and "irreversible" so the "SQL: union all" of those two tables are always correct. All tables are in SQLserver db.
"order_until_yesterday" has hundered thousands of rows and is defined as Imported + "Not include in report refresh", "order_today" has max some hundered rows and is a DirectQuery + "Include in report refresh".
My query "order" is the base table for all reports and that has relations to dimension tables (like customer, product, date) is defined as
"Source = Table.Combine({order_until_yesterday, order_today})" and will aggregate amounts over the dimensions.
After midnight the "order_until_yesterday" should be Imported (takes many minutes, this data changes not during the next day).
The plan is that "booked" on refresh in a report only refreshes the direct query part ("order_today"), this takes max a second, but maintain the imported data for old bookings ("order_until_yesterday") that takes many minutes to load.
PROBLEM:
1. My problem is that when I filter the reports (select a customer etc) no refresh is made of "order_today" (can ev live with this, it works when all tables are DirectQuery), but when I (ie the end user) Refresh (all visuable objects in report) it loads also the "order_until_yesterday" and that is not needed and not acceptable.
Do I have the wrong design/approach, or have failed my configuration?
/jerome
desktop version, Version: 2.93.981.0 64-bit (may 2021)
