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