I'm building a model to analyze retail sales, inventory and margin (profit). Each of these are in a separate table organized by week_ending_date, item and location. Currently, the sales table has 6.7M rows, stock has 43.5mm rows, and margin has 58M.
For performance and data model size considerations, I would think that the best practice would be to creating one table/view that consolidates the three areas each on line summarized by date/item/location then connect that view to power bi, rather than bringing in each table separately. This would give me one fact table with 58M rows, rather than three separate tables, each with the same three dimensions, totaling 108M rows.
Would appreciate any thoughts on my my reasoning is wrong and/or suggestions of how to revise my approach. Thank you!
