For the world of Power Apps users, the Common Data Services (CDS) might be more understandable, however, in the world of Power BI users, we still have many who don’t know what it is and what is the use cases of it. So I thought better to write about it in this article, and explain what is CDS, what it means for you if you are a user of Power Apps or Power BI? and how you can use it? If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.
What is Common Data Service?
Common Data Service, abbreviated as CDS, is a data storage service. Like a database. You can use CDS to store data in the form of tables, which is called as Entities. Common Data Service is a service that is used mainly in the Power Apps portal, however, it is accessible through other Power Platform services and Microsoft Dynamics. the data can be loaded into CDS entities through multiple ways, and it can be also extracted from there through different methods. So you can say it this way that; CDS is a data storage and retrieval system, like a database.
Common Data Service (CDS) is a data storage system, like a database.
CDS includes a set of base entities (tables), but you can add custom entities to it. You can access CDS through other Power Platform services (Power BI, Power Apps, Power Automate…) and some other Microsoft services.
Why CDS is important for you if you are using Power Apps?
If you are using Power Apps, then it means you are creating a mobile application. The mobile application is most likely to work with data, capture information from the user through a data entry form or something and it needs to store the data somewhere. You would need to have a database system that you can store your data and retrieve it.
CDS is free storage for you in the Power Apps environment because you are already paying for the Power Apps license, then you can use CDS for free.
Of course, you can go and build your database in other systems, such as Azure SQL database, but then you need to pay for that service separately, or you might prefer to keep it on-prem in a SQL database, which then you would need to set up a gateway to use it. The choice of which database to use for your Power Apps app is up to you. However, CDS will give you a free, and easy-to-use database system to work with, and build your apps using that.
CDS is the free database service that you can use in Power Apps to store and retrieve the data of your apps.
So in a nutshell; CDS stores your Power Apps data at no extra cost, it is easy to manage. You don’t need a database developer to go and build a database for you to load your data into it. It is a data storage system that can be used by a citizen application developer.
You don’t need to know about databases, or be a database developer, to use CDS. It is built for the citizen app developer.
What the CDS database management system looks like?
Like many other database systems, CDS also has a management tool, which you can go and see entities, edit them and manage them. At the moment, you can use Power Apps portal for this management portal, and you will find CDS under the Power Apps portal like below;
What is the point of CDS if you are using Power BI?
There are two aspects of using CDS if you use Power BI. One is to use CDS as a data source system.
In the world of Power BI, we don’t store the data. We do, however, get data from a data storage system to analyze it. And that is why most of the people in the Power BI world, might not be familiar with CDS, because, from their point of view, this is just another database system, just another data source to get data from.
CDS is another data source that you can use when you Get Data in Power BI.
Another aspect of using CDS in Power BI is to use it as intermediate storage. Intermediate storage for your Power Query transformations. I have explained previously that why you might need to decouple your Power Query transformation layer into Power BI dataflows. Using CDS, you can store the output of dataflows into CDS, like a database, or let’s say, like a data warehouse, and use it for further analysis.
CDS can be your data warehouse if you use dataflows.
I highly recommend you to reach the article I wrote about decoupling the data transformation layer, data modelling layer, and visualization layer in Power BI implementation, which explains how dataflows can be an essential part of implementation for multi-developer architecture;
And the concept of dataflow is nowadays, not just for Power BI, but also for Power Apps. It is Power Platform Dataflows;
What is the storage engine behind the scene for CDS?
CDS stores the data, retrieve it and controls it using Azure services. There are a number of Azure services that are involved with this. Azure SQL DB, SQL elastic pools used for relational data, Blob storage for the non-relational data, and CosmosDB for logs. the screenshot below from Ryan Jones session at Microsoft Ignite 2019 explains how things placed together:
Ways to Load data into CDS
Because CDS is a storage system, you might ask how you can store data into it? what are ways? here is the answer:
- Power Apps app. You can build an app using Power Apps that store the data into entities of CDS.
- Power Apps portal using Get Data and Power Query experience.
- Dataflow: In Power Apps portal, you can create a dataflow and schedule it to load data into CDS.
- Other services
- …
Way to retrieve data from CDS
You can extract data from CDS in many different ways, including:
- Power Apps app; You can have forms in your app that show the existing data from entities of CDS.
- Export data from Power Apps portal
- Dataflow
- Power BI, Get data from Common Data Services
- Other services
- …
How much does it cost for you?
If you are using Power Apps, then you have a license that covers also CDS, so you don’t need to pay anything extra. However, different licenses have different limitations. here you can find out more about it.
If you just have Power BI license, then at the moment of writing this article, the only way for you to use CDS, is to pay for Power Apps licenses. However, remember, for getting data from CDS, you don’t need the license (because someone already created the CDS and is paying for it). You would need a license if you are writing into CDS (through dataflows maybe, which needs its own blog article, which I’ll explain later in another post).
Summary
Common Data Service (CDS) is a database system. this database system stores the data in Azure data lake storage (cloud), and you can work with it through a management portal. CDS is a free database if you are using Power Apps licenses, You can then analyze the data of CDS using Power BI. CDS can be also used as a data warehouse layer using dataflows. In other blog articles, I’ll explain more about that scenario.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Great in-depth explanation.
Thanks Rada
Thanks for your comment 🙂
Hi Reza, thanks so much for this article. I’m very new to PowerApps (but am more familiar with Power BI and I understand many of the concepts you mention like dataflows and gateways). I’ve been tasked with creating a simple customer portal for customers to enter their own sales orders in shows using a tablet or phone. So if I understand the concepts, I would use the Portal type of Power App, and user the SQL server data source that is used by our ERP system to directly create a quote, which then internal users can review, correct, and then use existing ERP function to convert quote to an actual sales order.
Does this approach seem logical to you?
Hi Andres
I am not the best person to answer ERP or PowerApps questions. I believe this should be asked from a PowerApps expert
Cheers
Reza
Can CDS work for Android phones
CDS is not an app that works on a device. It is a database system. for connecting to that database system, you need to develop an app, which can be Power Apps, and yes, Power Apps, can work on android devices.
Cheers
Reza
The problem with CDS is Power Query in Excel doesn’t support it, and in almost every solution we are building with the Power platform, there is a bit of Excel in the workflow, and CDS prohibits that. Getting it out of Power BI doesn’t work either. Needs its own transformations in excel and sometimes dropped to a table vs Excel’s data model.
Hi Ed,
I hear you and agree that the support of CDS in Excel is one of the most needed. at the moment, we can use API connection from the web to get it, but there should be much easier straightforward way for it. I believe, however, there will be some updates for this sometime in the future, and we will have it at the end.
Cheers
Reza
Thanks for this article..Really helpful..
Thanks for this article. Now this topic became more clear for me.
😊
Hello! This was a great post that let me understand a lot more. The following is still not clear to me, is the common data service included for apps developed with the powerapps license included in Office 365 Business Premium?Can I build a database up to 50MB and have all my users with a premium license use that? Thanks!
Hi Pablo
The question is how the other users are going to use that database? would there be an app for it that everyone uses? in that case, they would need PowerApps license. But if they only use the data through Power BI or any other places, then correct.
Cheers
Reza
Hi,
Thanks for the article. really helpful.
Can you please let me know, if can connect to this CDS database from SSMS?
Thanks.
Hi,
The short answer is no (not at the moment).
the long answer is that: You won’t need to connect to CDS using SSMS.
SSMS is a database management tool that you connect to the SQL Server database and manage entities, views, etc because the SQL Server DB engine itself doesn’t have the admin panel.
with the CDS; the admin tool is already in the Power Apps portal, you can manage everything there, entities, views, etc.
Cheers
Reza
Great article. Allows me to understand more on this CDS thing.
However, can I use this CDS entities like any SQL tables and apply complex query to it? I wish to reuse my SQL query instead of using the new transformation tool (Power Query).
Thanks in advance.
Regards,
Max
If you want to query the CDS Data, You can use Power Query.
I am not aware of any SQL statement that you can run there, this is not a normal RDBMS, so it might be limited on that side.
Cheers
Reza
In summary I think CDS is a Self-Service data storage solution, so we don’t need to have advanced knowledge about databases.
Yes, and no.
It is built for citizen developers to use. which means people with no advanced knowledge about databases.
but to get the best result, following best practices is always a good idea, which brings the fact of database normalization concepts and the need for it.
Cheers
Reza
Hello, Reza
Nice Explanation, I want to ask you that I am developing a PowerApps application for an ogranization and they are using SQL Server Connector for that as their dataSource, will CDS can be replaced with that to fulfill the requirements?
and before that I test developed the app by using Excel DataSource, but then they said to use SQL Server as a DataSource.
which approach will be easier. as I have to change almost all of the formulas for SQL Server, which I had used when I was developing that application via Excel file.
Kindly Help!
Yes, CDS can be a replacement for your external SQL Server DB. However, if the app is already built with the SQL Server backend, there is a cost of time and effort to change it to CDS.
Cheers
Reza
Hi Reza, you have explained CDS in so simplified way. Thank you so much !
Thanks 🙂
Hi ,
Nice explanation!!!
Would be great if you will address my below questions
1. You said, dataflow can be refreshed as many times times one wants, now the question is will on every refresh
same record available in source will be added to the entity, if yes, how to remove duplicates
E.g.
-> Custom Entity say Country is created via DataFlow say MyDataFlow.
-> Source used in DataFlow1 is say Excel at One Drive having five records
– > Every time, i refreshed the data, same five record has been added to Entity country, i.e. on first
refresh, number of record is 5, on second refresh, same 5 record has been added and count reaches to 10 (if no
new data in source), what is siginificance of this.
2. What is the significance of Entity Mapping “DO NOT LOAD” while dataflow creation.
3. How Power APP DataFlow will be used in Power BI Report using Power Platform DataFlow not as CDS source.
Thanks
Amit Srivastava
Hi Amit
If you do not want to have duplicate entries in your CDS entity, then you should set an Alternate Key in the Entity mapping.
if you want the data to be synced with the source, you should also select the “remove rows that no longer exists in the source”
Cheers
Reza
Agreed that this is one of the great post to quickly understand the concepts of power platforms and apps services.