by gustaf | Mar 7, 2021
Some people might have heard about an industry best practice that you should never have custom columns (fields) on the systemuser table (entity) in dataverse. Is this true and why so? This article is based on my understanding of how the inner workings of dataverse works and hence what you need to think about when designing your application to not unintentionally create an application that destroys your environments performance. In short, be careful about adding custom columns to the systemuser and if you do, only add fields that have static data, ie data that doesn’t often change. Let me describe this in more detail.
First of all, I would like to give credit to a lot of this to my friend and former Business Application MVP Adam Vero, who described this in detail for me, I have also discussed this with other people and since had it confirmed but not actually seen it documented as such, why it might not be fully official. I do, however, not see any problems with people understanding this, rather the opposite.
Dataverse is an application platform that has security built into it as an integral part, there are security roles, system users, teams and business units that form the core pieces of the security in the system. As the system will often need to query data from these four tables, it has a built in “caching” functionality that per-environment loads these four tables and precalculates them into an in-memory table for easy and fast access. This is then then stored in-memory for as long as the data in these four tables is kept static, in other words, nothing is changed, no updates, no creates, no deletes.
What could then happen if you add a column to the systemuser table? Well, that depends. If this column is a column that you set when the user is created and then never change that, that isn’t a problem, as this wouldn’t affect the precalculated in-memory table. However, if the data of these columns are constantly being changed, like for instance, if you add a column called “activities last 24h” and then create a Flow which every time an email, appointment etc. is created it will increment this by one per day and reset it every night for every user. Then every time, this writes to any user, the precalculated in-memory table will be flushed and recalculated before it can be used again causing a severe performance hit that can be very hard to troubleshoot.
How would you create a solution for a the “activities last 24h”, as described above then? Well, I would probably create a related entity called userstatistics with a relationship to systemuser. In this case it could even be smarter to have a 1:N relationship to this other entity as you could then have many userstatistics per user and measure differences in activities day by day.
But wouldn’t the NLB:s (Network Load Balancer) make this irrelevant as each environment is hosted together with many others? Well, I cannot, due to NDA talk about the details of how the NLBs actually work for the online environements, but I can say this, no, it is still relevant. The NLB will make it so for performance reasons.
As for teams, it is only the owner teams that count in this equation, the access teams are only being used for sharing or other types of grouping and hence never part of this pre-calculation.
And the savvy person would then of course realize that the multiplied size of:
systemusers x owner teams x business units x security roles
Does make up the size of this pre calculated table and for large implemenations, this can give indications of where performance can start to make a difference as every time a user does not have organizational level privilige, the system has to go through the entire table to check what is right. And then of course the POA. But that table is story for another day and another article.
Just final word. The platform is constantly shifting and even though this was true and probably still is true, there might be changes going on or that have happened that I am unaware of, that have changed how this works. If I hear of this, I will let you know.
by gustaf | Mar 2, 2021
As you probably will have heard by now, my colleague and friend, Jonas Rapp suffered a stroke about two weeks ago. Daryl LaBar, Carina Claesson, Benedikt Bergmann and I talked about this on the XrmToolCast which you can listen to here: https://xrmtoolcast.libsyn.com/coming-together-to-support-jonas
I would, with this post, like to give my personal perspective on how I use FetchXml Builder as an integral part of making scripts with SSIS and Kingswaysoft.
And before I begin, I would just like to urge everyone to donate a dollar or two to Jonas as he is having tough time. Or just share your story of why you love to use his tools. Everything matters. And if you want to donate, just use this address: https://fetchxmlbuilder.com/donate. If he has helped you, as he has helped me, why not help him back. Why not leave a comment with how much you donated, and when we have 10 comments, I will share how much I have donated.
With one of my customers, I work a lot with trying to manage their rather larger database, around 500 GB, and doing what I can to make sure that it doesn’t grow out of proportion. This also entails looking into GDPR rules and making sure that personal information is removed, and kept according to specific rulesets that become rather complex. So complex that one advanced find query isn’t enough to define them. And the removal of data is not seldom in the millions of records, why I need a tool that can do deletes quickly. Hence, I have found that SSIS and Kingswaysoft with Azure Data Factory for deploying these packages to be run continously, is a good method.
However, there is a very important piece of the puzzle that is missing when you want to do this, and that is being able to assemble the FetchXmls to the nitty gritty level that they are as efficient as possible and for that job, there really is only one tool, FetchXml Builder. Hence I often set up my general idea of how I would like the rules to be executed and then start assembling the data often into different Cache transforms, and then filter the dataflows using lookups based on this.
So, for me, FetchXml Builder is essential when working with this and this is my story of how critical it is for me.
by gustaf | Nov 20, 2020
Dataverse, and new terms for picklists, fields and more. This has been described in detail by many others in the community like Jukka Niiranen. However Microsoft are saying that there will be no new API, that all API:s will stay the same so this article is just going to discuss this subject briefly.
First of all, they will, of course do exactly what they are saying. The current WebAPI will of course be available for many years to come as we have seen the old 2011 SOAP API still be available and used a lot, still today, often for the reason that there isn’t full functional parity between the API:s.
Some tools allow you to seamlessly choose between the API:s, like Kingswaysoft, and I have asked Daniel Cai, the owner and head developer/architect at Kingswaysoft which API I should use. I don’t know if he has changed his recommendation, but last year, he still recommended, if my memory hasn’t failed me, to use the old SOAP API due to stability and functionality issues. I will let him, on their blog describe their thoughts on this, if they want to.
However, I think this is rather telling of how Microsoft act and will act regarding an older API, that it will be maintained and kept alive for many years.
That brings us to the interesting question if there will be a new API based on these new terms, I frankly do not know, but my deduction would say a definite yes.
It think the easiest way of seeing this is falsifying this and extrapolating it. In other words, what would happen if they didn’t create a new API and we looked at the platform as a whole in 5-7 years without knowing the history? It would look totally crazy. A platform with one set of terms in the UI and another set of terms in the API:s. It would make documentation really complex as there would be need for translations between these two concepts everywhere and the bar for getting developers into the platform would be higher than necessary.
Hence the only reasonable deduction is that they will make a new API with the new terms. It will probably be release in 6 months or so, but the current WebAPI will still continue to work for many years to come.
These are my personal thoughts and are not based on any information from Microsoft, despite the fact that I am an MVP. I urge you to make your own deductions and I think you will find that this seems reasonable. Please share your thoughts disagreements or otherwise in the comment section below.
Recent Comments