Acumatica Power BI Data Models: Faster Refresh, Smaller Files

Building an efficient Power BI data model starts at the source. When pulling data from Acumatica via OData reports, most teams import everything and clean it up in Power BI. But that approach creates bloated models, slower refreshes, and unnecessary storage. The smart move? Optimize at the source.

The Core Strategy: Select at Source, Not After Import

The biggest performance mistake is importing all columns from an Acumatica OData report and then removing unused ones in Power BI. Here’s why:

Every column imported = storage overhead in your Power BI model
Every column refreshed = slower refresh cycles (even if hidden)
Unnecessary columns = larger file size and longer load times for users
More clutter = harder to maintain your data model long-term

The Fix: In Acumatica, configure your OData report to expose only the columns you actually need for your dashboard or analysis. This filters at the database level before Power BI touches the data.

Key Optimization Tactics

1. Datetime → Date Conversion (At Source)

Datetime columns from Acumatica often include timestamps you don’t need.

In Acumatica OData: Use date-only fields where time precision isn’t required (most reporting doesn’t need it)
In Power BI: If you must bring in datetime, convert to date in Power Query before loading to the model
Why: Datetime columns consume more storage and complicate DAX calculations. Date-only is cleaner for P&L, budgets, and project timelines

Pro tip: Keep one datetime column for audit trails if needed, but separate date/time into different columns or use the date version for analysis.

2. Join Tables in Acumatica vs. Merge in Power BI: Which Approach?

When you need data from multiple Acumatica tables (e.g., Projects + Tasks, or PO Headers + Lines), you have three options:

Option A: Join in Acumatica

Create a single OData report that joins tables at the Acumatica level
Import one flattened table into Power BI
Pros: Smaller import, simpler Power Query, faster refresh
Cons: Denormalized data, potential duplication (if one-to-many relationship), harder to reuse in other reports
Best for: One-time analysis, simple fact tables, aggregated summaries

Option B: Merge in Power Query (Power BI)

Import separate OData reports (Projects, Tasks, Transactions)
Merge/join them in Power Query before loading
Pros: Keeps data structured, easier to debug, can reuse individual tables
Cons: Merge operations slow down refresh, increases complexity in Power Query
Best for: Medium complexity, when you need flexibility

Option C: Both Tables + Relationships (Recommended)

Import both tables separately (Projects as dimension, Transactions as fact)
Create a relationship in Power BI’s data model (Project ID → Project ID)
Use relationships in DAX instead of pre-joining
Pros: Most efficient storage, fastest refresh, reusable dimension, easy to modify relationships, supports star schema
Cons: Requires understanding of relationships and DAX
Best for: Professional BI models, scalability, multiple reports

Recommendation: Use Option C (Both + Relationships) for any serious Power BI model. It’s the most scalable and aligns with BI best practices.

3. Organize as Dimension and Fact Tables

Structure your data model with clear dimension and fact tables:

Dimension Tables (lookup/reference data):
- Projects, Employees, Customers, Cost Codes, Accounts
- Contains descriptive attributes
- Import once, link to multiple fact tables
- Example: Project dimension has ProjectID, ProjectName, Status, Manager, StartDate
Fact Tables (transaction/metric data):
- General Ledger entries, Purchase Orders, Invoices, Timesheets, Budgets
- Contains IDs (links to dimensions) + numeric values (amounts, quantities)
- Example: GL fact table has Date, ProjectID, AccountID, Amount, CostCodeID

Why this matters:

✅ Dimensions are reusable across multiple fact tables
✅ Smaller storage (one Project table, not repeated in every transaction)
✅ Easier to maintain and update attributes
✅ DAX calculations become simpler and faster
✅ Prevents confusion about which table to use in formulas

Example Star Schema:

Projects (Dimension)
  ├─ ProjectID → Transactions (Fact)
  ├─ ProjectID → Budget (Fact)
  └─ ProjectID → Timeline (Fact)

Accounts (Dimension)
  └─ AccountID → Transactions (Fact)

Employees (Dimension)
  └─ EmployeeID → Transactions (Fact)

4. Flatten Hierarchies in Acumatica, Not Power BI

Acumatica hierarchies (Cost Codes, Account Groups, Projects) can be structured in the OData report itself.

Don’t import flat lists and try to rebuild hierarchy logic in DAX
Configure the OData report to return parent/child relationships cleanly
Let Acumatica do the structural work; Power BI handles the analysis

5. Remove Redundant Keys Early

If your OData report includes both ProjectID and ProjectName, decide which you need:

Keep the name if users filter/sort by it
Keep the ID only if linking to other tables
Don’t import both if one is sufficient

Example: For a P&L dashboard by project, you likely want ProjectName only—no need for the ID in the visual layer.

6. Filter Out Irrelevant Data at the Source

Use Acumatica OData filtering to exclude:

Inactive records (status = “Inactive”)
Deleted/archived transactions
Test/sample data
Future-dated records you’re not analyzing yet

This reduces model size before import, not after.

7. Standardize Column Names in Acumatica

Before importing, ensure Acumatica OData column names are:

Clear and consistent (e.g., TransactionDate, not TxnDt)
Free of special characters
Aligned with your Power BI naming conventions

You’ll spend less time renaming in Power Query and fewer errors downstream.

8. Use Aggregated/Summary Reports When Possible

If your dashboard needs monthly summaries, not daily detail:

Ask Acumatica to expose a summary OData report (by month, by cost code, by project)
Import pre-aggregated data instead of detail + aggregate in DAX
Result: Smaller model, faster refresh, simpler DAX

When to do this: Budget vs. actual reports, department roll-ups, executive dashboards.

9. Exclude Calculated Fields You Can Build in Power BI

Acumatica sometimes includes pre-calculated fields (like totals or percentages).

Import only base transaction data
Build calculations in Power BI where you control logic and can reuse measures across visuals
Exception: If Acumatica calculates something complex (earned revenue, WIP), import it and validate against your DAX

10. Limit Historical Data Scope

Don’t import 10 years of transactions if you only report on 2 years.

Configure the OData report to filter by date range
Refresh strategy: Keep current year + prior year, archive older periods
Benefit: Faster refreshes, smaller backups, easier troubleshooting

Implementation Checklist

Before creating your OData connection in Power BI:

☐ List every column you need (don’t guess)
☐ Confirm Acumatica OData report includes only those columns
☐ Verify datetime columns are converted to date-only format
☐ Decide on join strategy: Acumatica join, Power Query merge, or relationships?
☐ Plan dimension tables (Projects, Accounts, Employees, etc.) separate from fact tables
☐ Test the OData URL with sample data
☐ Check row count—does it match your expected scope?
☐ Document the column definitions (what each field means)
☐ Set up a refresh schedule that doesn’t conflict with Acumatica batch jobs

The Result

A lean, optimized data model means:

✅ Faster refresh times (fewer columns, less data transferred)
✅ Smaller Power BI file size (easier to share, cheaper licensing over time)
✅ Cleaner DAX (fewer hidden columns to work around)
✅ Better user experience (snappier slicers, faster drill-throughs)
✅ Easier maintenance (you know exactly what data you have and why)
✅ Reusable dimensions (one Employee table used across all fact tables)
✅ Scalable architecture (add new fact tables without duplicating dimensions)

Summary

The golden rule: Optimize at the source, not after import. Work with your Acumatica OData reports to surface only what you need, in the format you need it. Build a star schema with separate dimensions and facts, linked by relationships. Your Power BI model—and your refresh times—will thank you.