Doing Power BI the Right Way

This is an introduction to a series of posts and pages that will provide a comprehensive set of best practices for successful Power BI solutions. In previous posts, I have asked readers to suggest topics for future posts. Based on that and other feedback, I will be addressing questions and suggested topics. The topics list at the end of this post is a brainstorm list and I ask that you help me make sure it is complete. My goal is to provide a set of guidelines and practices that provide the best chance of success as you navigate many decisions about how to stage and transform source data, how and where to perform data shaping and calculations, how to model data and the best way to visualize the results. The biggest question of all may be how to make the right decisions so that the small project you design today will work when you add more data, more users and transition into a formal, managed solution.

There are many fantastic resources to learn about Power BI and rest of the Microsoft BI and reporting platform; but learning about Power BI and the choosing among design options can be like drinking from multiple firehoses at full pressure at the same time. I will be the first to admit that my “best practices” are “my opinions”. In many cases they work consistently for me but points are debatable and open for discussion. I’ll tell you when I have a very strong opinion about something being done a certain way, or when I have found a pattern that works for me and that I offer for your consideration. I’m not always right… just ask my wife :-). Please comment, ask and offer alternative points of view.

Rather than offering another training course or duplicating the contributions that so many others in the industry make through their blogs, courses, books and articles; this will be a condensed set of guidelines about the many choices you must make when designing a solution. In the posts to follow, I will reference other resources and discussions on various topics.

Just tell me what to do

Any attempt to apply universal best practices to Power BI solution design is a slippery slope. The tools are so flexible and powerful, and the requirements of each project are so varied that it is challenging to establish a set of steps or rules that, if followed, will always yield the absolute best design for a given scenario. With that out of the way, I’ll say this: In my job, I see a lot of poorly-designed Power BI projects. I’ve worked with dozens or scores (maybe even hundreds?) of consulting clients who bring us projects – some partially completed, some finished, and many that are just broken – to be fixed or completed. My reactions range from “that’s just downright wrong” to “hmmm… I wouldn’t have done it that way but I guess it will work for the time being”. I try not to cast stones and do, on occasion, realize that others have found a better way to solve a problem. I don’t have all the answers but I do have a lot of experience with Microsoft Business Intelligent solution design, and have learned many good practices and design patterns from other community leaders and many successful projects over the past twenty or so years.

A little less conversation and a little more action

Let’s start with a simplified flowchart and condensed decision tree. This first whiteboard drawing is the first half of the Power BI design process, ending with the data model, before measures, visualization and solution delivery. There is a lot more but I think this is a good starting point. Let’s start the conversation here and then I will enhance this post with a more complete list of topics.

Best practice guidelines topics

The following topic list will serve as a link menu for future posts. Expect this list to be updated and completed:

  1. Futureproofing Power BI solutions
  2. Preparing, shaping & transforming source data
  3. Choosing the best option to shape & transform data
  4. Power Query design best practices
  5. Power Query in dataflows or Power BI Desktop
  6. Data modeling essentials and best practices in Power BI and AS tabular
  7. Validating data model results
  8. Planning for separation – data models and reports
  9. Power BI delivery options
  10. Choosing the right report type: analytic or paginated
  11. Designing and managing large datasets in Power BI
  12. Multi-developer and lifecycle management for Power BI
  13. Certified reports, certified datasets & the self-service mindset

36 thoughts on “Doing Power BI the Right Way

  1. Pingback: Doing Power BI the Right Way: 7. Validating data model results – Part 2 | Paul Turley's SQL Server BI Blog

  2. Pingback: Doing Power BI the Right Way: 9. Choosing the right report type: analytic or paginated | Paul Turley's SQL Server BI Blog

  3. Pingback: Doing Power BI the Right Way: 7. Validating data model results | Paul Turley's SQL Server BI Blog

  4. Pingback: Doing Energy BI the Proper Method: 2. Making ready, shaping & reworking supply knowledge - Trader Sensation

  5. Excellent article(s)- this is just what is needed to guide you on Best Practices when working on Power BI Reporting Projects

  6. Pingback: Doing Power BI the Right Way: 2. Preparing source data | Paul Turley's SQL Server BI Blog

  7. Pingback: Doing Power BI the Right Way from Paul Turley’s SQL Server BI Blog – Sample News Aggregator

  8. Pingback: Doing Energy BI the Proper Method: 1. Futureproofing Energy BI options - Trader Sensation

  9. Pingback: Doing Power BI the Right Way: 1. Futureproofing Power BI solutions | Paul Turley's SQL Server BI Blog

  10. Hi Paul,

    Great list of upcoming articles I’m very excited. A few thoughts on additional topics I would be interested in. I’m probably 9 months into my Power BI journey consuming content as quickly as I can to come up to speed. One recent message I have absorbed is that I need to build my knowledge, fluency, and depth around DAX and Power Query command/functions. I know I would benefit from a primer as to how to develop my abilities, not just in command of syntax but also application into complex construction of measures, calculated columns, and tables based on use cases. A current challenge I have is providing project informational summary reports. I am needing to manipulate filter context on text data and reshape it using matrix stacking of multiple columns. Most of the examples I see for changing filters are for numeric data. I am finding solutions through trial and error but I would feel more confident if I had a model or pattern to follow.
    Thanks, Mark

  11. Pingback: Doing Energy BI the Proper Means - Trader Sensation

  12. Pingback: Energy BI Exterior Instruments: Studying the Tea Leaves - Trader Sensation

  13. Pingback: Power BI External Tools: Reading the Tea Leaves | Paul Turley's SQL Server BI Blog

  14. Great! Thank you very much Paul.
    With the July 2020 PBI version, I suggest to add a topic about using external tools like Tabular Editor in order to manage measures, add calculation groups …

  15. Probably the best thing I have come across today. As someone who recently moved from Oracle to Microsoft BI solutions , I can’t wait to digest your wealth of wisdom on how to deliver BI solutions using Power BI. Thanks so much for putting this together,

  16. Looking forward to the series. None of us want to waste our time with rework. Having a guiding framework will be invaluable when considering design/development trade-offs. Thanks, Paul!

  17. Here are some topics I would like to hear about:
    * when to use AS vs. PBI for a model
    * bi-directional joins
    * maintenance and enhancements (in my case, in AS) without breaking existing stuff

  18. I have a question. I have a dashboard that is connected to a sql server database, no DW. Is it better to create the queries, stored procedures, etc. at the database source, or is it better to import the data first into power bi and then create the queries, etc. locally?

    • Steven (and others with similar questions), I will most certainly address this question of where in the solution architecture to develop transforming queries. The decision to create queries as Power Query queries rather than database views depends on several factors. It is often a trade-off between flexibility in the BI solution design and the streamlining & performance advantages gained by creating database objects. Power Query doesn’t work with stored procs nearly as effectively as it does with tables, views and table-valued UDFs so I wouldn’t use stored procs for source queries.

  19. This is a great idea. I recently completed a report for a large corporation, and I had a lot of ‘what if’ questions. After the report was ‘done’, I have a lot of things I would do differently. I think that one of the key things to consider when designing a report (note that I am not even talking about an end to end solution!) is how to balance the time allotted and the time required. There are a lot of things I would have done differently if I had time allotted to go edit the report now. The other issue to consider is that when you start the report, you know less than you do after you have been working on it awhile. So then the issue is–do you go back and fix what you now know could be done more effectively, even though you are running out of time? I have so many more thoughts along these lines…

    • There is no doubt that this kind of learning comes from experience but that experience shouldn’t always have to be yours alone. The iterative development pattern has always been a challenge but there are good reasons to either keep or throw away a design after discovering a better way. Thanks for your thoughts.

  20. I would like to see you recommendation for where role of Power Query ends and the Data Model begins. There is tendency for some aspects like joins or row calculation to appear in Power Query. I am starting to see the benefits from leaving the join until the Data Model phase, and Row Level calculation until DAX SUMX or COUNTX formulas are used in measures.

  21. Pingback: Power BI the Right Way – Curated SQL

  22. Hi Paul,
    Thank you for the post I look forward for more to come. I’ve recently gotten into a role where I’m planning a BI solution using PowerBI for a client. It has been rough for me as I am usually the “doer” not the planner, so I’ve had a lot of anxiety in trying to ideate and plan the best way to Integrate everything together. This article contains a lot of great stuff on thinking bigger picture when creating a BI solution.

    I hope to see more,
    Edward

  23. Hi Paul, Great article. Looking forward to your next segments.
    Just some errata for you, there is a mix-spelling in the paragraph just above the Flow Chart. Second sentence up needs a k at the end of “think”.
    Feel free to delete this comment after you’ve corrected.
    Appreciate your work for the community!

Leave a Reply