The CRM Online platform has really come along way over the years coming closer and closer to its on premise counterpart. There is however one aspect which poses significantly more challenges than the on premise version. Specifically this is the limitation that the CRM online data cannot be accessed directly via SQL.
This limitation typically impacts the following two fairly common-place requirements:
1. Reporting
For CRM Online you are forced to use FetchXML to retrieve data rather than SQL. And while most report requirements can be satisfied using this technology it requires a little more effort than standard SQL reporting. Frankly, it is far easier to construct a query in SQL than FetchXML and the comparative difficulty between the constructs only increases with increasing complexity of the query required (or you can break the report down differently to avoid more complex query requirements when using FetchXML - "tricks" you wouldn't normally need to consider when using straight SQL). And of course for a complex reporting requirement, FetchXML may not be flexible enough to extract the data in the way that is required making it necessary to perhaps "flatten" the data using plugins or using a tool such as Scribe Replication Services before reporting against. This as compared to on premise reporting where you can use techniques such as dynamic SQL where the sky is really the limit.
2. Bulk Updates
In the on premise version of CRM, you have the ability to easily make bulk updates to your data. Though running direct SQL updates is officially "frowned" upon by Microsoft, this is done as a form of CYA. Allow me to explain - the integrity of the CRM database is maintained via the web services business layer. The minute someone is allowed to "tamper" with the underlying data directly all havoc can break loose. There simply is no way to protect against someone who is intent on corrupting the database. Not that anyone will do that intentionally of course but someone who is insufficiently knowledgeable in SQL and/or the CRM database may unwittingly introduce "problems" by trying their hand at SQL. And Microsoft of course cannot control who would be performing such updates so therefore they issue a blanket statement saying that the direct updates to the CRM database is unsupported (I once had the rather amusing case with Microsoft support where the resolution required running a script that they provided with a cautionary note that direct SQL updates are not supported - in the same email!).
However if you are sufficiently knowledgeable and take the necessary cautionary measures - it is difficult to argue against performing direct SQL updates with a straight face. The first reason is due to the relative ease that such updates can be constructed and run. And the second and often more valid reason is due to the speed in which it will be applied. If you need to run an update in a table with millions of rows - the difference will be between a few minutes and potentially a few days via web service.
Please don't get me wrong - I am not at all down on the CRM Online option. In fact, I think it is actually a pretty amazing platform and has some significant upsides from the on premise version - especially for smaller companies where the playing field is levelled in that they also have access to an enterprise level strategic software solution at a very affordable price and without the necessity of having their infrastructure and/or IT department. The barrier to entry was significantly lowered when CRM first offered its online solution. However, when evaluating the merits of going on premise vs. online for a given company's requirements - I would definitely factor in the above against the stated long term requirements of the undertaking.
No comments:
Post a Comment