Connecting using sql server vs dataverse or D365

[wpf_]

I have a directquery that connects to dynamics 365 using the sql server connector.  I am connected to a Dev environment and a Prod environment data source of the same application.   When I am connected to the Dev data source, the data/times are preserved in BI.  But the Prod data source data/times are converted to UTC.  I checked both data sources and they both have the same date/time settings.  

It seems that when I connect using dataverse, both data sources are then consistent with date/time, however some of the fields are missing.  I either fix the inconsistencies using sql server connection, or use dataverse connection but fix the missing fields.  Hope someone can help. Thanks. 

[fsim]

@wpf_ Hi ! I ran quite a lot of test about commondataservice.database and Sql.Database connector.
As far as I can tell, the SQL one works faster and should(*) have less impact on production server that the DV connector.
In both case, I was using the = Value.NativeQuery(src," an SQL query")
(*) as long as the sql read only database is really a replica of the production server. I cannot find any evidence of this , but chat GPT was quite sure about it 🙂

[v-zhangti]

Hi, @wpf_ 

To help keep the size of the returned data within acceptable limits, use as few multi-line text columns and choice columns as possible.

 Please refer to the following documentation to see if it can help you.

Create a Power BI report using the Dataverse connector - Power Apps | Microsoft Docs

Use SQL to query data (Microsoft Dataverse) - Power Apps | Microsoft Docs

Best Regards,

Community Support Team _Charlotte

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