Single wide table and dimensional schema

SamyAbdul​ 

I strongly suggest go with option one and follow the medallion architecture.

1. Bronze – Raw Data

• Preserve source data as received, including source release and ingestion metadata.
• bronze.raw_dataset_a
• bronze.raw_dataset_b
• bronze.raw_dataset_c
• bronze.raw_dataset_d

2. Silver – Cleansed and Standardized Data

Maintain separate tables, preserving each dataset's original grain while standardizing column names, data types, formats, and common business attributes.

• silver.dataset_a
• silver.dataset_b
• silver.dataset_c
• silver.dataset_d

Avoid consolidating datasets with different granularities into one wide table.

3. Gold – Dimensional Model (Warehouse)

Build the dimensional model, business logic, and reporting structures.

Dimension Tables:

• dim_hotel
• dim_date
• dim_geography (if required)
• dim_source_release
• dim_observation_period
• Fact Tables:
• fact_dataset_a
• fact_dataset_b
• fact_dataset_c
• fact_dataset_d

Each fact table should preserve its respective business grain and connect to shared/conformed dimensions where applicable.

If consolidated hotel-level reporting is required, create a Gold-level reporting view or aggregated table rather than introducing unnecessary consolidation in Silver.

My recommendation: Keep Silver independent of reporting requirements and perform dimensional modelling in Gold. This provides better scalability, reusability, and maintainability while preserving the original granularity and release context of each dataset.

If this helps, ✓ Mark as Kudos | Help Other

Hi SamyAbdul,

I'd go with your first option: keep the four Silver tables at their native grain, and build a shared Hotel dimension plus separate fact tables in the Gold Warehouse. The different observation periods and independent source releases are actually the main reason not to consolidate early.

Why I'd avoid the wide Silver table

• Pivoting four datasets into one row per hotel per snapshot forces a single grain onto data that doesn't have one. You end up deciding inside the ETL which release "wins", and you get nulls or repeated values wherever the periods don't line up.
• Every new release of any one source means re-pivoting the whole table, and you lose the ability to say exactly which release a number came from.
• It's hard to undo: going from separate facts to a combined view later is easy, the reverse isn't.

Suggested design

1. Silver: one cleaned table per dataset. Standardize column names, data types and the hotel business key, and add lineage columns such as source_release, observation_period_start, observation_period_end and load_ts. No pivoting and no business-level aggregation.
2. Gold dimensions: a conformed DimHotel keyed on the standardized hotel key (SCD Type 2 if hotel attributes change between releases), a DimDate, and optionally a DimRelease/DimSnapshot describing each source release.
3. Gold facts: one fact per dataset at its own grain (e.g. admissions per hotel per period, another dataset per hotel per month). Each fact links to DimHotel and to DimDate at the grain it actually has. Snapshot-style datasets fit the periodic snapshot fact pattern.
4. "One row per hotel per snapshot": if the business needs this, build it as a Gold view or aggregate fact on top of the facts, using a small mapping table such as ReportingSnapshot(snapshot_id, dataset, source_release) that records which release of each dataset belongs to each snapshot. Release selection then becomes data, not hard-coded ETL logic.

In the semantic model, the shared DimHotel and DimDate let users slice across all the facts together, so most reports won't need the wide table at all.

Useful Microsoft Learn references:

• Dimensional modeling in Fabric Data Warehouse: https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-overview
• Fact tables (incl. periodic snapshot facts): https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-fact-tables
• Star schema guidance (fact tables should load at a consistent grain): https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Hope this helps!

Hi SamyAbdul​ ,

Option 1 is right. On your specific question about observation periods and releases:

1. Keep each Gold fact at its own grain and period. If one dataset is monthly and another is annual, make both periodic snapshot facts and link each to dim_date at its own level, for example the first day of the period. Do not force them into one snapshot.
2. Treat source release as data, not as a filter. Store the release id on every fact row, plus an is_latest flag or a view that picks the latest release per hotel and period. Then reports default to current figures, and you can still reproduce older numbers.
3. Combine across facts in the semantic model, not in a wide table. Both facts filter through dim_hotel and dim_date, and measures from each sit side by side in one visual. If one fact is coarser than dim_date, Power BI handles that with the higher grain fact pattern.

Docs:

https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-fact-tables

https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many

If this helps, please mark it as the solution. 

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.

Hi SamyAbdul​,

I agree with Murtaza_Ghafoor​ on keeping the four Silver tables. The reason that matters most for your question is grain: datasets with different granularity and independent releases only combine cleanly in a wide table if you throw away information (or invent rows), whereas separate facts keep each grain honest.

A shape that usually supports both the differing observation periods and a single-row report view:

Silver : 4 cleaned tables, each at its own grain, with source_release and observation_period columns
Gold   : dim_hotel (shared), dim_date / dim_period, one fact table per dataset at its own grain
         each fact keeps hotel_key + period_key + source_release
Serving: a view (or semantic model measures) that joins the facts to dim_hotel for a chosen
         snapshot, pivoting admissions measures only at query time

That way the shared Hotel dimension supports cross-dataset reporting without an extra bridge, the release and period columns answer which data was used, and the wide row-per-hotel snapshot becomes a presentation choice that can change without reloading Silver.