Inherited a report that is one flat 12 million row table with 60 columns, refreshing in about 25 minutes. I want to break it into a star, but that is a week of work and the business is happy with the numbers today. Constraint is a Pro workspace, so a 1GB dataset limit, and I am the only analyst. Is the rebuild worth it or am I doing it because a book told me to?
Honest counterpoint: if the report is genuinely one grain, nobody else builds on the dataset, and it fits comfortably in memory, a flat table is not a sin. The cost is not correctness, it is every future change. Adding a new attribute to a dimension touches one small table; adding it to a flat table is a 12 million row reload every time.
Agreed on the principle, but "nobody else builds on the dataset" has a half life of about six months in most companies.
A 25 minute refresh on Pro is also close to a limit you will care about soon. A star lets you set incremental refresh on the fact table alone, which is where the real time saving is.
Split the difference so you are not gone for a week. Pull out the three biggest text columns into dimensions first — usually customer, product, geography — measure the file size, and stop if that gets you enough headroom. Most of the win lives in a handful of columns.
@idempotent_ian · 10mo ago · 2 replies
Star, and the Pro limit is the argument that wins on its own. A flat table repeats every customer name, product description and region string on all 12 million rows. Pull those into dimensions and the fact table keeps integer keys, which compress dramatically better. I have watched that exact change take a dataset from 900MB to 200MB without changing a single visual.
The second benefit is that slicers stop fighting you. On a flat table a product slicer only shows products that had sales in the filtered period, which confuses users constantly. With a dimension table you decide.
Reply
Report
@pinned_versions · 10mo ago
The compression argument is the one I can take to my manager, because "we hit the Pro ceiling in November" is a date and a consequence.
Reply
Report