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 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 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 memberto 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.
