Financial Systems

Power BI Tips and Best Practices

Power BI is an extremely powerful data transformation and reporting tool with solid modeling capabilities via tools such as DAX and Python/Panadas. Besides connecting a litany of Microsoft products (e.g., SQL Server, Azure, SharePoint, Excel), it can also connect to many other data sources including Salesforce, Anaplan, Postgres, Oracle and SAP. This in-house ability to access diverse data sources puts a strong tool in the analyst’s tool kit. The ability to access, transform and visualize large amounts of data does present some important best practices for long-term success. In this article, let’s review a few Power BI tips that can help you leverage the tool effectively.

Optimize Your Data Warehousing

The first of our Power BI tips is the actual condition of the “data warehouse,” whether it be a relational, multi-dimensional or other data structure (e.g., Big Data). To optimize Power BI, the data at the source should be cleansed, transformed and consolidated as much as possible in the source system. This doesn’t necessarily mean every table or cube should be perfect, but they should be in the best shape possible.

This optimization may translate into building special tables, objects or views in the source that result in cleaned, consolidated and transformed data. The importance of this cannot be understated: cleaning and transforming large data sets can slow and even stop Power BI processing.

Build Common Data Tables and Structures

The second of our Power BI tips would be moving Power BI calculations that occur in multiple dashboards, reports or datasets into a common data table or structure. These calculations should then be accessed in one location by all Power BI resources—versus each resource running the calculation(s) locally.

This effort will free up Power BI computing resources, making page loads faster and, more importantly, ensuring that all Power BI resources are using the same formula. Often when the same formula is used across multiple Power BI resources, changes in one are not always carried over to all resources using the formulas. Also, considerable time can be spent managing formulas across resources. Moving the formulas to a common data structure eliminates these issues.

Identify Necessary Data Granularity

With the ability to work with large amounts of data, one challenge that occurs is pulling only on the amount of data required versus everything available. Power BI resources can be overcome with too much data which results in slow performance and even the resource “freezing.”

Which brings up our third Power BI tip: One way to manage the amount of data pulled into a resource is to determine the correct amount of granularity required to support a visual. For example, if a visual requires analysis on a low level of granularity (e.g., daily), can that analysis be performed in the data warehouse and the Power BI resource pull only required elements? Another option is to create aggregated data tables/Objects in the source by omitting columns not required by the Power BI resource.

Implement an Oversight Process

Last tip: When a company has many Power BI resources (e.g., Dashboards, Reports, Data sets) in production, it is important to have an oversight process. This ensures consistency across Dashboards and Reports—both in terms of data and presentation when needed. For example, the process would ensure similar formulas are properly managed across resources.

An Oversight Process also helps manage the resource landscape by minimizing the occurrence of duplicate resources, supporting future development, and simplifying general management of the Power BI landscape (e.g., Security, Development Flow, etc.). Setting up a process can be implemented by having periodic meetings which include Power BI resource developers, project managers and data warehouse engineers and cover the current and future reporting landscape and how the data warehouse/sources play a role.

Learn More

Employing these Power BI tips will result in more streamlined resources that run faster, are easier to manage and can provide better insight into the data analyzed. That noted, many companies don’t have the internal bandwidth to implement these best practices. If you’d like support on your data transformation and reporting efforts, then consider contacting us. Our Accounting and Finance consulting team can deploy to support or drive key projects or serve in an interim management capacity if needed.

If you’d like to learn more about generating internal momentum for operational objectives, then you might be interested in our guide:

operational review program ebook

About the Author

As a finance executive with 20+ years of experience in finance and solid experience in partnering with departments and divisions, Devon has used financial analysis/modeling, negotiation, communication and software development skills to help companies and divisions achieve various business goals. Prior to joining 8020 Consulting, Devon was Executive Director of Warner Home Entertainment’s Acquisitions group overseeing title acquisitions and providing financial support for film productions. Devon has also held high-profile positions at Disney and CUNA Mutual Group in a finance and software development capacity. He has an MBA in Finance and International Business from UW-Madison and a B.S. in Finance and Accounting from UNC-Greensboro.

Categorized in: ,

similar articles

Learn to think and approach problems like our financial consultants.

Financial Planning & Analysis

Avoiding Common Pitfalls in Your Annual Planning Cycle

A proper financial planning and analysis process results in faster decision making, the mitigation of revenue and cost risks and performance optimization for the entire organization. Upon entering an annual planning cycle, it is critical not to take for granted the obvious questions that can provide key insights to financial assumptions in your model. And more… View Article

November 16, 2022Casandra del Carmen

Financial Systems

IBM Cognos TM1 vs. Planning Analytics: A Guide to the Transition & New Features

Planning Analytics is the new buzz word for organizations making the change to the latest version of Cognos TM1, but what is the buzz all about? The answer starts with the name: Planning Analytics, or “PA,” is the new name for TM1. In terms of the differences between IBM Cognos TM1 vs. Planning Analytics, much… View Article

November 9, 2022Kieran Chatulani

Business Stabilization

A Helpful Business Continuity Plan Checklist

For strategic business planners, COVID provided many lessons as the ultimate stress test, bringing issues seemingly from all angles. The scale and length of the disruptions likely stretched many a business continuity plan beyond their limits in a very short amount of time. It is imperative that companies redefine the breadth of their planning to… View Article

October 26, 2022Jim Hliboki

See All

Back to Insights