Forum Discussion
Organize the data for the fact table
This is kind of what my data looks like. There is just over milions of rows of data that I am trying to move foard in most efficient way. I am trying to capture by perhaps custom columns following data
Custom column #1 - Latest Status for the year of the date
Custom column #2 - Associate season with current year
Additionally do I need to keep this in same table or does this need to be aplit based on personid and requestid?
This is small sample of the data
| PersonID | RequestID | Date | Type | Status | Season |
| 11111 | 123456 | 15-Jul-25 | A | AAAA | Summer |
| 11111 | 123456 | 22-Aug-25 | B | BBBB | Summer |
| 11111 | 123456 | 11-Feb-26 | B | CCCC | Winter |
| 11111 | 232323 | 15-Jul-25 | D | AAAA | Summer |
| 11111 | 232323 | 20-Dec-25 | F | BBBB | Winter |
| 22222 | 654321 | 17-Aug-25 | C | AAAA | Summer |
| 22222 | 654321 | 22-Sep-25 | C | BBBB | Fall |
| 22222 | 654321 | 17-Jan-26 | C | CCCC | Winter |
Hi stribor45
Thank you for reaching out to the Microsoft Fabric community forum.
When working with a dataset containing millions of rows, it's best to keep all records within a single fact table. Dividing the data by PersonID or RequestID isn't needed and can reduce performance and make the model more complex.
To find the latest status for each PersonID–RequestID–Year group, start by adding a Year column derived from the Date. Then, use Power Query to group by PersonID, RequestID, and Year, and select the maximum Date. Merge this grouped data back with the original table on PersonID, RequestID, Year, and Date to get the corresponding Status. This approach avoids costly sorting and row-by-row calculations, making it efficient for large datasets.
If Season is determined by the calendar, it's better to calculate it from a Date dimension instead of storing it in the fact table. In summary, keeping a single fact table, calculating “latest” values during data refresh with grouping, and using a Date dimension for time-based fields is the recommended method.
If you have any further questions, feel free to reach out and we'll be glad to assist.
Regards,
Microsoft Fabric Community Support Team.
3 Replies
Read about Normalization, and about the difference between a Fact (something you calculate) table and a Dimension (something you filter by) table . Then design your data model so it fits your business scenario.
Hi stribor45
Thank you for reaching out to the Microsoft Fabric community forum.
When working with a dataset containing millions of rows, it's best to keep all records within a single fact table. Dividing the data by PersonID or RequestID isn't needed and can reduce performance and make the model more complex.
To find the latest status for each PersonID–RequestID–Year group, start by adding a Year column derived from the Date. Then, use Power Query to group by PersonID, RequestID, and Year, and select the maximum Date. Merge this grouped data back with the original table on PersonID, RequestID, Year, and Date to get the corresponding Status. This approach avoids costly sorting and row-by-row calculations, making it efficient for large datasets.
If Season is determined by the calendar, it's better to calculate it from a Date dimension instead of storing it in the fact table. In summary, keeping a single fact table, calculating “latest” values during data refresh with grouping, and using a Date dimension for time-based fields is the recommended method.
If you have any further questions, feel free to reach out and we'll be glad to assist.
Regards,
Microsoft Fabric Community Support Team.
Hi stribor45
We would like to confirm whether the issue has been resolved. If it is still outstanding, please share any additional information so we can assist you further.
Thank you.
