Modeling and working with data on the Dataverse within the Power Platform

The Common Data Model - Is an industry agreed approach to storing commonly used data.  Use it to store applications across your company/organisation.  Results in a single source of truth instead of multiple copies with data contained in different schema's.

Two types of relationships namely: 1:N and N:N (Dataverse hides the associate entity/intersect entity that is created in the background).  

Use "Option Sets" for small static data and use "Lookups" for larger or changing data.  In Multiselect Option sets for N:N relationships, rather use "Lookups".

SSMS can be used to view data using T-SQL, better to use "SQL 4 CDS" as it provides a full ability to work with data within the XrmToolBox.

XrmToolBox - is a 3rd party download that has a ton of contributed tools and there are good options for modelling data.  This is a collection of tools that are unbelievably useful and get continually updated and new tools added.

Excel and browser plugins - Can be used to import/export data and there are some nice Edge/Browser plugins to help such as "Level up for Dynamics 365/Power Apps".  Level up for Dynamics 365/Power Apps is a fantastic tool that I encourage any developer to add to Edge or Chrome as an extension.

Level up for Dynamics 365/Power Apps Browser extension

Dynamics 365 Power Pane Browser extension.

Dynamics 365 Power Pane is also a useful extension shown below:

Power Pane options

The built in browser Dataverse management tool is super easy to use to model your Common Data Model further.

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)

UK Tax - MTDfITSA

Update 21 December 2022:  MTD for ITSA delayed 2 years, will now start 5 April 2026, and applies to a way smaller base.   Theses are the key changes: 

Starting 6 April 2026 shall apply to roughly 740k users, people that need to register have a combined Self employment and property income of over £50k.  From April 2027, the threshold drops from £50k to £30K and will increase the user base to roughly 1.6 millions people.

1. There is no plan for Partnerships to fall under MTD for ITSA.
2. It's all subject to change, and it's likely the threshold will also be applied to lower threshold self employed people.
3. HMRC are looking at the API's, no road map but will put it together at some point.  The Sandbox will be reviewed for API testing (let see what comes out).
4. The new penalty points system only starts in 2026.
5. MTDfCT does not have any dates i.e. the 2026 start has been scrapped and no new date set.  HMRC plan to do MTDfCT at some point.
6. There is no change to the Basis Period Reform (BPR).

Making Tax Digital (MTD) for Income Tax Self Assessment (ITSA) is the replacement process to HMRC's Self Assessments (SA) that starts 6 April 2024 6 April 2025.  Of the 12 million UK people registered for Self-Assessment, roughly 40% are mandated to switch over to MTDfITSA on the 6th of April 2024 (circa 4.2 million individuals).  A combined gross income of over £10k from people with either a self employed business or a property business will need to register.

Are you or you self-employed client in?

Do you have over £10k income from self-employed businesses, then yes, from April 6 20224 you'll need to be using digital records to do quarterly submissions.  You first MTD for ITSA submissions are due submission for the quarter 6 July 2024 and 5 Aug 2024.  

What software options are the options?

There are approved vendors on HMRC's website for ITSA submissions, everyone has to submit via approved software vendors.  Anecdotally, Free Agent, Sage, Xero and QuickBooks are going to offer Quarterly self assessments submissions tied to their bookkeeping products.  Free agent is good for small customers, keep it simple.   I also quiet like what I have seen from Sage for accountants coupled with bookkeeping.  Personally, I've use QB for many years and played with Xero, both are excellent products and are strongly focuses on have MTDfITSA quarterly submissions.

There are software vendors that shall also look at bridging software solutions which will be more useful to self-employed businesses that support spreadsheets and are likely to be used by individuals to keep their costs down.  Accounts are likely to look at TR, Sage for Accountants, Capium, BTC, and WK for full practice management tooling including MTDfITSA, these vendors providing full suite solutions are likely to offer bridging and retrieve from source (booking and open banking) options for holding the digitally linked data.  Coconut looks interesting for individuals.

How do I enroll a client on MTDfITSA pilot?

Call HMRC and get client registered with HMRC for the pilot.  Software can be used to assign client to their accountants software. 

Is there a new penalty system coming in?

There is a new penalty and fining systems, the amounts of returns and work to complete an ITSA is significantly more than the single once a year SA.

Summary of comparison between ITSA & SA:

SA	ITSA
1 SA return done each year per person.	4 quarterly returns via approved software per business, required a digital record link to the underlying transactions per self-employed business 1 End of Period Statement (EOPS) per business. Crystallization/Finalization using HMRC approved software per person
Paper submission due 31 Oct for previous year. Online submissions due 31 Jan – 9 months after financial/tax year end.	Approved Digital Software to submit (no paper returns). 1 month after each quarter, submit the quarterly return, these can be revised later. Each EOPS is due on 31 Jan the following year.   The Crystallisation/Finalisation/Final Declaration is also due 31 Jan the following year.

MTDfITSA has been pushed back twice (although it is in pilot) and it's scoped changed.  ITSA follows on from Making Tax Digital for VAT (MTDfV) that is already live.  

HMRC use paper for self-assessments, then allowed XML submissions, and is starting MTDfIT to enable individuals to do their own Self-assessments.  Many people doing Self Assessments will be affected, and the associate costs & time shall be higher.  Just under 40% of people file SA themselves. If they need to be on MTDfITSA, it is highly likely that these smaller businesses will be severely impacted.  I believe Accountants that are doing small business self-assessment in the £400-£1,400 range will probably need to double or more these fees.  It all comes down to will the client do the quarters and the account just does the final submission, in which case there should not be much of a cost change.  Realistically, some clients will need booking to record the transactions and others will merely need guidance and year end adjustments from the accountant.  SA submissions start at about £120 (ex Vat) for simple Self-Assessments, these individuals, if they fall under ITSA shall probably be charged at least triple the amount compared to a TA doing all the work under self assessment or need to do considerably more work themselves.

MTDfVat took several years to implement (April 2022, all businesses, including those registered for MTDfVat but under the VAT threshold, must use MTDfV), and MTDfITSA is way more complicated than MTDfVat.

MTD for Corporation Tax (MTDfCT) is scheduled to start April 2026.  A pilot is scheduled to begin in April 2024 (unlikely).  Update earmarked for 2026, likely to be later.

MTD for ITSA (referred to as ITSA or MTDfITSA) 

• Who is in - Individuals and Partnerships.  Require either trading income from self-employed businesses and/or property businesses (if you have either of these business types), if their combined revenue/income total is over £10K.  (To determine, need to look two years' back at the individual's self-assessment income).   If not trading in the two years previous means you are outside MTDfITSA.  I.e. new self-employed businesses would get two years grace before they need to file using MTDfITSA.  Unlikely to be enforced by penalties.  The mandatory start for quarters is 6 April 2024. Must have combined property and self-employed (unincorporated) business revenue over £10k combined. 
• Partnerships are in if all partners are individuals (starts 6 April 2025).  I think there is a disconnect between the draft regulation and what HMRC are providing.  Partnerships declare income using SA800 for a partnership.  Then each individual partner has to do a self-assessment.  There is no way partnership income counts as self-employed income so partnerships are not a criteria for MTDfITSA.  Need to confirm what the "General partnerships with income over £10K that have individual partners need to join MTDfITSA" refers to, I suspect it is incorrect. 
• Trusts/estates, LLP and Ltd are out at the start.  
• 1.3 million landlords in the UK as of 2021.
• Roughly 40% of Self Assessments are filed by individuals for themselves.  This is going to be tricky with MTDfIT.  If these individuals wish to file, they will need to do the quarters, have digital records, and use free software (HMRC are not providing MTDfIT free software).  They will also need to do MTDfIT EPOS and crystallisation.
• Individuals doing Self Assessment can have ten or many self-employment businesses, 0 to 3 (4 - Foreign property is not clear) property businesses under MTDfITSA (Ord UK property, FHL UK, FHL EEA, Foreign property).
• One can defer MTDfIT by changing year ends.  Individuals/practices also can apply for exclusion based on age, religion...
• The HMRC ITSA service is only able to accept end clients who have accounting period aligned to the tax year (I believe this means 31 March or 5 April).  Basis period reform is coming and will sort out this issue.
• Digital links/keeping digital records.  Can't re-key/copy and paste.   There is no requirement to use bank feeds/PDS2 data.  Some booking software firms are likely to file quarterly MTDfIT returns for each self-employed (and property) business.  Spreadsheets are an acceptable form of record keeping.   Excel and bridging software is sufficient for the source for filing.  If recording sales can use daily sales totals for the digital source but ideally link to the raw input system.  
• PDS2/bank feed - unlikely to work, useful for bringing in data.  The issue is that the bank account would need to represent a single business; also, the mapping would be very rough into the MTDfIT categories.  Things like purchases from Tesco can't understand where to place the transaction.  As MTDfITSA quarterly is a rough estimate.  It is possible as an option.
• Quarterly MTDfITSA is done per business and is due 1 month after quarter period ends.  Penalty points for late filing, missing 4 quarters in 24 months, is £200 penalty.  Record of last 24 months retained.  Property business quarters and year end run in the same cycles as personal tax namely start 6 April and end the following year 5 April.  As MTDfIT starts on 6 April 2024 (was 6 April 2023 but postponed again notice provide on 22 Sept 2021), the 4 quarterly submission for the 2024-2025 tax year and filling due dates are:

Qrt start date	Qtr end date	Qtr submission due date
6 Apr 2024	5 July 2024	5 Aug 2024
6 July 2024	5 Oct 2024	5 Nov 2024
6 Oct 2024	5 Jan 2025	5 Feb 2025
6 Jan 2025	5 Apr 2025	5 May 2025

• Self Employed businesses with a year-end of 5 April or 31 Mar, are treaded as using the same period dates as property businesses'.  The majority of Self-employed businesses are already aligned with personal tax year ends and are "in".  If you tax year is outside of this i.e. 31 Dec each year you can't use MTDfITSA.  
• Each property business for the individual needs to submit an End of Period Statement (EOPS) each year and also a declaration for each business attached to an individual.  
• Lastly, the Final "Crystallisation" (basically the final Self-assessment) needs to be submitted covering the self-employed business, the property businesses and the personal tax affairs of the individual.
• Late payment has interest penalties, basically no penalty for 15 days late, then 2% for 16-31 days and then 4% is paid after this. 4% is on the outstanding balance from day past due.  Payment is due following year 31 Jan.
• MTDfITSA adjustments can be done after the quarter and resubmit or at the EOPS.
• Quarterly submissions are estimates and can be adjusted per quarter or finalised at year-end for each business.  Revenue could ask for proof of digital linking, so you can't just throw in a rough estimate.
• After every quarterly obligation is submitted, the response gets a CalculationID, that can be used to view the end clients "year to date tax estimate" for the business.

There are basically Two possible flows for completing ITSA Tax returns:

1. Full Process/Accountant/Practice/TA has booking data and does the quarters for each trade/business, the practice then does the end of year pieces also, the issue is it will be expensive.
2. Client Individual Driven Process is where the end user shall submit their ITSA quarterly returns, and then use a practice to submit the business/trades end of years and then the individuals end of year/Crystallization. 

High-Level Process for TA (full process):

1. Clients sign up to MTDfITSA on HMRC using their Government gateway credentials, assuming they meet the criteria (Agent if authorised to act on the clients behalf can also sign the client up to MTDfITSA).
2. Clients must maintain digital records/digital linking, and it must be digitally linkable (spreadsheets are acceptable, min req allows for daily totals in a spreadsheet).  Need to record the transaction date, category and amount.
3. Perform quarterly submissions per registered unincorporated business.  It can be submitted up to 1 month after the period ends.
4. EOPS submission per business.  Due 31 Jan following year.  HMRC will return the tax calc for the unincorporated business.  Also, a declaration for each business must be completed by the TA or individual.
5. Final Crystalisation & Declaration (HMRC does the calculation for the individual) are due 31 Jan following year.
6. The payment date remains the same for Tax liability due 31 Jan following year.

Note:  All opinions are my own, and I am not a personal tax expert.

Numbers:

• 32 Million Income tax payers, 4.1 million people are higher rate tax payers.
• 450k people on additional rate (45%).
• Median income per UK individual is £25k (males £27,400 females £22,200)
• +-35k Tax & Accounting Practices in the UK (rough estimate).
• 12 million people do self-assessments in the UK.
• MTD ITSA will impact around 4.3-4.7m people (Self employment & landlords).
• MTDfITSA affects 1.3m individual landlord.
• HMRC recon they will gather an extra £9 billion per year.
• HMRC estimate 1 million MTDfITSA customers will quality for the free software from vendors (less than £85k turnover and using cash basis accounting).
• of the 12.2 million SA's due for the year ending 2020/2021, 10.2 million were filed on time by 31 Jan 2022.  It gets pretty crazy in filing day see HMRC's info.
• HMRC recon that +-8 million of the 12.2 million 2020/21 self assessment (SA) tax returns have been filed as of 24 January 2022 (1 week to 31 Jan deadline, with Feb no penalty extension in 2020/21 year).  Interestingly the year before 9 million we filed at the same point the year before.
• The pilot ITSA HMRC sign up is extremely low.  With HMRC estimating 4.3 million ITSA user sign-ups for the year starting 6 April 2024.

My Thoughts 10 Sept 2021 on ITSA:

HMRC have a lot of open questions both on the mechanics of ITSA and the API for MTDfITSA.  The regulation should be finalised +-end of Sept/Oct.  There are very few people that are eligible for the Pilot 6 April 2022 and very few accountants wanting to put their client on MTD.  My personal opinion is that HMRC will end up delaying the start for ITSA as they did for MTDfVat until is if more flushed out.  The API sandbox is not fit for pre-production however, all the vendors are doing best endeavors.  So not impossible to keep the existing timelines but i do feel there will be huge changes to ITSA coming along shortly from Revenue.

Self Assessment filling options:

1. Most people use the current XML online filing done 31 Jan after the personal tax year.
2. Some people still use paper based self-assessment due Oct after tax year,
3. MTD for ITSA will be due 1 month after each businesses quarter, EOPS and finalization/crystallization process due 31 Jan the year after the personal tax year.

Azure Elastic Pools - DB creation, schema alignment for SaaS

Overview:  Provisioning and seeding databases is pretty straight forward however ensuring multi-tenant databases schemas are aligned is a little tricky.  Azure has the Elastic Job Agent service that has been in preview for many years and is a good service.  It is extremely useful for updating multiple database instances en-masse.

There are 2 common scenarios: 

1. Provision a new database for a client.  This involves 1) creating the SQL database instance either on a server or an elastic pool. 2) Update the new database with the appropriate schema 3) insert any seed data into the database.

2. Update groups or all database instances to a specific schema i.e. change schema for all clients.

Scenario 1 needs to apply the schema to multiple databases and Elastic Job is perfect for this.  Scenrio 2 also needs to update a single database and can use the same T-SQL code to ensure new dbs have the correct schema applied.

Elastic Job Agent:

A job can be created that uses T-SQL (from source control) to ensure the schema of target databases are updated to a specific schema version.  A dedicate database is used to monitor and manage the jobs.  I call this the "agent-elasticdb" database.  

Note:  The Target can be all databases on a server, elastic pools, groups of name database instances or a single database.

If a job has multiple target database's to update, the updates are run in parallel.    

Post a message into a Teams Channel using any HTTP client

Overview:  I need to post messages into Teams channels from my application, it is extremely easy to do and took me 15 minutes. 

Steps to Post a message from Postman into a specific Teams Channel:

1. Setup a channel to accept POST requests

Add a connector to the Channel

Find the "Incoming Webhook" connector

Create/Configure the new Webhook

Copy the webhook endpoint

2. Send a postman POST HTTP request to push the data into the Teams Channel

3. Verify the result in the teams Channel

The custom message is displayed in the channel.

Tip: Format the card/message using these instructions.

SaaS Azure Testing Thoughts

 Tooling:

1. API Automation - Postman, Newman
2. UI Automation - Selenium
3. IDE - Visual Studio 2019
4. Test Organization - Azure DevOps Test Plan
5. CI/CD - Azure DevOps

Code reviews:

Code review is used as a verification technique to ensure that each unit is coded as per standards and expected business logic and inline with coding standards and best practices.  Automate code review built into Azure Pipelines should include:  

• WhiteSource Bolt - Scan packages for vulnerabilities.
• SonarQube - Static Analysis, 
• Blackduck - Open-Source Scanning (OSS) tool.  Used to look for license risks and unused references.
• Checkmarx - Static Application Security Testing (SAST) tool benefits include: Detect security vulnerabilities, Improve developer practices, and reports on code ownership.  Static code anaylsis.  VeraCode is a competitor product.
• BugSuite

Code should pass OWASP (Open Web Application Security Project) shows the most common code vulnerabilities.  OWASP ASVS (Application Security Verification Standard) - framework for controls when building applications to cover functional and NFR's for web applications.

Unit testing:

Unit tests are written to ensure every unit of code is working as expected, and to prevent a defect from going to the next level on all C# code.  Xunit and Moq are the tools to be used for unit testing using the standard Arrange > Act > Assert pattern.

As long as Unit test coverage is high and of a good standard, I don't mind if the tests are written before the code (TDD) or as most developers tend to do the tests after the code is written.

API testing:

All API must use Postman collections and Environments for local testing.  The tests need to cover all API's dealing with authentication, authorisation, checking status codes, body responses, headers, data persistence, and post test clean-up.  Use Newman to integrate postman tests into Azure pipelines:

https://www.npmjs.com/package/newman-reporter-htmlextra

Selenium testing:

Code for UI must be automated where possible.

SonarQube: "automatic code review tool to detect bugs, vulnerabilities, and code smells in your code" SonarQube documentation

Code Smells:  Bloaters, OO abusers, ....

Checkmax detects potential security issues

Disposable email addresses: You often need to test login/account creation and it's useful to have temporary disposable email addresses:

• YOPmail - Disposable Email Address
• Home - Mailinator

Model Driven Apps in Power Apps

Overview:  Model-driven apps are one of three types of PowerApps we can build.  Sits on the CDS/Dataverse.

Model Driven Apps use the CDS to hold the data to drive the application.  CDS has an encrypted REST API to access the data which makes it great for building and connecting 3rd party applications.  

Choose from the three types of Power Apps available.

As the model driven app is based on the CDS model, you need to ensure you have the appropriate entities and attributes.  Very similar to ERD/UML modelling.  

• Either standard or activity (time base) as a starting point for creating new entities.
• There are lots of attribute type e.g. text field digital number, or email address...  
• The Security model is great for ensuring the correct people have the appropriate rights to the data and CDS instance.  User can have multiple security roles and are additive (positive) in nature.

Note:  A lot of this information comes from the Power Apps App Maker training day from Microsoft.  The course is excellent and I wish I'd seen this before doing the PL-100 exam.