Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Unanswered

Joining multiple data sources to make a customized entity

(0) ShareShare
ReportReport
Posted on by 25

Hi
I'm currently working to translate current views in SQL, made from D365 FO tables (on-prem) into entites for our transition to cloud instance.

Normally, I would create entities, get their data in Power BI, make joins and get the desired results.
Would it be possible to make joins within an entity created, say 7 datasources, so it may save processing time in Power BI?

  • RE: Joining multiple data sources to make a customized entity

    Can you explain explicitly?

  • Mohit Rampal Profile Picture
    Mohit Rampal 12,554 Super User 2024 Season 1 on at
    RE: Joining multiple data sources to make a customized entity

    You can add datasource ranges and table relations for where clause and group by is also available in data entity.

  • RE: Joining multiple data sources to make a customized entity

    I see

    What about translating WHERE and GROUP BY clause? Is it possible?

  • Mohit Rampal Profile Picture
    Mohit Rampal 12,554 Super User 2024 Season 1 on at
    RE: Joining multiple data sources to make a customized entity

    Hi, I can't tell all limitations but there are certain SQL functions which can't be applied in Data entity. For instance, keyword COALESCE, you can do use aggregation functions in select statement but can't apply same in Data entity directly. For that, computed column has to be created.

    This is the SQL Script that I used to convert into Data entity.

    pastedimage1683866870671v2.png

  • RE: Joining multiple data sources to make a customized entity

    Thank you

    Can you tell me specific limitation(s)?

  • Mohit Rampal Profile Picture
    Mohit Rampal 12,554 Super User 2024 Season 1 on at
    RE: Joining multiple data sources to make a customized entity

    Hi Roohan, I have done exactly same task where I got SQL queries having multiple tables and created data entity based on those SQl queries adding datasources,  ranges etc. You  can do it and to ensure the data entity is created correctly, you can execute SQL queries/views and your custom data entity in SQL Server and check if number of records are matching, all fields have same data (check for few records)

  • Anton Venter Profile Picture
    Anton Venter 19,493 Super User 2025 Season 1 on at
    RE: Joining multiple data sources to make a customized entity

    Yes and there any examples in the standard application (data entities) that do this. You have to keep in mind however that there are limitations in Dynamics 365 Finance in making joins, grouping etc. You do not have all the same possibilties as SQL Server query lanugage.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,979 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,848 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans