How to Transition from SharePoint to Dataverse

Clarissa Gillingham


This book is about using Microsoft Dataverse to build solutions on the Power Platform and how this differs from using SharePoint lists. Moreover, it is about how a different mindset is required when using Dataverse in order to look at your solution from a data-first perspective.

Changing Your Mindset

Many people use SharePoint as their main data source for solutions in the Power Platform. This eBook is all about transitioning from using SharePoint to using Microsoft Dataverse, formerly known as the Common Data Service (CDS). This transition means

getting into a data-first mindset.

A transition is a change, and this can be very scary, especially when you have been used to using something for a long time. Microsoft Dataverse is not a replacement service for SharePoint, and it is not a case of doing the same thing using a different environment and getting the same results. You need to adapt, and a new product means a new way of using it.

In this eBook, we will look at how we would have approached building a solution using SharePoint and then look at how we should do it in Microsoft Dataverse. The two technologies require us to think differently about how we design and build solutions from the very start of the process. Once we’ve covered the basics, we’ll then look at an example scenario to show how this translates into real-world solution development.

In The SharePoint World

Normally in the SharePoint world, we would start with a problem that needs to be solved, and this would typically be quite a specific problem with a narrow scope. For example, it could be that currently a paper form is used to record information on a regular basis, and we want to digitise this so it can be filled out in SharePoint. The data captured from this form can then be stored in a SharePoint list automatically and reported on at a later date.

Usually, the first thing we would do is think about creating a custom form or a canvas app in Power Apps. We could make the form dynamic, and it would be much better than the current solution of filling in paper forms by hand. We go straight from the problem to the solution and focus on the user interface, so everything is driven by what we want the user to see and interact with. We need the data in the background, but it isn’t the focus of our development.

This leads us into a single app focus where we are only thinking about the data that we need for this particular solution. Usually, when we’re developing solutions in SharePoint, we’re not thinking about a holistic data approach, we’re thinking more about what we want our app to look like so how does the data help with that. This, in turn, means we end up with a reactive data structure. The data we capture grows based on what we realise we need to record while developing forms.

There are some limitations to this approach. We generally have to stick to one list of our solutions or multiple instances of similar or templated lists. You can look up to thin FACT tables, but there is little linking between the different lists, and it is difficult in SharePoint to look up parent and child records.

SharePoint is not a relational database, and because we’re focusing on making the data work for one solution, it is very siloed and limited. The above way of thinking about developing solutions does not work in Microsoft Dataverse.

Dataverse – Broaden Your Horizons

In order to build solutions in Microsoft Dataverse, we need to change our mindset and broaden our horizons. We need to stop thinking about how the data will work for our solution and start thinking about how the solution will work for the data. This is what is meant by a data-first mindset.

When building solutions in Dataverse, we still start with a problem. However, instead of jumping straight into designing and building a solution, we first take a holistic view of the data that exists in the organisation, which allows us to represent it properly. From this holistic data representation, we also get a structure that we can build in Dataverse.

We can then build a UI that enables the entry of data. This is different from the SharePoint example, where we had a UI that was enabled by data. We’ve been able to do it the other way around in Dataverse as we started with the data first and then made the UI work for the data.

Once the above steps have been completed, we can then look at enhancing the UI. Initially, we would use views and forms in model-driven apps but then could build enhanced canvas apps that are empowered by the data underneath them.

This holistic approach means that we don’t end up looking through a narrow scope and

can see an overall picture of how our solution fits with the rest of the environment. There is more of a focus on abstract entities, so for example, rather than look at a specific paper form that someone fills out we can think about what data objects and entities they interact with on an abstract level. Each of these can then be treated within their own bubble, and so we can then put them together in a modular manner, which will create a relational structure.

We need to focus on what the actual data is, including what objects exist in this Dataverse, and what attributes each of these objects has.

Setting up a solution using Microsoft Dataverse does take a bit longer to get started compared to the traditional SharePoint method, but there are many advantages. Data can be split where it makes sense for the data. Separate data objects can exist in their own space, but we can then connect them to each other if and when we need to. This also means that the data can grow in a structured way. In the same way, as we can in SharePoint, we can look up thin FACT tables, but in Microsoft Dataverse it’s also possible to structure children and sibling DIM tables.

Microsoft Dataverse enables us to create a true relational database to store all our data. The major benefit of Dataverse is that once it’s set up, all your data can then be surfaced to multiple applications and solutions.

Bus Travel Scenario

In this example scenario, we are going to look at creating a solution for bus travel. This solution will be a digital interactive bus timetable. The screenshot below shows an example bus timetable:

Initially, we may be tempted to look at that as something we could quite easily convert into a list in SharePoint with each stop as a column that can have the time entered:

However, we need to rein in the enthusiasm and need a plan before we create anything. As mentioned previously, we need to think about the data, not the solution.

We need to think about this like Lego. We will start off with a blank board, and we want to build a scene. Our solution is about buses so the first thing we would like to place on the board would be a bus. Then we have to define what makes a bus; in other words, what attributes does a bus have?

A DriverRuns a route
A maximum number of passengersBelongs to a company
A current number of passengers 
An engine 
An MOT date 

You may notice that some of the attributes above are in bold. These highlighted attributes could also be individual objects that we could place on our Lego board. Some of the attributes of the bus may not be relevant to our solution but would open up the bus object to be a part of further solutions in the future.

Now we know that a bus can have a route we can place the route object on our board. We then have to do the same as we did with the bus and ask what a route is?

A first stop
A last stop
N stops between them

The route object is a bit more abstract than the bus and has fewer attributes but is still a legitimate object for our solution. We can add that to our board and then look at the stop object:

A locationCan be part of many routes
An order in the route 
Or a previous stop and next stop 

Already it is clear that this is going to be quite a complex data structure. However, because we have started with the data first, we can make a plan for the structure and take action before we start building our solution.

The Most Useful Tool There Is

When things get complicated the best thing to do is make a diagram. More specifically, the best tool for the job is an Entity Relationship Diagram (ERD). These allow us to structure our thoughts and plan proactively.

ERDPlus is an ERD tool, and it allows us to add entities, attributes and relationships. Adding a relationship is particularly useful as you can define it using a normal sentence such as ‘a driver drives a bus’ and then see it represented as an actual relationship in the diagram:

When defining relationships, we can also think about whether they are one to one or one to many. For example, a driver may drive many different buses:

Planning and designing the structure in this way means that now when we come to build our bus timetable solution, we don’t have to manually add all the data to SharePoint. Our data structure would enable us to generate the correct data for us based on the different related objects.

Creating The Solution in Dataverse

Converting the language used in the ERD to the terms used in Microsoft Dataverse can be thought of with the following:

One-to-oneLook Up Column

In order to create the solution in Dataverse, the first thing we would do is create a new table. In this case, the first table we add will be the Bus table:

One important thing to note is the Primary Name Column setting. This column is similar to the Title column in a SharePoint list, and once you’ve named it, this cannot be changed. It is, therefore, best practice to leave the default settings for this as we can just not use this column if we don’t need to. Once we’ve created the table a number of default fields will be added including a GUID to identify each record uniquely:

Any additional custom fields that we need, such as the number of passengers can be

added once the table has been created. Once that’s done the next thing we need to do

is define a relationship between the bus table and the route table:

In this example the relationship between bus and route will be many-to-many:

You can see that a default relationship and relationship table name is created, but these can be edited to make them easier to understand.

You can also configure how data is entered into tables by creating new forms for the table or editing the existing ones:

Columns from the table can then be added and moved around the form as required:

You can also add subgrids, which show details of related tables. In this example, all the routes that a bus can run on will be displayed on the bus form:

Once complete, the form could be published and added to a model-driven app so it could be used to add data to our solution.

0000-00-00 00:00:00

Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}