I am creating a suite of reporting that will leverage two structurally identical fact tables from a relational DB:
- Table A contains data at a daily-level. There are approximately 1 billion rows per year of data.
- Table B is structurally identical to table A, but aggregates data to a monthly-level, so there are approximately 33 million rows per year of data.
95% of the time, users only need a monthly-level aggregation of the data in the reporting, but in some ad-hoc cases they will need to drill down to more granular data (daily-level). To faciliate this, my idea is to create a hybrid semantic model in Power BI:
- Import (and incrementally refresh) data from Table B (to performantly satisfy the 95% of the reporting use-case).
- Live connection to Table A (to satisfy the ad-hoc use-cases where daily-level data is required).
In my mind, this seems like a good solution to satisfy both use-cases while keeping the reporting as performant as possible, but then it raises a bunch of questions in my mind, such as how to relate the fact tables to each other, how to structure all measures to pull from one table or another (ie. will I need a duplicate set of measures for each table?), etc.
As a side note, I'm aware of the ability of the incremental refresh policy to "Get the latest data in real time with DirectQuery (Premium only)", but this won't apply to my use-case because the data exists in different granularities in two different tables, rather than it being a case of data availability beyond my incremental refresh/archive window.
I'm wondering if this sort of hybrid model has been explored or achieved before and whether there is any documentation or best practice for it? Or, is there a better way to achieve what I am after in a different way? Any help would be greatly appreciated.
