You have a great idea for an app. You have no idea where to store your data. Which data sources do you use?
A green field project and you are free to use whatever you like. The Power Platform offers many options to store your data in.
The first steps to Data sources in Power Apps
When you go to the Power Platform site, the first thing you will see is the many options to start from your data.
For some of your projects you might already have your data stored somewhere but quite often you will need to design your data as well. With more than 325 connectors it can be difficult to make the right choices.
The preferred data source options
That home screen already points user towards the preferred options:
- Excel Online
- SQL Server
- Common Data Service
Excel is even mentioned twice as the pink banner also points users towards Excel. That must be the best option available then!
SharePoint as a data source
SharePoint uses lists and libraries to store data records as list items and documents. This is quite an easy concept and many users have been happy to create new lists and use lists to store their data.
Okay, lists are no relational databases and they can be limited in the number of items that they can hold. With the modern lists however you will find that the 5000 item limit has pretty much disappeared as indexes are created as needed.
SharePoint however is easy to manage by the citizen developers that the Power Platform is aimed at. Therefore often the combination of SharePoint and the Power Platform may work well.
An additional benefit of using SharePoint lists as your data source is that you can use the Seeded licensing model. This means that the licences required are part of the Office 365 licence. So no extra costs.
Excel as a data source
You may have looked at the Power Apps Templates that you can use to create you apps in the Power Platform.
Quite a few of these templates create excel files for you. But really are you planning to store your data in an Excel file if you have a vision for the low code development platform that the Power Platform is?
If Power Apps is just a pretty interface in front of an excel spreadsheet then we haven’t moved far from Microsoft Access.
There is however one benefit of using Excel. You don’t need any additional licensing on top of your Office 365 licence.
SQL Server as a Data Source
When you have a real need for a relational database or the volume of data is high then you might look at SQL Server.
If your organisation already uses SQL server and data that you want to use is already found there then SQL Server seems to be the obvious option to go for.
There is however one down side. You will need premium licence keys.
Common Data Service as a data source
The common data service is the Dynamics 365/CRM database that comes with the premium features of the Power Platform. If you don’t want to look after the database at the SQL level but you do want to logical options of entities and relations then the Common Data service will be the option for you.
Like with SharePoint, you can manage the data layer from within the Office 365 platform as all of the Common data Service is accessible straight from you Power Automate and Power Apps interface.
And the winner is …
… Well it all depends on your needs and home much you want to spend.