Forum Discussion

jjaywatts's avatar
jjaywatts
Regular Visitor
2 years ago

Power BI - Cosmos DB V2 Connector - Heterogenous Schema

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 ...
Need Help
jjaywatts's avatar
jjaywatts
Regular Visitor
2 years ago

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-missing-in-column-mapping

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