Filtered View Performance

As is well known it is best practice to use Filtered Views when performing queries on the CRM database most notably for report writing. This is because doing so adheres to the SDK standard and also yields many benefits such as:

• Security model adherence - reports written this way will automatically adhere to the CRM security model without having to do anything further.
• Exposing "name" attributes for Options Sets (including "Two Options") - these are denormalized in the filtered view so writing additional joins to retrieve the friendly names is unnecessary
• Embedding and Context Awareness - reports can be easily embedded into the CRM forms and run based on the current context without further input
• Advanced Find pre-filter - This filter mechanism becomes available eliminating the need to hard-code parameters in the report definition
• Formatting - Date, number, and currency formats defined in CRM can easily be applied to CRM reports for a consistent experience
• Built in parameters - Various other built in parameters that the CRM platform can leverage to simplify the report writing experience, avoid unnecessary hard-coding, and ensure consistency and cohesiveness between the CRM application and custom reporting (e.g. CRM_URL)

There is one downside - there can be a performance degradation when running queries against Filtered Views (vs. say the regular entity views). Of course this can be very significant as if your report takes a long time to run it more than negates all the benefits cited above.

However, the query performance of Filtered Views needs some additional analysis. From what I've read, most attribute this degradation to the fact that the Filtered Views (unlike other views) perform additional security checks against the CRM security model. And while this is indeed the case, my experience has indicated that this particular aspect does not really impact adversely. In fact, this additional check is fairly trivial (it might be that in cases where there is a heavily nested security hierarchy that this impacts more - so not to be completely disregarded).

So what causes the performance degradation? The following analysis of queries against various entities (customized - not OOTB) is very telling:

The crux of the analysis are columns 2 and 3. For example, "account" has 118 Option Sets and 169 Two Options for a total of 287 Options. As you can see, a simple query against the FilteredAccount vs. account entity yields a difference of 20 seconds! That drops dramatically for "event" where there are only 191 Options - dramatically but still not sufficient as the query difference remains at 6 seconds. The difference drops as the number of Total Options decreases such that when there are around 100 Options the difference becomes fairly trivial.

So in short, it is the number of Options on the entity that are the major cause of query degradation and that can be attributed to the additional joins required to obtain the friendly name (as described above). Consider that in the case of account the Filtered View is performing a little over 287 additional joins as opposed to the regular entity view ("a little over" as there are a few other joins required for the security model interrogation). And also the analysis shows that the degradation is exponential rather than linear after the "degradation threshold" is hit.

So what are the takeaways?

First of all it's important to mention that this performance analysis was run using SQL 2010. The experience might be otherwise on later versions. But according to the literature the number of query joins in this version of SQL is only limited by system resources (as opposed to previous versions where there was a hard upper limit). So one way of minimizing this issue is to add some horse power to the SQL Server. 

Secondly, it is noteworthy that the row count has no noticeable impact on query difference (for example, see row count of "account" vs. "invoice"). And similarly the difference is not caused by indexing issues. The basic Filtered View query leverages existing system generated indexes.

Third, the number of Option Sets shown in the analysis has to be slightly unusual. In most cases, I would imagine that there would be far fewer of these and therefore using the Filtered View for your queries will simply not have any noticeable impact on performance (although it's easy to run a quick comparison query to determine if this statement is correct or not).

Fourth (and perhaps most importantly), make sure to purge unnecessary or legacy attributes. The likelihood in cases where there is such a high number of options is that many of them have fallen into disuse over time. Opening the entity form and viewing how many of them no longer even appear on the form will probably shed some light here. This is not only good practice from a query performance point of view, but also helps to keep the data neat and clean which in turn contributes to a simplified and confusion-free user experience.

Finally, information is power and understanding what is underlying a Filtered View can be factored into design decisions well in advance. In fact, it is very likely that the reasons for going with a whole bunch of custom Option Sets vs. product-line-item kind of design have fundamentally changed with recent overhauls of the CRM user interface. In previous versions, the number of clicks to add line items was often prohibitive - an experience which has been significantly optimized especially in CRM 2013 (and probably more so with the very imminent CRM 2015 release).

User-Owned or Organization-Owned Entities?

When creating new entities you have to choose whether you want the entity to be user-owned or organization-owned. This setting cannot be changed after the entity is created. So it is important to get this right from the outset or otherwise this will come back to bite you at some later point in time. Especially if the entity you create has a lot of dependencies (i.e. relationships) set up. And this will be compounded a hundred-fold if a requirement surfaces post go-live that requires such a setting change...

The primary impact of entity ownership is related to the security model. With organization-ownership, access is binary - either the user can view (or edit, create etc.) ALL or NONE. With user-ownership, access is much more nuanced allowing for much finer control over the SCOPE of which records can be viewed (or edited etc.).

The issue is that it can be difficult to anticipate what to expect in the distant future. Meaning that all good analysis and critical thinking aside, a requirement might surface in phase 6 of the project that was heretofore unforeseen. And suddenly we're left ruing a decision made some years back - a decision which made perfect sense at the time it was being made.

When we think of user vs. organization ownership, we tend to think of it in terms of whether anyone "cares" if a record could be or should be owned by a particular individual in the organization. If the answer is negative then we tend to think of it as organization-owned. And not only do I think there's nothing wrong with that thinking, I think that if the designer of the system takes a moment to ponder this then that is in fact highly commendable.

However, as I've discovered over the course of many projects, the issue with record ownership is that the business logic of the concept may not always mirror the technical requirement. It may well be the case that no-one "cares" who owns a record. It might even be the case that seeing an "owner" on the record might be unnecessary or even confusing (i.e. the owner field can or should be hidden on the form). However there could still be a requirement to limit access to the record that still needs to leverage the user-ownership security model.

The "sharing" feature is a classic example of this scenario. For example:

• There might be a user or team ("team A") that you only want to grant access to a limited subset of records ("entity B"). 
• That subset might have nothing to do with ownership e.g. we want to grant "team A" access to "entity B" records which have been flagged as "Vendor" 
• The above effectively means that we wish to grant "team A" access to a subset of "entity B" records based on a non-ownership type attribute
• If "entity B" is organization-owned, there is no way to configure the above security restriction. Organization-owned entities are binary - either you can see all "entity B" or you cannot see "entity B" at all.
• In order to accomplish this requirement, "entity B" will need to be recreated as a user-owned entity.

Hopefully the above scenario illustrates how it's possible - best practice design considerations aside - to land up in situation where an entity needs to be converted to user-owned. And I'm sure I'm not the only one who has come up against this issue.

Based on this, we can come up with a rule of thumb for record ownership. And that is - if there is not a clear requirement for a record to be user-owned or organization-owned at design time, and even if it might seem to make sense from a logical business perspective that the entity should be organization-owned (using the "no-one cares" formula mentioned above), it probably still make sense to create the record as user-owned. After all, it's very easy to hide ownership fields on the form and one can still set up security so it behaves in a binary fashion as organization-owned entities. 

In short, if you got it wrong and set up an entity as user-owned, the price to pay is relatively small. On the other hand, if you got it wrong and set up an entity as organization-owned, you could be looking at open heart surgery.

The theory might all be well and good, but let's say you're presently in the predicament described above i.e. you're in production and have come across a requirement that means that you'll need to convert an entity from organization-owned to user-owned - what are the steps to achieve this? In the next post I'll attempt to address this with a step by step walk through.

CRM 2013: Compelling Reasons to Upgrade

There are many posts that describe and illustrate the new features that CRM 2013 has introduced. Here are a few choice samples:

• http://community.dynamics.com/crm/b/crmpowerobjects/archive/2013/09/06/top-10-new-features-of-crm-2013.aspx
• http://community.dynamics.com/crm/b/crmsoftwareblog/archive/2013/08/11/microsoft-dynamics-crm-2013-summary-of-what-s-new.aspx

Having now spent quite a bit of time working and getting familiar with the new platform, my intention is to try and determine what might be considered features that provide compelling reasons to upgrade assuming that you are on the previous version of the product. That assumption includes the fact that you've spent some time and effort configuring CRM to suit your needs. So while there may be some features that make it easier to achieve certain configuration options that were implemented previously - that in my opinion would not provide a compelling reason to upgrade as there generally is not going to be a burning need to rework the option that has already been implemented and working as desired.

Let's also stipulate that I'm not advocating against upgrading. Rather upgrading is a strategic decision that needs to be weighed with other competing priorities and budgets. And therefore this is more of a question of whether this should be prioritized now or perhaps down the line. And let's make no mistake about it - Microsoft will be spending their time and effort improving the new platform rather than CRM 2011, so if you're planning on performing any major process improvements then you'd be well served to bite the upgrade bullet so you can leverage new capabilities. This post is not for you if you fall into that category. If however you are somewhere in between and looking for features that just may take you to the next level - then this might help put things in perspective.

In my opinion too many posts out there just hype features of the product without providing critical analysis of the real world advantage they're likely to bring. In my list below, I am endeavoring to analyze the features on their practicality and real world application.


POTENTIALLY COMPELLING

• Business Process Flow - This is a net new feature, easy to configure/implement and can have a fairly significant impact to the ease of use. Especially for mobile devices such as tablets.
• Exchange Server Side Sync - Although the CRM Outlook Client has made tremendous progress over the years, it still remains the Achilles heel of the product - in my opinion. Mainly because of the decentralized nature of the configuration and troubleshooting headaches when there is a syncing issue. There were 3rd party solutions that allowed this to happen at the server level. And now Microsoft has finally added this functionality to the core product. If you are experiencing issues with contact synchronization then this could well provide the single compelling reason to upgrade.
• Mobile Optimization - The design of the product has been optimized to all mobile devices. Even more impressive - they have produce apps for the iPad, iPhone, Surface, and Android. If you think that mobile enabling your workforce will increase adoption and overall usage, then this is indeed a compelling reason for upgrading.

FACILITATIVE

• Business Logic - There is nothing new about this feature that could not be achieved with form jscript. In fact, you will still need jscript for more involved scenarios. But it does give less techie users the ability to configure simple dynamic behavior on the form and therefore become more self sufficient.
• Quick View Forms - This is definitely a nice new feature that makes it easy to display information from other related entities on the current form. But if you had that requirement previously, there were ways and means to achieve it.
• Real Time Workflows - I'd say this is a very nice new feature that could replace a lot of jscript and plugin requirements. But again this does not enable a capability that could not be achieved in CRM 2011.
• Access Teams - This is a nice new feature that is most likely to be used in scenarios of collaborative selling. The functionality was achievable with the standard team model in previous versions but is a lot more flexible and "on the fly" in the latest version.
• Actions - This extends the programming capability of the product essentially providing the ability to abstract business logic even further. I can see this feature as potentially very useful to minimize the amount of custom code logic making it easier to modify without having to update plugins etc. But again this does not enable a capability that could not be achieved in CRM 2011. 

INTERESTING

• User Interface/Navigation - The new version is pretty nice but in some ways can be more challenging to navigate. There are merits to the new user interface but also to the previous user interface. The flat user interface is nice but does not come without a cost (e.g. the benefits of alt+tabbing vs. having to re-navigate to where you were previously). I suspect there will still be a few improvements to come to help optimize this particular experience. And make no mistake about it - upgrading will require you to optimize your form layout to leverage and benefit from the new form layout. If you do not allocate budget for that effort, you are in fact likely to be negatively impacted by the new user experience.
• Command Bar - Microsoft thought it had it all figured out when they introduced the ribbon interface. Only to apparently discover that it was also too overwhelming for the average user and have now scaled it back to the simpler command bar. I do think that they are moving in the right direction with this design change but I hardly think this will make a meaningful difference to those who have gotten familiar with the ribbon interface.
• Dashboards - This feature has been slightly enhanced such that a different default dashboard can be configured for each area (sales, marketing, service, etc.) and per role.

NICE TO HAVE

• Auto-Save - Honestly? BFD.
• Social Pane - Ditto. Maybe I'm old school but this is a feature that I have rarely seen employed. There's already activities and notes and at some point the lines gray between what should be what. And this doesn't really help. Perhaps for larger companies.
• Quick Create Forms - If I'm not mistaken this is kind of a re-introduced feature from CRM 3.0 and earlier. Not revolutionary and unlikely to improve the user experience in any significant way. 
• Images - All entities now support associating images to them. Most likely usage is for contacts (although still doesn't sync with the Outlook contact image). Nice if you need it.

CRM Online vs. CRM On Premise

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.

CRM 2013 Form Design Guideline

This post is a work in progress and is far from being a definitive stance. Rather it is somewhat exploratory/tentative/philosophical based on experimentation using the CRM 2013 form designer. It may well be tweaked over time based on additional findings, user feedback etc.

In previous versions of CRM, the "default" design for forms was pretty straight forward. That is, when configuring a form, you could feel fairly "safe" in terms of selecting the "tab with one section and two columns" design option in terms of your starting point.

Sure on occasion when you knew you were adding lots of check box controls you might elect a 3 or 4 column section or conversely a 1-column section for a sub-grid. But the point is that you felt fairly certain you couldn't go too wrong in picking the "tab with one section and two columns" option in terms of how the form would render i.e. you were pretty certain that all fields would be easily visible and if the form was lengthy, you could easily add new tabs to simplify form navigation.

Maybe it's just me but I'm finding that I'm struggling a little bit more with the CRM 2013 forms. Suddenly we have a whole lot more form real estate to work with such that forms that used the previous "tab with one section and two columns" standard layout (2011, 4.0, 3.0, 1.2) , look rather sparse when viewing in CRM 2013. For example take a look at the upgraded contact form in 2013.

This is not helped by the fact that Microsoft has essentially eliminated the tab "jump" navigation option such that you have a lot of "scroll and hunt" in upgraded forms. As an aside, I personally don't know if eliminating the tab navigation is a long term decision - it seems to me that this could easily have been incorporated without compromising the look and feel of the 2013 form layout (e.g. by having a drop down where the general tab is showing or just having the tab names going from right to left at the top of the page). In contrast, they have indeed enabled a jump menu which enables navigating to different forms, so perhaps the long term design objective is to break up long forms into multiple mini-forms. Not quite sure at this point. But for now, I have to say that the lack of incorporating this tab navigation "bridge" to aid in navigation for forms upgraded from previous versions of CRM eludes me.

Anyway, the point of this post is essentially to be able to define a new "default" for CRM 2013 forms. There are definitely a lot more form design options - a simple comparison between the 2011 and 2013 forms illustrates this. We jump from having 2 tabs options to 6 tab options - this increases the form design options exponentially.

And while there are benefits to having more options there are also downsides to having so many choices. And correspondingly I've been finding that I've tended to vacillate a lot more than I did when working with previous versions of CRM. And so I've been looking for guidance as to how to go about thinking about form design in CRM 2013. But the most I've found is the following diagram which is Microsoft's take on form ergonomics.

And while this is helpful, it doesn't really address the overall issue described above. Therefore the objective of this exercise is to essentially take a stance on "default" form designs. That is, just as with previous versions of CRM, I'd like to have a fallback design that I can feel confident in taking based on the "you can't go too wrong" starting point premise. And when you reduce the requirement down, I think you come to the following basic guidelines:

1. The form should not look too sparse with all the new form real estate i.e. take advantage of the new space that is available on the form such that the need for scrolling down is greatly reduced.
2. Bring sub-grids that will be commonly accessed to the body of the form so that most of what the user will need will be right in front of them (I'll refer to these as "prominent" sub-grids).
3. Consider the laptop/tablet experience where monitors are smaller - some things that fit nicely on your maximized form on your desktop monitor will be cut off on these somewhat smaller screen resolution sizes.

We can also take a look at how Microsoft designed the core entity forms - account, contact, lead, opportunity - to hopefully draw some inspiration (as presumably they also took into account the above considerations).

When all is said and done, I will tentatively be employing the following "default" form design strategy:

• Any form where notes/activities is something that is relevant/tracked - I'll follow the guideline that Microsoft has used which is the "3-Column Varied Width" for the first form tab with the notes/activities in the central column using the new activity, notes and post control. This doesn't leave a generous amount of space for the other "prominent" sub-grids which we'll place in the third column but we'll live with that.

• Any form that doesn't have notes/activities but has other prominent sub-grids - I'll be employing the "2-Column Varied Width" for the first form tab. The right column will be for the prominent sub-grids with a more generous allocation of space.

• Any other form sections that are just a listing of fields - I'll be employing the "3-Column Equal Width" tab option.

That's pretty much it. This more or less throws out the  "tab with one section and two columns" which was the staple of previous versions of CRM - it just looks way too sparse in CRM 2013. In fact, as a general guideline it throws out having any section with more than one column (all the examples shown above have tabs with multiple sections, not sections with multiple columns - a tad confusing). As if you do add additional columns to the sections shown above, then you're bound to run into issues with fields being cut off when the actual form renders.

When you boil it down, it means that I will be limiting myself to the 3 form options below when designing the initial CRM form (using the 3 points mentioned above as a general guideline to determine when to use each option).

... with perhaps a few exceptions here and there for very specific requirements. And then of course, forms can subsequently always be tweaked using one of the other myriad options based on user feedback.

As I mentioned at the outset - somewhat philosophical - but this guideline helps me take quick and clinical decisions in terms of form design that I can confidently feel will will function as a very good starting point.

Handling Account and Contact Addresses

In my experience one of the most challenging issues to deal with in a CRM implementation is dealing with account and contact addresses. As is well known, the customer in CRM can either be the account or the contact. In a B2C business model, this is not likely to be a big deal as your contact record becomes the center of your universe and therefore all address information should likewise be on the contact record.

In a B2B business model, your customer is represented by the account records and the contacts (usually employees) belonging to these accounts. The challenge in this case is that you have address information on both account and contact records and the storage is neither efficient nor easy to maintain. At least out of the box. Before delving into the justifications for the above statement let's first stipulate that having the address information stored on the contact record in the default address fields (address1_line1 etc.) is important. The importance is because many default functions rely on this information. For example:

• Syncing contacts to Outlook - if you don't have the address information stored on the contact, the address information will be missing on the synced contact
• Mail merge - if you want to pull in contact address details while creating your merge documents, these will need to exist on the record

The reason why the storage of address information is not efficient is because in most cases, contacts of an account have the same address of the parent account (for example, generally with employees the address information for that employee is going to be the business address not personal address). Therefore storing it redundantly in 2 places is, well, redundant.

The reason why it is not easy to maintain is because it needs to be updated in multiple places. For example, let's assume you have an account with 5 contacts all with the same address information. If the business address changes then all 5 linked contact records should be updated as well. This of course can be done manually and perhaps even "optimized" by using tools such as the bulk edit tool (in which case the above scenario results in 2 manual steps) - however I am firmly of the opinion that if such updates are not automated the data will inevitably fall out of sync. Call me a skeptic but users can generally not be relied on to apply such manual procedures with rigor.

Fortunately if the scenario that you have resembles the one described above you can apply tools such as the Copy Utility or the Distribute Workflow to automate the updates. Both of these tools can be configured to copy down updates made from the account record to the contact records beneath them.

Frequently however companies have more than one office and the employees will be spread among these office. So simply applying a straight down copy will not work. A simple way to approach this is to use the account hierarchy whereby each office represents a separate account record with the relevant employees linked to each of the offices (with each having a separate address that will feed to the contact records). I would however argue that creating a separate account record solely to deal with the address issue is a little bit of overkill. And it results in disjointed information such that in order to "see" everything related to an account you need to view all account records and/or write custom reports in order to pull all the information together. Essentially you are creating "artificial" accounts in order to address the problem and I am not a fan of that. I believe that the account hierarchy should be used when the accounting requirements (reporting etc.) necessitate this. 

Aside from this, there are also other practical reasons where this approach can introduce problems. For example, if you have a mail merge requirement (or similar) whereby you want to pull the account name into the body of the letter, email etc. - the customer may not be too happy with the fact that letter head will go out from "ACME Medford" instead of just plain old "ACME". On the other hand, naming all these accounts with the same name of "ACME" will result in "duplicates" which will confound end users.

It would therefore be better to stick to a single account with multiple addresses and allow for the contact records to be associated to one of these account addresses. In the next post, I present an elegant approach to handling this requirement.

Data Import Options

When discussing data imports to CRM there are 2 distinct scenarios:

Data Migration

Data Migration generally refers to a large scale conversion effort and is typically performed as part of a CRM Go Live effort where data is migrated from the legacy system and is usually quite an involved effort. This exercise is technical in nature, typically performed by IT professionals and requires a great attention to detail. Correspondingly the migration logic is also usually quite involved. Meaning that there can be quite a bit of data transformation as part of the migration exercise. And if migrating from another system, you typically want to be able to connect to the data in the legacy database rather than extracting the data to an intermediate CSV file. So as a general rule of thumb when doing this type of data migration you typically want to be using a tool like Scribe to make it happen.

Importing Data

Importing Data generally refers to a more specific one-time or ongoing requirement to import data into a live CRM environment. For example, the need to import new leads into the CRM database. Typically this is a more straight-forward "one-to-one" exercise (i.e. no data transformation required) and if this is the case, the out of the box "Data Import Wizard" can be used for this function.

One thing to note is that while the "Data Import Wizard" has advanced by leaps and bounds, it still has one chief limitation - it only can import new records. If you're looking to use the wizard to update existing records, I'm afraid you're fresh out of luck. And if that is a requirement you'll once again need to look for a 3rd party tool such as Scribe.

For an excellent general overview of the features and functions of the Data Import Wizard please refer to this post. At the end of the post it lists a number of 3rd party tools that can be used to fill the gap in functionality of the Data Import Wizard should you encounter it. Although none of these products have what I would term a "no-brainer" price. Below is a rough comparison in pricing of the various tools on the market (as of this writing):

1. Scribe: 
• $3000: 15 user license
• $5500: 100 user license
• $1900: 60 day migration license
2. Inaport: 
• $1799: standard (should be sufficient for import function)
• $3499: professional
• $1195: 30 day migration  "professional" version which is apples to apples comparison for the Scribe migrate license)
3. Import Manager (best option if end user interface is required)
• ~$2500
4. Import Tool (attractive pricing) 
1. ~$1300: Full Version
2. ~$325: 60 day migration license

The following tools don't seem to be realistic candidates based on their price point:

1. Starfish 
• $4800/year: Basic Integration
• $1495: 60 day migration license
2. eOne SmartConnect 
• $4500 (not sure if this is a one time or per year fee)
3. QuickBix 
• $7000: 100 user license ($70/user)
4. Jitterbit
• $800/month: Standard Edition
• $2000/month: Professional Edition
• $4000/month: Enterprise Edition

The following tools do not seem to fulfill the requirement:

1. CRM Migrate
• Tool specifically built for migrating SalesForce to CRM
2. CRM Sync: 
• Very little information and no pricing for this tool

JSON vs. Ajax vs. jQuery - layman's guide

JSON, Ajax, and jQuery are all technologies that are frequently referenced and implemented to provide a great deal of flexibility and data manipulation options when customizing Dynamics CRM - in particular as it relates to Jscript customization. This post is therefore dedicated to providing a very brief layman's guide to each of these technologies and what the essential difference and function of each is.

JSON

JSON is simply a data format much like XML, CSV,  etc. Its primary function is to provide an alternative to the XML standard. For example (borrowed from Wikipedia), an XML format for a "person" entity might be as follows:

<person>
  <firstName>John</firstName>
  <lastName>Smith</lastName>
  <age>25</age>
  <address>
    <streetAddress>21 2nd Street</streetAddress>
    <city>New York</city>
    <state>NY</state>
    <postalCode>10021</postalCode>
  </address>
  <phoneNumbers>
    <phoneNumber type="home"> 212 555-1234</phoneNumber>
    <phoneNumber type="fax">646 555-4567</phoneNumber>
  </phoneNumbers>
</person>

Whereas the JSON equivalent would be as follows:

 {

    "firstName": "John",
    "lastName": "Smith",
    "age": 25,
    "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021"
    },
    "phoneNumber": [
        {
            "type": "home",
            "number": "212 555-1234"
        },
        {
            "type": "fax",
            "number": "646 555-4567"
        }
    ]
}

The benefit cited for JSON over the XML standard is that JSON is generally considered to be lighter weight and easier to process programmatically while maintaining all the other "aesthetic" benefits of the XML standard. For a more detailed analysis of these benefits, refer to the JSON web site.


Ajax 

Ajax is used for asynchronous processing of web pages. Meaning that once the web page has been loaded Ajax can be used to interact with the server without interfering with the already rendered web page i.e. such requests happen as part of background processing. Ajax is therefore typically used to make web pages highly interactive without having to reload the web page every time a new server request is made to retrieve data based on user interaction. A classic example is when you start typing an airport name at one of the online reservation sites and the drop down list shows relevant options based on what you are entering.

Ajax interacts with the server by means of an XMLHttpRequest object. And although the results that are retrieved can be in XML format (as is indicated by the object name) it is typically more common to retrieve the results in JSON format as that is easily consumed by JScript.


jQuery

jQuery is meant to simplify JScript programming by making it easier to to navigate, handle events, animate, and develop Ajax for web pages. The latter being most relevant for this summary. And therefore as far as
Ajax is concerned - jQuery leverages Ajax for performing server requests and simplifies interaction with the Ajax layer. Put simply, Ajax is the tool that jQuery will use for handling asynchronous server requests. So when you see the "$.ajax" method in your JScript code it means that jQuery ($.) is being used to execute an Ajax request (ajax).

At this point in time, only the jQuery ajax method is supported by Dynamics CRM. Or to quote from the SDK:

The only supported use of jQuery in the Microsoft Dynamics CRM 2011 and Microsoft Dynamics CRM Online web application is to use the jQuery.ajax method to retrieve data from the REST endpoint. Using jQuery to modify Microsoft Dynamics CRM 2011 application pages or forms is not supported. You may use jQuery within your own HTML web resource pages.

CRM Relationships: Part 2

In my previous post I concluded by saying that, in my experience, Connections tends to cause a degree of consternation in terms of relationship design. This is a fairly difficult topic to approach but in this post we'll review why that is and suggest an approach for determining whether using Connections is an appropriate choice.

The main reason why Connections can be confusing is that it can - by definition - be used to relate any two entities in the system together. And therefore you always have the option of using Connections instead of a custom relationship to set up the relationship between two entities.

I think that the use of Connections often can relate to something more fundamental in terms of relationship design. For instance, Connections come into play most frequently for businesses who use CRM first and foremost as a contact management solution. And in such a scenario Connections are typically used to relate contact and account records together to record current and past associations with one another. And very often you will have many different account and contact types (company, vendor, supplier etc.) being represented within the account and contact forms. The more fundamental question in this case is whether  you should be using the account and/or contact entity to represent all these incarnations or whether perhaps you should create a custom entity to represent a specific type of account. For example, if you're an investment capital firm, should a Bank be an account of type "bank" or should it be it's own separate entity?

The answer to that question (which is not really the topic of this post) will depend on various factors but ultimately should be driven by whether it's really necessary to make the Bank a type of account. Is the Bank a customer in this example? Are you likely to send marketing initiatives to the Bank? Or is it just a piece of compound information that is related to your actual customers i.e. your investors? I think the answer to the custom vs. account "flavor" decision lies within these questions.

Coming back to Connections - well more often than not once you're dealing with a custom entity then you're more likely to want to use a custom relation to define the relationship than using the Connections. Because that relationship is likely to be highly defined. Using the Bank/Investor example - if you need to record the bank that the investor uses then simply define the Bank as a lookup attribute of the Investor. There is simply no need to define that relationship via a Connection.

The above example uses a custom relationship for the sake of illustration but it is by no means limited to relationships to a custom entity. It can just as well be the case when relating an account to a contact by means of a custom relationship. The crux of the decision of whether to use a Connection should be based on how tightly the relationship is defined. I think the rule of thumb is that Connections should be used when there is not a highly defined relationship between entities.

For example, the relationship between a Contact and a Lawyer might be represented by a custom relationship if a Contact only can have a single Lawyer. If however the Contact can have multiple Lawyers and each Lawyer could be related to other Contacts in the system (N:N), it would probably be better represented as a Connection. You could of course also define a custom N:N relationship for this. The decision between these two options might be determined by the "centrality" of that relationship. That is, if a Lawyer is just one of the many "useful" client/vendor relationships that you want to store for the Contact (among say, doctors, plumbers, electricians etc.) then you could easily see that you'd have to start defining a whole lot of custom relationships for each one. This will result in your data being a lot more distributed i.e. click here to view lawyers, click there for doctors etc. (and the same is true for views, reporting etc.). However if the Lawyer is a critical piece of information that needs to be stored for your Contacts, then you probably should be separating it out into its own custom relationship.

Perhaps we can therefore summarize as follows:

Connections is not an essential part of the CRM database as in theory it can be replaced through the use of custom relationships. Therefore Connections should not be used in cases where the relationship being represented is important or definitive for your data. Separating out those relationships in terms of navigation via the use of custom relationships is probably a good thing. For other interesting, miscellaneous or viral type relationships that might be perused by a sales rep prior to heading into a meeting or for other similar less defined ad-hoc business procedures - using Connections as a general centralized repository is probably the way to go.

CRM Relationships: Part 1

There are many different ways to set up relationships in Dynamics CRM. The good thing about this is that you can accurately and economically model CRM relationships to mirror any real-world counterpart. The challenging thing about this is that inevitably when you have so many choices it can be confusing knowing which option you should go about choosing. And of course that decision has long term design implications so you ideally want to get it right or you'll most likely end up with a design that is not optimal. You could of course change this representation down the line but such a decision will involve data migration, modifying reports etc. etc. and therefore it is more than likely that the design you "go live" with will be a fairly long term proposition.

So the question becomes when deciding to relate 2 CRM entities - which relationship type should I use? At a high level you have the following options available:

1. 1-Many
2. Many-1
3. Many-Many
4. Nested
5. Relationship
6. Connection

I think 1-Many and Many-1 options are in general fairly obvious and we don't need to spend much time discussing those.

The Many-Many relationship can also be obvious but I think it also can be deceptively involved. That is because there are a number of different types of many to many relationships. Without delving into the detail right now we can summarize many to many relationships into the following 3 categories:

• Classic N:N
• Proxy N:N
• Complex N:N

We'll explore the many to many relationship options in a separate blog posting. For now, we can summarize that the classic N:N relationship as it appears in the CRM entity customization should be use to model any "simple" N:N relationship i.e. where entities are related to one another without having to "explain" the nature of their relationship.

The Nested relationship is just an extension of the 1:N and N:1 relationships where an entity is related to itself. The biggest decision when it comes to nested relationships is whether it makes sense to relate an entity to itself rather than creating a custom entity to house that relationship. For example, tickets in a professional services organization that deal with break/fix type issues related to a supported product - it is often a requirement to bundle related tickets that address the same fix or release together. This can be done by designating a single parent ticket and linking all other related tickets underneath it or it can be done by creating a custom entity (call it "Ticket Group") that functions as the umbrella for grouping the related tickets. Both approaches can be valid depending on the requirement but I personally have found the need to created a nested relationship to be few and far between. Therefore as a rule of thumb I would venture to say that unless you can find a good justification not to go the way of creating another custom entity then that is what you should be doing.

Relationships and Connections are pretty much the same thing. In fact Connections came to fully replace the Relationships option in CRM 2011 so making a decision as far as Relationships go is pretty easy - do not use this option. Pretend it's not there and hide it via the security model so it doesn't confuse. If you have inherited Relationships from your 4.0 upgrade then consider migrating to Connections as part of the upgrade exercise. If that is not an option then hide Connections until you're ready to transition because having them both is just confusing.

That leaves us with Connections. I personally find that Connections (and its precursor Relationships) causes a degree of consternation in terms of relationship design. But we'll delve into that in the next blog posting.

A brief history of XRM

I was recently asked by a client about the difference between CRM and ERP solutions. In particular I was asked whether a particular custom solution we were trying to implement fell more neatly into a CRM or ERP framework. As the answer I provided quickly evolved into a brief history of CRM or XRM (according to yours truly), I decided to share in this post -

Generally CRM is considered to be “front office” and ERP is considered to be “back office”. And what you’ll find is that the definition of what is front office and what is back office will vary from company to company depending on their business model. You’ll similarly find that CRM and ERP products have areas of overlap where a particular function can be achieved in either system. Which system to do it will ultimately depend on where it makes sense to perform the hand off between front office and back office. If they’re both equal, then you’d typically look to the system that’s traditionally more geared towards handling the requirement. For example – take payments. Typically that’s geared to the back office because the minute you enter something like this, you’re entering a world of credit memos, taxes, recogition, collections etc. But there’ll be many cases where clients would need to handle such a requirement directly from within their CRM application.

Another good example – which is more to the point – is inventory or product management. That is more typically handled by the back office applications as inventory movement is inherently an accounting activity which ERP systems handle quite well. But again you’ll find that there are areas of overlap here too.

I think all the above though is somewhat beside the point. Because I think the XXX (Customer Requirement) that we are talking about is not a typical ERP function. In fact, I’d venture to say that it is neither an ERP function nor a CRM function.

So why are we looking at CRM for this? To answer that question you’ll have to go back to our first session where I presented it not as a "CRM" solution but rather as an "XRM" solution. CRM stands for Customer Relationship Management and grew out of the need to manage the selling process. As being able to sell is inherently linked to performance on the operations side, it was necessary for sales folk to have insight into the service delivery organization. Such as support and ticketing to understand how things were proceeding on that front (as information is power in terms of being able to anticipate and predict the customer’s overall level of satisfaction). And of course, marketing is just the flip side of sales i.e. one feeds the other and vice versa, so a CRM solution is not complete without a marketing module. This is why the classic definition of CRM is an application that covers sales, marketing and service.

But then as a CRM solution essentially became an application that deals with the “front office” as driven by the need to understand the customer’s or prospect’s overall experience, there were always going to be cases where in a particular business model, other types of information would be essential to this visibility. For example, a sales person going to a client meeting might need to be able to easily access information housed in a home grown application, medical records, or purchase history. As such – being a front office application – a CRM solution became something that needs to essentially be something that can model a business in whichever shape or form it takes i.e. CRM solutions needed to be flexible enough to be configured to model a particular business model.

At this point, a CRM solution is in effect doing a whole lot more than just Customer Relationship Management. Not only might the “customer” be a vendor, partner, reseller etc. (i.e. not the strict definition of “customer”), but also the platform became highly configurable such that it is more common than not to incorporate functionality that does not fit into even a loose definition of “customer relationship management”. Hence the term “XRM” was born which unofficially stands for “anything relationship management” attempting to reflect the fact that the platform is not just limited to “customer relationship management”. Of course even the “R” in XRM might neither be relevant based on the above description since we don’t need to necessarily even be dealing with a “relationship”. But as far as I know, from a naming standard convention, we haven’t yet evolved beyond “XRM”.

In contradistinction to the above, ERP solutions are typically (at least at this point in time) more standardized than CRM solutions. This is because the “back office” tends to be more standardized from business to business than the front office. To be sure, back office systems have deep and complex functionality but in my humble opinion (from a non-accountant) standard accounting practices have in principle not changed all that much since the double entry system was first invented (perhaps a little over-simplified) and therefore companies are more likely to be modifying their business practices to model the “best practice” of the ERP solution they are implementing than the other way round. Whether the last statement is true or not, I think you’ll definitely find that CRM solutions are much more flexible solutions than their older ERP counterpart due to the nature of the business problem they are trying to solve.

In summary, although I don’t think the XXX requirement is either a classic CRM or ERP type application, I do believe that it is a great candidate for the XRM model. And we definitely require the flexibility of the XRM platform in order to accommodate the requirements.

Simplifying Navigation: Thoughts

One of the key factors to a successful implementation (of anything) is to make navigation as simple and intuitive as possible to the end users. Or to perhaps put it another way - confusion is the enemy and should be avoided at all costs. This is not to say that the default navigation of Dynamics CRM is unintuitive - in fact I would argue that the opposite is in fact the case. However there are many different levels of end users of Dynamics CRM and this often depends on the kind of market vertical it is being applied to.

For example, if CRM is deployed into a Professional Services organizations the end users are likely to be very computer literate and will likely take to adopting CRM like a duck to water and may even curtail substantial simplification of the UI so that they can navigate the application as they see fit (which may vary from end to end user - a practice which I personally don't encourage but certainly won't stand in the way of).

On the flip side, if CRM is deployed into a Financial Services organization the end users are likely to be much less computer literate. And any additional "options" for navigation that I personally may think are quite intuitive may in fact herald the unwanted "confusion" bogeyman.

Therefore - returning to my earlier statement - what I personally may think about the relative intuitiveness of the application is irrelevant. I need to be able to try and view it through the end users who will ultimately be navigating the application and responsible for the success of that deployment. And consequently it is crucial to take into account and understand what the level of the typical end user is going to be when it comes to configuring the end user interface.

Bottom line - if in fact your typical end user in a CRM deployment is the type that is on the "technology challenged" sector of society, then you'd better ensure that your CRM is locked down to a certain extent limiting navigation to the minimum in order to be able to do what needs to get done.

That all being said, I do believe that there are certain universal truths when it comes to the deployment of the UI navigation:

• Keep it relevant - For example, if a client does not use invoices in their CRM deployment, then make sure they don't see the invoices navigation option.
• Permissions - Ensure users only see what they should and nothing more.
• Simplify - Do not over complicate when it comes to design. Balance the "purist" relational approach with realistic scenarios in terms of how users will actually use the system (a key metric for this is the number of clicks involved in a particular action)
• Walkthroughs - Prepare walkthrough documents that users can reference for a particular function
• Training - Obviously provide the necessary training

In the next series of posts I will attempt to highlight some of the more novel UI navigation simplification ideas that I've experimented with.

Read Optimized Forms

I have been wanting to check this new feature out for a while that was introduced along with update rollup 7. There is a pretty decent write up of this new feature and how it can be enabled in the MSDN blogs.

While this feature is interesting and can further optimize the time it takes to open a form there are some important limitations. That is if you scroll down to the bottom of the article it contains a table showing when the "read optimized form" is loaded. And you will quickly realize that if a form has client side scripts it cannot take advantage of this feature.

I tested this out. I took a completely vanilla invoice form and - as advertised - when I opened the record it opened in the nice and clean Read Optimized format.

I then went ahead and added a jscript web resource to the form. To be clear I just added the web resource that are "available to the form", I did not actually wire up any OnLoad, OnSave, or OnChange functions. That is, this web resource was effectively doing nothing.

After I published this, I onced again opened up the invoice form and the Read Optimized setting was no longer respected i.e. it opened in full edit mode. In short, the mere presence of a jscript resource will make the form non-Read Optimized capable.

I am sure there are reasons why it was designed this way. There is certainly some jscript logic that can be impeded by the Read Optimization. Although I guess I'm wondering why Microsoft couldn't have introduced a 7th form type (in addition to create, update, read, disabled, quick, and bulk) in order to target this form option rather than taking the "all or nothing" approach. Perhaps that will be accomodated in a future enhancement or perhaps I haven't properly considered the technical issues that would accompany such a feature...

Anyway, in terms of the practical usage of this feature in it's current incarnation - I have to say I'm a little skeptical.

First of all, it has to be said - what percentage of forms have no jscript acting on them? Jscript really brings tremendous flexibility to form rendering etc. to the extent that not many forms (at least the ones that I touch) remain jscript-less. And even for those that do not have any jscript acting on them - it is of course quite likely over the course of time that you'll want to add some scripting to the form to accomodate some customer requirement. And at this juncture - assuming the client has been happily using Read Optimization for this form - you'll have to explain to the client that this feature that they've now come to know and love will no longer be available.

So when all is said and done, I think I'll personally be employing this feature quite sparingly.

CRM 2011 Document Management Settings: SharePoint Best Practice

This being a blog about Microsoft CRM it is perhaps a little bit of a reach to be discussing SharePoint best practices. But as the technologies overlap in this particular area, I think it might be worthwhile to briefly touch on some high level SharePoint best practices as those features could play in quite well with what we are ultimately driving towards i.e. a best practice recommendation in terms of integrating the 2 technologies. This post will be focused exclusively on the content management feature set of SharePoint as this is the area of integration that is provided out of the box in CRM 2011.

The way in which documentation is typically stored and classified on a File Server is by employing the use of folders. You physically stored different types of documentation in different folders in order to organize your content. This idea is still very entrenched in the thinking of most end users and is reinforced by the way in which we store content on our personal computers. Using this approach, a particular document is located in a single folder on a File Server.

SharePoint introduces the concepts of content types and metadata for tagging content. Using this approach, instead of storing content to a particular folder, you instead "tag" it to have certain defined characteristics when saving the document. The tags allow the document to be organized in different ways and the navigation can also vary depending on how the tags are grouped. By way of example, this is very similar in concept to how gmail works where instead of assigning an email to a particular folder as is the case with other email systems, you instead assign labels to the email. If 2 labels are assigned to a particular email, it can be navigated to using either of the labels. Another more immediate example is this very post which has been assigned two labels and therefore can be navigated to from each of those labels.

It should be mentioned that although SharePoint also allows the use of folders in the document libraries, it is generally best practice to avoid using them to the extent possible. This is because this feature is “retro” in terms of thinking; it also functions as a crutch to those who are more familiar with the old File Server approach and therefore may lead to bad content management practices. Having said all that, there may be valid reasons to use folders in SharePoint but generally they should be left to store imported or unstructured content.

Therefore, in summary, a much better content management practice is to leverage SharePoint content types and metadata for tagging content. Document tagging is a building block of SharePoint and can subsequently be used for many other SharePoint features such as:

• Aggregation (for content navigation as described above)
• Search scopes
• Audience Targeting
• Workflow
• Templates
• Office integration

All the above features can enable the improvement or elimination of internal inefficiencies within your organization. But that is beyond the scope of this discussion.

Previously we reached two conclusions with regards to the CRM/SharePoint configuration settings:

1. The Individual Path is a generally favored approach
2. Selecting too many entities for associating Documentation can lead to confusion and disorganization

Next we will combine the above SharePoint best practice to arrive at what could be considered to be a CRM/SharePoint integration best practice.

CRM Reporting Options

There are many and varied ways of reporting out of CRM. Most report requirements can be achieved using the CRM front end without having to resort to writing custom reports using SSRS and the like.Very briefly the following outlines the reporting options in order of complexity:

• CRM Views and Advanced Find - Being able to defined and save Advanced Find and system queries is by far the most common type of "report" in CRM. As long as your requirements are just to return records from a single entity and the criteria for the report can be supported by the Advanced Find query options, this is the report to be using. This report has been enhanced significantly in CRM 2011 by including visualization and drill down options using the built in charting capability

• Excel Reporting - Excel reporting is an extension of the CRM Views allowing for the data to be dynamically exported and then once you are in the Excel environment, you can use the full capability of Excel visualization to render pivot tables, charts etc. These can be saved as actual reports in the Reports area of the application as they can be connected to live data like all of the other reporting options.

• Charting/Dashboards - With the introduction of CRM 2011, this method of reporting/visualization has to be next in terms of level of complexity (i.e. not very). Anyone who is familiar with creating charts using the likes of Excel (as illustrated above) should be able to use the CRM front end tools to create charts and, by extension, dashboards (which are just a bunch of charts, views etc. grouped together in a single presentation to present key metrics in a visualized format). Charts are of course also then used to integrate and extend the CRM views as already illustrated above.

• Report Wizard - This walks you through the process of building a report. This is not too dissimilar from building a pivot table report. This approach is often useful as a starting point to build reports as you can then export the generated "rdl" and use SSRS to tweak the report further and add features not supported by the wizard.

• SSRS Reporting - Typically you would use SSRS to create a custom report because the query, format, grouping, layout etc. that is required cannot be supported by any of the above. Ensuring your report adheres to the SDK guidelines will allow the report to integrate in a context sensitive manner into your CRM forms and views. Most times a SSRS report is also fairly straight forward that involves creating a query joining several tables together and then rendering the results into a table. However the sky is pretty much the limit when it comes to SSRS Reporting as you can leverage the full capability of both SSRS and SQL in order to manipulate the data in exactly the way you want.

• Data Warehousing/Data Mining/Cubes etc. - This gets us into the most sophisticated area of reporting which typically involves extracting the operational data into a separate data warehouse and building cubes against that data to be able to create Key Performance Indicator dashboards, view trending etc. Customers typically do not get to this level of reporting until they have been using the CRM system for some time and have accrued lots of data that can be analyzed to identify interesting patterns, trends etc.There are a number of 3rd party ISVs that directly integrate with MSCRM and generally greatly simplify this activity.  

CRM/SharePoint integration: Best Practices

In a series of previous posts we reviewed the various settings available for configuring the SharePoint integration with CRM 2011. We started off by reviewing the primary configuration options available. We then attempted to take into consideration the pros and cons of each approach i.e. the entity based approach and the individual path. We then reviewed some SharePoint best practice considerations.

The intent of this post is to bring that all together to arrive at a general purpose recommendation for CRM/SharePoint integration.

As  has hopefully been illustrated in the series of previous posts, there are challenges to either of the SharePoint integration configuration approaches when it comes to having the SharePoint integration take place at multiple CRM levels. In addition to this, it can also been argued that this could also lead to confusion and/or inconsistency in terms of where content is stored. Conversely, we described the benefits of  using SharePoint metadata as part of a SharePoint content management best practice.

Therefore... perhaps we can keep this relatively simple in CRM while leveraging the power of SharePoint to organize the documentation? I'd venture to say that we can achieve this objective by doing as follows:

• Only create the CRM/SharePoint integration at a single level (most likely at the account or contact level).  

• Classify all content stored using SharePoint Content types. For example, we will store a quote for account ABC at the account level but give it a content type of "Quote"; we  will also store a "Statement of Work" for account ABC at the account level but give it a content type of "SOW". And so on and so forth.

By doing so, we achieve the following stated objectives:

• Keep it simple:  In CRM we have only a single location to navigate to for storing and viewing content. This also avoids the "user discretion" problem described.

• Organization: By leveraging SharePoint content types/metadata we are taking advantage of the power of SharePoint for organizing content as described in this post.

Obviously this "best practice" is not a one size fits all and there will always be other things to take into consideration based on specific customer requirements and analysis.