Dynamics Community Forum Thread Details

I need to develop a report where I need data with multiple dimensions.

Now if I try to get all data during the given period and then check the dimensions data to verify required dimension it will compromise performance.

Please help how I can get data with multiple dimensions quickly and processed quickly.

The required dimensions are
Project
Project Competency
Business Unit
Export Unit
Let me explain my problem in detail,with this query.This query is working fine with single dimension(Like given in the result below).

select generalJournalEntry.AccountingDate, DimensionAttribute.Name, DimensionAttributeLevelValue.DisplayValue , GENERALJOURNALENTRY.LEDGER,generalJournalAccountEntry.RECID, generalJournalAccountEntry.MAINACCOUNT, GeneralJournalEntry.SUBLEDGERVOUCHER, generalJournalAccountEntry.POSTINGTYPE, generalJournalEntry.ACCOUNTINGDATE,generalJournalAccountEntry.ReportingCurrencyAmount
FROM generalJournalAccountEntry
inner join generalJournalEntry on generalJournalEntry.RecId = generalJournalAccountEntry.GeneralJournalEntry
inner join dimensionAttributeValueCombination on DimensionAttributeValueCombination.RecId = generalJournalAccountEntry.LedgerDimension
inner join dimensionAttributeValueGroupCombination on DimensionAttributeValueGroupCombination.DimensionAttributeValueCombination = DimensionAttributeValueCombination.RecId
inner join dimensionAttributeValueGroup on DimensionAttributeValueGroup.recid = DimensionAttributeValueGroupCombination.DimensionAttributeValueGroup
inner join dimensionAttributeLevelValue on DimensionAttributeLevelValue.DimensionAttributeValueGroup = DimensionAttributeValueGroup.RecId
inner join dimensionAttributeValue on DimensionAttributeLevelValue.DimensionAttributeValue = DimensionAttributeValue.RecId
inner join dimensionAttribute on DimensionAttribute.recid = DimensionAttributeValue.DimensionAttribute
where GENERALJOURNALENTRY.LEDGER=5637145326 and DimensionAttribute.Name in ('Project')
and GENERALJOURNALENTRY.ACCOUNTINGDATE between '2022/01/01' and '2022/01/01'
order by GeneralJournalEntry.SUBLEDGERVOUCHER

Result (this is the result with one dimention which is project, this data is correct).But when we are working with multiple dimensions like in the second query it returns separte rec for each dimension and we need to process data horizantally,which requires a lot of time to process.

AccountingDate    Name    DisplayValue    LEDGER    RECID    MAINACCOUNT    SUBLEDGERVOUCHER    POSTINGTYPE    ACCOUNTINGDATE    ReportingCurrencyAmount
2022-01-01 00:00:00.000    Project    501-001    5637145326    5642727628    5637144823    FTV-000558    126    2022-01-01 00:00:00.000    558.500000
2022-01-01 00:00:00.000    Project    115-017    5637145326    5642741908    5637144714    FTV-000559    126    2022-01-01 00:00:00.000    9437.000000
2022-01-01 00:00:00.000    Project    267-002    5637145326    5642889170    5637144823    FTV-000565    126    2022-01-01 00:00:00.000    385.000000

query2
select generalJournalEntry.AccountingDate, DimensionAttribute.Name, DimensionAttributeLevelValue.DisplayValue , GENERALJOURNALENTRY.LEDGER,generalJournalAccountEntry.RECID, generalJournalAccountEntry.MAINACCOUNT, GeneralJournalEntry.SUBLEDGERVOUCHER, generalJournalAccountEntry.POSTINGTYPE, generalJournalEntry.ACCOUNTINGDATE,generalJournalAccountEntry.ReportingCurrencyAmount FROM generalJournalAccountEntry inner join generalJournalEntry on generalJournalEntry.RecId = generalJournalAccountEntry.GeneralJournalEntry
inner join dimensionAttributeValueCombination on DimensionAttributeValueCombination.RecId = generalJournalAccountEntry.LedgerDimension
inner join dimensionAttributeValueGroupCombination on DimensionAttributeValueGroupCombination.DimensionAttributeValueCombination = DimensionAttributeValueCombination.RecId
inner join dimensionAttributeValueGroup on DimensionAttributeValueGroup.recid = DimensionAttributeValueGroupCombination.DimensionAttributeValueGroup
inner join dimensionAttributeLevelValue on DimensionAttributeLevelValue.DimensionAttributeValueGroup = DimensionAttributeValueGroup.RecId
inner join dimensionAttributeValue on DimensionAttributeLevelValue.DimensionAttributeValue = DimensionAttributeValue.RecId
inner join dimensionAttribute on DimensionAttribute.recid = DimensionAttributeValue.DimensionAttribute
where GENERALJOURNALENTRY.LEDGER=5637145326 and DimensionAttribute.Name in ('Project','ProjectCompetency')
and GENERALJOURNALENTRY.ACCOUNTINGDATE between '2022/01/01' and '2022/01/01'
order by GeneralJournalEntry.SUBLEDGERVOUCHER

Result:
Result (this is the result with two dimention which is project and ProjectCompetency, this data is not correct, and data going to duplicate here)
AccountingDate    Name    DisplayValue                LEDGER    RECID    MAINACCOUNT    SUBLEDGERVOUCHER    POSTINGTYPE    ACCOUNTINGDATE    ReportingCurrencyAmount
2022-01-01 00:00:00.000    Project                    501-001   5637145326    5642727628    5637144823    FTV-000558    126    2022-01-01 00:00:00.000    558.500000
2022-01-01 00:00:00.000    ProjectCompetency    C12      5637145326    5642727628    5637144823    FTV-000558    126    2022-01-01 00:00:00.000    558.500000
2022-01-01 00:00:00.000    Project                    115-017   5637145326    5642741908    5637144714    FTV-000559    126    2022-01-01 00:00:00.000    9437.000000
2022-01-01 00:00:00.000    ProjectCompetency    C11      5637145326    5642741908    5637144714    FTV-000559    126    2022-01-01 00:00:00.000    9437.000000
2022-01-01 00:00:00.000    ProjectCompetency    C16      5637145326    5642889170    5637144823    FTV-000565    126    2022-01-01 00:00:00.000    385.000000
2022-01-01 00:00:00.000    Project                    267-002   5637145326    5642889170    5637144823    FTV-000565    126    2022-01-01 00:00:00.000    385.000000a