Jukka Niiranen

Tag: Connectors

  • Virtual Dataverse tables with no code, via Connectors

    Virtual Dataverse tables with no code, via Connectors

    The concept of a virtual table (previously: virtual entity) has existed in the Dataverse platform for quite some time already. The feature was originally introduced before XRM and Power Apps merged. This in turn means that the Connector feature used by Canvas apps and Power Automate flows is an alternative approach for the same core need: how to work with data that’s not physically stored within Dataverse?

    Since there are still three different flavours of Power Apps , let’s quickly recap what each of them think about data location:

    • Model-driven apps: “I’ll let you work with any business data, as long as it’s stored within Dataverse.”
    • Power Apps portals: “I’m essentially an external facing version of Model-driven apps, so I follow the same principle.”
    • Canvas apps: “Your data may be in whatever system you want! Just point me to the right API and wrap a Connector around it & we’re sorted.”

    The term “Model-driven” refers to the existence of a clearly defined data model, on top of which the visible app UI and background features (security, search etc.) are then generated by the Dataverse platform. You get all those features because a specific set of rules exists on how different types of data are related to one another.

    Canvas apps enjoy the freedom of taking some data from source A, another piece of data from source B, mashing them together in a common gallery, stitched together with a few lines of Power Fx code. The downside is that the app maker needs to build many of the generic features that in the Model-driven world would just magically appear within the app module.

    The best possible outcome would of course be if Power Apps were able to offer both the freedom of a Connector based Canvas app and the strong relational data management capabilities of Model-driven apps. While we are not quite there yet, some elements of the unified app / platform story are starting to emerge.

    Connectors in Model-driven apps

    Ultimately Microsoft wants to bring the Canvas and Model-driven app types as close together as possible. This means expanding the capabilities for working with external data sources in Dataverse to cover also the Connector technology. At Build 2021 the session “Dataverse for Developers” introduced the latest updates on what the sources for virtual tables can be:

    Previously the options for adding virtual tables to Dataverse was pretty much a pro-dev targeted story. The requirements for OData feeds were such that I don’t think I ever managed to find a sample feed to try out the feature. Same for the custom connectors, which are created via writing your own plugins. Technically they can be built, but if the requirements are similar to that of a traditional data integration approach, then it doesn’t exactly revolutionize the low-code data story of Dataverse.

    The new preview for Virtual Connector Provider looks more interesting, though. Supporting out-of-the-box connectivity to SQL Server databases is definitely a scenario that’s closer to the no-code level where I personally prefer to operate on. So, I decided to go and see how far this track can take me in building a Model-driven app that actually works with data not physically stored inside Dataverse.

    Even though the documents still say “private preview”, anyone can install the Virtual connectors in Dataverse solution from AppSource today:

    There’s a Power CAT Live video on YouTube that introduces the solution. If you’re like me and you prefer consuming written information instead of video walkthroughs, this PDF document will be the place to go for understanding the feature. Inside it you will find this diagram that explains the architecture of how concepts like connectors, data sources, connection references etc. relate to this new Virtual Connector Provider.

    Setting up SQL Server tables to expand your Dataverse

    I have a demo AdventureWorksLT database deployed in SQL Azure, just like the one used in Microsoft’s feature documentation for virtual connectors. I had already earlier used this demo SQL database as a data source for Power Apps Canvas apps, which meant I had an existing connection available in Power Apps Maker portal. Authentication is done with SQL username/password combo in my connection, but Azure AD authentication would also be an option if you’d rather not have stored credentials within the connection.

    After following the step-by-step instructions, including setting up an application user / service principal for the virtual connector provider, I had a brand new table visible in my Dataverse environment: “Entity Catalog for AdventureWorksLT”.

    Cool, we have a “table of tables”! I can see all the SQL Server database tables available via this connection. By opening up one of these records, I can specify that I want to create the corresponding SQL table as a Dataverse virtual table.

    I picked the Product and Product Category tables from there. (Note: modifying the table properties in the Power Apps UI doesn’t seem to work, so use the legacy web client and Solution Explorer to change things like table name.) After this, the virtual connection provider nicely maps all of the available columns in SQL into a matching Dataverse column, with the correct data type.

    I can then do the standard configuration tasks I’d perform for a native Dataverse table, such as adding views and modifying form layouts. Of course there are a number of considerations for virtual tables when it comes to the Power Apps features they support. Still, whatever works here is exactly the same experience from an app maker perspective, whether the table is “real” or virtual.

    Building a Model-driven app with virtual tables

    I created a small demo app module for testing how the different table types can co-exist and work together. I added a custom table called “Requests” and added it as the child table for both Product and Product Category virtual tables coming from SQL.

    Let’s first go and browser the external data from a view. Opening up the Products table, the experience is in practice the same as if I was browsing native Dataverse records. I can create a personal view “products currently sold” that filters out all products with a value in SellEndDate field. I can sort based on the SellStartDate. I can filter to see only products with Color value Black.

    This is already pretty darn impressive for someone coming from a Model-driven background. Sure, in the Canvas world I’ve been able to easily point a gallery to a SQL table and view the data, but having all of it available within the pre-generated Model-driven UI is a major step beyond that.

    Let’s try out how the native Dataverse table + external SQL Server tables work together on a form. Upon adding a new Request, I’m able to reference the related Product Category and Product tables via the standard lookup, just like everything would be stored in a single system. Behind the scenes, the native Request record will get references stored to the external Product Category and Product tables from SQL.

    But wait, there’s more! Did you notice that my Request form actually used the Form Component Control to show an embedded form of the Product table on the right side? Immediately upon populating the lookup field on the left side I see all the details of the selected product, just as if they were regular fields of the current record.

    In the above example I’m actually editing the Color field of the chose product with the value “White” before creating my request record. What this means is that within the same save event not only am I creating a new row in the Request table in my Dataverse, I’m also directly updating the data in my SQL Server’s Product table.

    That is powerful! No custom code was needed in creating an app UI that talks with multiple different line of business systems in real-time, on the very same form.

    From databases to Dataverses

    This simple example of simultaneously performing CRUD operations on data stored in different systems via a Power Apps form illustrates the reason why Dataverse needs to be seen as much more than just a database. It’s purpose is to be a value-add layer on top of different data storage systems, making them easy to leverage in your business apps. We already see today with the Dataverse file & image data getting stored in Azure Blob Storage and audit log entries in CosmosDB, alongside the core relational data in Azure SQL.

    The Virtual Connector Provider and virtual tables take things one step further. Especially in scenarios where you’d need to reference master data from an external system, there may not be a need to physically replicate it into Dataverse (perhaps you also want to reduce the storage costs). Specifying the virtual presence of such data will however make it appear as if it was part of the platform, thus brining it into both Model-driven apps and Canvas apps in a unified way. Even adding support for Dataverse business events to cover Power Automate is technically possible for virtual tables, although these understandably will require pro-developer involvement to get the external systems in sync with the API.

    Behind the scenes, these same concepts for virtual entities / tables are already being used by Microsoft in their first-party app features. By browsing the Data Sources within an environment we can see features like case/contact/activity suggestions listed here, as well as platform capabilities like component layers or non-relational data provider.

    Two years ago I wrote a blog post called “The Real Common Data Service Emerges” where I explored the direction where Dataverse (then CDS) was going. Since then we have seen Microsoft make the export of relational business data to a data lake a straightforward process with the built-in Azure Synapse Link for Dataverse. Similarly the import capabilities into Dataverse have expanded as the Dataflows / Power Query support keeps improving. Combine these physical data import/export pipelines with the virtual layers that the connector technology may soon offer for several tabular data sources and we’ve got a highly capable low-code toolkit for business data management needs in the Power Platform.

    You need to keep in mind that there are many considerations (read: limitations) for using virtual tables to review before deciding if they are a good fit for your business requirements. Even in building the above demo app there were things that don’t quite work the same way as with real Dataverse tables. For instance, I can’t specify a 1:N relationship between the two virtual tables for Product Categories and Products. Quick Find on the SQL data doesn’t seem to produce any meaningful results. Referencing virtual tables via lookups in a Canvas app seems to not retrieve related data at all times. Not to mention the fact that in two different environments the whole Virtual Connector Provider configuration process got stuck before any SQL tables ever materialized in the Entity Catalog.

    So, keep in mind that this is a preview of things to come, rather than production ready functionality to use today.

    Update 2021-08-20: the feature has now been officially released in public preview format, with new documentation available. Check out the Docs page “Create virtual tables using the virtual connector provider (preview)” that contains the information previously only available in the aforementioned PDF.

  • The Real Common Data Service Emerges

    The Real Common Data Service Emerges

    When Microsoft announced one year ago that XRM would become CDS v2.0 (officially Common Data Service for Apps), there wasn’t yet any big system redesign implemented to make this a physical reality. Today we are much further down that road where CDS truly becomes a Service that has less and less to do with the familiar XRM databases that we’ve previously been working with. In this blog post I’ll explore the three data related dimensions that give us an indication of where CDS is heading as a part of the Microsoft Power Platform.

    CDS is now Dataverse!

    While reading this article, you can translate the term “Common Data Service” to now refer to its new name, Microsoft Dataverse. See this post for comparison between CDS vs. Dataverse.

    Dynamics 365 Storage Model Changes

    As a part of the April 2019 release train, MS is changing the way how data storage is managed for both Dynamics 365 and PowerApps customers. It hasn’t been an official feature bullet on the release notes document, but that doesn’t mean its significance would be any less than what the shiny apps demonstrated in the April 2nd Virtual Launch event have.

    A new version of licensing guides for Dynamics 365 and also for PowerApps and Flow (for the first time ever!) was released in April. This outlines the commercial impact of the new model to customers, which is probably what most of us will have first paid attention to. Yeah, whenever the pricing mechanism of a widely used MS cloud service changes, it will be a big deal. What makes it even trickier is that MS considers storage as a “subscription add-on” for which they don’t publicly disclose any per GB list prices. I’m not entirely sure this model is beneficial for their ambitions of turning Power Platform into an actual foundation for building third party and customer specific apps, but I guess the shadow of the old CRM and ERP world still looms above this world when it comes to licensing and pricing practices.

    Let’s forget licensing for a moment and focus on the technical changes for Dynamics 365 online environments. All of the existing data that used to be stored in the Azure SQL relational database will in the future be divided into three specific storage types: database, file, log. This should have no immediate impact to customers, as the migration will be taken care of by MS. Their promise is that nothing should change in the way how users and developers work with data, since the APIs that govern access to this data will remain unaffected.

    File data will be in Azure blob storage, as this is the most efficient way to handle miscellaneous documents, images and other “stuff” that may end up inside a typical Dynamics 365 system via features like email tracking that carries over the attachments. Why would you ever store this in a relational SQL database to begin with? Well, the simple reason is that the original on-prem architecture of XRM had no other secure place to put these items, so it was all lumped up there. Now when CDS is a native cloud service, there are much more options available.

    Log data will be in Cosmos DB. This will probably offer a more suitable architecture for managing things like plugin trace logs, audit data and other items of similar nature. What should be noted is that Microsoft’s plans don’t just stop at this IT admin activities level. In a recent podcast by MVP Mark Smith, we heard the General Manager of Power Platform, Charles Lamanna, describe this storage type to be designed as the future place for other types of observational data, too. Charles referred to things like IoT device sensor data, which should give you an idea of how this again is data that is A) relevant to many CRM use cases and B) in no way optimal to be stored inside that relational XRM database.

    One significant and very welcome change that is introduced as a part of this new model is that there will no longer be any license cost tied to the number of instances you have in the cloud. Previously you had to buy add-on licenses for acquiring production and non-production (sandbox) instances for developing, testing, training and in general managing your complex Dynamics 365 online environment. Once the new subscription terms kick in, you’ll have the ability to create as many instances as you like, provided that you have sufficient database capacity available. A major driver behind this change is surely the PowerApps side, in which the licensing terms already granted any user with PowerApps P2 license to create 2 CDS environments for their applications. (For more details, see my presentation on Demystifying Dynamics 365 & Power Platform licensing.)

    In the short term, this storage model change should not result in much functional changes for the Dynamics 365 customers. Depending on when your current subscription renewal date is, the new terms will be applied either at that point in time or the renewal after that (if you choose to hold on to the old model for one more subscription period). Any new customer will likely be leveraging the new pricing model starting from April 2019.

    It’s important to understand that the actual data storage technology change and the commercial terms that are applied are not tied to one another. Migration of your Dynamics 365 data to the new database/file/log model will probably take place much sooner than what you’ll see in your subscription fees. Refer to the admin documentation on Common Data Service storage capacity for details on how you’ll be able to analyze and manage your storage consumption in this new model.

    Diving Into The Data Lake

    When looked at purely from the storage license model changes for Dynamics 365 customers, the story would end here, with the three storage types. However, the bigger picture of how data is used as a part of the Customer Engagement systems that cover various digital touchpoints is much broader. Or should I say “bigger” as in Big Data? As much as I dislike the casual use of tech marketing hype terms like Big Data and Artificial Intelligence, there’s no escaping the fact that the familiar world of CRM systems founded on SQL databases is being disrupted by what machine learning models and big data systems can offer today.

    (more…)