Dynamics Community Forum Thread Details

Best practices when using Dataverse as a source

Dataverse is great with all the logic built into the database structure, with choice fields, multiple lines of text fields and so on. However, when working with Dataverse as a source for Customer Insights - Data, with its "flat" tables, the metadata from dataverse just complicate the matter. Has anyone developed best practices or any cheat sheets on data conversion when unifying data from Dataverse into CI-D? As an example, a list of values field might contain 1, 2 or 3 when the table is imported. But the values you want is the text assiciated with the values.

Also, I find it frustrating that CI-D does not allow you to import relationships between tables from Dataverse. Being a Microsoft product, this should have been default, and would save lots of time.

Categories:

Dynamics 365 Customer Insights - Data

Hello CU03091316-0,

You've accurately identified a common challenge when working with Dataverse and CI-D. The rich data structures of Dataverse, while powerful, can be complex to translate into the simpler, flat table structure required by CI-D.

Best Practices and Cheat Sheet for Data Conversion

Here are some best practices and tips to streamline the data conversion process

Data Mapping and Transformation

Create a Data Map: Clearly outline how Dataverse fields map to CI-D fields. This will help in understanding the data flow and identifying any necessary transformations.
Leverage Power Query: Use Power Query to clean, transform, and reshape Dataverse data before importing it into CI-D. You can:
Expand lookup columns to access related information.
Use conditional logic to transform values based on specific criteria.
Pivot and unpivot tables to restructure data as needed.
Utilize Custom Functions: Create custom functions in Power Query to handle complex transformations and calculations.

Handling Choice Fields and Multiple Lines of Text

Create Lookup Tables: For choice fields, create lookup tables in CI-D to store the text values associated with each numeric value.
Normalize Text Fields: Break down multiple lines of text into individual rows to improve data structure and analysis capabilities.

Addressing Relationship Challenges

Create Surrogate Keys: Assign unique identifiers to related records in Dataverse to establish relationships in CI-D.
Use Joins: Employ joins in Power Query or DAX to connect related tables based on these surrogate keys.
Consider Entity Store: If feasible, explore using Entity Store to maintain relationships and hierarchies within CI-D.

Additional Tips:
Data Quality and Consistency: Ensure data quality by cleaning and validating data before importing it into CI-D.
Incremental Loads: Consider incremental loads to minimize data processing time and reduce the impact on system performance.
Testing and Validation: Thoroughly test and validate the transformed data to avoid errors and inconsistencies.
Leverage CI-D's Data Preparation Capabilities: Utilize CI-D's built-in data preparation features, such as data profiling, cleaning, and enrichment.

By following these best practices and leveraging the power of Power Query, you can effectively transform complex Dataverse data into a suitable format for CI-D, enabling deeper insights and more accurate customer profiles.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more.
If you have further questions, please feel free to contact me.

Best Regards,
Daivat Vartak

Quick Links