One of our tabular models kept failing due to memory allocation. The model sourced email results and texting results. Over time, it grew to 30 gigabytes and kept growing. The model supported 4 reports and was basically ingesting transactional data.
The first step in optimizing the model was to determine which reports were actually in use. After checking the admin app and talking to the likely users, it became apparent that two out of four reports have not been in use for a while. One of the team members, after seeing the reports, said the reports are not in use but could be… The “could be” in use required one gigabyte and was structured as a bidirectional bridge table. The report that was very unlikely to be used would take more than 5 minutes to load. Eventually, I built the user a custom report tailored to her needs.
Out of the currently used reports, the transactional table would pull hundreds of millions of rows of data, which later was rolled up in PowerBI matrix. For example, during testing two days of data in that table included 2.5M rows, which included records for each email sent to donors. When rolled up at an email level, the same 2 days had less than 4 thousand rows. A best practice is to push heavy transformation downstream to the SQL server, if possible, prior to loading the data to the semantic model. Ultimately, PowerBI models are meant for analytics and visualization and are not meant to act like a warehouse/lakehouse.
To conclude, work with your team to figure out what is truly needed/in use and adjust backend architecture accordingly.
