The answer to every licensing question is 42

The answer to life, the universe and everything else is 42 (check out The Hitchhiker's Guide to the Galaxy if you want to know where this comes from), so when a customer asks a question about Dynamics 365 and Power Platform licensing, I would also like to just be able to answer 42.

Having worked with Microsoft products for over 20 years now,  the single constant seems to be that Microsoft licensing is in a continuous state of flux. This is especially true for cloud platforms so  make sure that you involve the necessary experts  to help you in making the correct decisions and keep you up to date. 

In this blog post I am trying to shed some light on some interesting licensing updates for Microsoft Dynamics 365 and the Power Platform. I will also provide  some links to excellent posts/videos from other community members - this is by no means an exhaustive list.

Power Platform and Dynamics 365 request limits and allocations

I recently wrote a blog post about the Update to Power Platform and Dynamics 365 request limits and allocations . Microsoft has finally released a preview of the Power Platform requests reporting in May 2022. 

As noted in the official docs - What are the timelines for Power Platform Request limits? - enforcement of these limits will be activated at least 6 months after reports have been made GA.  In my opinion, things like requests limits and allocations are quite hard for customers to understand and also difficult to design for.

Pay as you go licensing for Power Platform

In March 2022, pay-as-you-go licensing for Power Platform reached general availability. With apay-as-you-go plan, customers can use an Azure subscription to pay for Power Apps based on active usage of the apps instead of purchasing and allocating licenses in advance. If you compare the unit pricing you will see that for similar workloads - the pay-as-you-go licensing price per unit is higher. So this will make an interesting balancing act for customers.

Scenarios combining Microsoft Teams and Dynamics 365

Don't get me wrong; I am really excited about the integration scenarios between Microsoft Teams and Dynamics 365 but announcements like Bring Dynamics 365 into the flow of work with Teams - at no additional cost generate a lot of excitement with customers but once you try to implement it you will notice that users do need a Dynamics 365 team member license. Be cautious and take big announcements with a grain of salt and always validate them yourself. When a user has a valid Dynamics 365 license, you are now able to view and edit Dynamics 365 records inside Microsoft Teams. 

Integration scenarios with Microsoft Teams are a great feature but the obscurity of Microsoft licensing around this, makes the discussion for consultants harder than needed. 

References:

• Power Platform licensing revisited
• Power Platform licensing, November 2021 updates
• PowerApps Pay As You Go licensing model - compare and configure (Youtube) from @ShaneYoung
•  Is Dynamics 365 data now "free" in Microsoft Teams by @Jukkan

Using Python and Pandas Datareader to retrieve financial data - part 2: Fama & French data library

This post is part of a series on using Pandas datareader to retrieve financial data:

• Using Python and Pandas Datareader to retrieve financial data - part 1: Federal Reserve Data (FRED)
• Using Python and Pandas Datareader to retrieve financial data - part 2: Fama & French library
• Using Python and Pandas Datareader to retrieve financial data - part 3: Yahoo Finance

In this post we will look at the datasets made available by Eugene Fama and Kenneth French. Eugene Fama and Kenneth French did a lot of research on which factors drive security returns. In 1993, they published the Three Factor Model (see article "Common risk factors in returns of stocks and bonds", Journal of Financial Economics 33, 1993), which showed that their factors (size of the firm, book-to-market values and excess return) capture a statistically significant fraction of the variation of stock returns. In 2014, Fama and French adapted their model to include five factors.  Fama won the Nobel Prize for Economics in 2013 for his research. Fama also published a number of papers on the Efficient Market Hypothesis and random walk theory.

Fama and French still publish the returns of various investment factors analyzed by them on their homepage on a regular basis.  You can download this data using the pandas_datareader library - you can take a look at the official documentation,  Fama-French Data (Ken French's Data library) to get started or take a look at the Jupyter notebook that I shared on Github https://github.com/jorisp/tradingnotebooks/blob/master/FAMA.ipynb

References:

• Fama/French forum ideas and observations from Fama and French
• How to use the Fama French model
• Fama-French 5-factor model: five major concerns
• In pursuit of the perfect portfolio: Eugene F. Fama (YouTube - MIT Laboratory for Financial Engineering )

Large AsyncOperationBase increase in Dataverse/Dynamics 365 CE: the canary in the coalmine

In normal circumstances, you probably rarely look at the System Job views in your  Dynamics 365 environment. But if you see a sudden and unexpected (large) increase in the storage used by the AsyncOperationBase table or AsyncOperation in File storage is like a canary in the coalmine - something is wrong in your Dynamics365/Dataverse environment.

System jobs (or Async Operations) are tasks that Dynamics performs in the background that don't have to be completed straight away. When such an asynchronous task is being setup, Dynamics creates a row in the AsyncOperationBase table and puts in all the details about when to run, what function/plugin/cloud flow to call, parameters to pass etc... 

All of this information used to be stored in columns of the AsyncOperationBase table but it this can get really large (KB or MBs) depending on the actual task being scheduled.  Part of this payload has now been moved out of the database and into a file stored in Azure Blob Storage and the database row now has a reference to the blob file.  You can follow up on the numbers in the capacity details for an instance in the Power Platform Admin Center (PPAC) - where you will now also see AsyncOperation in the file section after this architectural change from Microsoft. (Also see Storage capacity management for Dynamics 365/Dataverse - how to track storage evolution with Power BI and New Microsoft Dataverse storage capacity (Microsoft docs))

Dynamics 365 has a recurring system job to delete successfully completed system jobs  - see screenshot below - when an asyncoperation record is cleared from the database, the linked file storage is also cleared up.

One of the reasons you might see a large increase in storage consumed is because of a large amount of failed or cancelled system jobs.  The system jobs status (internal name statecode, possible values = ready, suspended, locked and completed)  and status reason ( internal name statuscode, possible values for completed jobs = succeeded, cancelled and failed)  can be used to identify possible issues.

I really like the SQL 4 CDS XrmToolBox add-in to troubleshoot issues with system jobs - it allows you to write simple SQL queries to identify system jobs causing issues. 

I also use  SQL4CDS for further analysis when troubleshooting async plugins using SQL queries e.g. select top 100 * from plugintracelog where typename like 'JOPX.CRM.Plugins.Notification%' but there are also other tools in XrmToolBox which you can use

References:

• Cleaning up records from the AsyncOperationBase/WorkflowLogBase tables
• asyncoperation EnityType (Microsoft Docs)
• Microsoft CRM System Job Views explained
• Asynchronous operation states (Microsoft Docs)
• SQL 4 CDS 6.1 released by Mark Carrington
• Dynamics CRM Data cleaning
• Storage capacity management for Dynamics 365/Dataverse - how to track storage evolution with Power BI

Using Python and Pandas Datareader to retrieve financial data - part 1: Federal Reserve Data (FRED)

The pandas-datareader Python library covers a number of APIs with global fundamental macro- and industry data sources including the following (for a full list see  Pandas Datareader - data sources ):

• St. Louis FED (FRED): Federal Reserve data on the U.S. economy and financial markets
• Fama/French data library : market data on portfolios capturing returns on key risk factors like size, value, and momentum by industry
• Yahoo Finance : retrieve daily stock prices, historical corporate actions (dividends and stock splits) from Yahoo Finance 
• World Bank: global database with economic/social indicators and demographics.

This post is part of a series on using Pandas datareader to retrieve financial data:

• Using Python and Pandas Datareader to retrieve financial data - part 1: Federal Reserve Data (FRED)
• Using Python and Pandas Datareader to retrieve financial data - part2: Fama & French library
• Using Python and Pandas Datareader to retrieve financial data - part 3: Yahoo Finance

In this post I will focus on retrieving data from FRED using pandas-datareader.  Federal Reserve Economic Data (FRED) - https://fred.stlouisfed.org/  is a database maintained by the Federal Reserve Bank of St. Louis. It has more than 800.000 data time series covering categories such as Economic growth & employment, monetary & fiscal policy, demographics, industries, commodity prices at different frequencies (daily, monthly, annual).  One of the interesting time series you can find here are 3-month Treasury Bill Secondary Market rate (TB3MS) or 1-year US Treasury bills which are used a proxy for the risk free rate in financial modeling. 

There is however some missing data on the TB1YR - so I will be using the TB3MS (3 Month) in my next example. You will notice that all time series are identified by a short abbreviation that you can find by searching on the FRED website.

Full source code is available at https://github.com/jorisp/tradingnotebooks/blob/master/FRED.ipynb 

References:

• FRED API: get US economic data which drives the real estate market
• Pandas-datareader python library

Documenting Power Automate Flows with Flow to Visio XrmToolBox Add-on

Last year I needed to document some rather complex Power Automate cloud flows and that's when I stumbled upon this great tool from Carl Cookson (aka @LinkeD365): the Flow to Visio XrmToolBox add-on. This XrmToolBox addon - XrmToolBox is the Swiss army knife for CRM  and Power Platform) consultants, check out https://www.xrmtoolbox.com/ for more info -  allows you to generate a Visio diagram for Power Automate Flows and Azure Logic Apps. Being able to generate Visio documents for Power Automate Flows is a great way to automatically generate a starting point for documentation. 

When you connect to a Dataverse instance, it will show you all of the different cloud flows. You can select one or more flows and simply click "Create Visio".

What I especially like about this tool that since the documentation is generated in Visio you can still clean up the output or remove parts of the flow in the Visio document which don't require explanation.

Note: You also might want to take a look at PowerDocu which contains 2 console apps you can use to document Power Automate Flows as well as Power Apps Canvas Apps

References:

• Flow to Visio XrmToolBox Add-on blogpost with basic documentation
• FlowToVisio - github repo with the source code