Connecting Power BI to Azure SQL
With your Dataverse data synchronized to Azure SQL, you can connect Power BI directly to your database for fast, efficient reporting without impacting your Dataverse environment.
To connect Power BI Desktop to your Azure SQL database:
1. Open Power BI Desktop and select Get Data → Azure → Azure SQL Database
2. Enter your Server name (e.g., yourserver.database.windows.net)
3. Enter your Database name
4. Choose your authentication method (Microsoft account or Database credentials)
5. Select the tables you want to import or use DirectQuery
Import vs DirectQuery
Power BI offers two connection modes:
| Mode | Description |
| Import | Data is loaded into Power BI's in-memory engine. Best for smaller datasets, complex calculations, and offline access. Refresh manually or on schedule. |
| DirectQuery | Queries run directly against Azure SQL in real-time. Best for large datasets and when you need the latest data. Requires always-on database connection. |
| 💡 Tip: Start with Import mode for development and testing. Switch to DirectQuery if you need real-time data or your dataset exceeds Power BI's memory limits. | |
Filtering Deleted Records
If you're using Soft Delete mode, remember to filter out deleted records in your Power BI queries:
• In Power Query: Filter the IsDeleted column to show only 'FALSE' values
• In DAX: Use FILTER(Table, Table[IsDeleted] = FALSE)
• In SQL (DirectQuery): Add WHERE IsDeleted = 0 to your custom queries
Using Operational Columns
The operational columns added by Data Mission Sync are valuable for Power BI reporting:
• _updated_utc: Create 'Last Refreshed' indicators or filter to recently changed records
• _created_utc: Track when records first appeared in your reporting database
• _deleted_utc: Analyze deletion patterns or create 'Recently Deleted' reports
Sample Queries
Active accounts created this month:
SELECT * FROM dbo.account WHERE IsDeleted = 0 AND _created_utc >= DATEADD(month, DATEDIFF(month, 0, GETUTCDATE()), 0)
Records updated in the last 24 hours:
SELECT * FROM dbo.contact WHERE _updated_utc >= DATEADD(hour, -24, GETUTCDATE())
Best Practices
• Schedule Power BI refreshes to run after your Data Mission Sync schedule for consistent data
• Use the _label columns for Option Sets in your reports for human-readable values
• Create a date dimension table and join on _created_utc or _updated_utc for time-based analysis
• Consider creating views in Azure SQL to pre-filter deleted records and simplify Power BI models