Microsoft Stack Lead BI engineer interview experience.
Recently one recruiter reached out for a Lead position in BI Engineering. She never wanted to expose the company whilst requesting the Job description. In return, she requested my resume. So afterwards she passed it to the company, and they set up an interview. I am a data scientist With full-stack experience with BI exposure as well. But after joining the interview, I got to know that the position is entirely based on BI with not much relevance to data science. Nevertheless, thought of accepting the challenge and hands-on the interview.
Met a beautiful mentor Chamila Mihiripenna who works as a lead architect at Virtusa. He has a sporadic quality that He asked questions, and if it’s not what he expects, he’s telling the way he thinks, and that helped me in many ways, to find that out or to search and improve my skill sets. Being participated in many interviews, If the candidate fails to answer, they won’t even say it’s wrong or not, instead help us to find our solutions through investigations. So these were the questions that was unable to answer.
Q: imagine a lengthy report, and you need to improve the report loading time in power BI. What would you do?
A: Use the Power BI Desktop performance Analyzer; using that, it helps to check the loading times of each visualization, Data, or the relevant Dax queries, and when you export it as a JSON, you can get a more detailed analysis of the report and improve the timing of the report. It mainly covers the following three categories · Dax Query · Visual Display — Rendering · Other — Wait times, and Synchronization times.
Additional Reading: https://www.sqlbi.com/articles/introducing-the-power-bi-performance-analyzer/
Q: When it comes to Parquet files, is it easy to query in Azure Synapse? And the way of doing that?
A: Can perform that by using a serverless SQL pool in Synapse analytics. When declaring the table, you can have an OPENROWSET function and specify the format as PARQUET.
Additional Reading: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files
Q: What would you do if the PBIX file is too big and if we want to reduce it?
A: Could reduce the size of the PBIX file by importing only the necessary columns to the dataset and by reducing the column cardinality. Also, this tool called VertiPaq checks the column cardinality and compressing columns and many more features in that.
Recommended by LinkedIn
Additional Read: https://towardsdatascience.com/how-to-reduce-your-power-bi-model-size-by-90-76d7c4377f2d
Q: Imagine a SharePoint dataset has been linked with power bi and since the minimum refresh time in power bi is 30 minutes user needs to update that soon as possible. What is the way of doing that?
A: The user can create a Flow in Microsoft Power Automate when the SharePoint file is updated to refresh the power bi dataset. So there is no need for a manual refresh or power shell queries.
Q: If we want to deploy a power bi report to 500 users, how do we do that? The solution should be cost-effective as well.
A: If the Power BI premium capacity license is available, it’s possible to share power BI content with anyone even outside your organization without purchasing a per-user license.
in the end, he asked me a question, and he told me to find out the answer.
Q: if we have historical data in one power bi dataset and want to alter the data in the power bi dataset. How can we do it? Replied as follows
A: With the usage of the ALM toolkit, it’s possible to compare the schema when it comes to models and datasets and perform straightforward deployment across the environments and retain incremental refresh of historical data. Also gives the possibility to check the diff and merge metadata files, branches, and repos.
Additional Read: http://alm-toolkit.com/
No regrets about the job, but It was a great avenue for expanding my knowledge capacity. Thank you so much for the knowledge sharing, Chamila. Thought of sharing this because it would help other interviewers to be like Chamila, and it will help anyone willing to become a tech lead in the Microsoft stack associated with power bi.
Comment down below if there’s anything to be added or corrected. If you find this useful make sure to add a clap and share it with your friends!