Data Factory can't see all contact table columns displayed in power apps data flow

Binway 716

I have created Linked Services in the Data Factory using both the Dynamics 365 and Dataverse connector. I then created a dataset and copy pipeline to export all the columns to an Azure SQL DB. When I compared the extracted columns landing in the SQL DB to columns I can see for the table using Power Query I noticed a few columns I need for filtering purposes where no visible. So using the same linked service, specifically the dynamics 365 at this point, I change the copy activity to a dataflow. The columns where then visible and were created in the destination SQL DB. However, no data was tranferred, just the column name although other columns are populated. There is definitely data in the columns as I can see it in power query, I am comparing the same Dynamics environments and can't understand why I can see the column but no data is present.

Chandra Boorla 4,695 Reputation points Microsoft Vendor

2024-11-28T04:19:17.2866667+00:00
Hi @Binway

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

I understand you're facing an issue with missing columns in your Azure Data Factory pipeline when exporting data from Dynamics 365 to an Azure SQL Database.

Using Copy Activity: When using the copy activity, it's possible that certain columns are not being populated due to limitations in the connector or the way data is being queried.

Please refer Copy activity properties for some useful insights that might help you.

Switching to Data Flow: It seems that after switching from a Copy activity to a Data Flow in Azure Data Factory, the missing columns are now visible in the destination SQL database. However, the data for these columns is not being transferred, even though you can see data in those columns when viewing them in Power Query.

Please refer Mapping data flow properties for some useful insights that might help you.

Why are the columns visible in Data Flow but no data is populated?

Column Visibility vs. Data Population:

It might be related to how Data Flow handles schema discovery and data retrieval from Dynamics 365. Data Flow may be incorrectly identifying the column as existing, but there might be permissions, data type mismatches, or API limitations causing the data for these columns not to be extracted correctly.

Power Query vs. Data Flow Behavior:

Power Query is able to display the missing columns with data, but Data Flow is not. This suggests that Power Query might be using a different connector or approach to pull the data from Dynamics 365, which may handle data extraction or schema discovery in a more flexible way.

Data Flow in ADF might be handling data extraction differently, potentially overlooking data types or having configuration issues with how it reads the Dynamics 365 schema.

Here are some troubleshooting steps that might help you in resolving:

Check Data Flow Transformation - Verify that the columns are being correctly mapped from the source (Dynamics 365) to the destination (SQL DB). Ensure no transformations are unintentionally filtering out data or setting it to null.

Check Column Data Types - Ensure there are no data type mismatches between the source columns in Dynamics 365 and the target columns in SQL DB. A mismatch could prevent the data from being properly transferred.

Check Column Metadata: Ensure that the columns in question are not virtual or calculated columns. Sometimes, calculated fields or virtual fields may be visible in Power Query but might not be directly accessible via the API used by Data Factory

Review Permissions and API Limits - Confirm that your linked service has the correct permissions to access all the necessary data in Dynamics 365. There could also be API limitations that are affecting the retrieval of specific columns. Check the API limits for the Dynamics 365 connector in Azure Data Factory.

Use Data Flow Debugging - Use the Data Flow Debug feature in ADF to check for any errors or warnings that might indicate why the data is missing for those specific columns.

Compare Power Query Connection - Check if Power Query is using a different connection method (e.g., OData) to extract the data. You might want to experiment with using the same method in Data Flow to see if that resolves the issue.

For additional information, please refer: Troubleshoot the Dynamics 365, Dataverse (Common Data Service), and Dynamics CRM connectors in Azure Data Factory and Azure Synapse

I hope this information helps. Please do let us know if you have any further queries.

Thank you.
Binway 716 Reputation points

2024-11-28T04:59:25.2466667+00:00

Thanks Chandra - I'll check these steps. The Dynamics environment also has MFA so I had to set up a Service Principal - this may also be part of the problem so I'll check what access this has.
Binway 716 Reputation points

2024-11-28T05:33:21.6166667+00:00

Thanks Chandra- I actually suspect it is the security role I gave the Service Principal I created when I add it as an application user to the Dynamics Environment. Can you suggest the correct role to use or is Sys Admin the only one.
Chandra Boorla 4,695 Reputation points Microsoft Vendor

2024-11-28T06:27:18.2933333+00:00
@Binway

I actually suspect it is the security role I gave the Service Principal I created when I add it as an application user to the Dynamics Environment.

You are correct that the security role assigned to the Service Principal as an application user in the Dynamics 365 environment could be the root cause of the issue. The Sys Admin role would indeed provide full access, but it's not always necessary to assign such broad permissions unless absolutely required.

Suggested Security Roles for Service Principal:

Instead of Sys Admin, you can assign more granular roles to ensure the Service Principal has the appropriate access without giving excessive permissions.

Here are some roles that you can consider based on the type of data access needed.

Common Data Service User - Provides broad access to most entities and data operations without administrative privileges. Ideal for general data access and transformation scenarios.

Custom Security Role - Tailored to specific needs, granting precise permissions to entities and fields. Best for fine-grained control over data access.

Dynamics 365 User - Grants access to records, dashboards, and operations, suitable for basic user interactions.

Best Practices:

Least Privilege Principle - Assign the minimum necessary permissions to the Service Principal.

Review Security Role - Double-check the Service Principal's role and consider assigning a more permissive role or creating a custom role with specific permissions.

Test Data Flow - Set up a simple Data Flow to extract data from the target table and verify if all columns are accessible.

By following these guidelines and troubleshooting steps, you should be able to resolve the issue and successfully access the missing columns in your Data Factory pipeline.

I hope this information helps.

Thank you.
Chandra Boorla 4,695 Reputation points Microsoft Vendor

2024-11-29T00:59:52.3733333+00:00

@Binway

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Thank you.
Binway 716 Reputation points

2024-11-29T01:01:59.7966667+00:00

no resolution yet - even chat gpt has no idea
Binway 716 Reputation points

2024-11-29T01:11:40.4366667+00:00

power query is using the dataverse connection with authentication as organizational account where as the dataverse connection for the environement using MFA only allows service principal. I have checked the access of my organisational account and made the service principle exactly the same but still can't see the data like i can in pwoer query. it would seem to be easier to export the dat using power query into azure storage
Chandra Boorla 4,695 Reputation points Microsoft Vendor

2024-11-29T02:29:18.7033333+00:00
@Binway

Apologies for the inconvenience.

It appears that you have already done a thorough job of comparing the permissions between your organizational account and the service principal, but you are still encountering issues with data visibility when using Azure Data Factory with the service principal. Given the constraints and if you find Power Query more reliable for accessing the data, exporting the data using Power Query into Azure Storage might indeed be a more straightforward approach.

Steps to Export Data Using Power Query to Azure Storage:

Set Up Azure Storage - Ensure you have an Azure Storage account set up with a container where you can store the exported data. Obtain the connection string or SAS token for your Azure Storage account.

Power Query Configuration - Open Power BI Desktop or Excel where Power Query is available. Use Power Query to connect to your Dataverse environment using your organizational account with the necessary permissions.

Transform Data - Perform any necessary transformations and filtering in Power Query to prepare your data for export.

Export Data to Azure Storage - Unfortunately, Power Query doesn't directly support exporting data to Azure Storage. However, you can export the data to a file format like CSV or Excel and then upload that file to Azure Storage using a script or Azure Storage Explorer.

Advantages of Using Power Query as a Data Source:

Enhanced Flexibility - Power Query offers a wide range of data transformation capabilities, allowing you to clean, shape, and manipulate data as needed.

Simplified Authentication - You can leverage your organizational account for authentication, avoiding the complexities of Service Principal setup and management.

Improved Data Quality - Power Query enables you to apply data quality checks and transformations to ensure data accuracy and consistency.

Scalability - By leveraging Azure Data Factory's orchestration capabilities, you can scale your data integration processes to handle large volumes of data.

Conclusion:

If the goal is to quickly get the data and you are having trouble with the service principal in ADF, using Power Query to export data to Azure Storage can be a simpler and more immediate solution. You can then use Azure Data Factory to further process and load that data into other systems as needed.

However, if you want to stay within Azure Data Factory and resolve the service principal issue, focus on ensuring that the permissions and field-level security for the service principal match what is available for your organizational account in Power Query, and consider using the System Administrator role for testing.

I hope this information helps.

Thank you.
Binway 716 Reputation points

2024-11-29T02:45:57.9733333+00:00

Re-checked the service principal security setting in dynamics and made sure it had the correct dpeartement etc. it is working fine now.
Chandra Boorla 4,695 Reputation points Microsoft Vendor

2024-11-29T03:35:31.23+00:00

@Binway

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Accepted answer

Chandra Boorla 4,695 Reputation points Microsoft Vendor

2024-11-29T03:35:12.37+00:00

@Binway

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Issue:

I have created Linked Services in the Data Factory using both the Dynamics 365 and Dataverse connector. I then created a dataset and copy pipeline to export all the columns to an Azure SQL DB. When I compared the extracted columns landing in the SQL DB to columns I can see for the table using Power Query I noticed a few columns I need for filtering purposes where no visible. So using the same linked service, specifically the dynamics 365 at this point, I change the copy activity to a dataflow. The columns where then visible and were created in the destination SQL DB. However, no data was tranferred, just the column name although other columns are populated. There is definitely data in the columns as I can see it in power query, I am comparing the same Dynamics environments and can't understand why I can see the column but no data is present.

Solution:

Re-checked the service principal security setting in dynamics and made sure it had the correct dpeartement etc. it is working fine now.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

Hope this helps. Do let us know if you have any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Data Factory can't see all contact table columns displayed in power apps data flow

0 additional answers

Your answer