Microsoft® Power BI™ best practices: Data modeling and sources

By Garrett Becker

Do you struggle with data modeling and data sources? Our team of certified Microsoft Power BI analysts have been working with our customers to leverage the end-to-end platform to create powerful insights into their business. Through our work, we have identified several best practices to help you on your journey in building a data-driven culture through business intelligence.

This article is focused on data modeling and data sources. The challenges of data quality, storage, validation, and disparate sources are real, but these eight best practices can help elevate your work with this powerful tool.

1. Use web-based data sources

• Eliminate your need for connecting to raw files containing ad hoc analysis.  Instead pull directly from your raw data sources such as QuickBooks®, Salesforce®, or a traditional web Application Programming Interface (API)
• Power BI has strong back-end Extract Transform Load (ETL) capabilities to clean and organize your data as needed
• This will help secure your data for better long-term data strategy

2. Avoid connecting to local file systems for long-term data strategy

• Connecting to local files as your data source is good for quick development, but we find it better to connect to web-based sources for long-term strategy and security
• Web based sources allow you to quickly share Power BI desktop files with other users and helps improve background query functionality
• Local files may be subject to increased security risks

3. Reduce data model size

• Optimize overall file performance including loading visuals, running Data Analysis Expressions (DAX) formulas, and slicing data
• In Power Query™, a data transformation and data preparation engine within Power BI, you can remove unneeded columns and rows, duplicate records and verify data types
• You can also enable and disable load of queries into the model; this helps save on file size and improve performance

4. Avoid using too many DAX calculated columns

• DAX calculated columns take longer to refresh and load during updates
• Making all key data transformations in the Power Query editor will allow steps to load faster upon refresh

5. Limit bi-directional and many-to-many query connections in your data model

• These query connections tend to check more data points and can cause slowdowns to reporting performance
• Try using one-to-many or many-to-one query connections with filtering in a single direction to improve reporting performance

6. Normalize data with a star schema (illustrated below)

• Making connections from fact tables that contain foreign keys to dimension tables improves efficiency and performance
• This schema allows other users to quickly decipher data query connections and identify how the model works together
• This schema decreases data redundancy

7. Utilize "Active" and "Inactive" relationship types

• You can mark relationships between tables as Active or Inactive, this allows you to make multiple connections between two tables
• This functionality provides increased flexibility when writing DAX formulas; you can specify which relationship you want to use when calculating values

8. Create a standard date table

• You can easily add in manual data tables, including a standard date table for your business needs
• This can include columns such as month, day, year, quarter, day of week, end of month (EOM), etc.
• Power BI provides standard slicer fields for viewing and analyzing your data

We Can Help

Contact a team member to learn more about our data analytics services.

The information provided in this communication is of a general nature and should not be considered professional advice. You should not act upon the information provided without obtaining specific professional advice. The information above is subject to change.