How to View Azure Billing Data in Azure Synapse or Power BI

Azure Cost Management exports provide raw billing data, but viewing and querying this data effectively requires the right analytics platform. This guide demonstrates how to connect Azure billing data to both Azure Synapse Analytics (for SQL-based analysis) and Power BI (for visual reporting), enabling comprehensive cost analysis workflows for your organization.

Prerequisites

Before connecting billing data, ensure you have:

• Azure Cost Management export configured and running (see related article on troubleshooting exports)
• Azure Storage account with cost export data (CSV or Parquet files)
• Azure Synapse Analytics workspace (for Synapse method) with dedicated SQL pool or serverless SQL pool
• Power BI Desktop installed (for Power BI method)
• Azure AD credentials with appropriate permissions:
  • Cost Management Reader (for API access)
  • Storage Blob Data Reader (for storage account access)
  • Synapse Contributor (for Synapse workspace)
• Basic SQL knowledge (for Synapse queries)
• Basic Power BI knowledge (for Power BI reports)

Method 1: Viewing Data in Azure Synapse Analytics (Dedicated SQL Pool)

Dedicated SQL pools provide enterprise-grade data warehousing for large-scale cost analysis.

Step 1: Create Dedicated SQL Pool

# Create dedicated SQL pool using Azure CLI
az synapse sql pool create \
  --name "CostAnalyticsSQLPool" \
  --workspace-name "YourSynapseWorkspace" \
  --resource-group "YourResourceGroup" \
  --performance-level "DW100c" \
  --tags "Purpose=CostManagement"

Or via Azure Portal:

1. Navigate to Synapse workspace
2. Click SQL pools > + New
3. Name: CostAnalyticsSQLPool
4. Performance level: DW100c (smallest for testing)
5. Click Review + create

Step 2: Create External Data Source

Connect SQL pool to your storage account:

-- Create master key for encryption (one-time setup)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword123!';

-- Create database scoped credential using Managed Identity (recommended)
CREATE DATABASE SCOPED CREDENTIAL ManagedIdentityCredential
WITH IDENTITY = 'Managed Identity';

-- Or using Storage Account Key (less secure)
CREATE DATABASE SCOPED CREDENTIAL StorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'YOUR_SAS_TOKEN_HERE';

-- Create external data source pointing to cost exports
CREATE EXTERNAL DATA SOURCE AzureCostExports
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://costexports@YOUR_STORAGE_ACCOUNT.dfs.core.windows.net',
    CREDENTIAL = ManagedIdentityCredential
);

Step 3: Create External File Format

Define CSV or Parquet format:

-- For CSV exports
CREATE EXTERNAL FILE FORMAT CsvFormat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2,  -- Skip header row
        USE_TYPE_DEFAULT = FALSE,
        ENCODING = 'UTF8'
    )
);

-- For Parquet exports (more efficient)
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

Step 4: Create External Table

Map external files to table schema:

-- Create external table for cost data
CREATE EXTERNAL TABLE ext_AzureCostData
(
    [Date] DATE,
    [ResourceId] NVARCHAR(500),
    [ResourceName] NVARCHAR(200),
    [ResourceGroup] NVARCHAR(200),
    [SubscriptionId] NVARCHAR(100),
    [SubscriptionName] NVARCHAR(200),
    [ServiceName] NVARCHAR(100),
    [ServiceFamily] NVARCHAR(100),
    [Location] NVARCHAR(50),
    [ResourceType] NVARCHAR(100),
    [MeterCategory] NVARCHAR(100),
    [MeterSubCategory] NVARCHAR(100),
    [MeterId] NVARCHAR(100),
    [Quantity] DECIMAL(18,4),
    [UnitOfMeasure] NVARCHAR(50),
    [Cost] DECIMAL(18,2),
    [Currency] NVARCHAR(10),
    [Tags] NVARCHAR(MAX)
)
WITH (
    LOCATION = '/daily/',  -- Folder path within container
    DATA_SOURCE = AzureCostExports,
    FILE_FORMAT = CsvFormat,
    REJECT_TYPE = VALUE,
    REJECT_VALUE = 100  -- Skip up to 100 bad rows
);

Step 5: Query External Table

Now query your cost data:

-- Test: View sample data
SELECT TOP 10 *
FROM ext_AzureCostData
ORDER BY Date DESC;

-- Total cost by service
SELECT
    ServiceName,
    SUM(Cost) AS TotalCost,
    COUNT(*) AS RecordCount
FROM ext_AzureCostData
WHERE Date >= DATEADD(month, -1, GETDATE())
GROUP BY ServiceName
ORDER BY TotalCost DESC;

-- Daily cost trend
SELECT
    Date,
    SUM(Cost) AS DailyCost
FROM ext_AzureCostData
WHERE Date >= DATEADD(day, -30, GETDATE())
GROUP BY Date
ORDER BY Date;

Step 6: Create Materialized View (Performance Optimization)

For frequently accessed aggregations:

-- Create regular table from external data (faster queries)
CREATE TABLE AzureCostData
WITH (
    DISTRIBUTION = HASH(ResourceGroup),
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM ext_AzureCostData
WHERE Date >= DATEADD(year, -1, GETDATE());

-- Create monthly aggregation table
CREATE TABLE AzureCostMonthly
WITH (
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT
    YEAR(Date) AS Year,
    MONTH(Date) AS Month,
    ResourceGroup,
    ServiceName,
    SUM(Cost) AS TotalCost,
    SUM(Quantity) AS TotalQuantity
FROM ext_AzureCostData
GROUP BY
    YEAR(Date),
    MONTH(Date),
    ResourceGroup,
    ServiceName;

-- Create indexes for better performance
CREATE STATISTICS stats_AzureCostData_Date ON AzureCostData(Date);
CREATE STATISTICS stats_AzureCostData_ResourceGroup ON AzureCostData(ResourceGroup);
CREATE STATISTICS stats_AzureCostData_ServiceName ON AzureCostData(ServiceName);

Method 2: Viewing Data in Azure Synapse Analytics (Serverless SQL Pool)

Serverless SQL pools provide on-demand querying without provisioning infrastructure—ideal for ad-hoc analysis.

Step 1: Create Database in Serverless Pool

-- Connect to serverless SQL endpoint (built-in)
-- No provisioning needed!

-- Create database
CREATE DATABASE CostManagement;
GO

USE CostManagement;
GO

Step 2: Create External Data Source (Serverless)

-- Create credential using Managed Identity (recommended)
CREATE CREDENTIAL [https://YOUR_STORAGE_ACCOUNT.dfs.core.windows.net]
WITH IDENTITY = 'Managed Identity';

-- Create external data source
CREATE EXTERNAL DATA SOURCE AzureCostExports
WITH (
    LOCATION = 'https://YOUR_STORAGE_ACCOUNT.dfs.core.windows.net/costexports'
);

Step 3: Query Data Directly with OPENROWSET

No table creation needed—query files directly:

-- Query CSV files directly
SELECT
    *
FROM OPENROWSET(
    BULK 'daily/DailyCostExport/20250101-20250131/*.csv',
    DATA_SOURCE = 'AzureCostExports',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) AS CostData;

-- Query Parquet files (recommended for performance)
SELECT
    *
FROM OPENROWSET(
    BULK 'daily/**/*.parquet',
    DATA_SOURCE = 'AzureCostExports',
    FORMAT = 'PARQUET'
) AS CostData;

Step 4: Create Views for Reusability

-- Create view for easier querying
CREATE VIEW v_AzureCostData
AS
SELECT
    CAST([Date] AS DATE) AS Date,
    ResourceId,
    ResourceName,
    ResourceGroup,
    SubscriptionName,
    ServiceName,
    Location,
    CAST(Cost AS DECIMAL(18,2)) AS Cost,
    CAST(Quantity AS DECIMAL(18,4)) AS Quantity,
    Tags
FROM OPENROWSET(
    BULK 'daily/**/*.csv',
    DATA_SOURCE = 'AzureCostExports',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) WITH (
    [Date] VARCHAR(50),
    ResourceId VARCHAR(500),
    ResourceName VARCHAR(200),
    ResourceGroup VARCHAR(200),
    SubscriptionName VARCHAR(200),
    ServiceName VARCHAR(100),
    Location VARCHAR(50),
    Cost VARCHAR(50),
    Quantity VARCHAR(50),
    Tags NVARCHAR(MAX)
) AS CostData;

Step 5: Query the View

-- Now query the view like a regular table
SELECT
    ServiceName,
    SUM(Cost) AS TotalCost
FROM v_AzureCostData
WHERE Date >= DATEADD(month, -1, GETDATE())
GROUP BY ServiceName
ORDER BY TotalCost DESC;

Method 3: Viewing Data in Power BI (Using Azure Storage)

Step 1: Open Power BI Desktop and Connect to Azure Storage

1. Open Power BI Desktop
2. Click Get Data > More...
3. Search for "Azure Data Lake Storage Gen2" or "Azure Blob Storage"
4. Click Connect

Step 2: Enter Storage Account Details

Azure Data Lake Storage Gen2:

• URL: https://YOUR_STORAGE_ACCOUNT.dfs.core.windows.net/costexports
• Authentication: Azure AD or Account Key

Azure Blob Storage:

• Account name: YOUR_STORAGE_ACCOUNT
• Authentication: Azure AD or Account Key

Step 3: Navigate to Cost Export Folder

1. In the Navigator window, browse to your export folder:

   costexports/daily/DailyCostExport/
   

2. Select the folder containing CSV or Parquet files
3. Click Combine & Transform Data

Step 4: Transform Data in Power Query

Power Query automatically combines files:

let
    Source = AzureStorage.DataLake("https://YOUR_STORAGE_ACCOUNT.dfs.core.windows.net/costexports"),

    // Navigate to export folder
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "daily/DailyCostExport")),

    // Combine all CSV files
    #"Invoke Custom Function" = Table.AddColumn(#"Filtered Rows", "Transform File", each #"Transform File"([Content])),

    // Expand combined data
    #"Expanded Table" = Table.ExpandTableColumn(#"Invoke Custom Function", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

    // Remove helper columns
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table", {"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),

    // Type conversions
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{
        {"Date", type date},
        {"Cost", type number},
        {"Quantity", type number}
    }),

    // Parse Tags JSON
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Department", each
        try Json.Document([Tags])[Department] otherwise "Unassigned"
    )
in
    #"Added Custom"

Step 5: Load Data into Power BI

Click Close & Apply to load data into Power BI model.

Method 4: Viewing Data in Power BI (Using Synapse Connection)

Connect Power BI directly to Synapse for real-time querying.

Step 1: Get Synapse SQL Endpoint

Find your Synapse serverless SQL endpoint:

YOUR_SYNAPSE_WORKSPACE-ondemand.sql.azuresynapse.net

Step 2: Connect Power BI to Synapse

1. Open Power BI Desktop
2. Click Get Data > Azure > Azure Synapse Analytics SQL
3. Enter server name: YOUR_SYNAPSE_WORKSPACE-ondemand.sql.azuresynapse.net
4. Database: CostManagement (or your database name)
5. Data Connectivity mode:
   • Import: Faster, data cached in Power BI
   • DirectQuery: Real-time, queries Synapse on-demand
6. Click OK

Step 3: Authenticate

Choose authentication method:

• Azure AD (recommended)
• Database (SQL authentication)

Step 4: Select Tables or Views

1. In Navigator, select:
   • v_AzureCostData (view created earlier)
   • AzureCostData (table, if created)
2. Click Load

Common SQL Queries for Cost Analysis

Top 10 Most Expensive Resources

SELECT TOP 10
    ResourceName,
    ResourceGroup,
    ServiceName,
    SUM(Cost) AS TotalCost
FROM v_AzureCostData
WHERE Date >= DATEADD(month, -1, GETDATE())
GROUP BY ResourceName, ResourceGroup, ServiceName
ORDER BY TotalCost DESC;

Month-over-Month Cost Comparison

SELECT
    ServiceName,
    SUM(CASE WHEN Date >= DATEADD(month, -1, GETDATE()) AND Date < GETDATE() THEN Cost ELSE 0 END) AS CurrentMonthCost,
    SUM(CASE WHEN Date >= DATEADD(month, -2, GETDATE()) AND Date < DATEADD(month, -1, GETDATE()) THEN Cost ELSE 0 END) AS PreviousMonthCost,
    (SUM(CASE WHEN Date >= DATEADD(month, -1, GETDATE()) THEN Cost ELSE 0 END) -
     SUM(CASE WHEN Date >= DATEADD(month, -2, GETDATE()) AND Date < DATEADD(month, -1, GETDATE()) THEN Cost ELSE 0 END)) AS CostChange
FROM v_AzureCostData
GROUP BY ServiceName
ORDER BY CurrentMonthCost DESC;

Untagged Resources Report

SELECT
    ResourceGroup,
    ResourceName,
    ServiceName,
    SUM(Cost) AS TotalCost
FROM v_AzureCostData
WHERE (Tags IS NULL OR Tags = '' OR Tags = '{}')
  AND Date >= DATEADD(month, -1, GETDATE())
GROUP BY ResourceGroup, ResourceName, ServiceName
ORDER BY TotalCost DESC;

Cost by Location and Service

SELECT
    Location,
    ServiceName,
    SUM(Cost) AS TotalCost,
    COUNT(DISTINCT ResourceId) AS ResourceCount
FROM v_AzureCostData
WHERE Date >= DATEADD(month, -3, GETDATE())
GROUP BY Location, ServiceName
ORDER BY TotalCost DESC;

Daily Running Total

SELECT
    Date,
    SUM(Cost) AS DailyCost,
    SUM(SUM(Cost)) OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM v_AzureCostData
WHERE Date >= DATEADD(month, -1, GETDATE())
GROUP BY Date
ORDER BY Date;

Best Practices

For Azure Synapse Analytics

1. Use Parquet format for exports—50-80% smaller than CSV, faster queries
2. Partition data by date for better performance:

   costexports/year=2025/month=01/day=15/data.parquet
   

3. Create statistics on frequently filtered columns (Date, ResourceGroup, ServiceName)
4. Use dedicated SQL pool for production workloads (>1TB data)
5. Use serverless SQL pool for ad-hoc analysis and exploration
6. Implement incremental load to append only new data:

   INSERT INTO AzureCostData
   SELECT * FROM ext_AzureCostData
   WHERE Date > (SELECT MAX(Date) FROM AzureCostData);
   

For Power BI

1. Use DirectQuery for real-time data (connects to Synapse)
2. Use Import mode for better performance (caches data in Power BI)
3. Implement incremental refresh for large datasets (>1 million rows)
4. Filter early in Power Query to reduce data volume
5. Create aggregation tables for common queries
6. Avoid high-cardinality columns in visuals (ResourceId, SubscriptionId)

General Best Practices

1. Schedule data refresh to run after Cost Management export completes
2. Monitor query performance and optimize slow queries
3. Document your data model and schema
4. Test with small date ranges before running large queries
5. Use views to encapsulate complex logic

Troubleshooting

Issue: "Cannot bulk load because the file could not be opened"

Solutions:

• Verify storage account name and container path
• Check firewall settings allow Synapse access
• Ensure managed identity has Storage Blob Data Reader role
• Verify file path is correct (case-sensitive)

Issue: Power BI Can't Find Files

Solutions:

• Check container and folder path in connection string
• Verify authentication method (Azure AD vs. Account Key)
• Ensure files exist in storage account
• Try navigating manually in Navigator window

Issue: Queries Are Slow

Solutions:

• Use Parquet instead of CSV
• Create filtered views for common date ranges
• Implement partitioning on external tables
• Use dedicated SQL pool instead of serverless for large datasets
• Add indexes and statistics to tables

Issue: Data Types Are Wrong

Solutions:

• Specify schema explicitly in OPENROWSET WITH clause
• Transform data types in Power Query
• Check CSV delimiter and encoding settings
• Verify FIRST_ROW setting skips header

Next Steps

After connecting to billing data:

1. Create dashboards to visualize costs (see related article on visualizing billing data)
2. Implement row-level security for multi-tenant access (see related RLS article)
3. Set up automated reports delivered via email
4. Create cost alerts based on thresholds
5. Build predictive models to forecast future costs
6. Integrate with budgeting systems for variance analysis

Related Resources

• How to Troubleshoot Azure Cost Management Export Issues
• How to Visualize Azure Billing Data in Power BI
• How to Set Up Row-Level Security for Billing Data in Azure Synapse or Power BI
• Microsoft Documentation: Query data in Azure Data Lake using Synapse SQL
• Microsoft Documentation: Connect to Azure Data Lake Storage in Power BI