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.
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.
That home screen already points user towards the preferred options:
Excel is even mentioned twice as the pink banner also points users towards Excel. That must be the best option available then!
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.
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.
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.
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.
Last week Shane Young asked me about calculating the Sum for a SharePoint column in…