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 Reporting & Accounting

A Brief Primer on Financial Data Management and Reporting

During my recent engagement with a leading management consultancy firm, I discovered an issue that is quite common among complex organizations: over-customization in financial data management, data tagging and financial report creation. This over-customization often manifests itself in financial reporting views that ultimately fail to correspond to GL data hierarchies. The issue is exacerbated by… View Article

March 15, 2023Chris Routhe

Business Advisory

A Look at Team Assessment Projects

As 8020 Consultants, we focus on issues that produce provable results measured by an increase in our Clients’ bottom line. While financial project execution and interim financial management are our bread and butter, sometimes, operational improvement goals bring our attention to the softer side of a corporation – its people. In the cases where the… View Article

March 8, 2023Ellen Vayner

Financial Planning & Analysis

Finance and Operations: The Path to Strategic Alignment

When thinking of Finance and Operations, envision two sides of the same coin. Operations executes the company mission and strategic goals while consistently adapting to ever-changing global dynamics. Finance assures resources are allocated properly to these same strategic goals, while working as the gatekeepers of the organization’s financial health. These two departments are more interrelated… View Article

March 1, 2023Romesh Jayawardene

See All

Back to Insights