• Ben Watt

Browsing Tables & Columns in Microsoft Dynamics, using Power Query

I have several customers using Dynamics CRM and have built Power BI solutions using these as a data source. Dynamics CRM uses the Common Data Service, now known as Microsoft Dataverse, as it's storage layer and being able to search or browse objects in there has been a challenge if you don't have access to the Dynamics/Power Apps admin portal.


The solution I am sharing below is going to be redundant in about 20 minutes as the evolution of the data platform, encompassing Dataverse and it's connectors, is moving at an incredible rate. However, the cool features making this redundant are only in preview so you can try this out today!

Traditional data sources such as SQL Server are great for the simple fact you can browse objects, see data types, write queries to explore the data & much more in tools like SQL Server Management Studio (SSMS). Dynamics is a different game, as it's not easy to browse or search objects. On top of this, Dynamics has several hundred tables and tens of thousand of columns so being able to quickly find things can be a very slow process.


To help you with this I've put together a quick Power BI report which pulls in all tables, columns & their meta data to help you find your way around.


I have two simple queries which grab tables & columns and joins them on their MetadataId. I have a parameter you need to edit to point to your own instance. I've put together a couple of report pages to help search objects. They are just basic, but you can improve these to suit your own preferences.


let
    Source =        
    OData.Feed("https://yourtenant.crm4.dynamics.com/api/data/v9.1/EntityDefinitions"}), 
        null, 
        [Implementation="2.0"]),
    SelectColumns = Table.SelectColumns(Source,
        {"MetadataId", "IsCustomEntity", "LogicalName",           
        "EntitySetName","ObjectTypeCode", "OwnershipType", "CreatedOn",
        "ModifiedOn"}),
    RenamedColumns = Table.RenameColumns(SelectColumns,{
        {"LogicalName", "Table Name"}})
in
    RenamedColumns

There are three connectors available to query Microsoft Dataverse. For this exercise I chose the least likely contender, OData.Feed. The other two are Cds.Entities and the new TDS connector. The reason for using the OData connector is access to a table called EntityDefinitions which gives very useful meta data, like data types, for tables & columns.



You can grab the sample PBIX file from my Power BI Gallery Page


Part II - The changes coming


As mentioned, this blog post will be redundant in a relatively short time as things are evolving. I decided to write this anyway for those who want to try this right now.


But from now on, get yourself ready for the Microsoft Dataverse world.


The new Tabular Data Stream (TDS) connector is a new shining star for Microsoft Dataverse. It gives you read-only access to the tables via SSMS so you can use SQL to query data, but this is in preview at the moment.


Further to this, if you are loading data into Power BI for analytical purposes then moving your data into Azure Data Lake might be a better option for you. The OData/CDS connectors are awfully slow whereas Azure Data Lake is far better suited for analytical or bulk loading workloads.


Part III - if you're still using OData


If you have any Power BI solutions using the OData.Feed connector, then make sure you're applying filter and select parameters to reduce the volume of data & improve performance. Remember I mentioned above that Dynamics has lots of tables & columns? Some tables have over 200 columns and you want to trim as much as you can vertically (in rows) and horizontally (in columns)


Relevant Links


OData URI conventions. For filter/select + more on your OData queries

https://www.odata.org/documentation/odata-version-2-0/uri-conventions/


Terminology updates: Entities, Fields are now Tables, Columns + more

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-intro#terminology-updates


Exporting CDS data to Azure Data Lake

https://powerapps.microsoft.com/en-us/blog/exporting-cds-data-to-azure-data-lake-preview/




Copyright © 2019 Datalineo Ltd. All Rights Reserved