6 Essential Data Strategies for Success in the Power Platform

I’m very often asked, where should I store my data? Most of the time data strategies include one or more of the following:

• SharePoint
• Dataverse
• SQL
• Excel

Considerations

Some of the considerations at a start of a new project could be:

• Current location of the data
• Performance
• Existence of the data (New vs existing)
• Data design
• Cost (Premium vs Standard)
• Developer skills (This is of course a poor reason to select a data source)

Current location of the data

Now when you create an app, and you already have data available in a spreadsheet somewhere, you could of course make your app read that data from that spreadsheet. Why move data if you don’t need to? But is that Excel spreadsheet really going to be the most suitable data source for your app?

Same for SharePoint, when you have a SharePoint list with some data in it, and you connect your app to that SharePoint list, will that make your SharePoint list the perfect data source? Well maybe. If the data isn’t expected to grow very much, or your not planning to load 1000s of records into your app, then SharePoint lists can still be suitable.

How about if you have a SQL database already holding a lot of business data. Why would you move that data to a different place SQL is a perfectly suitable data source, making it possible to design a solid data structure for your high performing apps that can handle high volumes of data.

Dataverse, as the native database system to the Power Platform will of course also have benefits. Being able to create Model Driven Apps and being able to manage your tables, relationships, views and form all within the Power Platform can be a great reason to use Dataverse. Begin bale to deploy the data model together with you apps in solutions is one of the benefits of using Dataverse.

Performance

As mentioned of some of my previous posts about SQL Stored Procedures in the Power Platform when you want to read large volumes of data, then SQL is your option.

There are various performance considerations within the Power Platform. Now if you have decided to use Dataverse then Model Driven Apps may give you the right performance for your app. If however you need your app to be a canvas app then a lot comes down to making sure that your queries (Lookup, Filter, etc) are delegable.

The following three pages on Microsoft learn are important to understand delegation in the various data sources:

• Power Apps delegable functions and operations for Microsoft Dataverse
• Power Apps delegable functions and operations for SharePoint
• Power Apps delegable functions and operations for SQL Server

In short when you query your data source, if your code has delegation warnings records are red from your data source before the app runs the query. This results in you not being able to get past the first set limit of records. When your query is delegable, then the data source will run the first set of records matching your query. Limiting the number of records in the end result only.

Now this is exactly why I like the Stored Procedures in SQL implementation in Canvas apps. Stored procedures are always delegable, and return unlimited records.

Existence of data to decide your data strategies

Now this is a difficult one. If I have data in SQL or in Oracle then I’m unlikely to move my data just to accommodate and app that I’m building.

If I have an Excel spreadsheet with data, I would prefer to run away from a project than implement Excel as my database.

If I have a set of SharePoint lists, it is all going to depend on the solution that I’m building.

So in short, in my opinion there isn’t a straight forward answer. Other than Excel is not a database of course. Now SharePoint isn’t a database either, but it can handle data ok. SharePoint will of course also avoid the dreaded premium licences. Therefore for high number of users, low amounts of data and less complex projects SharePoint lists may still work.

Data Design

When you’re worried about data design and you want a clearly optimized and managed data store, then SQL and Dataverse are of course the options most suitable. However the licence question is often holding back organisations. Taking the low per/user/month cost does add up when you try to develop an app for a large team of users within an organisation.

Within Dataverse, it is easy to create a set of table that can support your app. Relationships between records can be defined in 1 to many and many to many relationships. And before you know it your data model is there. It is easy to develop ( when you know how to ), however with a database expert you can do the same within SQL as well of course.

Even SharePoint, could potentially used, to create relationships between the various lists. Your data strategies, more often depend on the manageability of your data design than the ability to design your data.

An important part of the Data Design is also security. Within Dataverse security roles can be deployed as part of the solution making Dataverse potentially a winner. There is of course nothing that stops you from implementing security roles as part of your SQL solution.

Managing security within SharePoint can be handled with SharePoint’s permissions model.

Costs

I already mentioned Premium vs Standard licences earlier in this post. And this is one of the reasons why so many people ask about Excel and SharePoint as their data sources.

Although, Costs frequently need to be a factor in app development. Limiting the data strategies options by costs mean that we are limiting ourselves technically, making development a lot tougher than needed.

Included in Standard	Premium only
SharePoint	SQL
Excel	Dataverse

Developer skills as a data strategy

We shouldn’t forget that the Power Platform has often been sold as a citizen developer tool. Now should you use Excel as my data store because that is what you know best? Learning something new can even be a good strategy. Otherwise we would still all be using paper for every process.

Data Strategies in the Power Platform

Now what should drive your data strategies? There is always the “We want the best” vs costs. However what does an app costs you that doesn’t do what you need it to do? Or how about an app that doesn’t match your organisations security requirements?

I’m interested in hearing your reasons for picking one data sources or another. Of course there are more data storage options possible than I mentioned in this post.