Every day your organization is building Power BI reports that cover the full spectrum of enterprise data needs—financial, sales, inventory, and the list goes on.
- Are you developing smart from the beginning?
- After you put a report into production, how do you support it?
- Do you have plans to maintain that report moving forward…or not?
Power BI is our day job here at Collectiv. Through our own experience and countless experiences with enterprise teams, we’ve learned what it takes to build a proper report in Power BI from start to finish.
The purpose of Power BI reporting is to make data accessible and easily understood, all while helping your organization stay focused on achieving goals. Here are the four stages for building better Power BI reports.
1. Gather Your Data
Choose Import
The first step is to bring your data together from a database, analysis services, or some other source. Every time you are presented with the option to choose import or DirectQuery, you should be choosing import 95%-99% of the time.
DirectQuery allows you to connect directly to the source of the data and you never bring any of the data in. Import is always going to give you better performance because it’s not going back to the source.
Find Missing Data
If you don’t have the data you need (which happens), you have to figure out where you will create that data. While there is no easy answer here, Microsoft’s Matthew Roche says “data should be transformed as far upstream as possible, as far downstream as necessary.”
What that means for you as a report creator is if you need to add content to your data, do it in the source system. Most of us do not have access to the source system, so you’ll need to add content to your data in the Power Query editor.
Consider Credentials
When it comes to accessibility, credentials are another important factor. Don’t forget about how you’re credentialing into the database, into the data source, and where the data source lives. Make sure it is available on something that other users can reach.
Parameterize for Flexibility
Think about your parameters very early on in the process. Using parameters helps you develop flexibility in your Power BI reporting as you dynamically change inputs into your report. The most common use case for this is incremental refresh, where you might go in and change the date selection that you’re pulling from the data source.
Use Incremental Refresh
Incremental refresh is a way to avoid ingesting all of your data at once. If you’re building a report for the first time, you don’t want to work with ten years of historical data in that first developmental phase.
Take a subset and import just that piece. So, as you’re developing your report, you’re taking a representative sample of the data (timeframe, customers, locations, etc.). Using a small subset will help you shorten the refresh time.
Incremental refresh used to be restricted to Power BI Premium but it’s now available with a Power BI Pro license.
2. Document and Organize
Document Your Steps
If you’re familiar with Power Query, you know that as you make changes to your data (i.e. remove rows, split columns), all of that is being tracked in your applied steps window so you’re keeping a running list of these changes.
However, when you get into a particular query that needs a lot of work, you might have to take 50-75 steps. Going back step-by-step is a huge pain. When you take a step, rename it something useful and recognizable. Doing this immediately will save you a lot of time down the road.
Group Your Steps
After you document your steps, you need to group them to make your process more efficient. Rather than doing one piece of work at a time, think in “groups of work.” If you need to rename, duplicate, or remove columns, do similar tasks in one step. If everything is consolidated into one step, it will be much easier to troubleshoot later on.
Remember that Sequence Matters
In the Power Query editor, it matters what order you do things in because of query folding. Query folding refers to the fact that, as much as possible, when you’re putting a step into the Power Query editor…you want it to work.
If you take a step that breaks the folding—meaning that particular step cannot be done in the source system, it has to be done locally—as soon as you have that step in there, every other step beneath it will not be able to do query folding because you broke the chain.
Weed Your Columns
Power BI is a very fault-tolerant environment, so feel free to delete columns as needed, apply those changes, and only select the columns that need to go over into your data model. If you make a mistake along the way, it’s easy to fix as long…as you are documenting and grouping your steps.
Limit Your Data Scope
Similar to the incremental refresh section takeaway, remember to limit your data scope. As long as you have a subset of the data that is representative, you don’t have to work with a million rows. You can absolutely work with a thousand rows. If you’re confident that those thousand rows represent the entire dataset, that’s really all that you need to create your model.
Create New Content
We covered this earlier in Stage 1…when you are missing data and you need to create content. Remember to make data changes as far upstream as possible and as far downstream as necessary.
Use the UI and Tweak the M Script
Lastly, make use of the user interface to generate your M script as you go along. Once you’re there, use the advanced editor tool. That opens up all of the M for that particular query and you can tweak the M to make it more flexible, fix some tables—whatever adjustments you need.
3. Design for Performance
Slice or Filter
At this visual design phase, consider using the slice or filter. If you’re wondering which one is best, there isn’t one perfect answer.
A slicer is always easier and more intuitive for users. It takes up report canvas space, so keep that in mind. Filters are more powerful and customizable, allowing you to choose and/or, is blank/is not blank, between, etc. The downside for filters is that the user has to be comfortable working with that filter pane and understand how to navigate it.
Consider the User
Plan ahead to make records accessible and think about what the user is focusing on.
Is there any specific data that they use more often? Set up a bookmark for each data preference and link these preferences to a button to further simplify the user experience. That way users don’t even have to open up the filter pane. They just click the button to see the data.
Remember You’re Not a Painter
Consider creating a Power BI template file where it’s easy to pick colors, fonts, backgrounds, and so on to prevent visual chaos in the reports. People, including you, don’t have time to make design choices. Make the process as standardized and seamless as possible.
Keep Maintenance in Mind
Don’t ignore the maintenance of critical reports in your system. If and when you work with outside developers who have the Power BI skillsets you lack in-house, make sure that any report generated is also easy to maintain. Hold a couple of training sessions with internal teams so everyone understands how to maintain these reports.
Preview with Care
Power BI is being refreshed and updated all the time, so you will definitely want to preview then decide which preview features you plan to use and how you plan to use them. Microsoft does not stand by these features in support until they are released for general release. The best approach is to avoid using a preview feature that is in production.
Vet Custom Visuals
While Power BI custom visuals are great, they can pose security risks. Think about your policy and which visuals will be approved. Some certified Power BI visuals pose much less of a risk, but you still want to have a policy in place to cover your bases.
Go Data Model Only
Consider creating a report that just has a data model. You don’t necessarily need to develop reports with fancy Power BI visuals. It’s more important to develop a really good data model that people can then use to build their own reports.
4. Determine Your Workflow
Figure Out the Publication Schedule
The best practice for report development is to develop in Power BI Desktop. However, if you develop a record in the Desktop and publish it to the Service, other members might edit your report in the workspace. If you publish another version of that report, it overwrites your changes.
Be clear about:
- Your report publishing cadence.
- How you’re going to control reports.
- Who is responsible for editing reports.
- How and when reports will be edited.
- How you will communicate this process with the organization.
Think About Workspace Purpose
In true Microsoft fashion, Microsoft is giving us dozens of choices with workspaces these days. Be thoughtful about the purpose of your workspaces. Be very conversant with the different layers of membership in each workspace, because that drives who can do what in the workspace. Before you create your next workspace, take a moment to define its purpose.
Consider Apps for Audiences
When you create the workspace, be careful about the audience since your workspace can only have one app.
Let’s say you’re a finance department—you have an app and the target audience for your app is your senior leadership team. You can’t also publish more generic data for a broader audience in that same app. You can only do one thing. So be sure about who that app is meant to serve.
Establish Version Control
Version control is a common challenge with Power BI…it doesn’t do version control natively. Establish version control as an organization and figure out which techniques will work best.
Achieve Golden Datasets
Golden datasets are really the holy grail for everybody at your organization. This is where you publish a data model, then other people use that data model to self-sufficiently develop reports. Golden datasets are very powerful. Start achieving them and you will quickly eliminate a lot of unnecessary work.
Refresh Appropriately
Know what your options are with incremental refresh. Do you need to refresh eight times a day or 38 times a day? Do you need to refresh continuously? Dig deep into user needs and understand business requirements. You might be surprised how you can go with a much less intense refresh schedule.
___________
We hope this detailed Power BI reporting walkthrough was helpful. Our team of Power BI experts is ready to provide more guidance if you need it (contact us here). If you’re interested in Power BI training, sign up for our Bootcamp which covers everything from report building to DAX.
