📊 How to Split Financial Dimensions into Columns Using SQL in D365 F&O

In Microsoft Dynamics 365 Finance and Operations, financial dimensions are stored in a compressed form using fields like LedgerDimension and DefaultDimension. While this is great for system efficiency, it’s not very human-readable—especially when you're running queries or building reports directly from the database.

In this article, I'll show you how to split LedgerDimension and DefaultDimension into individual columns using SQL, so that each dimension (like CostCenter, ItemGroup, etc.) appears in its own field. This is very useful when building reports, integrating with Power BI, or analyzing data directly from SQL Server.

🧩 1. Split Ledger Dimension into Individual Columns

Use this query when you want to extract Main Account + Dimensions (i.e., full ledger dimension combination used in transactions):

SELECT
    Dimension,
    MainAccount,
    Claims,
    Functions,
    ItemGroup,
    CostCenter,
    Market_IC,
    MTO,
    Projects,
    Vendor,
    Agreement
FROM
(
    SELECT
        DAVC.RecId AS Dimension,
        DA.Name,
        DALVAV.DisplayValue
    FROM
        DimensionAttributeValueCombination AS DAVC
        JOIN DimensionAttributeLevelValueAllView AS DALVAV
            ON DALVAV.VALUECOMBINATIONRECID = DAVC.RecId
        JOIN DimensionAttribute AS DA
            ON DA.RecId = DALVAV.DIMENSIONATTRIBUTE
    WHERE
        DAVC.MainAccount != 0
) AS SourceData
PIVOT
(
    MAX(DisplayValue)
    FOR Name IN (
        MainAccount, Claims, Functions, ItemGroup, CostCenter,
        Market_IC, MTO, Projects, Vendor, Agreement
    )
) AS PivotTable;

 🔍 Note: Replace the dimension names in the IN (...) clause with the actual dimension names configured in your environment.

📁 2. Split Default Dimension into Individual Columns

Use this query when you're dealing with DefaultDimension (used in master records like customers, vendors, items, etc.).

SELECT
    DefaultDimension,
    Claims,
    Functions,
    ItemGroup,
    CostCenter,
    Market_IC,
    MTO,
    Projects,
    Vendor,
    Agreement
FROM
(
    SELECT
        DAVC.RecId AS DefaultDimension,
        DA.Name,
        DAVSI.DisplayValue
    FROM
        DimensionAttributeValueCombination AS DAVC
        JOIN DimensionAttributeValueSet AS DAVS
            ON DAVS.RecId = DAVC.RecId
        JOIN DimensionAttributeValueSetItem AS DAVSI
            ON DAVSI.DIMENSIONATTRIBUTEVALUESET = DAVS.RecID
        JOIN DimensionAttributeValue AS DAV
            ON DAV.RecId = DAVSI.DIMENSIONATTRIBUTEVALUE
        JOIN DimensionAttribute AS DA
            ON DA.RecId = DAV.DIMENSIONATTRIBUTE
    WHERE
        DAVC.MainAccount = 0
) AS SourceData
PIVOT
(
    MAX(DisplayValue)
    FOR Name IN (
        Claims, Functions, ItemGroup, CostCenter,
        Market_IC, MTO, Projects, Vendor, Agreement
    )
) AS PivotTable;
 

💡 Use Cases

• Exporting transaction data for reporting

• Creating simplified views for Power BI

• Debugging the ledger and default dimensions

• Building a staging table for the data warehouse

✅ Tips

• These queries are meant for read-only reporting purposes.

• Use them in your Data Lake, BYOD, or on-prem reporting environments.

• Ensure dimension names match your environment’s configuration.

🔗 Conclusion

Splitting LedgerDimension and DefaultDimension into readable columns using SQL is a powerful technique to simplify financial data analysis. This is especially helpful for technical consultants, BI developers, and finance teams working on reporting, integrations, or validations.

Let me know in the comments if you'd like a downloadable view or Power BI model version of this!

Happy coding! 💻

🔍 Stay tuned for more D365FO technical posts

📌 Bookmark: https://d365fohunt.blogspot.com