Friday, March 11, 2011

CRM 2011 Online and datawarehousing

I attended a half day presentation by Microsoft yesterday on the subject of Dynamics CRM 2011 and integrations. It seems that many parterners are a bit hesistant to integrating Dynamics CRM 2011 Online and are promoting the on-premise versions when the issues of integration are important.

Microsoft did a good job in showing that they do have sufficient technology to handle integrations to Dynamics CRM 2011 Online including the Azure based technology ACS - Access Control System that together with Active Directory Federation Services 2.0 can be used to allow Dynamics CRM 2011 Online to use a local AD as authenticator. It is probably not as easy as setting up a normal on-premise solution but at least it can be done and there hopefully are some whitepapers or Youtube-clips on how it is done.

One of the issues that were discussed was the issue of integrations in reagards to Business Intelligence and data warehousing. This is usually done using SQL Integration Service (SSIS) with direct SQL communication. Their suggestion on how to address this issue was to use the new OData interface that exists in Dynamics CRM 2011. I am no SQL expert but I do believe that this being a standardized protocol, integrations will be possible. However, there will still be issues with performance as the amounts of data that need to be transfered are quite large and the OData protocol is still a strict pull-protocol which does not allow for trigger-based updates.

Another issue that I asked about was the licensing issue in regards to Dynamics CRM 2011 Online. Take the following example: A large company of about 40 000 employees has about 500 people activly working in with Dynamics CRM 2011 Online for SalesForceAutomation. A data warehouse is created and data is integrated from many different systems, ERP, production systems, quality control systems and CRM. CRM being the customer data master. SharePoint is used as the global Intranet platform and some of the data from the data warehouse is published on the SharePoint portal to all employees. For instance our currently 10 most important customers. So, the 39 500 employees are only viewing a minute part of the customer data, and it indirecty, via the data warehouse, originates from the CRM system. What licenses in CRM are required for these users?

In the case of an on-premise installation, the best licensing option for Dynamics CRM is the Application Platform Agreement (APA) that is sort of a "free-for-all pass" which is negotiated with Microsoft. However, in the Online environment there is no correlating licensing agreement to the APA. I explicitly asked Micrsoft how this was to be licensed and their answer is that 40 000 separate users licenses are required for Dynamics CRM 2011 Online, making it a rather impossible option, in other words forcing the company to either an on-premise solution, removing some of the data from the data warehouse/Intranet or moving to another CRM supplier. Neither of which is in line with showing off the power of Microsofts cloud services.

I hope this is just a temporary flaw since it does limit Microsofts business opportunities with larger companies and I would think it is probable that some similar agreement form that matches the on-premise APA will be introduced.

On the other hand, the External Connector license is not required at all for CRM Online (or SPLA) making it even more interesting for smaller CRM customers as customer/event/portal integrations are more and more common.

Gustaf Westerlund
CEO, Chief Architect and co-Founder at CRM-konsulterna AB


  1. I am having problem trying to use SSIS for integration to Dynamics CRM 2011 online. Not able to write single package. Any workaround or code sample would really help.

  2. Hi,
    I thinkt that you will have some major issues with that. You will probably need to write some .NET based SSIS proxy that connects to the CRM Online data. Not going to be very easy. If you get it to work, I would be more than happy to let you write a guest posting on this blog about this!

  3. Any idea if this scenario has changed? Its been over a year since this posting was published and we are currently evaluating Online versus Premise however this is a huge issue to us.

  4. @bzwart: No, it hasn't. I spoke to one of my contacts at Microsoft Sweden who was involved in creating a datawarehouse based on two separate instances of CRM Online and two instances of CRM on-premise. The solution we discussed was based on having plugins in CRM that write all changes to some datawarehouse data collection service and then creating a separate rebuild service that was periodically run every weekend or similar that rebuilds the entire datawarehouse data that originated from the particular system. This is not a very nice solution and it is not even certain that it would work depending on the amount of data stored in CRM Online. I do believe that it would be necessary to have the rebuild service that is periodically run (and could be run on demand aswell) since the data would risk being desynced at some time if just based on normal differentiated updates from the event based plugins.

    From your perspective, there might also be a third option, that is Partner Hosted CRM. With the right partner, you can have a server available via VPN with database access and still only pay for it on a monthly basis. Depending on the partner, you might need a dedicated server and that might make this solution slighly more expensive than the CRM-Online, but it will still be a low CAPEX investment. You do have to get the right CRM hosting partner for this though as they need to be able to offer you additional services. We (CRM-Konsulterna) offer this service to some of our customers and there are also some other companies in Sweden like Midpoint that I think offer this service as well.

    If this is not a viable option to you, you do have large amounts of data that needs syncronizing with the DW, I would recommend the on-premise option. Do note that there are several licensing options that might be more beneficial to you like the "rent" version which also reduces CAPEX.

  5. Somebody necessarily help to make severely posts I might state. This is the first time I frequented your website page and to this point? I surprised with the research you made to create this particular post extraordinary. Well done admin..
    Data Warehousing Training in Chennai