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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
OQ
Frequent Visitor

Possibility of hybrid (aggregate / granular) data model

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.

2 REPLIES 2
OQ
Frequent Visitor

Hi @v-tianyich-msft,

 

Appreciate your response, but I think you've missed the point. It really is more of a theoretical question about the feasibility of a hybrid semantic model that consolidates and relates both imported aggregated data (monthly) and a live connection to the same data at a daily level for the purpose of facilitating ad-hoc (and edge-case) drilldown to the daily level within the same report.

 

I'm wondering if anyone has ever accomplished something like this before, and what difficulties and pitfalls to expect. For example, how should measures be structured? Will there need to exist seperate Month and Day measures to leverage from each of the fact tables respectively, or is there a better way to do it?

v-tianyich-msft
Community Support
Community Support

Hi @OQ ,

 

I can understand that you want to filter the table by month? You can check the following results:

Day Table:

vtianyichmsft_0-1702889709921.png

Month Table:

vtianyichmsft_2-1702889734318.png

They have nothing to do with each other.

vtianyichmsft_3-1702889783807.png

vtianyichmsft_4-1702889792707.png

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.