When working in CI-D and replacing a datasource with another, for example replacing Power Query Datasource with Delta, then finding any dependencies for this is important. This is a tip that I got from Microsoft Fast Track Architect Ashwini Puranik, so credit should go to her. You can use the API testing functionality that is part of CI-D to query the ListAllMeasuresMetadata. Keep on reading to get some more details.
As the general recommendation for CI-D is to move to Delta-based data sources from Power Query due to performance. However, doing this shift will require you to reroute all dependencies to the new table before you can remove the data source.
Below is a way to find the dependencies based on Measures and Segments defined in CI-D.
Start by going into the API testing tool that is available for CI-D using the Permissions-area and the tab “API”.
Choose the only available API, Customer insights – v1.
Now search for ListAll and select “ListAllMeasuresMetadata”.
This will show a more detailed description of the endpoint. Now click “Try it!” in the green button on the right hand side.
This will show a panel on the right hand side where you have to input some required data. For an easy test, just select “implicit” in the Authroization drop down and put the instanceid into the field for it. In case you don’t know where to find the instanceid, you can grab it from the URL when you are using CI-D using the normal UI as it is the only guid in the URL.
That is all that is required, so scroll down to the bottom and press “Send”
You will now see a long text (json) which is the response from the api call. You can use the built in search in the browser to find all occurances of a datasource. You can use either the Display name or the schema name as both are in the JSON. As you can see below, the Measure “Total unique clicks last month” is one of the measures that has a dependency on the Datasource named “FreshRelevanceAZblob_FreshrelevanceAZBlobInbox”.
Now there are a few options, either remove the measure. This is a good option if you want to fix this quickly or the measure isn’t being used, if it is used there can on the other hand be many dependencies on the measure making it a lot more complex to remove. The other option is hence to just change all dependencies in it to another (Delta) datasource. You will then have to refresh the data source to remove the dependency fully. You can rerun the query by resending it using the button at the very end.
Once you have removed all dependencies on the data source from measures, there might still be dependencies from segments. You can use a similar method as above, but the endpoint you want to use in this case is “ListAllSegments”.
You can still have dependencies left on the datasource, for instance from things externally using the table from the CI-D API. This doesn’t actually block you from removing the data source but whatever you are doing externally will stop working once you have removed the data source. Naturally.
I hope this has helped and thanks a lot to Microsoft Fast Track Architect Ashwini Puranik for pointing me in this direction.
When measures are created in CI-D to be used in CI-J there are dependencies created which will stop you from removing the measure. CI-D is not very helpful in telling you how to remove it. I will try to be a bit more helpful.
When trying to remove the Measure this is the error I got:
Error: Detected DataVerse dependencies in Measure: ListMemberships. Please delete these dependencies and merge again.
Request ID: 14056846-b9c1-4ec8-98c2-88778e518b88
Time: 2024-12-05, 11:12:10
My investigation below with the help of Microsoft Support lead me to the understanding according to the image above. As you can see dependency is actually sort of circular, or at least one part of the dependency (dependent component) is at the entityanalytics on the record which points to msdynci_listmemberships, on the other hand (required component) on the table msdynci_listmemberships.
The fix, is to remove the record in entityanalytics. Easiest way in this case, is to just use a browser console where you are logged into the right Dynamics.
To remove the entityanalytics record that is blocking this:
After this I was able to remove the measure in CI-D.
Below I will detail the steps I did to understand this.
The first step is to look in the table “entityanalyticsconfig” searching for the name of the Measure. Its name is “msdynci_” + name of the measure. Check out the metadata if you are unsure.
Hence, from this I generated the image above. Since it is something of a circular reference, Microsoft Support suggested that I remove the entity analytics record with id: e46e52db-46a7-4585-8a6a-6ba888a5bd1f. However, not entirely sure what this is and hence I tried the one I had gotten in the first steps above, and that worked.
Generally I think it is a good idea to investigate what the dependencies point to, before removing something. In this case the dependency record in itself wasn’t removed, only the dependent part.
Thanks to Microsoft support for helping out with this and I hope it might shed some insights if you are having similar issues. I also think breaking down the dependency table in a tool in for instance XrmToolBox would be a great idea. I have a bit of a bad conscience for BulkDeleteManager that I own and I am not giving enough love, so feel free to build it based on this and I guess some more stuff. I will be happy to help out the the investigations if you are willing to build it.
I have recently been working with a customer with a large (500+ GB dataverse db) production instance and we are attempting to cut this down quite a lot. The natural way of doing this is bulk delete but it seems that if the underlying SQL isn’t up to speed, your jobs might end without actually being done.
My customer has a production instance of over 500GB which is costing them some substantial money and hence we started looking at removing some of the less necessary data. In this case it was the order products (salesorderdetail) which we have perceived that we do not need more than one year after delivery. Hence we set up an advanced find, agreed on the exact filters, saved the view and then tried to remove the records (well over 5M) using bulk delete. I restarted it several times. You can see the chronological results in the screenshot below:
As you can see, sometimes it actually deleted a few records, but most times, it didn’t. My professional analysis of this (=guess) is that this is caused by bulk delete not handling exceptions like SQL Timeout properly or that it has a limit on the number of times it will retry.
I also, in parallell tried to remove the same records with the same FetchXML using SSIS/Kingswaysoft and here I have several times gotten this error. I have had to turn down the knobs to a very low setting to get it to work, but the error message I did get was:
(Status Reason: ServiceUnavailable): The remote server returned an error: (503) Server Unavailable.”.
As you can see, there is a lot of noise but it clearly, in the highlighted part, say that there is a SQL Timeout.
Hence my takeaway from this is that you need to be a bit wary of bulk delete in large instances or in general as it might indicate that it has completed successfully but in fact it stopped due to SQL Timeout (or some other platform related issue).
On a personal note, I really hope the bulk delete functionality gets a modernized revamp soon. It is really old and is becoming more and more relevant.
I was recently helping my colleague Ebba Linnea Nilsson with a support ticket with data not being propagated correctly from dataverse to a datalake via Azure Synapse Link. It turned out that this was all by design. A design that might not be what normal users would expect.
Calculated columns and now recently the formula columns are both very useful way of being able to calculate data in a field that is based on other fields. Common scenarios are calculations like “Weighted revenue” which is the probability multiplied by the estimated revenue for an opportunity. However, there are scenarios where you need to be aware of how these fields actually work or you might get an unwanted or unexpected behaviour.
The first thing that needs to be understood is that these column types are calculated “on-the-fly” everytime dataverse attemts to access these columns. It might seem like the data is “in the columns” but it really isn’t, it is calculated. This is a big difference from for instance rollup-columns is that those columns are calculated on a regular interval by the system, and the result is stored in the record.
What does this mean for Azure Synapse Link? Well, let’s say we have a simple calculation, that sets the value “A” into all records for this calculated column. We then enable the Azure Synapse Link which will make an initial sync and set the column in the datalake to “A”. Now we change the calculation of the rule to output “B” instead. As no records are actually changed, this will not cause any records in the datalake to be updated, hence they will all still have the value “A”. From a user perspective comparing Dynamics 365 to the datalake without any underlying understanding of how this functions, it will look like an error. Same column has different values comparing what is in dataverse with what is in the datalake.
As soon as a record is actually changed, all columns for that record will then be sent to the datalake, and hence the calculated column will be set to “B” at that time. It is hence possible, to manually or semimanually force a resync, but it would require some bulk like for instance SSIS with Kingswaysoft especially for implementations with large amounts of records.
An important question to ask, is why would you want to calculate the data in dataverse and then use it in in the datalake. If you have a propper datalake architecture it should be easier to make calculated columns/fields in the datalake/datalakehouse. If the data is calculated only for use in the datalake, I would suggest moving the calculation to the datalake.
There are, of course, scenarios when it is preferrable to have calculations in one place and reuse the output in many places. However, this understanding of what can reasonably be expected is then essential.
As for product improvements, I have added an idea on the subject, if you agree with me, please vote! Microsoft Idea (dynamics.com)
A final note is that this type of unexpected behaviour is not limited to just Azure Synapse Link but really to any integrations based on either “modified on” or change tracking without doing periodic synchronizations. Hence I would also like to give a general warning about this.
Top Ten Table usage is back from being lost when Organizational Insights was discontinued. A bit tricky to find so check out the video. Awesome tool when trying to reduce the size of large instances, especially important now that the prices per GB are going up to $40/GB (subject to you license agreement).
Recent Comments