Single wide table and dimensional schema

Hi SamyAbdul​,

I would also favour option 1.

If the four datasets have different grains or observation periods, I would not force them into one wide Silver table just to make reporting easier. Microsoft’s star schema guidance recommends that fact tables maintain a consistent grain, and Fabric’s fact table guidance follows the same principle.

So I would keep the four Silver tables clean and source-aligned, then build separate Gold fact tables where each fact preserves its own business grain.

A shared Hotel dimension is a good fit here. You can also use shared Date and Source Release dimensions where appropriate. Microsoft refers to these as conformed dimensions, meaning the same dimension can consistently filter multiple fact tables.

The observation period should be modeled explicitly rather than hidden in a wide table. For example, one fact may be monthly, another quarterly, and another event-level. They can still coexist in the same semantic model as long as each keeps its own grain and relates correctly to the shared dimensions.

For source releases, I would also keep release metadata as data rather than using it only as an ETL filter. That makes it possible to report the latest release by default while still reproducing historical releases when needed.

If the business later needs a specific one-row-per-hotel snapshot, I would create that as a Gold reporting view or aggregate table rather than making it the primary model.

So my preference would be:

• Silver → retain the four standardized datasets at source grain.
• Gold → shared Hotel/Date/Release dimensions plus separate fact tables.
• Optional reporting layer → create a wide aggregate only for a clearly defined use case.

That gives you better lineage, easier maintenance, and avoids losing detail by forcing unrelated grains into one structure.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.