I love Power BI, sincerely, since as early as the PowerPivot 1.0 era. However, I must admit Power BI does drive me crazy sometimes. It can play very well when you follow the rules Power BI expecting you to follow, otherwise, it can cause you much pain. This blog series summarises my experience with Power BI from the real-world projects, organised in three parts:

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

This blog post covers the first part, things better to know before implementing Power BI service:

1. Location! Location! Location!

The location of Power BI tenant is important. The data center where hosts the Power BI tenant should be as near as users and be as near as on-premises data sources if they are used. The reason I emphasise location and put it on the top of the list is that the location of Power BI tenant cannot be changed. You will have awful pain when you realise your Power BI tenant is provisioned in a wrong region and must move the tenant (along with all the data already produced) due to performance or compliance reasons.

2. Users in your organisations can sign up your Power BI service by themselves

At certain stage of your Power BI service implementation, you may want to release Power BI service to certain user groups. However, you may find some other users out of those user groups starting to use your Power BI service as well. By default, Power BI service allows self-signing up, and this setting can only be changed by Azure AD administrators through changing AllowAdHocSubscriptions AAD setting. Therefore, it would be wise to take this into consideration at early stage of your project.

3. 70% workload is outside of Power BI

For the medium-to-large size of Power BI development projects, although the title of the project may be “Power BI” something, you will find at least 70% workload is actually outside of Power BI. More efforts are required on data collection, storage, and ETL even before you start to touch Power BI (unless, at early stage of a project, you may use Power BI as a data exploration or prototyping tool).

4. Kimball is still useful

Thanks to Microsoft’s well-made demos, people tend to believe all they need to do for building nice Power BI dashboards is to simply connect to the LOB databases and drag/drop some charts.  For some extremely simple use cases, it may like that. However, once the requirements are getting a little bit complex, you will find you are walking into a big mess without proper data modelling. For example, you may want to skip the efforts of building star schema, however, you will soon find you have to write much more complex DAX and you may hit into circular relationship issues without star schema. Similarly, you will find you do need to make design decisions following Kimball model, e.g., slow changing dimensions, date dimensions, fact table types.

5. Keep column storage in mind

When working with Power BI, it is no harm to keep reminding yourself that Power BI is running on the in-memory columnar database (Vertipad engine). Any unnecessary high cardinal columns, e.g., text description, date and time, transaction id, can harm the performance badly.

6. No more free meal

Microsoft has changed their Power BI licensing model that removing sharing features from the free edition. That means the free edition is strictly for personal use only. Now the only way for report authors to share their work is to send the source pbix file over.

7. S-shape learning curve

Many new Power BI users don’t realise the S-shape learning curve of Power BI software. When a user initially plays/prototypes with Power BI, they often feel Power BI is easy to use, and they can build some very nice dashboards with a few clicks. However, when they start to use Power BI to solve real-life problems, they realise there are a lot of more stuff they need to master. The learning curve starts to get steep. Some users, especially the users with experiences of traditional reporting tool such as SSRS, start to feel awkward with Power BI. Some work can be done easily using SSRS, the pixel perfect reporting tool, become difficult in Power BI. Users need to learn DAX which is the task many users are trying to keep away from.

For Power BI SMEs within an organisation, it is wise to manage Power BI users’ expectations at early stage, and let them realise that they may meet some challenge on their Power BI learning journey, but after they conquered the challenge, the life will become so beautiful where they will find Power BI is actually a very flexible, powerful and productive tool for their work.

8. Multi-dimensional model SSAS is not brother, or even friend with Power BI

This one is better for you to know when your project requiring access to multi-dimensional model SSAS. Unlike Tabular Model SSAS which is the big brother of Power BI (who shares same analytics engine and uses same query language with Power BI), multi-dimensional model SSAS is not that close to Power BI, and it is not even friendly with Power BI.

To access multi-dimensional model SSAS through live connections, there are many limitations, e.g.:

• SSAS Servers must be running SQL Server 2012 SP1 CU4 or later versions
• Performance is slow compared to Tabular Model SSAS
• Does not support report level measures as Tabular Model SSAS

9. Many cool features are coming! … (but only for Premium license)

Many very useful Power BI features are on Microsoft roadmap and expected to release this year, such as incremental loading, pinning datasets to memory, dedicated data refresh Nodes, Ready-only replicas, geographic distribution. However, this is the good news only for users with Premium setup.

This blog post summarises the things better to know before implementing Power BI services. The next blog post will cover some very interesting topics, namely the pain points of Power BI which have driven me crazy.

• Power BI/DAX/M