Storing data in dataverse is very expensive. Especially the data that is stored in the actual database (db data). Hence, for many customer with larger datasets, typically with some B2C type of business, it is a good practice to overviewing the data you have in the system and figure out different ways of keeping it from costing too much money.
Below are 10 tips you should consider to keep the data in check.
Make a deletion list – and set up deletion jobs
Flatten verbose fields
Use virtual tables
Use datalake with analytics
Use datalake for archiving
Compress complex structures
Clean up non-production environments
Set lifecycle for instances
1 – Make a deletion list and set up deletion jobs
The first thing you need to start doing is to go through the biggest storage contributors. You can find which these are by looking in the Power Platform Admin Center under “Resources -> Capacity”. Open the view of each of the dataverse instances by clicking on the small chart symbol next to the instance.
You can export the full list by clicking on the hamburger symbol in the right hand corner of the graph. See picture below:
You can then start by trying to break down each table from the top. Based on the picture above, ask yourself questions like;
Do we need to store and save all orders? By removing some orders, maybe cancelled orders, we can cut down on two of the three largest tables as the order rows are removed at the same time
What activities are really causing activitypointer (the common table for all activities) to become so large? It is quite common that the email table is a culprit here, as the body of the emails, is usually a quite a few bytes and with thousands or tens of thousands of emails, they do add up. Marketing integrations can also bloat the activitiy pointer. I suggest investigating this further by exporting the data to an Azure Datalake and analyzing it with PowerBI. It is also quite common that not all emails need to be saved, for instance if you have email enabled queues like firstname.lastname@example.org it is quite common that you get some spam into this. Maybe searching for “unsubscribe” in email body to see if there might be some newsletter looking spam which enable you to remove them.
Are there any patterns of contacts and/or leads that can be removed? Working with B2C you might find that there are quite a lot of contacts and leads with incorrect data or similar. Ask yourself what value a lead with no phone number and no email has… removing contact data will also remove customeraddress as there will be at least two customer address records for each contact automatically created. The same goes for leads but in that case it is the leadaddress where extra data is being created.
The point with the list is trying to identify possible patterns of data that can be deleted. I typically have one row per rule like so:
status code = inactive AND modifiedon older than 6m
body contains “unsubscribe” AND modifiedon older than 1m
Flatten w Power Automate
Outgoing AND subject = “Covid information”
2 – Flatten verbose fields
Sometime it can be a good idea to just clean out or some “body” data. For example if you have sent emails to a lot of customers with the same Covid related information, the actual content of that email is the same for all and is known by everyone. Hence the body can be removed which can typically be done with a Power Automate Flow or SSIS/Kingswaysoft depending on the size of the data.
3 – Use virtual tables
Not all data needs to be stored in the expensive dataverse database. The recommendation from Microsoft is typically that data which you interact with should be in dataverse and the rest can be somewhere else. As it is now possible to use SQL as a source for virtual tables, as well as Cosmos DB and many other sources, moving data out of dataverse and accessing it through virtual tables can be a viable option.
Virtual tables is also something that should be considered when designing integrations. It is not always critical that all data actually reside in dataverse. Sometime just being able to access the data through dataverse is good enough. Especially if the data source is fast, it isn’t that much data, the data only needs to be accessed seldomly, It is also easier to use virtual tables if the data is read-only and used for reference.
4 – Use datalake for analytics
Dataverse isn’t really a good source for analytics. All endpoints (even the T-SQL) go through the application layer to the database. This makes large data crunching less than optimal in dataverse. This is also why Microsoft have developed methods for replicating the dataverse data to external stores. The just discontinued Data Export Service (DES) which synchronized data to an Azure SQL and the new Azure Synapse Link which synchronizes data to a datalake, are clear examples of this.
Hence, a good architecture for analytics of dataverse data is doing the analytics outside dataverse and the current best place for this is in a datalake with Azure Synapse Analytics.
This also has a direct implication on the data stored in Dataverse, the data you have in dataverse that is only needed for analytics, might not actually be needed in dataverse anymore. This can be anything from old orders, and of course all old communication like emails and phone calls. Datalakes are also way more efficient for doing large scale AI/ML analytics, which many organizations are looking for.
5 – Use datalake for archiving
If you havn’t already thought about it, a datalake can hence be used as an archive for data that you might need but isn’t actually something that you will use every day. Often a lot of data is stored for compliance reasons. When setting up archiving, it is important to make sure that what ever method you use, you probably want to keep the data in the archive after it has been deleted in dataverse, hence you might need to move the data from the initial storage container to the actual archive making sure to not actually move any deletes.
Microsoft have announced that they will be putting their own hot/cold-storage solution for dataverse into public preview this spring. This sounds a lot like some archiving functionality and I am certainly looking forward to seeing what it will be, how it will work and the licensing for it.
6 – Compress complex structures
For some businesses it is often needed to have quite a complex datastructure to handle the operations of what is done. It can be complex order structures with allocations of order rows to specific users or product configurations with billing, logistics and provisioning settings. Once an order has bee fullfilled in all aspects, it might not be required to store and keep all that complexity. It might just be required to keep the order header and the most critical information about what the order rows were about. Perhaps even flattened into a JSON or text field for future reference. If it is possible to move from a order header + 10 records in related tables to just a order header with a summarized text, for thousands of orders, than can save quite a lot of space.
7 – Clean up non-production environments
Data isn’t only stored in the production instances of dataverse. Many times production instances are copied and turned into test or staging instances. Integrations may be running towards development and test instances generating substantial amounts of data. Have a look at your non-production instances and check which data you actually need in them.
8 – Revise datamodel
Sometimes datamodels can become unnecessarily complex. This is most common when someone with little experience of dataverse and model driven applications design solutions. Other typical problems I have seen are repurposing of tables like leads, accounts, sales order to other simpler purposes. The problem from a dataverse storage perspective becomes that the additional storage overhead in these cases, especially in cases with businesses that have a lot of customers, can be rather drastic. Redesigning the datamodel to be more sleek can make it both more user friendly and use less storage.
9 – Revise integrations
Integrating data to dataverse can be a large culprit for data. For instance, many marketing applications generate quite a lot of data regarding the behaviors of customers. This data can then be integrated to dataverse to be used there. As behavioural data can be very granular, to the level of “Customer x has be send email y”, “customer x has read email y”, “customer x has clicked link z in email y” this data can take up substantial amounts of storage if integrated to dataverse. What options exist for this are different for different marketing applications. For ex Adobe Marketing can shut off this integration and can instead synchronize behavioral data to an Azure datalake where it can be unified with other dataverse data.
ERP integrations are also a common area of problem. ERP systems often have a very deep granualar level of data which might not be needed in dataverse/Dynamics 365 CE. Sometime just enabling deeplinking directly to the ERP system can be a better method, combined with virtual tables. However, this is not always the case and sometimes the best solution is just synchornization of the data.
However, do recognize that there should be a reason for having the data in dataverse. It should genrally be actively used.
10 – Set lifecycle for instances
Having dataverse instances with a bit unclear usage and not knowing what they are for and if they can be removed is, of course, a source of quite a lot of data consumption. By clearly assigning owners, reasons etc for all instances it makes answering the question “Do we really need ‘Internal test applications sprint 7′” or is it just an old remnant from a two year old development. This is part of the Center of Excellence starter kit and the processes that are important to set up in relation to this.
I hope any of these 10 tips have been useful. If you think so, the best way to thank me is to tweet or share your thoughts on this in social media. Also feel free to leave a comment below.
Perhaps you have some other thing that you think should be done or that I missed something. If so, please share in the comments below.
With Release Wave 2 2022, the old advanced find will be removed and the new advanced find experience will be its replacement. However, there are many old goats, like me out there that still want to use the old advanced find. I think one of the main advantages of the old advanced find is that it is not bound to the tables/entities of the specific app which makes it easy to have a look at things that are a bit more hidden from normal view. The new experience will require you to, in essence, create an admin app with all the required tables. Another option is of course to use Jonas Rapp’s Fetch XML Builder to find what ever you need, but that has another level of geek to it that might not fit all superusers out there. (Although I think they really would like it if they learnt how).
So, with the following URL you can still access the old advanced find and you can hence just bookmark it and have it easily available. How long this will be the case, I cannot say. But probably a year or so. And do send your feedback to Microsoft of the new experience so that they can make it as good as it should be.
Have you ever tried creating bulk deletes in dataverse/Dynamics 365? It is still a very old interface and it is hard to control the exact definition of what is to be deleted as you cannot see the actual FetchXML that is being used. It is also hard to see existing recurring bulk delete jobs and what FetchXML they are using. Based on these facts, my colleague Ebba Linnea Nilsson and I decided to make our first plugin for XrmToolBox (XTB). We are now proud to announce that it is released and available for free (as usual in XTB). We have also identified a bug in the platform related to this, which I will describe below.
The bulk delete functionality in dataverse and hence in Dynamics 365 CE is an essential function for many organizations. This is especially true since GDPR was introduced and there is a strong legislative requirement to remove personal data that cannot be justified to be stored. There are also other reasons why the bulk delete functionality is more and more important and that is based on the capacity costs that can be inferred on a Power Platform tennant. Firstly just storing data, especially in the database storage in dataverse has a non trivial cost at $40/GB. There is a lot of value per GB, so it might not be justified to say that it is too expensive but removing unnecessary data is definitely something that can be worthwhile especially for larger implementations. I personally work with a customer in the travel retail industry which has millions of customers and some tables have 40M+ records. There are also a lot of integrations and automations causing a lot of data to be created. Data that at some point needs to be removed. As all data should, or there should at least be a conscious decision not to remove it and why if so. As you might be aware, if you have read my other articles in this blog, I have previously used SSIS and Kingswaysoft to remove massive amounts of data. However, now that the API Entitlements will be introduced (6 months after the report for API Entitlements is made Generally Available), we need to start to become more and more restrictive in using the APIs for massive data management, like deletes. Hence, we try to move as much as possible to the built in Bulk Delete.
Working with the built in bulk delete functionality is a bit sad. It is very old and you have to click through a wizard kind of experience to be able to set them up. But the most limiting factor is that you cannot see the actual FetchXML of an active recurring bulk delete and you cannot input FetchXML directly into the bulk delete.
Having worked with this wizard you might also have noticed something a bit off. If you create a view which shows all contacts that have no activities. Then this will work when using it in the system like a view. However, if you try to use this view in a bulk delete it will “simplify” this and remove that part of the query. My thought that this was a limitation in the UI based on the fact that it is very old and hasn’t gotten any love for many years (decades). My assumption was hence that creating a bulk delete via the API would allow me to create bulk deletes that were based on FetchXMLs that you couldn’t even input from the UI. These were the reasons for us starting to create this plugin and it was so useful that I used it in debug mode for several weeks before finalizing it and publishing it.
Now we have released the first version and you can download it directly from XTB. I would like to give a huge thanks to Jonas Rapp who helped us out a lot with both connection to his tool FetchXML Builder but also the general setup of the plugins and the details of getting it approved as a Plugin for XTB.
If you have any suggestions, comments or otherwise, leave them on the GitHub repo https://github.com/crmgustaf/BulkDeleteManager or down below. We already have a bunch of stuff we want to do. Ah, yes, and the bug we found, it seems that the outer joins that was a rather recent addon to FetchXML is not supported by the actual platform. Hence the UI and the platform match in that perspective. Just to make it clear, what happens is that you input a FetchXML saying “All contacts with no activities” or something like that, which it will simplify to “All contacts” which is not really what you want.
As it is supported to create bulk delete jobs via the API, I do think that this still can be seen as a bug as there is no clear documentation on this or even a control when creating the bulk delete job with a FetchXML that will be incorrectly parsed. My suggestion is hence to implement the new FetchXML parser in Bulk Delete functionality, at least on the platform side. With the current setup, it is very possible that bulk deletes are created that remove a lot more than what was initially intended which can be very damaging to any organization. And from a GDPR perspective, this type of query is rather common, as it might be definied that contacts with no cases can only be stored for 2 years, but with cases for 10. To remove the ones without the cases, you would make the question “Remove all contacts with no cases with created on > 2 years”. This would then cause all contacts with created on > 2 years to be removed regardless of if it has a case or not.
To inform users of this, we have added a warning, every time a new bulk delete job is to be saved. Hopefully Microsoft will fix this soon.
Previously integrations between systems have been rather specific. For instance, someone has build an integration between Dynamics 365 and Mail Chimp or something similar. The introduction of Logic Apps and Power Automate has, however, at least from a Microsoft ecosystem perspective, changed this quite a lot. Now, when someone asks for an integration it is usually preferable to build and use custom or standard connectors in Power Automate / Logic Apps instead. This allows for more flexibility in the integration with the downside that the actual logical flow of the integration still has to be built, which can be a daunting task in itself in some cases.
Hence, when we at CRM-Konsulterna started a conversation with one of the larger digital signature companies in Sweden, Verified, about helping them create an integration to Dynamics/Power Platform, we suggested to them that we acutally not build an integration at all but rather a connector to Logic Apps/Power Automate. This connector has now been released and we are very proud of this and hope that it can be of great use to many companies that want to build integrations to Verified.
And as LogicApps/Power Automate are more generic solutions, this actually enables integrations to any of the other 600+ systems to which there are connectors nowdays. Hence someone who wants to build an integration to Saleforce or even SurveyMonkey can now do this using Power Automate or Logic Apps.
One of the things that need to be mentioned with regards to Power Automate is, however, that there sometimes are issues with the execution of Flows. This is not particuarly common but it does happen. It has also been improved over time by Microsoft but we do feel that it is important to consider this fact when building integrations with Power Automate. So, for instance making sure the logic both triggers on a particular event and has a “clean up” job which runs periodically and catches any records which for some reason wasn’t processed correctly.
I don’t think this should discourage the use of Power Automate or Logic Apps but that you need to make the logic a bit more robust.
Another interesting issue which can arise due to the fact that Power Automate is so easy to work with is that people with no programming experience start making Flows. This might not be a problem if you make a simple flow but with more complex logic you typically have to consider some fringe cases, what we call exceptions in programming, for instance if there is no data in a field where data is expected. This can cause a Flow excution to break or do unforseen things. Hence, having a good process for monitoring Flows and having experts have a look at them to notice any potiential problems, especially when they grow in complexity, compliance or use.
With this, I hope you have fun with Power Automate and create some amazing Flows to help you in your everyday life! And thank you all amazing colleagues who made the launch of this connector possible.
First of all, the most important fact is that non-licensed users have been upped from 100k to 500k for all Dynamics 365 Enterprise and Professional licenses. On top of that, 5k requests are added per Dynamics USL. Capped to 10M. Hence a small org with 5 Dynamics Pro users will get 500k + 5x5k = 525k requests per 24h. A large org with 1000 Enterprise users will get 500k + 1000x5k = 5.5M. A larger org with 10k enterprise sales users will be capped at 10M requests.
Normal, payed users have also been changed a bit. An enterprise or pro license is entitled to 40k requests. Note that this does not include team member licenses, which are entitled to 6k.
The capacity addon has also been changed to include 50k instead of the previous 10k. If the prices at $50 is still the same, I don’t know at this time. Then this price has been reduced to 20%. I will get back to this later.
This change is good as it will probably cause the majority of the customers to not exceed this. There will probably still be a few larger customers or complex solutions that will exceed and I do suggest that you talk to your partner and your Microsoft account manager to try to arrange something.
There are still some issues that I think need addressing;
How can ISV:s like Click Dimensions which by nature will be rather verbose be able bundle requests?
Larger corporate/global tennants with multiple instances are still punished by this model and would benefit from splitting the one large tennant to several smaller. But that makes it a lot more complex from an IT-perspective and isn’t the point that it is great to keep them all in one?
Licensing is still rather complex from a capacity perspective and that might scare customers. I have talked to customers that have chosen SalesForce just due to this reason.
There is more to be discussed regarding this, but I wanted to give my perspective on this as soon as I could and also put some light on this. I will be back on the subject.
I was recently in charge of a large migration. It all went fine but not without hickups that typically are connected to moving large amounts of data to dataverse. We were using SSIS with Kingswaysoft and ended up using a local SQL database as staging database too. This article will discuss the different lessons learned and give some concrete tips when doing similar migrations.
One of my more popular articles is the article that describes how to optimize the writing of data to Dataverse/CDS. If you are working with migration of large amounts of data, as I will be describing here, I do suggest you have a look at it: https://powerplatform.se/fast-data-management-in-a-limited-cds-world/. I will not discuss those concepts in any detail here but we did use all aspects mentioned in that article.
I recently was in charge of a migration which used CSV-file exports from an old German system (with German field names!) which had many millions of records, in both large tables like “Contact” and “Sales Order”. However, the system we migrated from had a completely different data modell than the one used in Dynamics. For instance, each row describing a “Flight” had to be divided into two rows, one for the outgoing flight and one for the homecoming flight, in the order detail table. We also had to create a lot of related data which was referenced from the “Flight” table, for example location, agent and brand. In other words, there was quite a lot of heavy transformations going on and a lot of logic involved, such as change format on the old data to match the Dataverse model and apply rules to resolve old issues, such as bugs.
Initially we only got a quite small subset of the entire database load, and we started our migration journey by creating all the migration logic in SSIS (which facilitates the script and makes updates easy to handle). The script did include some functions that “joined” rather large tables, both from the CSV files but also related data fetched from the Dataverse based on primary and alternate keys. I was clear with the customer from the very beginning that I wanted a full export with the same amount of data that we could expect in the final migration, mainly for the opportunity to stress-test the SSIS script before the migration to the production environment took place and after a while we got the big files…
…And this was when the excrement hit the wind generator. The afore mentioned lookups just stalled forever. We noted that having a lookup (using Kingswaysoft Premium Lookup) works fine on a computer with 16 GB memory up to a few 100k of records. However, once the data starts reaching 500k and more, it just stalls forever (and don’t even get me start on the sort tools…). Not sure exactly if it would have been possible to fix this by adding more cores and memory, we didn’t try. We hence had to rewrite the script and implement a staging database instead. What we found, is that a dataflow with 1M+ records of lookups will be 100x faster if you import the data into SQL and do a join instead. Lookups still works for smaller tables and I am not against them per se, as they do make the migration simpler. Adding more tables to a migration database will increase complexity, and if you want to add a column in a table, that table do not only has to be added to one SSIS dataflow, but probably a few more. And you also must do an ALTER TABLE in SQL to add the field there too. It is therefore important to have a good mapping set before you start to create the script. And keep the complexity as simple as possible. You can also use SQL tasks in the migration script to update the tables straight after you read them to the staging database per automation, if you need to apply some kind of rules after the read to the staging database, and find it easiest with an SQL query.
The method we used for developing the migration was to first make a “skeleton” migration, based on the target data model. In other words, we started with trying to get a few of the easiest fields, not all, from all tables that was to be involved in the migration – maybe it could be called – model-first-approach, instead of starting with one table, completing this and then moving on to the next. The advantage of the model-first-approach is that you quite early can start some tests on the data, for instance setting up some quantitative test by checking in the source system for the quantities of contacts and then comparing these quantities to the target. The tests can typically be done by other people than the people building the migration scripts and hence this methods scales a lot better than table-by-table-approach. It is also possible for several devs to work in parallell with different tasks. Typically the more senior will build the skeleton and then more junior can add fields by field to each respective table. A negative aspect of this approach is that it requires a lot of re-loads (keep in mind that this was a first migration, so there are no prior data in the Dataverse that we needed to consider) and re-mapping. And it may be easier to “fall out of” the structure, if you just need “to add a little bit here and there”. It is however indeed hard to go table for table, especially with related data. If you already have a lot of live data, you should think about a way to easy identify the migrated data so you can bulk-deleted. And do not forget to engage the client early with raised questions and the mapping to make sure you have understood everything correctly and avoid unnecessary errors.
We also tried to create unique row identities that strictly was based on the source data. This is very useful as that allows for delta-migration, or to continue where we left off in case of a problem. Let’s say for instance that you want to migrate 3 million contacts. If, after 2.1 Million contacts the script breaks for some reason, it is good to be able to continue at 2.1M instead of restarting. In this case we didn’t use modifiedon-date to be able to do a full delta migration logic but it is certainly possible. For this we used the cache-transforms, easily fetch the already migrated data (if any) with the unique and sort out the already migrated data if it matched the key.
Another pattern that we used was that, after creating a specific record, like contact, we reimported the recordid (in this case contactid) together with the legacyid. This allowed us to directly join with this table when later adding tables with dependencies like lookups towards the contact table, could be joined with this mapping table so that we directly got the contactid when querying the related table.
When migrating from CSV, import them directly as source tables in the staging database. That way, in case you need to fix something, you have a good reference for quantities.
Get an example of the full data load as early as possible. A script that works for a subset might not work at all for the full dataload as was the case for us.
Automate as much as possible. Don’t use any hardcoded values that are environent specific, such as transactioncurrencyid, but rather read these to small tables or to SSIS variables. Use SQL Truncate to remove all data quickly in a table, and make this part of the SSIS script as an SQL task at the appropriate stage.
Always check the quantities. How many rows in source data, how many rows after a match and check if it differs so you very early can identify bugs in your script that might be the reason for dropping rows. For example, you might use a JOIN when you should use an OUTER JOIN. Always check the total number and see if it is what you expect. Watch out for duplicates, and always check so your unique IDs (if you got some from the source data) really are unique and not NULL. Do note that if you have duplicates, that you join on, that will create multiplications. Hence it is possible, after a select-statement with joins to get more records that the initial table.
Define reasonable goals and test cases for the migration. Some examples:
99.9% of all contacts to be migrated correctly. With 1 M records, this means that anything lower than 1000 incorrect migrated contacts/missed, is defined as still ok.
Randomly pick 10-20 records on a base level, like 20 customer, and then compare these in the UAT/Test environment to the source system, as it is seen there. This needs to be done by the business people, so that they can have a say if the migrated data is fine.
Select some filters, like “all customers in Munich” and some other segmentations and compare source system to destination. If there are large amounts of errors, backtrack to the staging database to see where you did loose some records or created too many (not uncommon).
Complete entire transformation to destination tables in the staging db. Then you can move directly from there to dataverse. This is particularly important when moving large quantities of data when managing the data in SSIS can be problematic.
Make sure to have unique identifiers on all tables that preferably can be regenerated from the data. Store these in some “Legacy ID” field. This allows for delta-migration logic, ie. where part of the data is migrated and then the rest later. If you have some issues during one of the dataflows, and it stops on 3 230 234-th record of 6 M, you can continue from there and you don’t have to redo it all. If there is no decent way of getting a legacy id, you can generate classic row numbers by creating an identity column. This will make the migration utilize this, but only within that particular instance and load of the staging db. Hence you must be careful everytime you reload the database.
Utilize the backup-restore functionality of the dataverse environments. Do note that you can make manual backups just before you start migration. If you have a production environment, this will need to be converted to a sandbox environment before you can restore to it. Another option I got from a colleague was to use 3 different environments, with temporary names, and then just rename the final one when done.
Once you have transfered an entire table to the source system, it is typically very useful to have a mapping table, with just the table record id and the legacy id. So for instance, after migrating Contact, read all contacts from dataverse with the contactid and the legacy id. That way, when later migrating “salesorders”, which identify the customer by legacy id, it is easy to just join with this table to get the contactid.
Production environments are faster. Fastest is to ask Microsoft Support to relax throttles on all environments that are used during migration.
Use a VM that is located geographically (or really with low latency and high throughput) to where the environments are hosted. This is a very common recommendation by Kingswaysoft too.
The settings for number of threads and batch size needs to be set based on some factors, namely:
Have throttles been relaxed
Size of payload (ie how many columns) – larger payload -> smaller batches.
Type of action – creates are faster than deletes. Updates are in-between.
I hope these tips can help you along. If you have any comments or you have other experiences in this subject, don’t hesitate to leave a comment.
During this migration and the writing of this article, I had excellent help from my highly intelligent colleague Ebba Linnea Nilsson and it is certainly true that two heads are better than one, and the end result is often a lot better than just the sum of two people. So for my final recommendation, make sure to have a good colleague with you to help you out, as you most probably will run into some issues and having someone to discuss with is really great!