Reporting from Dataverse HLD options

Overview:  Recently I was looking at reporting from the Dataverse/CDS and I drew up these options that give the business various options but the overhead grew as the solution improves.  Client is on the MS stack so I have looked at the reporting options, There are a ton of variations but this is a good start for my options.

Reporting options from the Dataverse

Synapse, can be replace by any Data warehouse or Data lake solution and as Dataverse is not massive could also just use regular SQL Server for reporting.

Power BI can be replaced with other tools such as Tablau but for embedding and the MS stack, Power BI makes the most sense.

Continuous Validation with Azure

I listened to Jen Perrin from Microsoft today with Scott Hasselmann on testing for Power Apps from the Ignite 2022 conference.  Chaos Studio is a tool to faking issues such as network faults, and coming backup after say 10 minutes, and it helps determine the behavior that happens.  

I have been thinking and doing continuous monitoring for awhile in SaaS products, thus can be as simple as checking you API's and SPA's are working and checking every few hours and notifying key people as early as possible.  More advanced options are running postman and/or selenium tests that run thru various tests such as login in add and cleaning up some data.  Continuous Validation is fantastic as you can perform scenarios from a specific target.

The tooling and Playwright (which i played with a few years ago) on Azure looks amazing and well suited for the Power Platform.  This is a massive area to improve software, and reliability. 

Power Apps Portal and Power Automate licencing thoughts

Update: 13 August 2023 - Power Apps Licencing has change considerably since this post, for example here is updated information on Power Automate licencing.

My Technical Working Notes for Microsoft Technology: App Insights for Power Platform - Part 9 - Power Automate Licencing (pbeck.co.uk)  A major change in naming and cost has been release August 2023.

Overview:  The Total Cost of Ownership (TCO) is key to any project.  I recently was asked about a project that wanted to use Power Apps for external user access, the data is in the dataverse/CDS) and their are tons of workflows.  The cost of buying a per app licence for external users is a non-started (without volume discount $5 or £3.80 per user per month  and the only viable options left are:

Design by Cost:

Write the front end using free to distribute front end application (i.e. ReactJs, Angular, C#, Flutter, Blazor,...) and all the flows using the per-flow licencing model.  Per-flow licencing is crazy expensive as it is per flow and my users have lots of flows when the login once a year.  So per flow is also not an option with a custom front-end.  At Ignite 2022, Microsft announce a new Power Automate Embedded SDK with pay as you go pricing, this may be a good option, but's its untested.

The $5/month/app plan doesn't really work as users login over the last few months of the year and all users would need to be licenced all year around so $60 is way too high.  The Power Apps Pay-as-you-go Plan allows costs $10/£7.45 per user that logs in during a month.  So in my case an average user logs in 2 times in a year with the monthly consideration, so i'm still looking at $20 per year.

User/Per App/month: £3.80

User/Month (multiple apps): £15.10

Active User/app/month Pas-as-you-go  £7.45

Power Apps Portals/Power Pages/Dynamics Portals is my last hope.  The licencing is based on per login per 24 hrs.  So a user that logs in three times in a 24 hr period is considered 1 licence.  If the user logs in on 4 different dates at any point in the year, the client is changed for 4 logins.  Need to buy the licences in advance each month.  Sold in blocks, the smallest being 100 logins in a month.  At the lowest levels, cost is $2 per 24 hour login, but this reduces to a much lower cost relatively quickly.   With our expected numbers, we'd get onto Tier 3, and the cost per login is $0.70 per login.  If an average user logs in 4 times a year on separate dates, our cost is basically $2.80 per user per year.

Note there is also a Page Portals Capacity licence required.  We are expecting an average of 175k page views, which cost a further $200.

13 Oct 2022: Licencing for Power Pages change yesterday, 1) monthly anonymous active users 2) monthly authenticate active users.  Prepaid is cheaper than pay as you go, assuming you get you numbers right.  https://www.microsoft.com/en-us/licensing/news/power_pages_general_availability#:~:text=Today we are announcing licensing and pricing details,to purchase through prepaid subscription and pay-as-you-go plans.

Summary: In this case, Power Pages is the best option.  

Model Apps - Controlling the UI with JavaScript

Problem:  I have a form of view in a Model Driven App (Power Apps) that needs to show 1 of 2 tabs depending on a control in the form having a value.

js> If the "Status"="Active show the "Agent" tab else show the "General" tab

Hypothesis: inject JS into the specific form that will check the status display control, and if active, get the Agent tab via the DOM, and hide it.

Solution:

1. Ensure you have two tabs on your form, my form is called "Information", see below

2. Create a JavaScript file to manipulate the DOM, my example is below:

Create a .js file to upload

Reference Blog for most common JS CRM work

3. Upload the file and publish.

4. Bind the JS function on the form or page being adjusted.

5. Result when the Status is active to hide the "Agents" tab.

Generating a Canvas App from a Custom Connector (Open API)

Overview: The Power Platform CLI in August was updated and 1 of the new features is the ability to use the CLI command line to generate a Canvas app using an Open API as the data source.

The Power CLI supports the GET and POST endpoints.  Which is normally +85% of the endpoints anyway.  

In this post I walk thru the steps to add books using an Open API and retrieve all the books as well as the new book added.  

Solution: 

1. I used the free postman API endpoints (2 gets and a post) > Exported the collection

2. In Power Apps > using a Solution > Add a new Custom Connector (tip: I used Automate)> Upload from a Postman Collection > Pass in the Postman collection generated in the last step.   Save the Custom Connection as shown below.  Lastly test the Custom Connector.

3. I used Visual Studio code with the Power Platform Extension

Using the Power Platform CLI, in the terminal run ps> pac to verify the Power Platform extension is available.

// Get the environment you want to work on

ps> pac admin list

// Get the Custom Connector Name or Id you are going to base the Canvas app on

ps> pac connector list --environment a9adbbba-c45d-eac1

// Generate the msapp package

ps> pac canvas create --msapp "C:\Radimaging\BookDemo.msapp" --connector-display-name "BookDemoCN" --environment "a9adbbba-c45d"

4. Import the export Power App.  Select your environment, and create a new Canvas driven Power App.  "Open" has recently changed in the UI.

The app will get created, I had to add the Custom Connector to the App and you are ready to customise

Beware: Custom Connectors and Connection References in Managed Solutions.  It's a good idea to check they work in the deployment pipelines as the rework can be a pain.  

Connection references in solutions - Power Apps | Microsoft Learn

Summary: This is a great way to build an application quickly using the pac create.  It is a bit rough, and needs amending after import on the 3 endpoints I've used but a great start to a new feature. 

More Info:

https://learn.microsoft.com/en-us/power-platform/developer/cli/reference/canvas

Power Platform Added Features

 August/September 2022 has been a busy month for the Power Platform

Here are two experimental features I think are massive: Named formulas & ParseJSON

New upcoming feature

ParseJSON

Coauthoring improvement will also be important for enterprise development.

October 2022: 

Power Pages are now in GA (Genral Availability)

Nearly 700 Connectors

A few new features/functionality I like: 

Generating a Word Document from a Row change in Dataverse without SharePoint

Problem:  Dataverse/Dynamics has a great way to generate Word or Excel documents in Model apps using the UI.  I want to generate a generate letter from a CDS entity/Table when the status changes.  And I'm not allowed to use OneDrive/SharePoint (governance restriction).

High-Level Steps:

1. Create Dataverse Table - ensure "Notes" & "Business process flows" are enabled

2. Generate the Word Template & add it to the "Template View"

Generate Word Template with dynamic column vals

Add Template

3. Create a Process to generate the output/docx from the Word template i.e. generate when status of the row changes

Activate the Workflow

4. Use Power Automate to call the Dynamics Process