“We want to give our sales representatives access to sales history from Dynamics CRM. It will be about 50 million records. Is this ok?”
Can you do it? Yes. Should you do it? Maybe not.
Before forklifting a large quantity of legacy data into CRM, there are some questions you should ask.
1. Is the data actionable data or reference data? Just because you want to present data to users doesn’t mean that the data has to live in Dynamics CRM. If data is actionable, it drives business processes. For example, if you have a change in a field that drives a workflow process, that is actionable data. Granular transactional data like sales orders are not typically actionable data. They are consumed as reference data while researching a customer, planning a sales call, completing a support call. They provide valuable insight to users, but typically don’t drive CRM actions.
2. What do you want to do when you get there? Say you load 50 million records into Dynamics CRM, once you are there, what are you going to want to do with it?
- Advanced find? Good, but since AF only shows 250 records at a time, how usable is that going to be for millions of records?
- Export to Excel? The standard export to Excel is limited to 10,000 records, While this can be raised, you cannot go more than around 66,000 rows. You can raise the limitation higher, but you won’t get more than 66,000 rows
- Charts? Dynamics CRM charts limit the number of records included for performance reasons. You cannot chart a million records in a CRM chart.
- Dashboards? Good idea, and dashboards can show external reports and data grids as well as internal CRM charts and list views.
- Marketing lists? Dynamic marketing lists are limited to the same limitations as charts and aggregate fetch in CRM. Static marketing lists don’t have the limitation, but practically speaking, adding members to a marketing list will time out when you exceed hundreds of thousands of members.
This is not to say that these tools are bad. They are just not designed to be big data reporting tools. See my post
Make sure that before you go down the road you can get to where you want to be by driving the direction in which you are moving.
3.How real time do you need it to be? If you have over 100,000 records updated or created by an integration per day, you are most likely going to want to use a batch integration that runs during off hours to mitigate performance impact on Dynamics CRM. With this approach, there is a built-in delay before the records will be available in Dynamics CRM. If users need to have access to order statuses to support customer service calls, they will need to have near real time data. If a delay is acceptable, updating hundreds of thousands of records in a batch integration nightly can be time intensive and complex.
Alternatives
If you have large amounts of reference data that need to be accessible from Dynamics CRM in real time, there are some great alternative approaches you can take.
- You can summarize the data rather than loading all of the granular records. If the answer to the second question above is that sales representatives need to know total revenue in sales by customer by month, loading in a monthly sales summary by customer can be a much better choice than loading individual sales records. It may be that the summary is actionable, but the individual records are not. So if I need a view showing customers where the monthly sales volume is over 5 million dollars, loading a monthly summary will give me what I need to support the view, without overloading the limitations of Advanced Find and FetchXML, and significantly reducing the amount of data loaded.
- If you need to have a real time feed of sales history or other reference data, this can be achieved by displaying data from an external system in Dynamics CRM. This can be a report from a data warehouse, a third party tool like , or a custom web resource querying a web service. Using these types of tools we can display data from external systems on Dynamics CRM dashboards and forms, making the data accessible from within Dynamics CRM in real time, just as if it were in the Dynamics CRM database.
by Customer Effective
