Monday, August 07, 2023

Reducing size of the SubscriptionTrackingDeletedObject in Dynamics 365 CE or Dataverse

When you delete a large amount of data from Dynamics 365 CE or Dataverse, you will notice that the total storage consumed does not decrease. This is because the SubscriptionTrackingDeletedObject table stores deleted records from the Dynamics 365 database. This table is used to track deleted records for replication and synchronization purposes and records are being kept by default for quite a large amount of time.

When recently encountering this problem, Microsoft support suggested us to reduce the number of days the records are kept in the table from the default 90 to 15 days - to do this  take the following steps:

  • Install the latest version of the OrganizationSettingsEditor solution which you can download from https://github.com/seanmcne/OrgDbOrgSettings/releases   (The OrgDBOrgSettingsTool dates back to Dynamics CRM 2011 but is being maintained by Sean McNellis and is still quite relevant)


  • Change the ExpireChangeTrackingInDays and ExpireSubscriptionsInDays from the default setting of 90 days to 15 days. In sandbox environments, you might be able to change this value to 0 but for production environments Microsoft recommended to keep it on a minimum of 15 days. The OrgDBSettings utility utilizes the solution configuration page to provide access to the editor (in classic mode) - see OrgDBOrgSettings - where to find it after installing? for details.


Afterwards - just keep patient until the lapsed number of days have been passed and then you should see a size in reduction for the SubscriptionTrackingDeletedObject


Friday, July 21, 2023

Dynamics 365 and Power Platform monthly reading list July 2023

2023 Release Wave 2

Technical topics (Configuration, customization and extensibility)

Topics for Dynamics 365 Business Applications Platform consultants, project managers and power users


Wednesday, June 28, 2023

Cost optimization: older mails in Dataverse now stored in Dataverse File Storage

Back in April 2019, Microsoft introduced a tiered storage architecture. This tiered storage architecture allows Microsoft to use the most appropriate storage type for different types of data used in Dynamics 365 and Dataverse. Last month, Microsoft started moving  the storage contents of the e-mail body (email.description) from more expensive Dataverse Database storage to cheaper Dataverse File Storage - for the official documentation check out Dataverse - Email activity tables -Email storage.



Based on the list prices I see in my personal tenant (see screenshot below) - this cuts the unit cost for additional capacity required for e-mails down from 37,4 €/GB/month to 1,87€/GB/month (Quite interesting how they still use the older name Common Data Service :-) )

Your pricing might defer based on the type of licensing agreement that you have.  If you had to buy additional storage capacity in the past, you might want to revise your storage usage and the required storage capacity add-ons.



You can check out the storage capacity in the Power Platform Admin Center - I added an example from a specific instance were Dataverse storage for e-mails (see ActivityPointerBase table) from 63 GB to 30GB.


A the same time, we saw a new storage file type appearing called Email which grew to 3,8 GB. This is a lot less than the storage saved in the database since the data in file storage (Azure Blob Storage behind the scenes) is actually compressed.

All in all, a great job from Microsoft which might save you a lot of money.  Don't hesitate to add a comments sharing your experience with this.... 

Friday, June 02, 2023

Microsoft Fabric Firehose

Microsoft Fabric generated quite a buzz after the announcements at Microsoft Build last week but it feels a lot like drinking from a firehose. (If you can't get enough check out the Microsoft Build 2023 - Book of News)

Microsoft documentation:

Besides the official announcements from Microsoft (which have a certain marketing fluffiness) it might make sense to check out the list of unofficial resources below.

Link collection:

Tuesday, April 18, 2023

Looking at historical returns of stocks and bonds with Power BI and Python

You might already have seen below graph taken from a study by JP Morgan Asset Management, but what if you would like to look at historical returns without going  through the hassle of having to collect all the data yourself?


There is an interesting Excel sheet shared by Aswath Damodaran (@AswathDamodaran)  that you can download from Historical Returns on Stocks, Bonds and Bills: 1928-2022 which looks at returns of different asset classes (stocks, bonds, bills, real estate and gold) over a longer time period.

In this post I will share some tips on how you can use this data in Power BI, Python and Jupyter notebooks. 

This Historical Returns on Stocks, Bonds and Bills: 1928-2023 - Excel file  file is updated in the first two weeks of every year and it is being maintained by Aswath Damodaran, who is a professor of Finance at the Stern School of Business at NYU, he is also known as the "Dean of Valuation" due to his experience in this area.

Visualizing S&P 500 and US Treasury bond returns using Power BI

I first converted the Excel from  xls to xlsx format and afterwards it is quite easy to  import the data from an Excel workbook files in Power BI . It is quite easy to visualize the returns of  both stocks and US treasury bonds using a clustered column chart - I also added a minimum line for both stock and bond returns.

Expected risk and expected return should go hand in hand: the higher the expected return, the higher the expected risk. Risk means means that the future actual return may vary from the expected return (and the ultimate risk is loosing all of your assets). The first visual showed a 20-year annualized return between 1999 and 2018 for the S&P 500 of 5.8%.  Average returns hide however the big swings in yearly returns - e.g. in 2008 (the Great Financial Crisis), the S&P 500 had a -36.5% yearly return. Bonds on average have a lower return but also have a lower risk profile. 

The basic rule of thumb is to keep your “safe money” (i.e., money you don’t want to risk in stocks) in high-quality bonds. While this doesn’t give you 100% protection against losses at all times, it does provide you some peace of mind. I really like this quote: "If you can't sleep at night because of your stock market position, then you have gone too far. If this is the case, then sell your positions down to the sleeping level. (Jesse Livermoore)"

As you can see in the visualization below, in most years with a negative return for the S&P 500, the return for bonds is positive - with two notable exceptions 1969 and 2022. A common saying is to have your age in bonds. Using that general rule, a 45-year-old might have 45% of the total portfolio in bonds. If you want to more aggressive, you would have less than your age in bonds. The last decade with interest rates very low (or even negative) this probably wasn't a very profitable asset allocation but 
things might have shifted.




The US Treasury Bond used in the Excel file is the 10-year US treasury bond for which you can download the data from FRED . The yearly return has been calculated by taking the yield and the price change for a par bond with that specific yield.


In the long run (see example below for different rolling windows from a 1-year to a  20 year period)  stocks will outperform bonds but this again works with averages and it ignores the tail risk which might wreak havoc in your portfolio.




Reading data from Excel using Python

Now let's take a look at how you can read and manipulate the data in this Excel sheet using Python. To read an excel file as a DataFrame, I will use the pandas read_excel() method. Internally, Pandas. .read_excel uses a library called xlrd which you also need to install but I used the  openpyxl library as an alternative which also works. So before you can read an excel file in pandas, you will need to install 


The above code reads only the table with data from the Excel file (which I downloaded in a subdirectory data from the Jupyter notebook) - see  pandas.read_excel in the Pandas referencel documentation for full details:
  • sheet_name: can be an integer (for the index of a worksheet in an Excel file, default to 0, the first sheet) or the name of the worksheet you want to load
  • nrows: number of rows to read
  • skiprows: number of rows to skip
  • usecols: by default all columns will be read but also possible to pass in a list of columns to read into the dataframe like in the example

I just started exploring some data around stock-bond correlations and will be updating the Juypyter notebook on Github - https://github.com/jorisp/tradingnotebooks/blob/master/HistoricalReturns.ipynb

A couple of weeks ago I noticed this interesting tweet on rolling one-year-stock-bond correlations for six regimes from @WifeyAlpha - I think it would make an interesting exercise to see how to rebuild this using Python.


References:

Related posts:

Wednesday, March 22, 2023

Upgrade to Azure Functions runtime v 4.x

Even though  the Azure Functions runtime 2.0 and 3.0 were retired on December 13th, 2022 , I recently still found some Azure Functions using these older runtimes.  See Azure Functions runtime versions overview which outlines the support for the different version.


Azure Functions that use runtime version 2.x or 3.x will continue to run but Microsoft is no longer providing updates or support for applications that use these versions. There a number of reasons why you should upgrade:

What is the Azure Functions runtime?

For those not so familiar with Azure Functions, the Azure Functions runtime is the engine that powers Azure Functions to execute user-defined code (written in . It is responsible for loading the user's code, executing it in response to specific triggers, and scaling the execution as needed to handle the workload. The runtime is a key component of the Azure Functions service, and it allows developers to focus on writing and deploying their code, rather than worrying about the underlying infrastructure.

How to find the Azure Functions runtime version?

When you log in to the Azure Portal and go to the Function runtime settings tab on the configuration page of an Azure function, you will see which runtime version is in use. Instead of having to look at every individual function, you might also take a look at the Azure Resource Graph








Monday, March 20, 2023

Notes and links for the DP-900 Microsoft Azure Data Fundamentals

As providing reporting and analytics capabilities in a Dynamics 365 solution is becoming increasingly important I decided to brush up my knowledge about available technologies and patterns in the data area. I think that taking a certification exam is a good way to get started on such a broad topic and it also allows you to set a clear goal. So end of last year, I finally took the DP-900: Microsoft Azure Data Fundamentals exam




Whenever you want to attain a Microsoft certification, the first place to start is always the study guide which you can find on the certification home page.  Another interesting addition from Microsoft is the ability to take a practice assessment for DP-900 (link is listed on the certification home page - there is also a full list of all practice assessments for Microsoft Certifications. Listed below are some of the links and resources that I used - mainly on topics where I had less hands-on experience - so this is definitely not an exhaustive list.

YouTube videos playlists

Describe core data concepts

Describe capabilities of Azure storage

Describe capabilities and features of Azure Cosmos DB
Describe relational Azure Data services

Describe common elements of large-scale analytics
Describe data visualization in Microsoft Power BI

Friday, March 17, 2023

Quick tip: use the new release planner for Dynamics 365 and Power Platform to track and planning upcoming changes

The new release planner for Dynamics 365 and Power Platform is awesome - check it out at https://aka.ms/releaseplanner . It allows you to track the features that are planned, coming soon or already available for trial. You have visibility across active release waves in a portal (built with Power pages) and you can personalize, filter and sort release plans when you sign in.





Thursday, March 16, 2023

Quick tip: resolving no hosted parallelism has been purchased or granted in Azure DevOps

I recently setup a new free Azure DevOps organization but when I tried running my first pipeline - I got an exception "##[error]No hosted parallelism has been purchased or granted. To request a free parallelism grant, please fill out the following form https://aka.ms/azpipelines-parallelism-request".

It seems that Microsoft has made some changes in Azure Pipeline Grants for Private projects to prevent abuse as also outlined here Configure and pay for parallel jobs.


To be able to use the free Microsoft-hosted built agent, you will now need to fill in the form which was mentioned in the exception message - https://aka.ms/azpipelines-parallelism-request. Microsoft completed this request in 3 business days for me - which was in line with



Friday, March 10, 2023

Quick tip: update to Dataverse developer plan

Great update from Microsoft on the Dataverse developer plan - check out the new video from Power CAT team on Dataverse Environments for everyone - New Developer Plan - Power CAT Live on YouTube.

Interesting updates in the new developer plan:

  • 3 developer environments available per user
  • Don't take up storage capacity with a maximum of 2GB data.
  • Premium connectors included for testing without additional licenses
  • Possible to play around with ALM to get a to know solution management better.
  • Automatic cleanup of inactive environments after 90 days.
If you don't have access to the PPAC, you will need to use https://make.preview.powerapps.com/ for the moment which shows the option to create new environments from within the environment selector.




Thursday, March 09, 2023

Dynamics 365 and Power Platform monthly reading list March 2023

 Power Platform and Dynamics 365 release 2023 wave 1

Technical topics (Configuration, customization and extensibility)


Topics for Dynamics 365 Business Applications Platform consultants, project managers and power users



Thursday, January 19, 2023

Technical debt and Dynamics 365 solution architecture

Technical debt is an important concept to understand as a solution architect. As outlined in Thoughts about the Dynamics 365 solution architect role, a solution architect has to keep the long-term use of the solution in mind and implement scalability and adaptability into the solution for the future. This is where you will need to understand the concept of technical debt.  Software design and development is full of metaphors to help non-technical people understand specific concepts - technical debt is one of them. 



What is technical debt?

Technical debt is created by (un)conscious actions to take shortcuts in development or design of a software solution. These shortcuts are taken due to some short-term benefits such as faster delivery time, easier implement etc. ... but they have an impact on the longer term. 

Having technical debt is like having financial debt, where time spent on this "not-quite-right implementation" counts as interest on the debt and paying the interest on the loan (the debt) might slow down the project/program in the long term. 

You will see 3 types of technical debt in projects:
  • Strategic technical debt: technical debt caused by decisions made to shorten time to market, delay development or expenses, ...
  • Unavoidable technical debt: technical debt accumulated due to the fact that the platform/technology/product version might not be supported anymore, ...
  • Naïve technical debt: due to business immaturity (unclear or changing requirements, misalignment between product owner and business stakeholders) or technical immaturity (no coding standards, no peer reviews, no automated tests, no refactoring, no consideration for)

It is important to grasp that technical debt is not always an indicator of low quality (but it might be). Technical debt happens because even though we do the best we can - given what we know - we subsequently learn better ways to solve a problem or better ways appear due to technical improvements in the underlying platform or technology stack.  You probably heard the phrase "Ignorance is bliss" - well for technical debt this is not the case. The fact that nobody in a team has heard of technical debt should be treated as a red flag for software quality.

How to repay technical debt?

Regardless of the type of technical debt you accumulated, in the end you will always need to repay the technical debt to make sure that you don’t jeopardize your product evolution. If the cost to implement a certain change (be it a bug fix or adding an additional feature) keeps increasing over time, it might be an indicator that you have incurred technical debt on your project.

The first step is however making business aware of the existence of technical debt and the impact of it. I really like the ideas suggested in The Wall of Technical Debt on how to make technical debt visible. Building a regular habit of identifying technical debt and learning on how to negotiate with business on technical debt payoff is a good approach.

Keeping technical debt to a minimal is a team effort - make sure that delivering quality is engrained in the culture and way of working of the team.

How to identify technical debt in your Dynamics 365 solution architecture?

As mentioned previously technical debt is automatically accumulated as changes are happening on the technical platform underpinning your solution. So I highly recommend to follow up on the  important changes (deprecations) in Power Apps and Power Automate page and also on the Deprecation of Dynamics 365 apps page. 

By writing code, you are automatically accumulating some amount of technical debt but there is tooling available to help you identify technical debt in your code and solutions:

  • SonarQube - static code analysis tool which helps you identify bugs and code smells - works for multiple languages. Quite useful for .NET and Javascript development which are typically used in CRM development
  • Power Apps solution checker - specific Power Platform tooling to analyze Dataverse or Dynamics CRM solutions. Can be run from the maker portal,  Microsoft Power Platform Build tools for Azure DevOps or PowerShell
  • Checkmarx provides static application security testing (SAST and open source composition analysis (SCA). It helps you uncover security issues in your code base or in 3d party libraries/packages that you are using. If you are looking for alternatives, you might take a look at the vulnerability scanning tools listing from OWASP
Technical debt goes broader than purely code. Technical debt is a collection of issues which makes the overall solution hard to maintain, prevent easy implementation of updates, might impact performance and scalability. So lack of technical documentation, absence of a good application lifecycle management (ALM) process, lack of test automation/heavy reliance on manual testing, missing architectural design decision log, brittle integration, monitoring, etc ... are also things to take into account as part of a health assessment of a solution architecture.

References:

Tuesday, January 10, 2023

Interactive chart visualizations using Python and bqplot: visualizing S&P500 returns

A couple of months ago, I stumbled upon this interesting presentation Jupyter Notebooks: interactive visualization approaches. The presentation showed how you can use bqplot to build interactive visualizations. 

Bqplot contains a set of 2D plotting widgets built on top of the ipywidgets framework for Jupyter notebooks. The bqplot package aims to bring d3.js visualizations to Python while retaining the flexibility and ease of use of ipywidgets and was developed by the quantitative research team at Bloomberg. You can install bqplot using conda or pip. 



One of the examples built by the team that you can find on Github is a Jupyter notebook which shows US equity market performance (using the S&P 500 index) where you can select an interval on a time series chart - for the selected area you get the total return as well as a histogram of the daily returns.

References:

Monday, January 09, 2023

Why I blog and you might want to consider it as well

A couple of weeks ago I got the question why I write a blog since it would be better to do a podcast or publish videos on technical content. In this post, I will explain why it is still a good idea to start blogging.  I wrote my first blog post almost 18 years ago  and got inspired by Robert Scoble (Scobleizer) who worked as a technical evangelist at Microsoft at that time and had a very popular blog.  1483 blog posts and almost 2 million views of these blogs I still consider it a good idea, since  I think that a lot of good things have happened to me in my professional live because I decided to start my blog and share my thoughts on technology.  



Starting a blog is an easy starting point for you to be active in the technical community. Sharing is caring might sound lame but it is a good credo to live by as an IT professional/consultant.  In a professional context,  most persons  only know the people that they directly work with - colleagues or customers. 

Blogging (but also Twitter and technical communities) allows you to expand that and suddenly there are people all over the world that you can learn from and they can learn from you. Suddenly you will realize there are others who face similar challenges. By sharing your experiences and offering advice to support, you can build a community of readers who value your experience. By writing good high-quality content on a regular basis, you can establish yourself as an expert in your field which in turn will help you build your personal brand and increase your visibility. Starting a blog is a lot easier than creating a video on YouTube or doing a podcast.

The most underrated skill to have in IT is the ability to communicate efficiently. Blogging is levelling that skill up. In an era where people mostly rely on PowerPoints slides for presenting a plan or a vision, it is quite refreshing to see them back it up with a well-written, solid document afterwards. I usually cringe when seeing poorly written documents or e-mails so I think that good writing skills will set you apart. 

I think of a blog as a way to write notes to myself and you should too. Throughout your daily job, you are continuously solving problems: write about how you approached solving these problems and where you found helpful information. By writing your approach down, you will remember it better and it will be easier for you to find it later (and others will benefit of this as well). 


The more you explain a concept or a topic to other people, the better you will get at understanding it yourself. This might sound strange but it is true, teach to learn. When you need to teach something, you are required to understand it in a deep and comprehensive way. The process of writing, will help your own understanding and requires you to think critically and find creative ways to present the topic which further enhances your understanding and retention of the information.

Even when you are learning something new, you are probably taking notes. Blog these notes, taking these notes. Rewriting them will help you understand the content much better, while it helps others too. If you think that everything is already written, do it for your own reference. If you find it useful yourself to go back to your blog, then it is OK. Don't fuzz to much about getting little visitors to your blog in the beginning.

References:

Thursday, January 05, 2023

Dynamics 365 and Power Platform monthly reading list December 2022

First post of 2023 - so let's close with last month's  suggested reading list. If you want to catch up on previous reading lists for 2022 - here is the list:

 Technical topics (Configuration, customization and extensibility)

Topics for Dynamics 365 Business Applications Platform consultants, project managers and power users

Monday, January 02, 2023

Notes on deploying and troubleshooting a Streamlit app on Azure App Services

 A couple of weeks ago I was playing around with Streamlit and decided to deploy it on Azure a
using Azure App Services using the guidance from Deploying Streamlit Applications with Azure App Services . 

Streamlit is an open-source Python library that allows you to create interactive, data-driven web applications in just a few lines of Python code. It does not require you to have any JavaScript, html or CSS experience.  




The deployment using the steps outlined in the blog post went quite smooth but when I navigated to the website, I was greeted by an exception.

Since I haven't worked with Linux for over 20 years now, I feared to be in for a long and painful experience to get this resolved but it actually turned out to be easier then expected. 

First step, I took was looking at the Application Logs for the Azure Web App. Go to the Azure App Service > Diagnose and solve problems > Application Logs. 

When scrolling through the Application Logs

The exception log "TypeError: Descriptor cannot be created directly. Your  generated code is 
out of data and must be regenerated with protoc > 3.19.0. If you cannot immediately
regenerate your protos, some other possible workarounds are: 1. Downgrade the protobuf package to 3.20.x or lower" actually pointed me to a thread on the Streamlit forums - Issue with Protocol Buffers. After changing requirements.txt  to deploy a newer version of Streamlit (see Configure a Linux Python App for Azure App Service for more details on how the Azure App Service deployment engine automatically runs pip install.) all started working correctly again.