Data modeling - star schema vs. very simple flat table

[frithjof_v]

Hi,

I am trying to get a deeper knowledge about the difference between using a star schema and a very simple flat table (potentially containing millions of transaction rows).

In general, I know that star schema is the preferred solution for modelling, and that Power BI is optimized for working with star schemas. So general rule: always use star schema, when possible.

However I would like to ask about this case with a very simple flat table (which could potentially contain millions of rows).

The flat table is very simple, like this:

The OrderID column contains unique values - no duplicates.

And the report is very simple, like this:

Assume the current report covers all our needs.

The data model is just one table:

There is only one measure:

Average amount = DIVIDE(SUM(Sales[Amount]), SUM(Sales[Quantity]))

To me, this is the upside of just using a single table:

• Very easy to implement.
• Slicer selections will cross-filter the other slicers, so if I choose 2025 in the Year slicer, then only the colors sold in 2025 will be visible in the Color slicer.

Downside of just using a single table:

• I think the DAX query to populate the slicer values will be more costly, because by using the columns from the fact tables in the slicers, the DAX query to populate the slicer needs to scan all the millions of rows in the fact table to determine which values to show in the slicer. When using dimension tables, there are less rows to scan.

Downside of creating a star schema:

I could create a star schema by using Power Query to derive dimensions like Dim_Color and Dim_Year from the flat table, and then create relationships between the dim tables and the fact table. Then I can use the dimensions in the slicers. However, if I still want cross-filtering functionality between the slicers, then I would need to apply a visual-level filter measure on the slicers, something like this:

CountrowsSales = COUNTROWS('Sales') 

and set the visual-level filter to greater than 0.

I'm not sure how costly that DAX query will be. I could test that in DAX studio, but I haven't done that yet.
But at least I know it's more development work involved because I will need to create the star schema tables in Power Query instead of just using a simple flat table.

Which approach do you think I should go with in this case, and why?

Flat table or star schema?

It's very rare that such a simple need as this case occurs, but sometimes it can happen. I'm also interested to learn more about both the practical and theoretical side of this problem.

Any suggestions, and some other aspects to keep in mind?

Thanks in advance!

[Greg_Deckler]

@frithjof_v Keep it simple. Only introduce additional complexity if necessary. Star schema is mostly smoke and mirrors behind the scenes anyway. If your dimensions are only a single column, I cannot possibly see the use in them.