Power BI - Cosmos DB V2 Connector - Heterogenous Schema

[jjaywatts]

I'm connecting Power BI to my Cosmos container using the Cosmos DB V2 connector.  I'm using DirectQuery mode, as the dataset is quite large.  The schema amongst my cosmos items is not the same, some have 10 attributes but others may have 15.  When Power BI builds its schema definition by reading the first 1000 rows, its ommitting some attributes that are present in the rows beyond 1000.

I was thinking I could pass a query as part of my connection to cosmos, to select specific records that I'm looking for and would therefore have the schema be built off of.  However, there doesn't seem to be a way to provide a sql query using the Cosmos DB V2 connector. Is that accurate? If so, is there any other suggestions on how to handle this? Thanks!

[jjaywatts]

Hi Dino,

Thanks for the reply.  You’re suggestions were not able to solve my problem.

Enabling profiling on the whole dataset is to allow profiling of values within a column on a whole dataset.  I’m not needing to know all values within a column, but instead I need to know all columns within a dataset.  In my case some columns are not displaying, because they are not present within the first 1000 Cosmos documents.

Regarding your second point.  I checked and see the native query option is greyed out.  What’s interesting, is that there are two Cosmos connection options in PBI.  The V1 and V2.  The V1 option doesn’t allow DirectQuery mode, but does allow the option to provide a sql statement.  However, in both cases, I’m not able to see the native query option, even though in V1 you can provide a sql query.

M code for V1

M code for V2

Is there a way to introduce a sql query in the V2 connector? If so, what’s the syntax, as I’ve tried a number of different ways with no luck.

[Anonymous]

Hi @jjaywatts ,

First, regarding the question “Power BI builds its schema definition by reading the first 1000 rows, it’s ommitting some attributes that are present in the rows beyond 1000.” You can change this in Power Query to be based on entire data set by clicking here:

Regarding "there doesn't seem to be a way to provide a sql query using the Cosmos DB V2 connector.", you can right-click the operation you are performing in Power Query and see if the option "View Native Query" is available. As shown in the screenshot I provided, if the "View Native Query" button is grayed out, it means that the data source cannot convert the query in Power Query to a sql query:

You could probably use the Azure Cosmos DB Data Migration Tool (dt.exe) to export the data to a JSON file, making sure you include all the necessary properties, and then import that JSON file into Power BI. This way you can control the schema that Power BI sees, but keep in mind that this approach doesn't use DirectQuery and is better suited for smaller datasets.

In addition, regarding the Data Factory you mentioned, this is already a problem within the scope of Fabric. Perhaps you can post on the Data Factory forum, and more professional engineers will come to answer your questions.
Data Factory forums - Microsoft Fabric Community

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jjaywatts]

Hi Dino, realized my response wasn't specificlaly a reply to you.  Did you get a chance to see my response and have any other suggestions? Thanks!

[jjaywatts]

I found this for data factory, which is my same problem.  I just don't know how to manually force the schema/column definition.

https://learn.microsoft.com/en-us/azure/data-factory/connector-troubleshoot-azure-cosmos-db#columns-...

Columns missing in column mapping

• Symptoms: When you import a schema for Azure Cosmos DB for column mapping, some columns are missing.

• Cause: Azure Data Factory and Synapse pipelines infer the schema from the first 10 Azure Cosmos DB documents. If some document columns or properties don't contain values, the schema isn't detected and consequently isn't displayed.

• Resolution: You can tune the query as shown in the following code to force the column values to be displayed in the result set with empty values. Assume that the impossible column is missing in the first 10 documents). Alternatively, you can manually add the column for mapping.

  SQLCopy

  select c.company, c.category, c.comments, (c.impossible??'') as impossible from c