App Insights for Power Platform - Part 9 - Power Automate Licencing

Overview:  Licencing is extremely complicated, but there are threshold limits that are being reduced at the moment, August 2023.  

O365 users get get the lowest priority profile, can only run the standard connectors, and have a "request" limit of 6,000 requests per day.

What is a Request?

Each flow consists of a combination of triggers, actions, and responses when cloud flow is run, the instance walks thru the actions such as Create a SharePoint list item, setting variables, 

What counts as a Power Platform Request

"Here are some guidelines to estimate the request usage of a flow.

• One or more actions run as part of a flow run. A simple flow with one trigger and one action results in two "actions" each time the flow runs, consuming 2 requests.

• Power Automate Flows, by default, run in the context of the Flow Owner.  The "actions" are worked out against the Flow Owner.

• Every trigger/action in the flow generates Power Platform requests. All kinds of actions like connector actions, HTTP actions, built-in actions (from initializing variables, creating scopes to a simple compose action) generate Power Platform requests. For example, a flow that connects SharePoint, Exchange, Twitter, and Dataverse, all those actions are counted towards Power Platform request limits.

• Both succeeded and failed actions count towards these limits. Skipped actions aren't counted towards these limits.

• Each action generates one request. If the action is in an apply to each loop, it generates more Power Platform requests as the loop executes.

• An action can have multiple expressions but it's counted as one API request.

• Retries and additional requests from pagination count as action executions as well."

Here are my thoughts which seem to differ from the MS notes provide above: Not all Actions count as a request, If i look at the Power Automate Analytics it gives me a break down on the API calls to understand the "Request" counting.  Basically any action that does an API call when run adds to the request count.  

Guide for planning for limitations:

• O365 users get 6k request per days
• Dynamics and most per user plans get 40k requests per day.
• As a rough guide, I count simple workflows as 3 requests average, medium as 7 requests, large can be over 100 so it is better to build the workflow and from the analytics you can get the number of requests per day.
• For each flow multiply by the estimated number of calls
• Understand who the quest is attributed to (either the user or the owner of the flow, the requests are counted against the flow owner unless the flow use a pay per flow model.)

Example: to calculate billable actions/billable requests

I have a single Flow running against my O365, the flows has a Power Apps trigger, then creates a new list item and lastly responds to Power Apps.

1 Cloud flows that has 3 billable actions run 5 times will result in 15 billable actions.

I have 6k per 24 hrs on an O365 licence, most of the other licences such as Power Automate premium, an account has 40k requests per 24 hours.

I could run the flow 1,200 times in 24 hrs under an O365 licence.

Series

App Insights for Power Platform - Part 1 - Series Overview 

App Insights for Power Platform - Part 2 - App Insights and Azure Log Analytics 

App Insights for Power Platform - Part 3 - Canvas App Logging (Instrumentation key)

App Insights for Power Platform - Part 4 - Model App Logging

App Insights for Power Platform - Part 5 - Logging for APIM 

App Insights for Power Platform - Part 6 - Power Automate Logging

App Insights for Power Platform - Part 7 - Monitoring Azure Dashboards 

App Insights for Power Platform - Part 8 - Verify logging is going to the correct Log analytics

App Insights for Power Platform - Part 9 - Power Automate Licencing (this post)

App Insights for Power Platform - Part 10 - Custom Connector enable logging

App Insights for Power Platform - Part 11 - Custom Connector Behaviour from Canvas Apps Concern

Microsoft Dataverse (CDS) - Overview

Overview:  Dataverse is CDS, there is a long story on the naming but ultimately Dataverse is a data store with a advanced security model, Open API's, workflows, pipeline injection...  It is awesome.

It is high performance, and would take considerable effort and components to deliver similar functionality or even semi close functionality.  It does have limitations mainly around performance but don't let that fool you, Dataverse is fast and powerful but for massive industrialized storage it's not the right option.  The costs are also a key consideration.

The biggest mistake I see is people making the same mistakes as they do with relational databases namely: 

Poor Dataverse implementation down to 1) poor entity relationship design, 2) either too many table containing duplicate data or to few table being expanded for a dev teams capability but ignoring existing systems, 3) poor security 4) too many cooks.

Basically, like any Database service, you need to have owners and try keep the structure logical and expand it appropriately.  The idea behind the data model used by the dataverse is to have centralized secure shareable data like customers or account information.  It's simple, treat dataverse as you would your most precious core database, have an owner that needs to understand and approve changes.

Note:  Microsoft have had some trouble naming Dataverse, it was previously known as the Common Data Service (CDS).

Dataverse logo

Overview: Dataverse helps improve processes.  And Dataverse helps reduce time to build IT capability, remove shadow IT, improve security and governance.  Data is the common data store we need to use to be effective.  As part of the Power Platform, it allows us to build custom software fairly quickly.

Updated 07-July-2022

Dataverse provides relation data storage (actually runs on Azure SQL (Azure Elastic Pools), Cosmos DB, and Blobs), lots of tools e.g. modelling tools.  I think of it as a SQL database with lots of extra features.  Most importantly business rules and workflow.

• Dataverse relies on AAD for security
• Easy data modelling and supports many-to-many relationships NB!
• Easy to import data using PowerQuery compatible data sources
• Role-based data (previously called row) and column (previously called fields) level security.  See Dataverse security in a nutshell at the bottom of this post.
• Provides a secure REST API over the Common Data Model, it's awesome
• Easy to generate UI using PowerApps model driven app
• Ability to inject business rules when data comes in or out of the Dataverse (can also use .NET core code)
• Can also stored files (ultimately in blob storage)
• Search that indexes entities and files
• CDS used tables, Dataverse calls them Entities.  Some of the UI still refers to table.  Just assume Entity and Table are interchangeable terms.

Dataverse basically allows you to have a PaaS data hosting service that mimics what we have done for many years with databases and Open API, has advance features and tooling and it is all securely managed for you.  

The cons are basically: is that it is expensive.  So you need to know your size and keep buying add-ons to the plans.  Scaling Dataverse is expensive.

Common Data Model: Collection of tables (previously called entities and most CRM people still call them entities) e.g account, email, customers for a business to reuse.  Comes from CRM originally, the starting point consists of hundreds of entities pre-created.  Common standard for holding data.

Each Power Platform Environment has a single Dataverse associate to it.  It's a good idea to have more than one environment but at it's simplest, use a trial to learn and progress to production.

Once I have a new environment, I can use Power Apps to access my environments Dataverse and model out a new table to store info, I am storing people tax returns.

Go into the Dataverse and model directly

Model the table in you Dataverse instance

Dataverse Security in a Nutshell:

1. A user is linked from AAD to the User entity in the Dataverse.
2. User Entity record is aligned to the AAD User.
3. AAD Users can be part of AAD security groups.
4. Dataverse Teams (Dataverse Group Teams) can have Users and or Security Groups assigned.
5. Dataverse Group Teams are aligned to Business Units.
6. Business Units have roles (rights).

"Security is additive" in Dataverse (generally the whole MS and security world these days).  i.e. no remove actions.  If you have permission in any of the groups you can access the data/behavior.

Business Units used to restrict access to data.  Can be hierarchical i.e. Enterprise > Audit > EMIA > UK (Don't use it like this, keep it simple)

Security Roles define a users permissions across the Dataverse entities i.e role can read only from Accounts entity 

Teams consist of users and security groups.  That get assigned roles.  There are two types of Teams in Dataverse: Owner teams & Access Teams

Field-level security, only allows specified users to see the field data

https://learn.microsoft.com/en-us/power-platform/admin/wp-security-cds (Good clear post on Dataverse security, core concepts are Business, Units, Teams, Roles, Users & OAuth/AAD)

AAD Conditional Access

What is Conditional Access on AAD: Microsoft AAD with conditional access allows for users or groups to verify themselves more securely as after the login attempt an additional check is required to identify if the account may be compromised/at risk or is good.  Microsoft use algorithms and a ton of collated information to determine the risk on the attempted login.  A simple example would be a users location is unusual or logging in from different places in the world in too short a period.

• First factor Authentication happens before conditional access. 
• Setting up conditional AAD access 
• Conditional Access is part of Azure MFA
• Configure conditions for access
• Easy to bypass MFA if a used is a ADFS federated user or coming from a specific IP range (head office location) or region.  Can also allow a one time bypass if a user loses there phone.
• Required Azure AD Premium licences

Azure Active Directory, B2C and Rights

Azure Identity Management is a fairly large body on knowledge.  Basically, dividing it into different areas makes if easier to understand.

RBAC in Azure:
Azure AD and B2C bother offer a way to authenticate a user thru the user providing an identity.
The user is assigned to 1 or more groups, and then the groups (or individual users) are assigned to Roles.  The diagram below shows internal and external users and how permissions can be given out.  Resulting in Role Based Access Control (RBAC).  The application itself deals with the operations a user can perform but having the users role/claims allows the individual applications to figure out what action the user can perform.

RBAC can be assigned at 1 of these 4 levels to manage Azure Resources:

Tip: For small Azure Tenants, managing resources are the resource level works well, but in most enterprises, you should mange at the Resource group or even subscription level to keep management controllable.

Note: There is the concept of "Directory", multiple "Resource Groups" are setup to a directory.  I believe all companies should have a single directory but it is more common to find even relatively small businesses common to have multiple directories. 

"Multiple subscriptions can trust the same Azure AD directory. Each subscription can only trust a single directory." Microsoft Docs

Adding users to all new SQL database using Azure AD groups

Problem:  I have a dedicated SQL 2017 VM on Azure that is joined to my Azure AD tenant e.g. int.contoso.com (Azure AD Domain Service).  I need a set of users to have read and write access to all databases that get provisioned on the SQL 2017 instance.

Initial Hypothesis: 
Create an Azure AD security group and add all the AAD users and
Add the AAD group to the Model database with the permissions that all new database should have.

Resolution:
1. Using Azure AD create a new security group, I called my group developers and add the users as members Fig 1.& Fig2.

Fig 1. Azure Portal, go to Azure AD and Groups

Fig2. Add the security group

2. Add the AAD Group e.g. int.contoso.com\Developers to the System "Model Database", I have given the group read and write access below in Fig 3.

Fig3. Add permissions to the Model DB

3. Create a new database and validate that the new permissions are added to the new database as shown in fig4.

Fig4.

Note: Changing exsiting DB permissions
To add permissions to existing database, an option is to run
EXEC sp_MSForEachDB 'exec sp_addrolemember ''db_datareader'',''INT\paul.beck'''
T-SQL to list of Daatbase: EXEC sp_MSforeachdb 'USE ? SELECT SF.Name FROM sys.databases SF'

Switch Master Page Minimum Permissions

Problem: Use the Client Side Object Model (CSOM C#) to add a new master pages to a site collection and switch the master page.

Initial Hypothesis: Writing to a site collection only required contribute rights or even "designer" rights at the web application permission level.

Resolution: The minimum permission set for changing master pages is "Full Permission" which a site owner and the site collection admin have.  So to switch master pages you need a high set of permissions.  UI allows master pages to be switched when the user only has "Design" permissions. This proof is flawed as the UI and CSOM permissions are different.  Can the UI have different permissions to the CSOM API???  Am I going mad.  
SPWeb object with Design user permissions cannot be updated and the API returns an "Access Denied Error" - Thanks to Sachin Khade for identifying this.

Updated 26/05/2017:  So the reply I got from the engineer who raised a Microsoft ticket is "SharePoint designer and  SharePoint GUI only need to have design permission to change the master page. This is because SharePoint designer is created as an extension of the SharePoint product. However, since CSOM calls are coded using Visual studio, the code flow involved in this is different and hence requires permissions that are higher than what SPD needs."

Summary: "Design" rights allow the user to change the master page using the UI however the same user cannot switch the master page using the CSOM C# approach.

Updated 26/05/2017: Thanks to Aswin Bhaskaran for working out a minimum permission set for using CSOM to switch the master pages on a site collection:

Note: "Design" rights can be applied at the Web Application Policy level allowing the accounts with "Design" rights the ability to add master pages.  The "Design" permission is only built into SP at the Site Collection level, I created the "Design" permission with the same permissions at the web application level to ensure my account in the Web app Design group has access to all site collections on my web app.

Note: Microsoft do not recommend customized master pages for O365 or future development.  Rather inject JavaScript to modify pages.

SharePoint Hosted Apps vs Embedded JS

Overview: The use of Apps (specifically SPHA) in SharePoint seems to be misunderstood, developers and architects often want to use the App model for functionality that folks have built using previous versions of SharePoint.  Apps are reusable pieces of custom logic akin to a specialised document library.

The app needs to be deployed to the catalogue store and permissions granted to leverage SP functionality.

SharePoint Hosted Apps (SPHA) are the internal sub web created with SharePoint, that can use JavaScript to perform customisation.

For example I want to read values from a term set, you can simply embed JavaScript and using the current users context get the term set data you want.

Permissions in SPHA run in the context of the current user as opposed to Provider Hosted Apps that can run in either: current user context, app context or app and current user context.

Deployed JavaScript will perform exactly the same when called from a page or from a SharePoint page or from within the SPHA (app web).  JavaScript runs in the context of the current user for both approaches.

The following embedded JavaScript works both in a web part page or in a page inside a SPHA (app web):

<script type="text/javascript">
var termSetName = //document.getElementById('termsetID').value;
var locale = 1033; // your locale. Here is English
var context  = SP.ClientContext.get_current();  //User the current users context.
var taxonomySession = SP.Taxonomy.TaxonomySession.getTaxonomySession(context);
var termStore = taxonomySession.getDefaultSiteCollectionTermStore();
var termSets = termStore.getTermSetsByName(termSetName, locale);
var termSet = termSets.getByName(termSetName);
var terms = termSet.getAllTerms();
context.load(taxonomySession);
context.load(termStore);
context.load(termSet);
context.load(terms);
context.executeQueryAsync(function onSucess(){
  var termEnumerator = terms.getEnumerator();
  var termList = "Terms: <br/>";
while(termEnumerator.moveNext()){
var currentTerm = termEnumerator.get_current();
termList += currentTerm.get_name() + "<br/>";
}
Windows.alert(termList);// Output to the screen                                 
                },function onFailure(args){
                    // Notify user of error
                });         
}

The user only needs to be a visitor to have read access to the term store.  JS works in the same way whether inside an SPHA or within a page on a SharePoint site.

“Apps that do not make OAuth authenticated calls (for example, apps that are only JavaScript running in the app web) cannot use the app-only policy. They can request the permission, but they will not be able to take advantage of it because doing so requires passing an app-only OAuth token. Only apps with web applications running outside of SharePoint can create and pass app-only tokens.”  MSDN article

JavaScript inside a SPHA can only run within the context of the current user.
Provider-Hosted Apps (PHA) can use either:

• context token (user context)
• user+app access token
• app-only access

This was spoon fed to me from some good folks I'm working with Nick, Sachin & Peter- thank-you.