PowerPlatform Data Migration and Users, from SharePoint

So this was a frustrating time for me. Every turn there was a challenge to overcome, each time left me wondering "Why Microsoft, why?"

Background:

We needed to move a business critical application to PowerPlatform. The plan was simple, create a model driven application with a Dataverse backend. Use a Development, Testing and Production environment to manage the development, remake the business process with Power Automate, and migrate old data from a SharePoint and Nintex setup that was causing a huge array of issues.  

Access Architechture:

Before I start talking about some of the challenges and their solutions there is some important details that you need to know to understand how these issues and challenges came about. It is a series of unfortunate events.

1. 600 users from all areas of the business
2. Users have different roles within the application, Approver, Submitter, Administrator etc.
3. Access granted to the application (not the environments) via Azure AD Security Groups, one for each user role.
4. PowerApps per App Licensing, not per user (this is a very important detail!)

Inside the application, users are assigned to Dynamics Business Units and Access teams, based upon a matrix of related records within the app. So if you are a Submitter, for Company 1 in Division 1, you are assigned to the Division 1 business unit, and you have the Submitter role, via the AD Security Group.

Challenges:

With that out of the way let's start talking about some of the challenges. In the old SharePoint Online version of this application, there is a lot of data, thousands of records and there are a lot of relational lists. The data is also very dirty, there are a large number of records that are very old yet not "Completed", some of the matrices and relationships have not been well maintained, and the system has been modified and patched up over a number of years. It is also worth noting here that the data structure of the new application is very similar to the old, however there are a number of changes that provide their own challenges.

Challenge 1: How do we extract the data from SharePoint to import Dataverse?

Well there are a few options here. We decided that the best way was to use the in-built import system in PowerApps that allows you to use Excel or CSV files to import data into the tables. There were a few reasons for this, but ultimately due to the size and the complexity of the data, the native import system served us better due to its helpful status updates and reporting such as failed records etc, as well as mapping relationship fields for you. What about getting the data into the Excel or CSV files though? Well for this we used PowerAutomate. We used flow to loop through the SharePoint records, and we then processed those records into the required Excel templates that we generated from the Dataverse tables. We could have used PowerAutomate to put the data straight into the tables, however as the data was not clean, needed to be processed and given the number of complicated relationships, this was actually more difficult compared to using the import files. We also used the PowerAutomate flows pushing the data into the import files, to automatically add users into the correct AD groups for their roles.

Here is an example Flow of processing some of the records:

1. Connect to the SharePoint List of Companies
2. For each company you find, extract the list of submitters, approvers etc and add them to some arrays
3. Build an array of the companies, and the division they belong to
4. Use the Graph API to push this table to the Excel import file stored in a different SharePoint site, in a single call, (yes you can add thousands of rows to an excel table in a single Graph API call saving hours of PowerAutomate time)
5. Check each of the submitters, approvers etc are still enabled in AD, if they are, add them to the correct AD Access group
6. Push a table of <company><submitter name> to the submitters import file using the same method as in 4.

The result of this is ultimately that we have a cleaned version of the data from the SharePoint site, in a nice bunch of clean import files, with the right relationship references. We also have the right users in the right AD groups, and all we have to do to generate the data is push the button on the flow and wait for the output. By the way, we managed to make this incredibly efficient, by thinking out of the box a little (excuse the cliche) we are able to extract somewhere in the region of 40,000 records into different spreadsheets, all cleaned and processed within around 2 hours.

Challenge 2: User licenses and Roles

OK. So I have my development environment with my App, Tables, Configuration, Flows, everything is ready to go, now to import my data into the development environment. The first few files go smoothly, "Awesome, this will work." I say to myself. These first few files are Master Data lists, mostly single column tables which are used in other relationships, such as Countries, Divisions etc. I then reach my first more complex file, this has some relationships and users within the data. Instant fail. All 1500 records refuse to import with an error "The lookup reference could not be resolved" on the user field. The system can not find the users in the "systemusers" table. Even though the users have access to the environment, and they are in their respective role based AD groups. The groups are added correctly to the environment, so what is the problem?

It turns out that the way users are added to the PowerPlatform environment is more complicated than just adding the users to the AD groups and the groups to the environment. Microsoft only syncs the users with the environment under two distinct circumstances:

1. When the user is assigned a PowerPlatform Per User license
2. The user attempts to access the environment

This is a disaster. I cannot use per user licenses for this project, my 600 users are globally distributed, and I have three environments to set them up in. The chances of getting all 600 users to log in to an environment that they cannot use yet, three times, when they are all over the world is slim at best. There must be a better way. Time to trawl through documentation, talk to colleagues and see what can be done. My first search comes up with a Microsoft PowerAutomate action called "Force Sync User". I think "Amazing, I can encorporate that into my flow when adding the users to the AD Groups!", you can find the post I found on this action HERE.

No luck. The action runs successfully, but even after waiting a whole day, the users are still not listed in the environment. After a chat with some colleagues and professionals in my network, the consensus was, either get the users to log on, or pay the extra cost for a Per User license that we do not need. There has to be a better solution than this, otherwise, what is the point of the Per App Licensing model? It is worth noting that the Per App model is very new at this point. Time for a ticket with Microsoft to see how they can assist. Long story short, the answer was, "Get the users to log on, or buy Per User licenses". Not happy with this as a solution, I got to thinking, and had a small brainwave. The users have to have a PowerApp or PowerAutomate per user license, but it does not specify which one! We have an AD Group which assigns the members of the group a PowerAutomate Free license. I modify my flow to add the users to this AD group when they are added to their Role group, wait 35 mins, remove them again, and see if they are set up. As if by magic, this works! The users now appear in the evironment as expected and the first complex import works, mostly.

Challenge 3: SharePoint and Dataverse see users differently

When looking at a SharePoint user field, you are using looking at a User Object or an Array of User Objects if it is a multi-select field. In the interface the user is generally referred to using their AD Display Name. In Dataverse (if my memory serves me correctly), it concatenates the Firstname Surname fields. This caused an issue as for some of our users their is a mismatch here causing the import to fail. This is a fairly quick fix by modifying our flow to compensate for this and use their email address, this does mean that we have to use the classic import interface to change the mapping in the import process, but it works. Not a mjor issue, but something worth considering if you are doing something like this.

Challenge 4: All change

So we complete a successful migration of all data from the SharePoint Online to Dataverse Development environment. Due to some feature and functionality changes, some time passes before we complete a mock release migration into the Test Environment. Not an issue, we have the import template files, the flows, and the workaround for previous issues. Time to get this done, and the extract is triggered. Now it is worth noting that we have different AD groups for each role in each environment, so this should be just a repeat of the original migration into Dev, with some different AD Groups. Thinking that my fix for syncing users worked fine for Dev I proceed with the extract and import into Test, I was a fool. It fails on the same import file as previously with the same error. After checking my setup everything looks as it should. Has Microsoft changed something?

The Solution:

I decide to pull some strings with some of our Microsoft contacts, something I rarely do, and see if I can get a new ticket escalated to one of the engineering teams. After some effort, my newest ticket is assigned to a Microsoft Engineer and a call is scheduled.

I never thought that pretending to be one of my 600 users and accessing the environment was a viable solution, however it turns out, that you can tell PowerPlatform "This user wants to access the environment", and a result of this is a backend Microsoft process that creates the user in the environment, and because they are a member of one of our AD Security groups that assigns a role, they get the correct role too! The meeting with Microsoft proved very successful, the engineer assigned Daniel Perez Aguiar, was very helpful and provided us with the method for this workaround, and even posted it on the Dynamics Communities forum to assist others. This can be read HERE.

To summarise the solution, we can use a HTTP call to the Dynamics environment, passing in the GUID for the user we want to have synced, and the backend processes will handle the rest. Once again all works as expected. As soon as the Per App licenses are added to the Production environment, we can repeat this process and users will be able to access their records with the correct license and roles and be able to use the new app effectively.

Conclusion:

This kind of data migration must be needed in varying complexities across a number of businesses and Microsoft would do well here to improve this experience.
I do not wish to turn this into a rant against Microsoft and their support teams but this should have not been this hard. It seems as though the release of the Per App license model was rushed out before the backend infrastructure was ready to handle these circumstances. I also feel that even if that was the case, there is a large element of unreliability to those backend processes that we administrators do not have access to, not even the logs.
Microsoft could also improve their documentation. I have encoutered a few instances of almost "deal breaker" style caviats, or unsupported cases in a single line in one piece of documentation. These often have a large impact, and are not well linked to related resources. Often, these scenarios are not well known by Microsoft's own support teams either, or even explained in more detail than a single sentence.

This one made me think outside of the box, and required a persistence that over the course of months finally gave me the information I needed to achieve this goal with some heavy constraints. This is one that I will remember and use in future instances of similar situations should they arrise.

What do you think to this? Is there something you would have done differently? Did you already know about this? I would be interested in hearing your thoughts on this. Please feel free to drop a comment below.