Before you create any new objects, it's critical to understand your data as it is today and the vision for how it needs to end up.
This is an exciting clean slate, but the Dataverse is very particular about how you bring your data in and once your structure is there, it's likely not going to change. It's important at this stage to understand all of the SharePoint lists that are going to be migrated as well as how they are related.
You'll need to begin with a migration mapping document. I use Excel to create basic data mapping documents, but there are many tools to choose from. To create this document, I simply go to the List settings of my SharePoint list(s)
Then highlight all of the list column rows in that view and copy them.
Once copied, paste them as plaintext (Ctrl + Shift + V) into a blank Excel file. You may need to manually add a '1' to the required fields, as the checkmark does not come over in plaintext. Double-check that all columns are accounted for.
Note: We will be using the SharePoint ID column to establish relationships during import. Since all tables have it and it will be for reference only, it's not necessary for the mapping, as a new ID will be created for the data in Dataverse during import.
Now, format the selection as a table.
Then rename a few of the columns and add some new ones to give yourself this table.
This gives us a document that can be shared with stakeholders that tells us a lot about our data:
The name of the column
What type it is in SharePoint
Whether the data needs to be migrated from SharePoint to Dataverse or not
The type we will use in Dataverse
Whether the field is required or not
Any additional notes such as formatting rules, default values, renaming, or security concerns. These can also be broken out into additional columns if you'd like.
You can of course expand on this to include things specific to your data or organization.
After mapping my data (below), you can see how the SharePoint and Dataverse column types will change slightly as well as new fields being marked as required. Now is the time to make these adjustments. Migrations are usually a large effort and will likely only happen once. This will set the schema for your business's data going forward and it's important to get it right. These requirements usually come from business stakeholders and should be documented here.
I've marked some fields as 'No' for the Requires Migration column if the column exists by default for a custom table in Dataverse such as Modified, Created, Modified By, and Created By. It's important at this stage to be familiar with the standard columns that come with a custom Dataverse table. For a list of those default columns and their purpose, see my other blog post here.
Note that I've added a new column that was not in the SharePoint list - a LookUp column to the Account table to relate a Supplier to the Product. In SharePoint, we were storing a Supplier URL, but now during the migration, we can take advantage of the standard Account table to relate this directly to another Dataverse record. This is another example of a change that could come from discussions while gathering requirements. We no longer have to store the Supplier URL on each Product record, as the URL will be in the associated Account record.
For Choice columns, it's helpful to document the possible choices in another Excel sheet in the workbook.
I've also added conditional formatting for the two Yes/No columns using this formatting to make it easier at a glance to see what data is coming over to Dataverse.
Continue this in a new Excel workbook sheet tab for each SharePoint list that you need to migrate. What you'll find is that some data may need to be broken out from one SharePoint list into multiple Dataverse tables or vice versa. You'll also need to plan for changing column types during migration. For example, let's look at the SharePoint list columns below from a different Inventory table. The data structure is similar. Try to find the data errors that need to be corrected during migration as well as how the list can be expanded into multiple tables.
After you think you have found them all, compare your notes to mine below (and let me know if I missed anything).
In this example, one SharePoint list will be migrated into three tables - one standard table (Account) and two custom tables (Inventory and Category). We are also mapping incorrect data types in the Price and Cost fields to the best fit for the data that is coming in and noting that we'll need to convert a text column to a decimal (money) column. Note that UPC will remain a text column even though it may make sense to convert it to numeric at first glance. UPCs are 12-digit numeric codes that refer to a specific product (the barcode you scan at a grocery store). In Dataverse, storing a 12-digit number as a whole number might seem like a good idea, but it's generally better to store it as a single line of text. Here's why:
Leading Zeros: UPC codes can have leading zeros (for example, a valid UPC may be 000123456789), which would be lost if stored as a whole number.
No Calculations: You're unlikely to perform any arithmetic operations on a UPC, so treating it as a number doesn't offer any advantages.
Flexibility: Storing it as text gives you more flexibility for formatting. Check my Notes column in the migration document, we can now use this column to store alphanumeric SKUs in the UPC column if we need to.
This example is why it's important to understand your data and spend time planning the migration.
Here is a list of the primary standard tables you get in Dataverse and how to use them. There are many more tables, but these are very useful and should be leveraged when possible.
Account - a table with a wide range of fields related to an organization or client, such as address, industry, ticker symbol, payment terms, and website. This table should be used to relate a group of contacts to a single organization or financial account. For example, if I do business with a supplier, company, charity, or vendor, those should become Accounts.
Contact - a table that holds information about an individual person, such as phone number, email, and of course which Account they belong to. This table should be used to store individual contact information about everyone you interact with in the context of your business application.
User - a system table that holds information about the internal users of your app. This table will be populated as users are added to your environment and the data is imported from Azure Active Directory.
Task - an activity table that holds information about an obligation or event, such as start and end dates, due dates, subject, and a general regarding field that allows you to relate a task to any other table. For example, a task can have a regarding value pointed to an Account, Contact, User, or any standard or custom table.
These tables are not meant to include every field you need. You can add custom fields to these tables to make them fit the way your data is structured. For example, if a Contact should be related to a specific Category of product in my example, I can add a Lookup field to Contact that relates it to my custom Category table.
Relationship Diagram
After your mapping document is complete (I recommend you have the mapping document reviewed and signed off on by stakeholders at this stage), it's a good idea to model your data by drawing it in a diagram. This gives you a more structured data map and serves as a way to visualize how your data will be related and ensure you haven't missed any relationships. It also serves as documentation that you can share with stakeholders. Let's stick with our most recent Inventory list example. I like to use a free online tool called draw.io. Starting with a blank document, I will add a List element for each Dataverse table as well as their fields.
After adding list elements for each table you'll have post-migration as well as their fields, you'll have something like this (though yours is likely more complex).
From here, you can drag an arrow from the side of the fields where a relationship is established (your Lookup fields) and color-code them. Below I made the custom tables blue, the standard table green, and the lookup columns purple.
You'll end up with something like this. Notice in the diagram view it's easy to understand that the category table will be self-referential meaning Categories can have parent categories that will point to a different record in the same table.
Attachments
Now that the data itself is mapped and diagrammed, we need to think about our options for importing and relating SharePoint attachments in the Dataverse. One option would be to have a custom Attachments table that has a file column as well as any fields for data directly related to the file. Another option is to include a URL field for the file and simply point the user to the existing file in SharePoint. File storage in Dataverse is more expensive and limited to one file per column (hence the option for a separate table where we can relate multiple files to one record via a relationship to the Attachment table). If you have specific predictable files for your records (for example one Assembly pdf file for each Product record) then you can build those directly into your table. For dynamic or for unknown amounts of files, I'd recommend a separate custom activity table. That structure would look like this:
With these two distinct data migration documents, one for mapping the fields from SharePoint to Dataverse, and the other for displaying the relationships and structure, you should have a clear understanding of your data as it is now and as it will be post-migration. Spend plenty of time in this stage as it's important to get this right. This process could take weeks or months depending on the complexity of the transformation. Work with the people who know the data and get as many eyes on these documents as you can while planning, as this stage will pave the way for the rest of the process.
In my next article, we'll begin setting the stage for the migration by creating our solution and all of the necessary solution objects within it.
Comments