Solution Rebuild: Part #1 | Database – Access to Dataverse (Extracting + Transforming)

/ Charles

The ask, rebuild a legacy VB.NET desktop application as a Power Platform solution. The app was written maybe 17 years ago, hasn’t been updated, and is a media tracker app, like a personal IMDb. Because it was a personal solution, not an enterprise solution, its data was stored in a Microsoft Access database, so this data first needs be migrated to something more scalable like Microsoft Dataverse:

Figure 1 - Microsoft Dataverse table with imported media information.
Figure 1 – Microsoft Dataverse table with imported media information.

Now, before this data is loaded into Dataverse, it should be extracted and transformed (ETL). Whenever legacy data is being considered, don’t assume the data is already clean. Confirm, then proceed. Though, even if the data is already clean-ish, because best practices may have changed over the years, this is still a great opportunity to transform the data. For instance, here there are individual Boolean columns indicating whether the record is a movie, series, or manga. These three columns can be transformed into a single column, optimizing the data model:

Figure 2 - Microsoft Access table with legacy media information.
Figure 2 – Microsoft Access table with legacy media information.

One approach to transform the data, decide on a modern data model and import the Microsoft Access data into Microsoft Excel, then use Power Query to make the necessary adjustments. Again, don’t “lift and shift” the legacy data into Microsoft Dataverse without first looking for opportunities to clean and/ or transform the data:

Figure 3 - Microsoft Excel workbook with actions to import data from Microsoft Access Database.
Figure 3 – Microsoft Excel workbook with actions to import data from Microsoft Access Database.

As the primary data table is being imported, choose to transform the data before loading everything. Within the Power Query window, choose to add a new column with conditional logic to combine the three Boolean fields:

Figure 4 - Power Query feature to Add Conditional Column during the transformation process.
Figure 4 – Power Query feature to Add Conditional Column during the transformation process.

Another consideration to transform data, some values may need to be replaced. For example, when rating media, the legacy approach may have been a numerical value, but today, it needs to reflect letter ratings. During the transformation process, the column data type could be converted to text, then numerical values replaced with letter ratings, over vice versa:

Figure 5 - Power Query feature to Replace Values during the transformation process.
Figure 5 – Power Query feature to Replace Values during the transformation process.

Conclusion:
Microsoft Dataverse is a modern data storage solution, ideal for modernizing legacy processes. However, prior to Loading legacy data into Dataverse, don’t forget to Extract and Transform the data (ETL). For legacy solutions, it’s almost guaranteed that data will need to be cleaned and/ or transformed, which helps make the data model more efficient.

“Let the nation and the world know the meaning of our numbers. We are not a mob. We are the people.”

Bayard Rustin

#BlackLivesMatter

Leave a comment