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 sourcesThe challenges of data quality, storage, validation, and disparate sources are real, but these 8 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 analysisInstead 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.