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?
Can you explain explicitly?
You can add datasource ranges and table relations for where clause and group by is also available in data entity.
I see
What about translating WHERE and GROUP BY clause? Is it possible?
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.
Thank you
Can you tell me specific limitation(s)?
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)
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.
André Arnaud de Cal...
291,979
Super User 2025 Season 1
Martin Dráb
230,848
Most Valuable Professional
nmaenpaa
101,156