Managing Microsoft PowerApps and Flow like a pro – Part 4

In the first three parts of this blog post series about managing Microsoft PowerApps and Microsoft Flow, I have focused on introducing the concept of environments, explaining what is inside environments, and looked into managing Flows.

What is Common Data Service (CDS)?

If you’re asking yourself “What is Common Data Service (CDS)?“, in essence, it is a data management platform in the cloud, where it is easy to create entities and their properties, to make relations between them, to create validation rules, field calculations and much more.

CDS is very well known to Dynamics 365 users since this is where Dynamics stores its data.

For the old-schoolers among you: If you think of CDS, PowerApps and Flow as a “new Access”, but in the cloud, you wouldn’t be completely wrong. Common Data Service is used for data definitions and storage, PowerApps for the forms and UI, and Flow for the business logic behind that.

Now, the SharePointers among you might ask the question: why do we need another structured data store if we already have SharePoint lists? It is a valid question, and the answer is not one-dimensional. On the pro side, Common Data Service databases can contain relationships, and model-driven PowerApps can use those relationships to create master-detail, multilevel applications in a very easy way.

The SharePoint PowerApps and Flow connector is able to expose only one list at a time as a data source, so if you need multiple lists working together (for example, for master-detail), you need multiple data sources in your PowerApps and a lot of patience and knowledge in order to make them work together – even simple cascading lookups become an issue there. Second, accessing CDS data is faster than accessing SharePoint lists.

On the other hand, SharePoint lists are already there. Everyone is using them, and there is already a ton of data residing in them. They are also convenient and are easier to create and look more beautiful (at least in the modern SharePoint sites) than the table-like CDS interface.

And last but not least, there is the question of licensing: Common Data Service requires a PowerApps / Flow Plan 2, on top of Office 365 licenses, which can be quite pricey. So, with all the pros and cons, it is basically up to the architect to figure out what is better to use in which situation.

Where is my common data stored?

As you can see in the picture below, the Common Data Services data resides on Azure SQL Servers, but it is wrapped and exposed for our use in a very user-friendly manner.

CDS databases, in the PowerApps and Flow world, are always tied to an Environment. One environment can – but does not always have to – contain one CDS database, but not more than one. Remember that, if you want to use model-driven PowerApps in an Environment you will need to create a CDS database for that environment.

As we see in the schema above, the two environments – “Contoso USA” and “Contoso Canada” each have a CDS database (the abbreviation “CDM” is for Common Data Model, which is basically the data model inside a CDS database). Contoso Australia, on the other hand, doesn’t have a CDS database.

How do we manage CDS databases?

What can be done to track and trace the CDS databases in your tenant? Each environment admin can add CDS databases, and you will want to be in control, or at least know, what is going on.

There are a few important things to remember:

• You can only add a CDS database to environments, you cannot delete it
• The only exception to the previous rule is that you can delete “old”, legacy CDS databases (the new version of CDS is significantly different than the previous one)
• Once you create a CDS database in an environment, you cannot change its main properties, which are “language” and “currency.

When I say you cannot do it, I really mean it: there is no way, neither in the UI, nor through PowerShell, to delete a CDS database or to change its properties. If you really want to get rid of the database, you need to delete the whole environment.

So, let’s find out how we see which environments have a CDS database.

In the first article in this series, when I was talking about environments, I introduced you to the “Get-AdminPowerAppEnvironment” PowerShell cmdlet. Besides all the other information this cmdlet returns about the environments, it also tells you the status of your CDS databases within environments.

Note: To be able to use this cmdlet, you need to install the following PowerShell modules: Microsoft.PowerApps.PowerShell and Microsoft.PowerApps.Administration.PowerShell, and you have to connect to your environment. I have explained in the first article of this series how to do it, so I will assume that you have already done it and you have your PowerShell setup to work with the Power Platform and environments.

You can see that there are two return properties of the environment that are interesting for us here: “CommonDataServiceDatabaseProvisioningState” and “CommonDataServiceDatabaseType”.

There are different database types for the CDS, however, the one which we will get here after provisioning the database is “Common Data Service for Apps”.

The Provisioning State property tells you if the database provisioning has succeeded, and what is its current state. This property is an enumeration, which can have the values listed below. Note: PowerShell will often list more user-friendly provisioning statuses (such as “Linked Database Provisioning” instead of “Creating”), but in essence, they are all wrappers around the 6 following states:

• Accepted
• Creating
• Deleting
• Failed
• Succeeded
• Updating

Note: even if there is no CDS database in the Environment, this property will show “Succeeded”, as we can see in the screenshot above.

One useful cmdlet to get all the Environments without a CDS database is:

Get-AdminPowerAppEnvironment | Where-Object {$_.CommonDataServiceDatabaseType -eq "None" } | Format-Table -Property DisplayName, CreatedTime, EnvironmentName

As a result of this cmdlet, we’ll get only those environments where a CDS has not yet been created:

We can, of course, easily change the where condition above, to get only those environments with a created database.

Creating a New CDS database

So, let’s create a Common Data Service database in the environment using PowerShell. The cmdlet we want to use here is:

New-AdminPowerAppCdsDatabase

There are a few parameters we need to pass to this cmdlet:

• EnvironmentName – this is the guid of the environment where you want to provision the database.
• CurrencyName – which currency will be used in all currency field formats and calculations inside the CDS database
• LanguageName – which language will be used for the CDS database
• WaitUntilFinished – should PowerShell wait until the CDS database creation is finished, and then continue, or should it continue the script while the database is still being created in the background.

As we see, the EnvironmentName and WaitUntilFinished parameters are self-explanatory. But what should we do with the languages and currencies? Especially because not all ISO languages and ISO currencies are supported within PowerApps and Flow.

And, to make it even crazier, not all languages and currencies are supported within each location where environments can be created (there are differences between locations in this regard).

Luckily, we have cmdlets which help us with that:

Get-AdminPowerAppCdsDatabaseCurrencies

This cmdlet will return us all the currencies which are available inside a specific location. In the next screenshot we can see all the currencies available in Europe:

The same is true for languages – the “Get-AdminPowerAppCdsDatabaseLanguages” will list all the languages which are available within the location:

Now when we know this, we can proceed with creating the CDS database in this environment. We will use Croatian as the language, and the Croatian Kuna as the currency:

New-AdminPowerAppCdsDatabase -EnvironmentName $YourEnvironmentId -CurrencyName HRK -LanguageName 1050 -WaitUntilFinished $false

Now when we try to get our newly created environment, we will see that the CommonDataServiceDatabaseProvisioningState property has changed to “LinkedDatabaseProvisioning”.

Now, in order to track the changes in the CDS database provisioning state, I have made a simple while loop to see when that state changes, and to what it changes to. Less than 5 seconds later, the database has fully provisioned, and the CommonDataServiceDatabaseProvisioningState property changes from “LinkedDatabaseProvisioning” to “Succeeded”, and CommonDataServiceDatabaseType changes from “None” to “Common Data Service for Apps.“

Note: if you try to create a CDS database in an Environment that already contains one, you will get no error message, the New-AdminPowerAppCdsDatabase will just list the data from the existing CDS.

Is there anything else to it?

Only one more thing: if you have legacy CDS databases inside the environments, which are created with the old version of CDS, you can – and you should – use the “Remove-LegacyCDSDatabase” cmdlet. The data can be exported or updated to the new version.

Conclusion

When can all of this be useful? Think of all the scenarios where we have automated provisioning of environments, as discussed in the first article of this series. We will then probably want to automate the provisioning of the CDS databases, as well. Or, even if there is no automated environment provisioning in place, we can still have a policy that each environment needs to have a CDS database with precisely specified properties. These sorts of things.

In the next and final article of this series, we are going to talk about managing Data Loss Preventions within PowerApps and Flow environments.