I am Microsoft PFE for Data & AI. I am always learning something new; I wish I had time to post about all my learning and experiences. This role is rewarding and frustrating sometimes. More rewarding than frustrating, or I wouldn’t be in it for nine years running.
Anyhow, while working on a moderate size model (2.5GB) of Power BI dashboard with my DSE, we hit a significant bottleneck this week. Adding a new measure would take us about 60-70 seconds. It became very frustrating to point we were ready to throw the brick through his whiteboard.
None of the tools, DAX Studio, VertiPAQ, or Performance Analyzer, told me why?
We had to delete one element at a time until we found the root cause.
A calculated column, DaysSinceCreated = DATEDIFF(ObjectDate,Today(),Day)
My understanding was “calculated columns are only reviewed during creation,” however, so WRONG I was. Talking to folks internally, I learned a valuable lesson today. Power BI pre-processes calculation of every computed column while making any model changes. Therefore, the more computed column you have the higher the cost. I know best practice is to minimize them in the model to minimize the model size and memory but never for this.
I would not have looked at this computed column as it was only 4MB in the 2.5GB model. I wish I knew that I wish there were a tool that helps identify that. We wasted half-day to track this down by deleting one component at a time.
Best practice, move them to the ETL job outside Power BI, or to Power Query (so maybe it can be query folded).
PS My goal was to get posts every month, but work-life-blogging life doesn’t work together for me. Maybe one day in the future it will.
SQL Canada (SQLCAN) 