1. Data Modeling: Create a Star Diagram
When your data is modeled in a star schema format, Power Pivot works at its best.
Transactional data, such as sales, orders, or inventory changes, are contained in fact tables.
Dimension tables: Include details about customers, products, and dates.
Steer clear of:
Normalized relationships between dimensions are known as snowflake schemas.
Relationships between many people when they can be made simpler.
Why? Because star schemas make joins simpler and boost the efficiency of Power Pivot's and Power BI's VertiPaq storage engine.
2. Lower the Volume of Data Early
Eliminate superfluous data as soon as you can, preferably at the data source or in Power Query.
Eliminate unnecessary rows, such as test or archived data.
Eliminate any columns that aren't utilized in any filters, visuals, or measures.
3. Optimize Columns and Data Types
- Use numeric or integer columns instead of text wherever possible.
- Replace repeated text values (e.g., "New York," "California") with numeric surrogate keys.
- Avoid high-cardinality columns (e.g., GUIDs or timestamps with milliseconds).
- Don't include unnecessary long text fields like descriptions or notes.
Why: Fewer unique values per column improve compression efficiency and reduce memory usage.
4. Prefer Measures Over Calculated Columns
- Use measures for all calculations that can be done at query time (like sums, averages, counts).
- Avoid calculated columns unless you need them to establish relationships or define keys.
Why: Measures are evaluated only when needed, while calculated columns are stored in memory and can increase the data model size significantly.
5. Use Aggregations
Where appropriate, use pre-aggregated data or summary tables for performance.
- Pre-group data in Power Query or at the source (e.g., monthly totals).
- Create summary tables that support high-level dashboards without querying full-detail tables.
6. Optimize DAX Calculations
- Avoid complex nested CALCULATE or FILTER unless necessary.
- Use variables (VAR) to store results and reuse them within a measure.
- Use iterators (SUMX, AVERAGEX) only when required.
- Optimizing functions like DISTINCT COUNT over manual row counts is preferred.
Why: Efficient DAX leads to faster visuals and better memory usage.
7. Improve Compression and Storage with VertiPaq Analyzer
Use DAX Studio and VertiPaq Analyzer to examine your data model.
- Check for large tables or uncompressed columns.
- Identify columns with high cardinality or poor compression.
- Reorder data or split date/time fields if necessary.
Tips for better compression:
- Sort tables before loading.
- Convert long text fields to category codes if needed.
- Use consistent formatting for dates and numbers.
8. Relationship Optimization
- Use single-column relationships wherever possible.
- Ensure dimension tables are smaller and well-indexed.
- Avoid bi-directional relationships unless absolutely needed (they increase model complexity and performance cost).
9. Manage Data Refreshes
- Schedule data refreshes during low-usage hours.
- Use incremental refresh for large tables to avoid full reloads.
- Prefer query folding in Power Query so that the source database handles transformations.
