PowerApps 101: where do I store my data?

Any business IT solution must store data, be it customer information, inventory data, financial transactions, documents or pictures.   It is important to chose the right place for your data.

PowerApps can interact with a number of data sources, and I have tried the following, in order of complexity:

  • Excel files stored in OneDrive etc.
  • SharePoint list and libraries
  • Common Data Service (CDS) entities and picklists
  • Azure SQL Database tables (basically SQL Server in the cloud)

I would avoid Excel files.  Excel was never designed to be a database, and it probably never will be.  Microsoft are working to improve the ability of PowerApps to use Excel files to store data, but the effort required to use any of the alternatives is not large (and often easier than getting Excel to work as a back end) and the additional capabilities are usually worth it.  I highly recommend using Excel for creating printable reports, but you can do this pretty much wherever your data is stored, so this should not affect your choice.

SharePoint lists were also never designed to be a database, however performance is good and it is so easy to get going with PowerApps that it can be a valid alternative.  For storing documents, SharePoint libraries are easy and you can access the metadata from within PowerApps.  This is a great plus.  Please note that you will need to index columns that you want to include in filter/sort criteria if your list has more than 5,000 items.

CDS is the native PowerApps back end.  It is promising, but currently lacks so many of the capabilities of mature relational databases, that it is not much more useful than SharePoint lists.  The only real advantage of CDS currently over SharePoint is that you can store images in CDS.  So you can choose CDS or SharePoint for a quick and dirty solution.  Currently CDS is also not open to querying from Excel’s Power Query, and Power BI can only see the standard entities.  This means that many reporting options will not be available to you if CDS is used.  This is why I personally will avoid CDS at least until it will be possible to query from Excel.

My favourite so far is Azure SQL Database.  This is a fully fledged relational database with a lot of functionality.  It requires learning a bit of T-SQL, but there are tons of resources and examples out there for almost anything you need to do.  Setting up an Azure SQL DB is also relatively cheap and hassle free.  Microsoft is actively improving compatibility with PowerApps with every new update.  It is now possible to connect to views from PowerApps, so avoiding most of the delegation issues related to other data sources.

Please be aware that a great possibility with PowerApps is to mix and match your sources, so for example you could store basic table data in SharePoint (so that you can analyse/report with Excel), and store images in CDS.  Both sources can be accessed by PowerApps in the same app at the same time.

2 Replies to “PowerApps 101: where do I store my data?”

  1. I am grateful for the freely given perspective on the important few in the immense range of database options and their APIs available for PowerApps construction. As a beginning-level PowerApps user, it is surprising to see that the “native backend” CDS is NOT the best database option, given that PowerApps “runs” on it if it’s the backend. Given the performance issues currently in PowerApps, I’d be curious long-term to see how “mixing and matching” sources could be done in a way that doesn’t overload PowerApps. But maybe one step at a time and start simple with quick and dirty solutions for my internal business processes.

    1. Thank you Wyatt for the comments. In my understanding, PowerApps does not ‘run’ on CDS. PowerApps simply can use many data sources, including CDS. In actual fact CDS runs on Azure. There may be some advantages in using CDS with PowerApps, but the functionality and flexibility of Azure SQL Database is clearly superior in my opinion. The performance issues can be addressed with some techniques in this blog and others, first of all by appropriately caching data. Simple, quick and dirty, is always good to start with.

Leave a Reply

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