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.
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:
- Futureproofing Power BI solutions
- Preparing, shaping & transforming source data
- Power Query design best practices
- Power Query in dataflows or Power BI Desktop
- Data modeling essentials and best practices in Power BI and AS tabular
- Validating data model results
- Planning for separation – data models and reports
- Power BI delivery options
- Choosing the right report type: analytic or paginated
- Designing and managing large datasets in Power BI
- Multi-developer and lifecycle management for Power BI
- Certified reports, certified datasets & the self-service mindset
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.
hello, Paul that was an informative content, keep posting such content.
https://www.sevenmentor.com/power-bi-training-in-pune.php
hi paul….excellent work. Do you have any thoughts on version control of Power BI Dataflows? Dataflows are supported by PBI deployment pipelines, however, is there a recommended way of versioning the dataflow?
thanks
mario
Quite exhaustive article, covering most of aspects. As a power bi developer can relate to most of things mentioned. Thanks for sharing.
Paul:
Good article and series. I look forward to the next entries. I’ll be passing it on to other people asking about getting into more advanced uses of Excel or PowerBI that require data modeling, even if they don’t know it yet.
.
Could you make a HTML copy member with links to all of the published articles. That way anyone finding the series in the middle will be able to easily find the rest of the articles. Playing hide and seek with websites and search engines is always so frustrating. You authors are always refining the titles so even if you mention an upcoming article, we may not be able to find it because the title is different …
Keep up the good work.
There is now a link to the index list at the top of each post
Excellent article(s)- this is just what is needed to guide you on Best Practices when working on Power BI Reporting Projects
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
Thanks for your thoughts, Mark. I do have a blog post topic queued up to discuss transformation options and best practices.
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 …
Thanks. It’s in the works now.
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,
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!
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
Thank you, Rhonda. These are good topics.
Excellent list of topics, please get started!!
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.
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.
Life is learning.
Make a list of things you would like to change/improve. Then you can present / sell it to management as “Phase 2” of the project.
.
Even if they don’t buy into a “Phase 2” now, keep the list handy when they come back asking for enhancements and improvements. Some of their requests are probably already going to be on your list, and with a little encouragement you probably get them to agree to pay for more items from your list. Or you can “sneak” them in as part of their request.
.
If you don’t get to use the list on a “Phase 2”, you can use it when developing new projects.
.
I once worked on a project building a new app. The users liked the end result, but didn’t like the tool we wrote it in (the one they insisted on …). So we used the first effort as a template tor the second iteration, with improvements suggested from the first.
.
They liked the result so much, that at a conference when they were bragging about it, another company arranged to buy a copy of it from us. As part of the deal, they agreed to pay for our shopping list of improvements that we collected from the phase 2.
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.
Thanks this will help my learning process.
Thanks
Following.
Oh great approach… Am looking forward…
This sounds very promising, eagerly anticipating the coming articles!
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
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!
I appreciate it, Mike. I’ll correct it when back in the office.
Excellent approach – thanks, Paul. Looking forward to the series.