Replicating a SQL View within Dataverse on multiple Dataverse tables

[D_PBI]

Hi,
I would like to know if it's possible to create Views within Dataverse combining data from several Dataverse tables.
My current setup is that that we have Dynamics 365, so held within a Dataverse, that has its data pushed to Azure Synapse (the Data Export Services' substitute). From within Azure Synapse, I have created several Views that combine data from multiple Synapse tables i.e. the original Dynamics 365 tables). PBI consumes these Views.

We're looking to ingest various third-party data into our domain. For simplicity, I'm thinking to utilise Power Platform Dataflows as the integration tool and have the data reside in the same Dataverse (the same Dataverse where our Dynamics 365 sits).

Once we have the various third-party datasets in the Dataverse, I would like to combine this data with our Dynamics 365 data. This combined data will act as a Business specific data mart. We may have several data marts each focusing on a different Business process.

I guess I have the option of creating a table (so permanent storage) that combines the various Dynamics 365 tables data and the third-party ingested data in a single place. However, this table (this single place) will add to the Dataverse data volume. If I end creating many data marts then this will quickly add to the cost of running our Dataverse instance.

I would like to know, once I've ingested the third-party data in the Dataverse using Dataflows, if I can create a View (like a SQL View) that would combine data from various Dataverse tables and third-party table data (that sits wihtin the same Dataverse) but, due to it being a View, no extra storage is being utilised, so there's no volume of data increase and therefore no increase in Dataverse costs.
Is this possible? Would someone be able to provide me with a link that demonstrates this process?  I am new to this.

Thanks in advance.

[ivan_apps]

Yes, Dataverse can support views of data that span multiple tables. But there has to be a link between the tables, usually a Many-to-one relationship. When creating a 'view' from the table in a solution - there is a tab to switch to 'related' tables, where you can reach the related fields. What isn't supported is views with Many-to-many relationships.

Now if you want to use SQL server directly, you could push your data via a Data Gateway to SQL. It shifts the cost from Dataverse to SQL and then you can use virtual tables to view that data.

[ChrisPiasecki]

Hi @D_PBI,

Virtual Tables using the SQL Server connection provider might be a way to go in this scenario. It can connect to the Synapse SQL Serverless endpoint like a normal SQL database, so you can bring other data into your data lake alongside your Dataverse data.

The only caveat is that SQL views are not currently supported yet as far as I know, so you'd need to convert that into a flat denormalized table to use it as a virtual table.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[D_PBI]

@ivan_apps @ChrisPiasecki - thank you for your replies.
I have now been asked to devise a process so we can remove our need for Synapse (to recap, our Dynamics/Dataverse data is pushed to Synapse, sitting on top of Synapse are SQL Views which the reporting consumes).

I envisage the process as:
1) Our Dynamics 365 data continues to reside in the Dataverse tables as they currently do (i.e. account, contact, and any custom tables, etc..).
2) I aim to use Power Platform Dataflows to extract third-party data from an external domain and load it into a new custom Dataverse table, within our domain, to act as a staging table. This third-party data maybe spread over many third-party tables and so I would like to extract that data using a SQL Script, or the like, which combines the data from the many tables and load it to a single custom table inside our Dataverse solution.
3) I would like to import the external third-party data into our domain on a scheduled basis. I expect once per 24 hours would suffice in case there's a limitation on refresh frequency.
4) I would like to create Views within Dataverse merging the Dynamics 365 data with the third-party data (all now within the same Dataverse instance). Being Views, it means the data isn't physically duplicated and therefore isn't increasing our data storage costs.
5) There may be a need to create Views consuming other Views so that would need to be possible.
6) Through our Dynamics solution, we're able to use Advanced Finds on our Dynamics/Dataverse data which means the end-users can see data insights quick/easy without the effort of creating reports using something like Power BI. There is a limitation when using Advanced Finds with only being able to join two tables (i.e. account or contact) together. Could I join several tables together via a View?
Is an Advanced Find able to connect to a View or can the Advanced Find only connect to a physical table?

I understand there are several data integrations tools I could use. SSIS, Azure Data Factory, etc.. - all of which I know little about - however, my aim to use the Dataverse and Power Platform Dataflows is so the third-party data in consistent in its storage with our Dynamics data. The capability on using Advanced Finds to connect to merged internal/external data is a real plus - although, if Advanced Find cannot cannot to data to a View then the merging of internal/external data will have to be made available in a physical tables which will all add to the cost of our Dataverse implementation.
 
Having read the two replies, I'm realising there is much I don't understand and it makes me nervous about going ahead with this without any type of guidance/training. I'm sure, in the grand scheme of things, my aims are very simple but without having see all in action I could be facing a lot of unforeseen hurdles.

With the added bit of information in this post, is what I'm trying to do still feasible?
Should I still be looking to complete my aim using Dataverse and PP Dataflows?
Are you (anyone) able to provide a link(s) to good material on performing what I need to do so I can see it in action?
Thank you.

[Fubar]

If trying to 'combine' data in views in Dataverse the native view capability is limited e.g. a view on your main table can only display columns directly on that table and columns from the immediate parent table (not from a parent of the parent etc, and cannot show a list of child records).

You may be able to get around this by implementing a Canvas app and 'joining' the data in the Canvas App.  In a Model Driven app you would need to either code a PCF control, embed a Canvas App in your form, or create a Custom Page (basically a Canvas App that is called directly off Model Driven App menu).

Generally, you would probably need to think about how you structure your data, as unlike SQL your native joining capability is not as robust inside Dataverse.

[D_PBI]

@Fubar - thanks for your reply.
Okay, it sounds like my need to create a View (Virtual Table) in Dataverse that combines data from several tables, via different cardinality, isn't possible. Instead, I'd only be able to create a View that combines two directly related tables at a time - this is how Advanced Finds function and so I now realise an Advanced Find is the same View (Virtual Table) mechanism as being described to me.
Can the above comment by me be confirmed as accurate?  If a Dataverse Virtual Table cannot combine data from several tables then it's a blocker for me. This further talk of using Canvas App is something I don't understand as I'm completely new to the Power Apps/Dataverse world.

If the above is accurate then I will revert to the trusty method of using SQL Server to do the needed. Ingest data into SQL tables, then create SQL Views combining data from the SQL tables and from Dataverse tables. If I were to follow this method, can I confirm the below:
1) Azure SQL Server can extract data from Dataverse tables (i.e. Dataverse allows for Azure SQL to extract from it)?
2) If I was to use Azure SQL Server to create a SQL View which combined data from several Azure SQL Server tables and several Dataverse tables, could the Dataverse connect to this single SQL View and allow a Dynamics Advanced Find to show the SQL View's contents?  Our users enjoy the simplicity of using Advanced Find to get quick insights into the Dynamics data. They will want to see insights from our Dynamics data and third-party data. If my SQL View approach works and an Advanced Find can show the contents of this single SQL View, that will be a big win for us.

Thanks in advanced.

[Fubar]

Advanced Find is roughly the same as the system view.  However there can be some small differences, as Advanced Find is really for Personal Views, vs the other editor is for System Views - but for generally for what you are wanting it is essentially the same.

Canvas app has the capability to use different data sources not just Dataverse.

Dataverse Tables are Web Service enabled (oData), so if what you are using can connect and use Web Services then yes.

I haven't tried it, but from the limitations doco, "SQL views can be used to create a virtual table but they'll only provide read operations" https://learn.microsoft.com/en-us/power-apps/maker/data-platform/limits-tshoot-virtual-tables?tabs=s...

[jklombard]

Just watch out for multiplexing licencing rules from Microsoft if you are using the Data Gateway to SQL.