This is the third part in the blog series summarising my experience with Power BI from the real-world projects.

• Things Better to Know Before Implementing Power BI Service
• Pain Points of Power BI
• Things Better to Do When Working with Power BI

1. Create separate QA/UAT app workspace from Dev app workspace

Separate QA/UAT app workspace from Dev app workspace. Only app workspace admins have contribute permission with QA/UAT app workspaces. Normal report authors will not be able to modify the content in QA/Test app workspaces. Any change/bug fixing requested from QA/UAT will be feedback to report authors and will be implemented in Dev app workspace. The final on-prod Power BI app need to be published from QA/UAT app workspace.

2. Prefer SSAS Tabular Model over SSAS Multidimensional Model if need live connection

For large-scale dataset, we may need to build data model outside of Power BI and to use Power BI for visualisation. Within Microsoft stack we have two options: SSAS Tabular model and SSAS Multidimensional model. If there is no specify restriction that limits you to use the multidimensional model, prefer to use tabular model for the following reasons:

• SSAS Tabular Model and Power BI use the same analytics engine and support the same query language (DAX). NFUM BI developers can reuse their Power BI development skills for SSAS Tabular Model development, instead of learning other skills or seek external supports.
• In-memory processing model used by SSAS Tabular Model is identified as the desired model by Microsoft and continued investment in the tabular model has made it a mature capability

3. Using DirectQuery mode with cautions

The main reason to use DirectQuery mode is to reduce latency on data updates. This mode is recommended to use only when near-real time reporting is required, or the database is too large to fit in the memory of a single Analysis Services server (> 100 Billion rows). While DirectQuery mode is capable to reduce data update latency, this mode requires directly query from the same relational engine used to update the data with the data is not cached and organised in query efficient structure. Therefore, DirectQuery mode will increase the load on data sources, and can reduce performance of end user report rendering as reports in DirectQuery mode cannot take advantage of Power BI in-memory process engine.

4. Source control Power BI pbix file and release control organisational Power BI app publishing

Keep the Power BI pbix file in source control repository. The OneDrive storage associated with a Power BI app workspace group (will be auto provisioned when create an app workspace) is a good choice as it is accessible for business users and do not require extra licenses. The release control policy, procedure, and management tool (can be built on the team site in app workspace group) need to be planned.

5. Use ExpressRoute if possible

You can use Azure ExpressRoute to create private connections between your on-premises data sources and Power BI services through public peering (not Microsoft peering or private peering), instead of through public internet connection. ExpressRoute not only provides you with a network with better performance and reliability, but also ensure the better security and privacy of your data.

6. On-premises gateway needs to be close to the data sources

The on-premises gateway needs to be deployed as close as possible to on-premises data sources to avoid data transport/network performance issues.

• Power BI/DAX/M