Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Tammie Boer]

Hi everyone,

We are trying to build some financial reports in Power BI but cannot find/access the Financial Dimensions.  Has anyone been able to link to this information in Power BI?

Thank you!

——————————
Tammie Viot
Business Architect
Command Alkon Incorporated
Birmingham AL
——————————

[Michelle Steffen]

?Following – we have the same issue when trying to query financial dimensions out of Atlas.  I’ve heard it may be a configuration issue, but I’m curious.

——————————
Michelle Steffen
Corporate Controller
Jasper Engines
Jasper IN
——————————
——————————————-

[Milinda Vitharana]

If you are using Dyn365 F&O Entity store to integrate with PowerBI, Financial dimensions are added during refresh. Entity store schema adds Financial dimensions as additional columns in to the table. See the following article that explains how to add Financial dimensions into your aggregate measurements (ie. star schema in Entity store). These are the steps you would follow if you are using Entity store.

1. Include aggregate dimensions references in Aggregate measurements (see article below) – this is a developer step
2. refresh Entity store and create PowerBI reports (a power user can do this)
3. upload reports into LCS and deploy to sandbox or production environments
4. You can either deploy the reports to PowerBI.com or create an Analytical workspace with embedded PowerBI
5. If you create additional Financial dimensions later, Each refresh of Entity store expands them. you don’t need to repeat steps 1..3

It’s important to keep in mind that Financial dimensions are “configuration data”. So for an example, in your production environment, if you have 3 Financial dimensions, you need to define the same 3 in your developer environment.  

Add financial dimensions to aggregate measurements – Finance & Operations | Dynamics 365 | #MSDyn365FO

Microsoft		remove preview
Add financial dimensions to aggregate measurements – Finance & Operations | Dynamics 365 | #MSDyn365FO This feature lets power users include financial dimensions in ready-made Microsoft Power BI reports. Power users can also create new Power BI reports that use financial dimensions. Financial dimensions are user-defined configuration data that enables the ledger chart of accounts to retain additional information. View this on Microsoft >

——————————
Milinda Vitharana (Microsoft)

——————————
——————————————-

[Axal Ton]

Hi Milinda,

I’ve run into this discussion, I thought I just give it a try. 
Need some advice on the subject.

Noticed on the link you gave us, it all talks about Ledger dimension, tables which are using also from LedgerActivitiy…. Another thing it is used table DimensionAttrbuteValueCombination.
I’m wondering whether this also include transactions which not yet post to Ledger (Uninvoiced SO/PO)

On my finding there is another table which is DimensionAttributeValueSet and in this table I can find Financial dimension for those transactions not yet posted.

So, whether it is true that DimensionAttributeValueCombination holds data already posted to G/L while DimensionAttributeValueSet is holds data not yet posted to G/L ?

If that so, once some transaction is posted, the record from DimensionAttributeValueSet will be moved to DimensionAttributeValueCombination ? These two table will not have the same data, right ?

Again, if so, is there a built-in view that holds these two table, some kind of join table so we do not need to switch back and forth at two table (if we did not know data is posted / invoiced or not)

Thanks in advance,

——————————
Morgan Fields
Hunter Douglas
——————————
——————————————-

[Gina Pabalan]

?@ Milinda

In your step: “You can either deploy the reports to PowerBI.com or create an Analytical workspace with embedded PowerBI?”

This comment implies that the Entity Store is available as a source for BOTH PowerBI.com AND to Embedded PowerBI. 

Is this true, and if so, when specifically deploying the report to PowerBI.com, are you able to “DirectQuery” the Entity Store or “Import” from the Entity Store?  Can you elaborate on how to publish the D365 report (when the model is defined as an Aggregate Measure and data is pushed to the AxDW/Entity Store) to PowerBI.com?

thanks, Gina

——————————
Gina Pabalan
Director, Business Intelligence
Edgewater Fullscope
Ft Myers FL
——————————
——————————————-

[Ian Waring]

Hiya,

You can directquery the entity store on test instances but you’re barred on D365 production – it can only query localhost there. The gotcha we had was changing the LCS dashboards away from Company ID as a filter to Legal Entity and Directorate (effectively an operating division). To do that, we’ve deployed a copy of Production and rdp into it to run Power BI Desktop (effectively a Citrix type remote session).

We add a LedgerActivityMeasure_DimensionCombination from AxDW and do a join (for example, in Actuals vs Budgets, set up a relationship between Budget Activities LEDGERDIMENSION and DIMENSIONCOMBINATIONRECID to effect the join).Then your dimensions are available to query or use as filters.

All a bit of a hack. When done, we’re told to tell our SI to push the pbix file to Production via LCS. We’ve not done that yet, but think we’re on the right path.

Just wish there was a schema diagram somewhere, as it takes an age to work out the tree hierarchy and joins to do custom reports. But that’s another story!

——————————
Ian Waring
Finance Special Projects
Jisc
Harwell Didcot
——————————
——————————————-