SharePoint Online as Data Store for Power Apps
Problem
I need to design data store for a PowerApp on multiple environments (i.e. DEV/UAT/PROD) using a single SharePoint Online production tenancy.
Solution
1. Use a single-site (one environment per SPList)
Pro:
P1.1. SPList can be copied to another environment via browser UI.
P1.2. Site-column internal name (SPList JSON schema) is consistent across all environment.
P1.3. Site content-type cascaded update via browser UI will allow refreshing SPList content-type in all environments if required.
P1.4. Adding new site-column can be implemented via browser UI.
P1.5. Document Library schema can be copied via browser UI leveraging site-content type.P1.6. All environments permissions are managed via Site-Groups within a single-site.
Cons:
C1.1. Changing existing site-column can accidentally change all environments.
C1.2. Changing existing site content-type can accidentally change all environments.
C1.3. Site-administrator (Full Control) must ensure lookup site-column is not shared across environments.
C1.4. All environments (i.e. DEV/UAT/PROD) are contained within one site hence the site-administrator will have full-control over all environments.
When to use:
W1.1. When all environment is managed by the same team/user account.
2. Use multiple-sites
2.1. One environment per site-collection
Pro:
P2.1.1. Changing existing site-column cannot accidentally change all environments.
P2.1.2. Changing existing site content-type cannot accidentally change all environments.
P2.1.3. Lookup site-column is isolated in its own environment.
P2.1.4. Each environments (i.e. DEV/UAT/PROD) is contained within one site hence site-administrator will have full-control over that enviroment only. Suitable for SPO team of two or more persons to maintain data-store security.
P2.1.5. Each environment permissions for service account or data-store administrators is managed via Site-Groups on its own site. This will allow separate service accounts or data-administrators to maintain each environment.
Cons:
C2.1.1. SPList cannot be copied to another environment via browser UI as site-column will become list-column.
C2.1.2. Site-column internal name (SPList JSON schema) can be inconsistent across all environment if not scripted or migrated with third-party tool.
C2.1.3. Site content-type cascaded update via browser UI will not allow refreshing SPList content-type in all environments if required.
C2.1.4. Adding new site-column cannot be implemented via browser UI hence PoweShell script or third-party tool is required.
When to use:
W2.1.1. When each environment is managed by the different team/user account.
W2.1.2. When organisation have PnP PowerShell developer or third-party tool to maintain SPList and Document Library schema.
2.2. One environment per sub-site
Pro:
P2.2.1. SPList can be copied to another environment via browser UI however must use root-site content-type defined.
P2.2.2. Site-column internal name (SPList JSON schema) is consistent across all environment.
P2.2.3. Site content-type cascaded update via browser UI will allow refreshing SPList content-type in all environments if required.
P2.2.4. Adding new site-column can be implemented via browser UI.
P2.2.5. Document Library schema can be copied via browser UI leveraging root-site site-content type.P2.2.6. Each environments (i.e. DEV/UAT/PROD) is contained within one sub-site hence sub site-administrator will have full-control over that enviroment only. Suitable for SPO team of two or more persons to maintain data-store security.
P2.2.7. Each environment permissions for service account or data-store administrators is managed via Site-Groups on its own sub-site. This will allow separate service accounts or data-administrators to maintain each environment.
P2.2.6. Each environments (i.e. DEV/UAT/PROD) is contained within one sub-site hence sub site-administrator will have full-control over that enviroment only. Suitable for SPO team of two or more persons to maintain data-store security.
P2.2.7. Each environment permissions for service account or data-store administrators is managed via Site-Groups on its own sub-site. This will allow separate service accounts or data-administrators to maintain each environment.
Cons:
C2.2.1. Changing existing root-site site-column can accidentally change all environments.
C2.2.2. Changing existing root-site content-type can accidentally change all environments.
C2.2.3. Site-administrator (Full Control) must ensure lookup site-column is not shared across environments.
C2.2.4. All environments (i.e. DEV/UAT/PROD) are contained within one site-collection hence the site-collection and/or or root-site administrator will have full-control over all environments. Only a service account to provision changes in PROD should have site-collection administrator access. Root-site administrator is only for adding/modifying site-column and site content-type on root-site.
When to use:
W2.2.1. When all environment is managed by the same team/user account.
W2.2.2. When root-site administrator can be shared among all environments.
W2.2.3. When site-collection administrator can be limited only to Global Admin/SharePoint Admin.
Tips and trick:
T1. Create lookup column on SPList if you have to use existing Display Name on sub-site or root-site.
T2. Use existing site-column first if suitable.
T3. Lookup column multi-values export to CSV may contain a single value. I think it's a bug (tested on 21 Jan 2022). To workaround this, use a projected value (i.e. Tag:Title) instead of the lookup column (i.e. Tag).
Comments