Posts
  • Michaeljon Miller

    What is an application platform?

    • 0 Comments

    I'm working on a short paper / presentation that describes my position on what an application platform is, the services it provides, and what it does for the application developer. This is this initial outline. This doesn't talk about a specific (LOB) application platform, but instead talks about a set of requirements that a platform should meet. One of the things that started me down this path was noticing partners using the Microsoft CRM application as a platform.  I'm curious to understand how other people think about this "problem" and whether there's any benefit in pursuing the definition.

     

    I want to set the stage by talking about what I mean by "application" first. This paper will look at large-scale line of business applications. This isn't to say that productivity applications such as Office aren't applications, it's just that they're not the class of application that necessarily has this set of requirements.

       

    Diversion

    1. Line of business applications
      • Tools for automating the business process
    2. Productivity applications
      • Tools for executing business tasks

       

     Caveat

    • A platform isn’t useful without an application
    • It’s not possible to determine requirements without an application
    • Most people aren’t interested in buying a platform – they want an application
    • An application is the start, customers and partners want a solution

     

    Then I'll talk about the necessary and sufficient requirements for a platform. I'm trying to keep this list as short as possible because most application logic tends to leak "down" into a platform thereby making the platform less applicable to other applications.

    Platform services

    1. Identity and roles
    2. Rich type library
    3. Security
    4. Storage and persistence
    5. Extensibility
    6. Process identification and execution
    7. Solution packaging and containment
    8. Deployment models
    9.  

    Identity and roles

    1. What is a “user” of the system?
    2. Collaboration scenarios drive SaaS
    3. Roles come in many flavors
      • Work roles (position), security roles, reporting structures
    4. Roles are facets of an identity
    5. Relationships between roles are primary
    6.  

    Rich type library

    1. Meta-types
      • Constituents
        • party, role, relationship, contactMethod
      • Collaborations
        • collaboration, interaction, goal, participation, structuredDocument
      • Unstructured collateral
        • simpleDocument, annotation
      • Opaque data
        • Reference data, lookup tables, auditing, etc.
    2. Data types
      • enumerations, elemental types, higher-level types

       

    Security

    1. Authorization and authentication
    2. Security roles are privilege collections
    3. Privileges provide access
      • User interface, data, process, tasks
    4. Security roles trump work roles
    5. Storage and persistence

    6. Storage structure is schema independent
      • From an application perspective
    7. Storage is disconnected from logic
      • From an application perspective
    8. The platform controls persistence
      • Types DO NOT know how to persist themselves

       

    Extensibility

    1. Schema
      • Types and storage
    2. Presentation
      • Navigation
      • Data capture
      • Clients and user interface
    3. Process
      • Business rules (simple and compound validation)
      • Business logic (“big” and “little” process definition)
      • Process execution structures (message definitions)

       

    Process identification and execution

    1. Message definition and execution
    2. Actor identification by identity and role
    3. Task and work lists
    4. Loosely bound to an extreme
    5. Declarative vs. imperative
    6. Long-running business processes
    7. Transactional business logic
    8. One entry point to execute processes
    9.  

    Solution packaging and containment

    1. Everything is an extension
      • Schema, process, presentation
    2. All extensions are named and grouped
      • Names are like CLR strong names
    3. Packages are like applications or modules
    4.  

    Deployment models

    1. Multi-tenancy is important
    2. Multi-language per tenant
    3. Presentation is independent of the platform
      • But the platform can provide presentation tools
  • Michaeljon Miller

    CRM callouts are just plain hard to write

    • 9 Comments

    Building callout handlers for MS-CRM is hard. It’s just plain hard. Not because writing a COM object that implements an interface is hard – we’ve got a boatload of tools to help with that. They’re hard to write because the model is inconsistent and incomplete. The v1.x callout interface is very simple and has very simple semantics. It’s this simplicity that makes it useful, powerful, and hard to use.

    The callout interface looks like so. You’ve seen it in the documentation, and probably tried to implement it somewhere along the way. You might have even succeeded.

    [

        uuid("F4233E5B-17DC-4661-9ABC-6707A9F99215"),

        dual

    ]

    interface ICRMCallout : public IDispatch

    {

        HRESULT PostCreate([in] int ObjectType, [in] BSTR ObjectId, [in] BSTR OrigObjectXml);

        HRESULT PostUpdate([in] int ObjectType, [in] BSTR ObjectId, [in] BSTR OrigObjectXml);

        HRESULT PostDelete([in] int ObjectType, [in] BSTR ObjectId);

    };

    In case you’re a C# person, here’s the definition from the SDK. There’s not much difference.

    [GuidAttribute("F4233E5B-17DC-4661-9ABC-6707A9F99215")]

    public interface ICRMCallout

    {

        Int32 PostCreate(Int32 ObjectType, String ObjectId, String OrigObjectXml);

        Int32 PostUpdate(Int32 ObjectType, String ObjectId, String OrigObjectXml);

        Int32 PostDelete(Int32 ObjectType, String ObjectId);

    }

    The basic semantics are as follows:

    ·         PostCreate provides the handler with the instance XML as supplied by the platform consumer (this could be the application, integration, or another callout). The first parameter is self-explanatory, as is the second. OrigObjectXml on the other hand deserves some explanation and some discussion. I’ll get to that in a bit because it applies to PostUpdate as well.

    ·         PostUpdate follows the same pattern but happens after the changes have been submitted to the database. Note that I said “submit” and not “commit”. There’s an important distinction and this is one of those incomplete things about the callout interface. This problem applies to PostCreate as well so I’ll talk about it in a bit.

    ·         PostDelete is the simplest to understand and one of the hardest to use. It’s fired after a soft delete request is made to an instance. The only information supplied is an instance identifier (the type code and id).

     

    Side note – if you’re implementing a callout handler in C# you need to declare your class as follows. The thing that seems to be one of the biggest PSS issues is the GUID. It is a requirement that you add your own GUID. Don’t use the one from the documentation because someone else might have made the same mistake and now there are two registered COM classes implementing the same interface. Well, that’s not entirely true, there’s one – the last one to get registered.

     

    [ClassInterface(ClassInterfaceType.AutoDispatch)]

    [GuidAttribute("put a CLSID here")]

    public class MyCallout : ServicedComponent, ICRMCallout

     

    Submit vs. Commit

    Transaction semantics are not well-defined for callouts. Sometimes the callout is made inside of a transaction and sometimes it’s made outside. The sometimes isn’t well understood on the CRM team right now. That’s one of the problems with building large software systems from the ground up with a growing team. But that’s a discussion for another time. The important bit is that the transaction rules around callouts are ill-defined and the only assumption a callout author can make is that the instance in question is likely inaccessible for the duration of the callout function execution.

    What does this mean? Well, first it’s important to know how the platform calls the handler class. Let’s start by looking at the basic flow.

     

        COSERVERINFO si;

        si.dwReserved1 = 0;

        si.dwReserved2 = 0;

        si.pAuthInfo = NULL;

        si.pwszName = wszComputerName;

     

        MULTI_QI qi = { pIID, NULL, 0 };

        hr = CoCreateInstanceEx( callout, NULL, CLSCTX_REMOTE_SERVER, & si, 1, &qi );

    The thing to notice is that the platform is using an explicit out-of-proc call. Why? Because we really don’t want the platform to crash if the callout crashes. This means that the callout handler must be registered as an out-of-proc server, which is easily done using a COM+ application. But that also means that callouts can’t just be dropped on the server machine and registered as COM objects.

    Let’s look at how the platform actually calls a handler.

    for each pCallout

    {

        // call the event handler

        pCallout->PostCreate(otc, bstrId, bstrXml);

     

        // then release the interface pointer

        pCallout->Release();

    }

    The platform will walk the list of registered callouts and for each one it finds, and can create, it’ll call. But isn’t the definition of PostCreate supposed to return an HRESULT? Yup, and it’s being ignored by the platform because there’s no clear answer about what should occur if a given callout fails. This is good and bad. It’s good because the platform doesn’t get mired down in the transactional details necessary to “fix” things that might break in an unknown chunk of code. It’s also terribly bad for the same reason.

    Oh yeah, and all this happens deep, deep, deep in the platform infrastructure. So deep that we might as well consider them the equivalent of a database trigger. Not that triggers are bad or anything, many GP ISVs have built all kinds of solutions by adding triggers to the GP database. They’re bad because they happen to look like a trigger, and happen to behave like a trigger, but just aren’t triggers. There’s just no trigger context available. One thing that makes them very much like triggers is that they happen for every WRITE (for example) and WRITEs happen all the time in the platform for reasons that callout authors usually don’t care about (like on a security descriptor update because someone changed a role – does the callout care, probably not).

    The next thing to notice about the callout is that the platform calls it inline with the rest of the business logic happening on the current thread. This means that your customer (the application user typically) is sitting there patiently waiting for your callout code to complete. If you’re making a long-lived call to another application the user is effectively blocked from getting any other work done. It also means that the platform is blocked. At least one critical resource is waiting: the thread servicing the user request. But there may be other resources blocked depending on which platform call was made: database resources.

    Why does this matter?

    Well, if the database is blocked because the platform is in the middle of a transaction it means that other callers can’t get at the blocked resource. That caller might very well be your callout handler if it needs to call back into the platform to retrieve data. And, if the callout author is well-behaved then the callback to the platform is happening over SOAP, and that’s clearly an out-of-proc call.

    This is what I meant by incomplete earlier. The callout only gets the information that was supplied by the original caller and this data is clearly incomplete. The auditing, owner, and default data is missing from the XML for PostCreate, and it’s probably old data for a PostUpdate. The way around this is simple: call back into the platform to query the rest of the data. Oops, now we’re in trouble because we’ve probably gotten ourselves into a deadlock situation, and that’s not a good thing.

    There’s also the issue of PostDelete. Normally the delete handler works fairly well. It gets called when something gets deleted and it gets enough information to do something about it. Let’s say that a Sales Order Detail was deleted though. The callout will get the line item instance ID and nothing else. How should it deal with this? There isn’t a story. You could cheat and make an ADO call into the database to try to read the Sales Order ID from the just-deleted line item, but this is bad because we don’t want you reading the database directly (because we can and will change the structures from release to release) and that pesky deletion service might beat you to it and really delete the instance. Like I said, callouts are incomplete and themselves don’t have enough context to help the callout author.

    Uh, then what?

    Given all that, what’s a callout coder to do? First, make the callout hander as short, fast, and simple as possible. I recommend converting the callout parameters, along with some general contextual data, into a message and dropping it in a queue somewhere. This doesn’t have to be anything fancy. It can be MSMQ, a database table (in another database please), or even in the file system. This way control is returned to the platform as soon as possible which means control is returned to the user sooner. The next thing is to use a service (or other application) to watch that queue and do the expensive work out-of-band. This service is just another platform user and can call into the API to read the rest of the necessary data. Well sort of.

    Remember when I mentioned incomplete (yeah, I think I’m starting to repeat myself here). Sure, not getting the complete instance data is a problem, and if that problem were solved we could mitigate a lot of the call-back-into-the-platform problems. But what’s really missing here is the contextual information necessary to know what to do about the callout. For example, who made the original platform request? Was it an application user or was it your own callout code? There is no way to know. Part of protecting the platform from callout crashes means that getting context (current transaction, current method, and current user) to the callout is really difficult.

    For now we’re stuck. We can’t change the interface without breaking all those people who’ve already started writing handlers. We can add a new interface, but it’ll still suffer from a lot of the same problems. We need to go back to the drawing board and rethink how all this stuff can and should work, where it might best fit, how the ISV community might use it, and what tools should be supported (i.e. COM? VB6? .Net?)

    If you’ve read this far looking for enlightenment, I’m sorry, I don’t have the answer. I’ve told you what’s wrong with callouts, but you probably already knew that. Though I have given a little insight into how they work and I’m hoping that helps you understand how to write handlers that behave well. But, for now, building callout handlers for MS-CRM is hard.

     

  • Michaeljon Miller

    Roles, Privileges, and CRM Security

    • 8 Comments

     

    One of the feature requests that we get on a regular basis, and a feature that a few enterprising ISVs have started to build, is the ability to have per-role UI. This also comes under the name “field-level security” – but only because hiding access to attributes in the UI is a possible, but insecure, solution to the problem. That is, one way to implement per-attribute security is to implement per-role UI. At the surface that makes a lot of sense, but there’s always something deeper than the surface that we need to worry about first.

    Before we talk about why implementing per-role UI is only a surface-level solution, and an insecure one at that, it’ll help if we figure out what a role is in the context of MS-CRM. Believe it or not neither the platform nor the application really has any notion of a role. It’s a concept that was invented as a way to package privileges in a way that’s easy for administrators to manage. Current CRM builds have just over 320 distinct privileges. Managing these individually would be extremely difficult. So, the idea of a security role as a privilege container was created. As a side note, early V1 builds had 20 or more security roles, it wasn’t until the last minute that we trimmed that list down to a manageable 7.

    So, what does this all mean? It means that the platform and application worry about what users can do by inspecting the set of privileges that the user has. If a service needs privilege X to continue, then that user better have a privilege X somewhere in one of the roles that are assigned to that user. Which raises another side issue: which direction is the assignment? Do we assign roles to users, or users to roles? If you’re a CRM administrator then it really doesn’t matter. You assign a role to a user. If you’re a CRM developer on the platform team working on V1 code, then you assign the user to a role.

    “OK”, you ask, “what does this have to do with per-role UI?” Well, it has everything to do with it. The application loads the one form for the entity (ignoring the special forms like Quick Create and Print) but looking in the form cache. This is a very fast and very simple lookup (ignoring the organization- and language-specific stuff). We could add a second level to the map key, perhaps something like a role identifier. That wouldn’t be difficult to do. We look at the user and then grab the information about that user from the cache. Simple enough. Next we look at the cached user information. Hmm, there’s no role information in there (remember, I said that the application doesn’t really know about roles), so we’ll need to extend the cache to hold on to the users’ roles. It’s all just code and memory, so that one’s simple enough. But wait, isn’t it possible to assign a user to multiple roles? Sure it is.

    That’s where the per-role UI falls apart. It’s entirely possible that a user has been assigned to multiple, overlapping roles. If each role has an associated UI, and those UI are different, then we don’t have a way to know which UI to load for the entity, because we don’t know which role governs the interaction.

    One option is to introduce a new concept orthogonal to the security role. Well, it’s really not orthogonal because the management of security roles and these new “UI roles” would best be done in the same spot, by the same person, at the same time. If there were two places to manage this information, then we’d double the administrative work, and probably bury the support team. There are other things that we could do, like force a single UI role on a user. But what happens if you want a role that has the ability to set attributes on Create, but only read them for Update? Then you need two pieces of UI that are different. This just gets us mired into the per-process, per-user, per-document UI mess, which doesn’t seem to have an elegant solution… yet.

  • Michaeljon Miller

    Announcing the RSS connector for MS-CRM 3.0

    • 6 Comments

    We've finally released the RSS connector for MS-CRM. I've mentioned this tool a few times. This has been a long release mired in a few documentation, legal, and technical issues. But, that's not your concern, you probably just want to download this thing, install it, and make things happen. Well, here's the backgrounder on what's making the connector tick. The MSDN article which comes with the connector download covers some basic information. Look for a longer whitepaper from our UE team in the next few weeks.

     

    We want to hear how you've used, modified, or extended the connector. If you use it, let me know. This is the last bit of code that I built for the CRM team (well, it's the last bit that I released, I was still working on the add-entity framework and address book right up to RTM) and I'd like to see what happens with it (and no, this isn't typical of the code quality that I usually write, this was a prototype first and a public release second; if we were going to release this is would be very different). We're releasing this under a different model (see the EULA) and we're very interested in its life once it leaves here.

     

    Basics of the connector

    The RSS connector for CRM is built on top of the advanced find and web service Fetch functionality. For the most part it directly executes the requested query and returns the results as RSS-formatted XML. However, there are a few changes that are made to the base query (if you're wondering, all queries are stored as serialized <fetch> requests, which means the connector gets to mess with XML).

     

    First thing that happens is that the connector loads the actual <fetch> definition for the requested user or system query. Next, it creates an array of the columns specified in the query's grid. These are used for specifying the simple list extension attributes for IE7 sorting and grouping. The one change from the grid columns is that the connector adds the modifiedon attribute if it's part of the underlying entity's definition.

     

    Once the connector has cached away the display attributes it modifies the in-memory copy of the query so that all attributes are available. The query definition has all of its selection criteria removed as well so that the feed data is as broad as possible given the caller's security attributes.

     

    User queries vs. system queries

    Under the covers system queries and user queries are structurally the same. They are stored in different tables in the database (don't ask, it was a decision that couldn't be undone by the time it was noticed), they have the same columns, and they have the same semantics. The primary difference is that the security model changes: system queries are effectively public and user queries are effectively private. A secondary difference is that they had different APIs during the TAP and alpha releases because they just happen to be written in two different languages (again, don't ask).

     

    This does have the nice side-effect that the RSS connector simply reads the query definition through the proper entity. It's really just a <fetch> that changes entity names and some minor decorations.

     

    When the RSS connector displays the nice HTML-based list of available feeds it does so in two sections. The top contains user-specific feeds and the bottom contains all accessible system feeds. The connector also generates HTML <link> elements for each user query. This tells RSS-aware browsers and readers that there are feeds published on the page. The connector only does this for user queries otherwise the list would be unreasonable long.

     

    How the connector selects attributes for display

    Because the connector modifies the query definition to force all attribute selection (this isn't just done for display, it's also done to support instance delivery, but more on that in a minute) it's able to present a rich view of the instance data to the RSS aggregator. In WriteItemData the connector loops over the entity's attribute list in a semi-intelligent order. It writes the primary field, any audit attributes, any state or status attributes, ownership data, any "description" attributes, and then all the rest. Nearly all attributes retrieved from the platform are displayed: there are a few attributes that have no public view and no reasonable display label, so those are skipped.

     

    Riding on coattails - using the list extensions

    The connector uses the query's grid column to select the set of attributes used in the list extensions elements. Technically, this is a de-selection, because the connector rewrites the query to remove the attribute list and adds an <all-attributes> clause to the <fetch>. In the foreach loop in WriteCrmAttributes there's a check to see if the "current" attribute is in the cell list and if it's not it's skipped. The data is written in a manner that makes list extension display useful to the user and executable to the extension processor. That is, all "codes" and other internal details are tossed away and nice display values are used instead.

     

    The lightweight metadata cache and the service proxy

    Two things that held up earlier release of the connector were technologies that I used to make the connector happen but which aren't supported outside of the CRM team. These are use of the 1.2 COM proxy (which is finally gone in the upcoming CRM release - I hated that thing because I had to code it over my wife's birthday a few years back and that got me in a lot of trouble) and the internal metadata cache assemblies.

     

    I didn't want to freak out our development team so I had to use the same metadata interfaces that everyone else uses. The problem is that the MD web service delivers too much data too slowly for me (speaking of slow, one optimization I'd like to see in the connector is to read the queries in one batch instead of on a per-entity type basis). To get around the metadata problem I rolled a very lightweight and purpose-built cache that uses the web service to read the metadata and keep it around in a static. There are a ton of problems with this approach: there's another copy of the cache floating around and CRM is already memory-hungry, and this cache isn't aware of customization changes (i.e. Publish) so it can get out of sync. I didn't consider either of these show-stoppers for this add-on, but the PM in charge of programmability does and he's doing something about it for V.next.

     

    One thing that I did that might be a little surprising was that I asked for the WSDL and then hand-edited down to its absolute basic bits for this solution. I didn't want a 650Kb proxy loaded into the connector and I didn't want the connector to pay the late compilation and reflection hit when W3WP loaded the proxy. The connector only uses the Fetch method and the SOAP header. That means I was able to strip all the types and method definitions out (sorry Kevin and Arash). And no, I'm not using this as an apology for the web service shape in V3, it's a good thing. I didn't have to do the same thing with the metadata proxy because it's fairly small and the connector needs a lot of the definitions from it.

     

    If you've gotten this far make sure you read my entry on using the offline client hosting process otherwise known as Cassini. I used the connector to verify that I could make the offline client web services work.

     

    Optional non-IE7 "list extension" behavior

    When the RSS feeds are displayed to the user there are two RSS icons and a text-based hyperlink. The two icons represent the "simple" RSS feed and the RSS feed with the complete instance data. The text link will show a down-level IE representation of the IE7 RSS viewer. This bit of code is a very early prototype put together by the IE and RSS team to show what the IE7 experience might look like. I lifted the code from those teams for the PDC demo and just never got around to removing it. Someone better versed in cross-browser AJAX stuff might be able to make this work better in other browsers. For now, this link can be ignored (and I would recommend replacing the link with the "real" RSS link and let the browser figure it out).

     

    Delivering a complete CRM instance in the <item> data

    The RSS connector has the capability to deliver, as part of the item data, the XML serialized representation of a complete entity instance. It does this to enable a set of scenarios supported by really simple sharing and by some hub and spoke delivery models that we're looking at. When this option is enabled the <channel> element contains the underlying entity's XSD (this is a different XSD generation process than the WSDL uses). When a smart RSS aggregator loads a feed with the CRM namespace it knows that the entity definition and entire entity instances are available to it. This means you can tunnel select CRM instance data over RSS without exposing the CRM web services. RSS provides the pipe through which this data moves. We've come up with dozens of applications for this delivery mechanism and will start building some software based on this model over the summer. (This is the project that I've left the CRM team to work on and I couldn't be more excited about it.)

     

    Wrapping things up

    The rest of the code is just infrastructure used to make CRM data into RSS. It's missing support for HTTP 304 and ETags. I'm hoping that someone will add that and drop me an update so I can reverse-integrate it into the code. I'm assuming that the connector will fall under the "unsupported sample code" umbrella which means that there isn't a formal support infrastructure in place for it. However, if you post a comment to this entry I'll see that they get to someone in the CRM team.

     

    Building and installing the connector is easy. I'm assuming that the MSDN document talks about this, but if it doesn't here's the short and sweet. With the connector code is a small CMD script that if executed from a VS2003 command window will compile and copy the assembly to the bin directory. My demo installation uses the ISV extensions to add a "Web feeds" item to the menu which points at the RSS feed display and a convenient OPML page. There's a 16x16 PNG file that fits nicely in the menu and just happens to match the IE7 and Firefox RSS icons.

     

    More things to read

    RSS and CRM - a little history

    Where is the RSS connector for CRM 3.0

    “Democratizing” Business Logic and Data

    Simple List Extensions

    Really Simple Sharing

    Using the CRM SDK offline

    Microsoft Dynamics CRM RSS Connector

  • Michaeljon Miller

    Playing with Microsoft CRM programming models

    • 8 Comments

    I’ve spent the last few days playing with a new programming model on top of the CRM platform. This work was part prototype, part investigation, and part complaint. I wanted to see what might be possible if I took a completely radical approach to the API. Some of you might remember an earlier article I wrote (which isn’t linked here because it’s been wiped out) about the CRM programming model in which I mentioned that I didn’t like the v1.x API set and that I wanted to see something different for V2. Well, we’re still working on a V2 model and we’re still trying to get our heads around what might make sense. In the meantime though I decided to see what was possible using V1.x.

    The motivation behind this is simple: I don’t like the mess we got ourselves in to on the V1 product. There’s a lot of history about why we have what we have and I won’t try to defend it. Instead, I’m going to present a sample web service along with the build steps. This work is based on a few of the previous articles that I’ve written. The service is simple, completely type-safe, supports ‘pre’ and ‘post’ method events (because you own the code), and can be extended as necessary.

    The interface looks like so... the first thing you should notice is that this is the complete CRM web service; I’ve removed the per-entity endpoints because I think they just clutter up the story.

    [WebService(Namespace="http://www.microsoft.com/mbs/crm/services/2005")]

    [SoapDocumentService(SoapBindingUse.Literal, SoapParameterStyle.Bare)]

    public class Services : WebService

    {

        [WebMethod()]

        [return: XmlElement("entityId", typeof(Guid))]

        public Guid Save(businessEntity theEntity)

        {

        }

     

        [WebMethod()]

        public void Delete(businessEntity theEntity)

        {

        }

     

        [WebMethod()]

        [return: XmlElement("businessEntity", Namespace=EntityNamespace)]

        public businessEntity Get(string entityType, Guid id)

        {

        }

     

        [WebMethod()]

        public void SetAccess(businessEntity theEntity, securityAccessType accessType)

        {

        }

     

        [WebMethod()]

        [return: XmlArray("securityAccessTypes", Namespace=EntityNamespace)]

        public securityAccessType[] GetAccess(businessEntity theEntity)

        {

        }

     

        [WebMethod()]

        [return: XmlElement("results", Namespace=EntityNamespace)]

        public XmlElement Find(Microsoft.Crm.Query.fetch theQuery)

        {

        }

    }

    The client side is just as simple now. I won’t show the whole interface (in particular I’m going to skip showing what Find() looks like because I haven’t come up with a reasonable way to construct the <fetch> queries in code. For now using XML actually is better.

    CRMServices.Services webService = new CRMServices.Services();

    webService.Credentials = new System.Net.NetworkCredential("", "", "");

     

    CRMServices.account theAccount = new CRMServices.account();

    theAccount.accountcategorycode = new CRMServices.picklistType();

    theAccount.accountcategorycode.Value = 1;

    theAccount.accountclassificationcode = new CRMServices.picklistType();

    theAccount.accountclassificationcode.Value = 5;

    theAccount.accountnumber = "A123456";

    theAccount.name = "A sample account - " + DateTime.Now.ToLongTimeString();

     

    theAccount.address1_line1 = "One Microsoft Way";

    theAccount.address1_line2 = "110/2284";

    theAccount.address1_city = "Redmond";

    theAccount.address1_stateorprovince = "Washington";

     

    theAccount.emailaddress1 = "mikemill@microsoft.com";

    theAccount.donotphone = new CRMServices.booleanType();

    theAccount.donotphone.Value = true;

     

    Guid id = webService.Save(theAccount);

     

    The steps I went through to build this site were:

    1.       Create the correct XML schemas for the interesting entities. This was done using a modified version of the sample code I previously provided.

    2.       I ran the resulting XML schema document (all of the entities are defined in a single schema to make life easier) through the XSD Object Generator. To work around a bug in the 1.1 CLR I needed to modify the resulting class and rename the __fooSpecified fields.

    3.       Next, I created a schema for <fetch> so I could turn that into a class as well. That’s a topic for another rant someday.

    At this point I have schemas and classes for all the entity definitions. This works for added attributes as well because I just regenerate the schemas and classes as necessary. One way around this would be to add an extension point to the schema and let the client side figure out where to put the “found” attributes. I guess I prefer the code generator approach.

    4.       The next step was to create the web service itself using the above class. By the way, I also wrote the test driver code at the same time to make sure that what I was building was going to work the way I wanted it to. TDD can be your friend when you’re experimenting.

    I decided to use the unsupported COM proxy for this project for a few reasons. First, I wanted to sit as close to the platform code as I could because I just didn’t see the point in going through yet another serialization step when I already had done that. Using the COM proxy is fairly straightforward, but there are a few gotchas (particularly around memory management issues). This resulted in code that looks a bit like this (this is an account Save operation).

    case "account":

    {

        account theObject = (account)theEntity;

        theObject.ownerid.Value = ua.UserId;

        theObject.ownerid.type = (int) ObjectType.otSystemUser;

        string entityXml = ToString(theObject);

     

        CRMAccount theService = new CRMAccountClass();

     

        if (IsMissingElement(theObject.accountid)

        {

            id = theService.Create(ref ua, entityXml);

        }

        else

        {

            theService.Update(ref ua, theObject.accountid.Value, entityXml);

        }

     

        break;

    }

     

    The ugly thing about this code is the big switch on entity type. There are ways around this, but none of them are really pretty. So, for now this code will be messy. As you might have noticed already the methods don’t need the CUserAuth structure because the web service takes care of this for you. I figured that it was rare enough for a platform caller to actually want to supply different credentials (and if that’s the case then adding a SOAP header to this with the desired credentials would be the better way to go).

    One of the things I debated was changing the signature for Get to take a businessEntity as well. That would remove the need to pass the entity name. I might still do that, but I want to play around with this some more first.

    I have tested this code as part of a default CRM v1.2 installation. All I did was drop the resulting DLL into the wwwroot/bin directory and drop the ASMX file at the root of the application. Gotta love XCOPY installations.

    Please keep in mind that this is all sample code, that the V2 product will likely differ in style and substance, and that I don’t speak for the CRM team. That said though, this might make someone’s job a little easier for a while. If you’d like a copy of all the code let me know and I’ll package it up and send it. Right now I don’t have anywhere to post the bits for public consumption (I don’t want to use my SU personal pages) so I’ll have to send email.

    I have a few things left that I’d like to add to this sample – I really think we need a simple way to deliver commands as business documents which allow arbitrary business logic. I’ll probably do this using some flavor of Execute(). I also want to add support for the V1.x <columnset> parameter to Get(). That should be fairly simple, but I’ve left it out for now. I thought about using a delegate or reflection based model for loading ISV-defined code in Execute and for ‘callouts’, but that one I decided to leave as an exercise for the reader.

     

  • Michaeljon Miller

    Removing unwanted attributes

    • 1 Comments

    Any old post moving over to MSDN where I can find it.

    First, let me start off by saying that this is a completely unsupported process. There are a number of things that can go wrong and probably will go wrong, and if you break it there's nothing the support team can do to help you.

    Second, if you're using the Outlook client and have replication set up, this process gets a lot more complicated, and I'm not going to be able to help out either.

    So, let's say you've gone into the schema manager and you've added an attribute, and let's assume you've done something you didn't want to do (like create it with the wrong type, or the wrong size, or the wrong name...). You probably want to remove that attribute. I can imagine there are other scenarios where removing an attribute might be a really helpful thing to do.

    Well, removing attributes is a fairly straightforward thing to do. First, you need to make sure you're not using the attribute anywhere. If you are, and you remove it from the metadata, then the product will appear to break when whatever tool is using the attribute tries to access it. Attributes can be consumed in a number of places - the web application uses then obviously; reporting will use them sometimes; integration might use them; and the Outlook client might too.

    Make sure you've removed the attribute from the forms first, that way you can test out what you've done. Use the tools supplied by the product, they're really good and can do the right thing.

    Here comes the part where things might break. You need to edit the Attribute table in the metadata database. Find the attribute you want to remove (make sure it's the right one and bound to the entity you're expecting it to be bound to). Keep the attribute id handy because you're going to need it. You need to remove all the references to the attribute first - look in AttributeMap for references to the attribute on both sides and remove those references (preferably do this from the mapping tool in schema manager - it knows the right incantations, and if this step breaks you're still in supported territory, I think).

    I'm also assuming that you're not doing anything with an attribute used in any relationship because that will flat out break stuff that you can't fix. This means that AttributeOf, SortAttribute, TypeAttribute, and AggregrateOf will all be NULL for the attribute you're removing. It also means that you won't find the attribute used in KeyAttributes anywhere.

    Once you've got all that cleaned up, you can delete the row from Attribute. There are some limitations to what you can delete, but since you're only deleting attributes you added, right, you shouldn't have a problem. However, it might be educational to talk about some attribute characteristics that describe attributes that just can't be removed. Any attribute with RequiredForGrid, IsPKAttribute, or RequiresPlatformAuthorization == 1 have to stay. Any attribute with IsNullable == 0 have to stay as well - they are required by the product. By the way, if you find your attribute in KeyAttributes or referenced by name in GeneratedJoins (or JoinAttributes), then don't do this - that attribute is being used in a relationship and removing it will change the shape of the entity graph - and you're going to break the software.

    Once you've deleted the attribute reference from the metadata, you're almost done. You just need to ALTER TABLE on the *Base table to drop the column (if you don't know how to do that, you shouldn't be trying any of this stuff...) and then regenerate the views and triggers. If you look at the stored procedures in the metabase you'll find one called p_genSpecificViewAndTrigger. It takes the *Base name of the table that holds the entity as a parameter and generates a script that you can use to recreate the views and triggers. It won't recreate them for you, you need to run it, copy the resulting script, and run that script in the MSCRM database.

    Now, if you're using replication you've got all kinds of other issues do deal with. You need to drop the column from the publication (look in SQL BOL for information on sp_repldropcolumn).

    One thing you need to do now is reset the cache. In fact, you should probably have the application shut down while you're doing all this stuff. Simply do an IISRESET on all the web servers that host MS-CRM - that'll definitely flush the metadata cache from the platform.

    This might look something like:


    declare @attributeid uniqueidentifier

    select @attributeid = a.attributeid

    from attribute a join entity e on a.entityid = e.entityid

    where e.name = 'contact'

      and a.name = 'yomifullname'

     

    -- check that the attribute isn't used anywhere

    if exists (select * from keyattributes where attributeid = ...)

    raiserror

     

    -- check that the attribute isn't used anywhere

    if exists (select * from keyattributes where referencedattributeid = ...)

    raiserror

     

    -- check that the attribute isn't used anywhere

    if exists (select * from attributemap where sourceattributeid = ...)

    raiserror

     

    -- check that the attribute isn't used anywhere

    if exists (select * from attributemap where targetattributeid = ...)

    raiserror

     

    if exists (select *

               from attribute

               where (ispkattribute = 1

                      or isrequiredforgrid = 1

                      or requiresplatformauthorization = 1

                      or isnullable = 0)

                 and attributeid = @attributeid)

    raiserror

     

    delete from attribute

    where attributeid = @attributeid

     

    p_genSpecificViewAndTriggers 'ContactBase'

    xp_cmdshell 'IISRESET'



    Like I said though, this is all unsupported stuff and isn't typically something you want to do, but in the off chance that you've done something, like put in an attribute that blows out the 8k row size limit, and you want to fix the problem, then this procedure will probably help out a bit. Most likely I've missed a number of steps in the process, probably the important ones around removing the attribute from all the replication infrastructure (and there's a lot of it).

    As a reminder, if you remove any of the attributes that we shipped with the product you're probably going to break during the upgrade process. If V2 suddenly decides that one of those ill-named empty columns from V1 look useful for a feature, it might get used, and if it's not there, upgrade might not work. I haven't seen the final designs for V2 around the 8k limit but I've heard that there's a lot of work in this area - your columns might move around on you during the upgrade too. So, just careful, and be willing to put those columns back if you’re upgrade testing fails (you do plan on doing upgrade testing when the product ships, right?).

     

  • Michaeljon Miller

    Using the CRM SDK offline

    • 12 Comments

    I've been meaning to write something about using the CRM SDK in an offline state, and I've been meaning to write it for a few years now. I guess I never had the right prodding, but recent newsgroup posts show that there are people interested in this, and that they're stuck.

     

    So, I started doing a little playing around to see what might happen. First thing I noticed is that, as expected, if the client isn't in an offline state you can't work with the local web server. There's code deep in the platform security layer that flat out stops the calls. Ok, that's easy enough to do - let's put the client in an offline state for a while and see what breaks next.

     

    I needed an "application" to test with and I just happened to have my RSS feed generator bits handy and hot off the press. They're really simple and use a very narrow set of CRM SWS (what we call the web service) methods. In fact, it only uses Fetch() to do all of its magic (oh yeah, and it uses a ton of metadata, but that's another posting). Well, as many of you have noticed, you can't get reasonable WSDL from the offline SWS because the module that generates our WSDL (which happens dynamically if you're wondering) isn't on the client. There's just no need for it there.

     

    I pulled WSDL from the server endpoint and hand-tweaked it so it had just the API set that I needed. This isn't strictly necessary, but given the size of the generated code and number of classes there's a significant hit to start-up performance as the CLR reflects over all those types. Anyway, all I needed was Fetch() so I removed everything else and compiled up the resulting CS file into a client proxy assembly.

     

    After installing everything I thought I'd need to run my application offline I noticed that there was a problem hitting the SWS in Cassini, particularly around executing queries. In this case the thing to remember is that queries are old V1.x functionality and that they're implemented in native C++. That means the SWS needs its own proxy to get at those C++ bits. That's where the COM proxy comes in (warning: the COM proxy has already been removed from the next release's build environment, so don't assume you can use this in any supported way for anything).

     

    You might have noticed that the COM proxy isn't on the client machine anywhere (although there is another client-specific COM proxy, but that's not the one we want for this exercise). Go to your install CD or grovel the COM proxy from somewhere off your server and copy it to the res/web/bin directory on the client. Then, and this is important, GAC it so it's accessible from the Cassini process.

     

    That's all I needed to do to get arbitrary query support on the client in a custom application offline. I haven't expanded to arbitrary reads through other messages, but I'm assuming that they should all work. I also haven't done anything with create / update / delete yet because those requests must end up in the playback queue. The COM proxy doesn't do this work. If I remember correctly, this happens somewhere in the RC proxy or in Cassini itself (it would make the most sense for this to work as an HTTP handler inside of Cassini since we want to capture SOAP requests for later playback).

     

    Anyway, I hope that unblocks a few creative people and gets them moving in a direction that helps. I'd love to start seeing some add-on code running in an offline state. Granted, things like callouts and workflow won't work offline, so don't even both trying to make them work.

     

    If anyone comes up with a cool offline add-on I'd like to hear about it.

  • Michaeljon Miller

    How to add an "auto number" to a CRM entity

    • 1 Comments

    Warning: unsupported territory ahead

    Adding an “auto-number” field to MS-CRM is one of those features that has been requested several times. The problem is that there isn’t a solution that really meets everyone’s needs. I was asked during V3 to come up with a way to do this for an internal customer (actually, our development team) to track an ever-increasing number on customer issues. This was something that we needed so badly that we opened a DCR against ourselves to see if we could get it into the product. No dice.

    However, there is an unsupported way to do this, much like there are unsupported ways to do many things. Here’s the way I put this together for our internal site. Note that this will only work once on a table and only if the table doesn’t already have an IDENTITY column (I don’t remember adding any IDENTITY columns in the V1.x databases, but one might have slipped by).

    First, use Deployment Manager to add a new number attribute to your target entity (let’s call it myCounter). Once you’ve done this you should have a column named CFN_myCounter on the base table and in the entity view.

    Next, find this attribute definition in the metadata. You’ll want to tweak the ValidForCreateAPI and ValidForUpdateAPI bits to 0 so you don’t accidentally supply a value from outside of the platform. This will also give you a read-only attribute on the form. Also, keep in mind that this attribute won’t have a value in the database until after the row is committed the first time. This means the edit control on the form will be empty until a Save operation (which calls CreateAndRetrieve).

    Almost there. Next you need to drop the physical column from the underlying table (this might require some tweaks with replication which means you might need to use sp_repldropcolumn and sp_repladdcolumn instead). So,

    ALTER TABLE FooBase DROP COLUMN CFN_myCounter

    Then, turn around and recreate that column, but this time specify that the column is a non-NULL IDENTITY,

    ALTER TABLE FooBase ADD CFN_myCounter INT IDENTITY NOT NULL

    Finally, go add the attribute to the form, republish, and do all the other stuff we make you do in V1.x when you do a customization.

    As usual, you’re mucking with the physical database here. This will likely break in a V3 upgrade (because we won’t migrate the IDENTITY information to the extension table). If you do this, you’re on your own, but you won’t have problems with callouts locking transactions, hotspots in a counter table, and all the other problems that might come up.

    I haven’t tried this on a 1.x deployment (recently) so I can’t vouch for the correctness of the solution. I recommend backing up both your primary and metadata databases before starting with something like this (but then I recommend that even when you’re doing supported things).

  • Michaeljon Miller

    Web services aren’t distributed objects

    • 7 Comments

    Why do we continue to try forcing a distributed object model into the world of web services? Nowhere in the name is there any hint that web service architecture has anything to do with distributed objects. Nowhere is there an implication that there are even objects involved in web service architecture. Sure, underneath the covers, in the private service implementation there are certain to be some kind of objects, if the service was written any time in the last decade or so.

    But objects on the outside? Where did that notion ever come into play? Werner Vogel did a great piece [1] back in December 2003 that tried to dispel some of the myths that keep creeping in. I keep reading that article, and talking to developers around MBS, to see why web services keep getting lumped in with distributed object architectures. The only reason I can come up with is that the development community has had OO on the collective brain for quite a while and anything that looks like it might fit into the CORBA, DCOM, or RMI mold is dismissed as another distributed OO attempt. Web services will never take off until we start thinking in terms of services.

    It’s really hard to do that though. Objects have all those nice characteristics that are pounded into our heads in school: data abstraction, information hiding, loose-coupling, separation of code from data… Wait a minute here. Aren’t those all things that web services provide too? Maybe that’s the reason we keep getting wrapped around the axle when we try to build new systems in a web services world. We want all that goodness, and the thing we’re taught to use is OO, so it must be the case that OO and web services are equivalent architectures. What can we do about it though?

    Well, one of the key tenets of OO is that we should only ever deal with an object via its public interface. That means we shouldn’t worry about what’s inside the object, and we probably never should be shown what’s inside the object. That’s all good. What if we just change all occurrences of “object” in the preceding sentence with “service”? Close, but no cigar. Why? Because there’s a programming model difference between objects and services. When I create a solution using OO techniques I get to assume that I can create an object instance and hang on to it for as long as I want. I might even decide to pass it to another object instance for consumption. As the developer I probably get to hold on to precious resources, even though I don’t know that I’m doing it, because I get to control the object lifetime.

    Hmm. That doesn’t sound right. What if one of those precious resources is a row in a table somewhere? (I know, it’s pretty hard to hold on to a row, but it’s not too hard to create a database transaction that holds a row.) Maybe the issue is that there are two levels of processing involved. Maybe the Smalltalk guys had the terminology right. Maybe what we send to an object is a message. That sounds right. Now, if we replace “object” with “service” we get to send messages to services. We don’t get to hold on to the “object” because it’s not there any more, it’s been replaced by a service.

    Ok, so this is a lot of rambling. Is there a point? Sort of. The point is that we need to change the way we think about the programming model when we talk about building distributed systems using web services. We don’t get to control object lifetimes, we don’t get to hold on to precious resources, we don’t get to control transactions across multiple services. Instead, we get to ask a service to go do something for us and we let the infrastructure handle the how.

    What’s the tie-in to CRM? Only that there are no distributed objects in the product. It’s all web services, and the “objects” are simply data structures that barely pass for documents. Why all that XML in the first two releases? Well, that was the technology available to us when we started and that technology solved the problem of getting programmers away from thinking that they needed to create objects and call methods.

    [1] http://weblogs.cs.cornell.edu/AllThingsDistributed/archives/000343.html

  • Michaeljon Miller

    MS-CRM Programming model example

    • 5 Comments

    I posted the source over on microsoft.public.crm.developer. At some point I think it'll probably be put in a more permanent location.

    Updated 1321 -

    Sorry about not getting the context straight here. I usually don't read blogs through RSS so I keep forgetting that the feeds come in little disconnected pieces. Anyway, the code has beed posted to microsoft.public.crm.developer. The original posting talked about different ways to build a programming model on top of MS-CRM. These go back several months now and many of my original rants are long gone. Hopefully this sample and the "documentation" (usually I'm pretty good at writing docs, but this one is painfully short of useful information) will help get a few people off the XML strings and the old-school RPC/Encoded interfaces (Matt Powell, are you listening?).

     

  • Michaeljon Miller

    (Nearly) complete MS-CRM entity serialization sample

    • 1 Comments

    [I’m finally getting around to formatting this thing so it’s readable and changing the broken links]

    This example assumes that you've created correct XSD from the MS-CRM metadata (see http://blogs.msdn.com/mikemill/archive/2004/12/01/273254.aspx) and that you've run that XSD through the XSD Object Generator. The one change you should making prior to creating classes is to change the tns:dateTimeType to derive from xsd:string instead of xsd:dateTime, otherwise you'll end up serializing dates that the platform can't really cope with.

    With a few simple tweaks to the XSD generator you can create XSD that the VS XSD.EXE tool can use. The way I've done this is to change the generated xsd:complexType to be the entity name followed by 'Type' and to create xsd:elements that reference the complexType.

    (Apologies in advance if the formatting here is off, I'm still working my way around the .Text editor).

    using System;

    using System.Text;

    using System.Web.Services;

    using System.Runtime.Serialization;

    using System.Xml.Serialization;

    using System.IO;

    using System.Globalization;

    using Microsoft.Crm.Entities;

    using Microsoft.Crm.Platform.Proxy;

     

    namespace Cheerios

    {

        class SerializerSample

        {

            /// <summary>

            /// Returns a string (XML) representation of a serializable

            /// entity. The namespaces are removed because the CRM

            /// platform deserialization code won't cope welll with them.

            /// </summary>

            /// <param name="o">The instance to serialize</param>

            /// <returns></returns>

            public static string ToString(object o)

            {

                StringBuilder sb = new StringBuilder();

                XmlSerializer serializer = new XmlSerializer(o.GetType());

                XmlSerializerNamespaces ns = new XmlSerializerNamespaces();

                ns.Add("", ""); TextWriter writer = new StringWriter(sb);

                serializer.Serialize(writer, o, ns); return sb.ToString();

            }

     

            /// <summary>

            /// Returns an instance of a object created from the supplied

            /// XML. You must cast the return value to the correct class.

            /// </summary>

            /// <param name="t"></param>

            /// <param name="s"></param>

            /// <returns></returns>

            public static object ToObject(Type t, string s)

            {

                XmlSerializer serializer = new XmlSerializer(t);

                TextReader reader = new StringReader(s);

                return serializer.Deserialize(reader);

            }

     

            /// <summary> /// The main entry point for the application.

            /// </summary>

            [STAThread]

            static void Main(string[] args)

            {

                // create the service proxies

                BizUser userService = new BizUser();

                userService.Url = "http://paint/mscrmservices/bizuser.srf";

                userService.Credentials = System.Net.CredentialCache.DefaultCredentials;

               

                CRMContact contactService = new CRMContact();

                contactService.Url = "http://paint/mscrmservices/crmcontact.srf";

                contactService.Credentials = System.Net.CredentialCache.DefaultCredentials;

     

                // get our credentials from the platform

                CUserAuth userAuth = userService.WhoAmI();

     

                // create an contact object in "client-space" and set some properties

                contact theContact = new contact();

     

                // the CRM platform wants to see dates like YYYY-MM-DDTHH:MM:SS

                theContact.birthdate.Value =

                    DateTime.UtcNow.ToString(CultureInfo.InvariantCulture.DateTimeFormat.SortableDateTimePattern);

     

                // set some other properties on the object

                theContact.customertypecode.Value = 6;

                theContact.creditlimit.Value = 123456.0F;

                theContact.creditonhold.Value = true;

                theContact.firstname = "Bob";

                theContact.lastname = "Jones";

                theContact.description = "This is my sample contact....";

     

                // set up the ownership information because the platform won't

                // automatically do that for you

                theContact.ownerid.Value = userAuth.UserId;

                theContact.ownerid.type = 8;

     

                // turn it into XML

                string initialContactXml = SerializerSample.ToString(theContact);

                Console.WriteLine("Created contact XML\n{0}\n", initialContactXml);

     

                // stuff in into the platform and get the new one back

                string updatedContactXml =

                    contactService.CreateAndRetrieve(userAuth, initialContactXml);

                Console.WriteLine("Retrieved contact XML\n{0}\n", updatedContactXml);

     

                // turn the new one into an object

                theContact = (contact)ToObject(typeof(contact), updatedContactXml);

                Console.WriteLine("Serialized new contact to\n{0}\n", SerializerSample.ToString(theContact));

                Console.WriteLine("Hit <return> to continue"); Console.ReadLine();

            }

        }

    }

    The XML generated from the first serialization (from theContact to initialContactXml) looks like the following. This string can be passed directly to the MS-CRM platform as the ContactXml parameter.

    <?xml version="1.0" encoding="utf-16"?>

    <contact>

        <birthdate>2004-09-10T16:40:54</birthdate>

        <creditlimit>123456</creditlimit>

        <creditonhold>true</creditonhold>

        <customertypecode>6</customertypecode>

        <description>This is my sample contact....</description>

        <firstname>Bob</firstname>

        <lastname>Jones</lastname>

        <ownerid type="8">{9CBA0E1D-882F-4A39-9441-B4FA3BA14724}</ownerid>

    </contact>

    The call to CreateAndRetrieve returns the following XML (note, I've formatted this a bit so that it's readable). Notice the date, boolean, and lookup elements have extra XML attributes automatically. These will be mapped to corresponding values in the supporting C# types. One thing to notice here is that the platform XML doesn't include the processing instruction. You can assume that the platform will return UTF-8 formatted XML.

    <?xml version="1.0" encoding="utf-16"?>

    <contact>

        <contactid>{CD303B40-B8E5-4B84-ABFF-A2DA4C6D9E35}</contactid>

        <customertypecode>6</customertypecode>

        <owningbusinessunit>{7DE28647-59CA-4C95-B7A3-FA7E31CD6DA8}</owningbusinessunit>

        <participatesinworkflow>0</participatesinworkflow>

        <firstname>Bob</firstname>

        <lastname>Jones</lastname>

        <fullname>Jones, Bob</fullname>

        <birthdate date="09/10/2004" time="4:40 PM">2004-09-10T16:40:54-07:00</birthdate>

        <description>This is my sample contact....</description>

        <creditlimit>123456</creditlimit>

        <createdon date="09/10/2004" time="9:40 AM">2004-09-10T09:40:32-07:00</createdon>

        <creditonhold name="Yes">1</creditonhold>

        <createdby name="Miller, Michaeljon" dsc="0">{9CBA0E1D-882F-4A39-9441-B4FA3BA14724}</createdby>

        <modifiedon date="09/10/2004" time="9:40 AM">2004-09-10T09:40:32-07:00</modifiedon>

        <modifiedby name="Miller, Michaeljon" dsc="0">{9CBA0E1D-882F-4A39-9441-B4FA3BA14724}</modifiedby>

        <statecode name="Active">0</statecode>

        <statuscode name="Active">1</statuscode>

        <address1_addressid>{B315C1B1-ACFF-4438-97B5-FF9EA34A681F}</address1_addressid>

        <address2_addressid>{80FB9B47-4F68-44B1-A619-402F4AD94061}</address2_addressid>

        <ownerid name="Miller, Michaeljon" dsc="0" type="8">{9CBA0E1D-882F-4A39-9441-B4FA3BA14724}</ownerid>

    </contact>

     

    Finally we can take the returned platform XML and convert it into a class. For this example I'm simply turning the class back into an XML string, but you can use that resulting object like you would any object. It's also quite possible to create collection classes and use the bulk retrieve operations to hold collections of CRM "objects".

    <?xml version="1.0" encoding="utf-16"?>

    <contact>

        <address1_addressid>{2025B5AA-BAD7-4816-9406-BEF62BA89358}</address1_addressid>

        <address2_addressid>{5135ECBE-56E3-41A7-99F8-7ACC3C3B9FDA}</address2_addressid>

        <birthdate date="09/10/2004" time="4:50 PM">2004-09-10T16:50:08-07:00</birthdate>

        <contactid>{012014CF-350C-42CE-90D1-533A222CEE0A}</contactid>

        <createdby name="Miller, Michaeljon" dsc="0">{9CBA0E1D-882F-4A39-9441-B4FA3BA14724}</createdby>

        <createdon date="09/10/2004" time="9:49 AM">2004-09-10T09:49:55-07:00</createdon>

        <creditlimit>123456</creditlimit>

        <creditonhold name="Yes">true</creditonhold>

        <customertypecode>6</customertypecode>

        <description>This is my sample contact....</description>

        <firstname>Bob</firstname>

        <fullname>Jones, Bob</fullname>

        <lastname>Jones</lastname>

        <modifiedby name="Miller, Michaeljon" dsc="0">{9CBA0E1D-882F-4A39-9441-B4FA3BA14724}</modifiedby>

        <modifiedon date="09/10/2004" time="9:49 AM">2004-09-10T09:49:55-07:00</modifiedon>

        <ownerid name="Miller, Michaeljon" type="8" dsc="0">{9CBA0E1D-882F-4A39-9441-B4FA3BA14724}</ownerid>

        <owningbusinessunit>{7DE28647-59CA-4C95-B7A3-FA7E31CD6DA8}</owningbusinessunit>

        <participatesinworkflow>false</participatesinworkflow>

        <statecode name="Active">0</statecode>

        <statuscode name="Active">1</statuscode>

    </contact>

     I hope this helps developers better understand the relationship between the MS-CRM XML and .Net classes.

     

  • Michaeljon Miller

    MS CRM V1.2 Logical Model on MSDN

    • 6 Comments

    Quite a few people have asked for the database schema for CRM 1.x. Well, we were a little hesitant about handing it out for a few reasons. One, we really don't think you should be partying on the database directly. Two, there was no reasonable delivery mechanism for doing so. Well, this morning our UE team posted the logical model on MSDN. This is a big Visio diagram that shows all the entities, the definitions (no attributes), and all the relationships, both logical and physical. Thanks to the CRM UE team for putting this together.

    A little trivia - this model was generated from the metadata using VBA and then hand-tweaked to fix some of the layout issues. Just thought that was an interesting take-away.

  • Michaeljon Miller

    Add / Entity scenario in CRM 2005

    • 4 Comments

    I’m trying to get a feel for how ISVs and VARs might use custom tables and custom entities in MS-CRM. The canonical example around building 110 is that someone would add a BankAccount child entity to a customer (likely an Account). But that sure doesn’t seem like a valid solution.

    I mocked up a student management system using some of the custom entities features that are in the V2 alpha when we where doing some of the original design work around relationships. For the most part things fit into place pretty well, but the solution seems a little forced (I have a background in survey and assessment systems so the concepts all work correctly, it’s just that I went out of my way to get all the relationship “rules” in the model).

    Those "rules control how various relationships behave. The way we went about this was to devise a relationship type taxonomy and the rules that surround those types. The rules are pretty simple:

    ·         Cascade – broadcast the action ‘down’ the link to any related entity. The receiving entity will perform the operation for any related instances. This is a "code" thing that the platform manages on the caller's behalf.

    ·         Do not cascade – do not broadcast the operation at all. This action is useful for MERGE and security operations in many relationship models. It really means STOP the current traversal action for this relationship graph.

    ·         Remove link – we’ve traditionally called this ‘cut link’. The action disconnects the relationship based on the relationship type (a 1:M relationship will set the relating attribute to NULL by sending a message to the ‘far’ entity, an M:M relationship will delete the specified rows from the association table but no broadcast will occur).

    ·         Restrict operation – if an instance exists on the far end of a ‘restrict’ relationship then the whole operation is canceled. This is most useful for DELETE, but can apply to other operations.

    Keep in mind that in many cases there’s an overlap between ‘remove link’ and ‘do not cascade’ and we’ve been overloading their meaning based on the model.

    Note that operations only traverse in one direction. That is an operation will always start at the 1-side of a relationship and terminate at the m-side. Reverse traversals are undefined.

    The following scenario shows one possible extension to MS-CRM. This extension models a student management subsystem. The system tracks Accounts and Contacts as customers and students. Those students can attend seminars to receive necessary training. Those training requirements are determined based on an assessment exam. Courses can have pre- and post-requisites. Exams can be created for pre- and post-assessment (to determine training needs and to assess learning after the course has been completed).

    Many of the preceding models are represented in the scenario. There are several entity graph loops present. The loops are terminated by defining traversal rules on the following relationships: primary_contact, preferred_location, held_at, and held_for. The numbers throughout the model refer to the model numbers from an internal design document (they’re not too important).

    Note that activities are represented by a fictitious entity called ‘Generic Activity’. Activity and Annotation relationships are not shown in the model but can be inferred from the associated model metadata. Assume for purposes of this example that Course, Exam, Seminar, Location, and Instructor have relationships to Annotation. Also assume that Attendance Record and Examination have associated scheduling activities.

    Ideally “Add Entity” in V2 will have sufficient infrastructure to build a subsystem like the one below. A few modifications would make the model even more powerful such as defining Instructor and Location as Resources, and defining Examination, Seminar, and Attendance Record as Activity types.

    Now, does this seem like the kind of solution that you’d want to build on top of MS-CRM, or is this way too deep? Is the core scenario like the one above, or more like “I need to add a simple 1:M relationship to hold some ‘extra’ stuff”. Just wondering.

     

  • Michaeljon Miller

    Dealing with many 1:M CRM relationships

    • 3 Comments

    I while back I mentioned in the CRM newsgroups that it was possible to create multiple relationships between two entities. For the most part this is true, but there are some hoops to jump through and each of those hoops is ringed in fire. More specifically, using this approach to solve the "many relationships" problem will plant you squarely in unsupported, probably-can't-upgrade, land.

     

    The problem I'm talking about is where you have a single parent entity - the 1-side, and you want more than 1 1:M relationship from that parent entity (let's call it P) to a child entity (smartly named C). Let's try an example. Say we have a Course that has an Author and an Instructor. Now, ignoring for a moment that CRM can't deal with additional party types and won't allow multiple relationships to SystemUser, we'll call Author and Instructor simply views over the same set of data. That is, they are the same entity with different roles.

     

    In CRM v3.0 you'd implement this by creating the three entities, setting up the Course-Author and Course-Instructor relationship, hiding the Instructor entity, and writing a callout to keep the Author and Instructor data in sync. That'll work and keep you supported. There are a few caveats that will cause some serious grief: you can't use the quick create functionality from an Instructor lookup, you need to keep the security consistent, and you can't completely hide the Instructor type as a separate type.

     

    Well, there is a way around this. It's a hack but it's been successfully hacked for some internal development deployments. Create the three entities as you normally would but keep the child entities Instructor and Author "empty". Set up the relationships using the configuration tools. You now have three entities with two relationships much like you had in the supported approach. Now, customize both Author and Instructor in identical ways by adding all the attributes that the type needs. When you're done you'll end up with two identical entities pointing at two different database structures. You're still in supported land.

     

    Do not add any instance data to either Author or Instructor yet.

     

    Next, change all the display attributes for the Instructor entity so that it looks like an Author (this really isn't necessary and might make sense usage-wise if you treat them as different). Ready to jump into unsupported territory? Open the metadata database and find the Instructor entity (let's assume that Author is the primary view) in the Entity table. You'll see a few columns that describe physical table mappings for the core and extension tables. Change the table names to reflect that the Instructor entity should instead point at the Author tables. You'll want to make a few modifications to the Instructor attributes as well so that they reflect the underlying physical Author attributes (I think the primary key in the base and extension table is the only attribute you need to really worry about).

     

    Now, every time you add an Instructor or Author the data ends up in the same physical table. That means that any query over either entity will return the same set of data. So, your lookup control from Course to Author will present the same physical data as Course to Instructor. As far as CRM is concerned you have two different entities with two different relationships. You end up with copies of all the web service methods and the schemas will look nearly the same (their logical views will reflect the as-configured names, not the physical names). But, at a physical level you only have one copy of the data that you look at and edit, there are no synchronization issues, and there's no tricky callout code to handle multi-master updates.

     

    I might have missed a few steps here because I'm doing this from memory and not on a live CRM installation. If you find that some of the steps seem incorrect (missing, out of order, unnecessary) then post a comment here so everyone gets a chance to learn along with you.

  • Michaeljon Miller

    More fun with CRM web services

    • 1 Comments

    I’ve received a dozen or so requests for the sample WSDL wrappers for CRM. I haven’t forgotten about sending these, I’ve been working on cleaning up and extending the sample code so it’s slightly more useful and includes some missing functionality that I think ISVs are using. Shouldn’t be long now.

  • Michaeljon Miller

    CRM metadata browser

    • 6 Comments

    It was interesting to see Mitch Milam's post about the metadata browser. This was a little tool I put together pre-V1.0 ship, but which didn't make the schedule until V3. Mitch points out the "published" component which displays the entity metadata in a nice format. If you edit list.aspx you'll see two sections commented out that provide links to individual entity schemas. This is unsupported and undocumented functionality that we considered calling "sample code". Turns out that it made it into the box but not enabled by default. These schemas work nicely with the code generators that I blogged about a while back.

  • Michaeljon Miller

    Dealing with broken XSD

    • 3 Comments

    I'm bringing this over to MSDN from its original home.

    Ok, I've been dealing with this broken XSD issue for so long now that I just can't stand it. The platform has ways to give you back XSD, but it's not really XSD, and it's not really friendly about doing so. Given the current v1.x API, which aren't exactly friendly to deal with, and their insistence on using strings of XML for everything, I put this script together to start hiding the complexities. Now, one thing you'll notice is that this script doesn't generate XSD in a flavor that the VS .NET XSD.EXE tool likes (it's not the greatest either, but it does work usually).

    The cool thing is that there is a great tool available from the SD West Conference. This tool will happily eat the resulting XSD that this script generates and will create some very powerful client-side classes that should make everyone's life much easier. You'll still need to serialize the platform XML into a class, but that's pretty simple. The following C# snippit should do just fine (you'll have to grab some of the XML serialization references, IO, and Text, but that's left as an exercise for the reader).

    The third code snip is the SQL script you've been waiting for. I recommend always generating the whole pile, you'll end up with about 3,000 lines of XSD and about 30,000 lines of C# when you're done, but it's worth it. Remember though, as shown, this script will not generate XSD that the XSD.EXE tool likes. Don't ask me why, it just doesn't (and that goes for generating typed DataSets too). There are ways to make it work, but would you want to when XSDObjectGen does all the right things in terms of creating real platform XML and dealing well with minimal update sets? Oh yeah, the classes work well with the ColumnSetXml parameter on Retrieve methods. I've even created XSD that represents collections of platform entities and serialized those properly.

    As usual, nothing you've read here is remotely supported, and if you call support they'll likely have no idea what you're talking about. They might even ask why you're running SQL scripts in the metadata. I won't support this either. So don't ask. I'm making this available because it's something that needed to happen and never did. The bug with the bad XSD was found the day we RTM'd v1.0 and we never looked back (who'd ever want the schemas anyway, aren't XML strings self-describing...)


    public static string ToString(object o)

    {

        StringBuilder sb = new StringBuilder();

        XmlSerializer serializer = new XmlSerializer(o.GetType());

        XmlSerializerNamespaces ns = new XmlSerializerNamespaces();

     

        ns.Add("", "");

        TextWriter writer = new StringWriter(sb);

        serializer.Serialize(writer, o, ns);

     

        return sb.ToString();

    }

     

    public static object ToObject(Type t, string s)

    {

        XmlSerializer serializer = new XmlSerializer(t);

        TextReader reader = new StringReader(s);

     

        object o = null;

        try

        {

            o = serializer.Deserialize(reader);

        }

        catch (Exception e)

        {

            throw new Exception("Failed to convert XML to object", e);

        }

        return o;

    }

     

    void fooBar()

    {

        // create an account object in "client-space" and set some properties

        Microsoft.Crm.WebServices.account a1 = new Microsoft.Crm.WebServices.account();

        a1.accountcategorycode.Value = 6;

        a1.accountcategorycode.name = "Corporate";

     

        a1.creditlimit.Value = 123456.0F;

        a1.creditlimit.value = "$123,456.00";

     

        a1.creditonhold.Value = true;

        a1.creditonhold.name = "Yes";

     

        a1.createdon.type = 8;

        a1.createdon.Value = userAuth.UserId;

     

        a1.name = "This is account 1";

        a1.description = "This is my sample account....";

     

        // turn it into XML

        string xml1 = ToString(a1);

     

        Microsoft.Crm.Platform.Proxy.CRMAccount accountService =

        new Microsoft.Crm.Platform.Proxy.CRMAccount();

     

        // stuff in into the platform and get the new one back

        string xml2 = accountService.CreateAndRetrieve(userAuth, xml1);

     

        // turn the new one into an object

        Microsoft.Crm.WebServices.account a2 =

        (Microsoft.Crm.WebServices.account)ToObject(typeof(Microsoft.Crm.WebServices.account), xml2);

    }




    set nocount on

     

    declare @view table (

      idvalue int identity,

      value nvarchar(4000)

    )

     

    declare @attributeName nvarchar(50)

    declare @typeName nvarchar(50)

    declare @entityName nvarchar(50)

     

    declare @buildDate datetime

    declare @buildNumber nvarchar(20)

     

    select @buildDate = coalesce(BuildDate, getutcdate()),

           @buildNumber = cast(coalesce(MajorVersion, 1) as nvarchar) + '.' + cast(coalesce(MinorVersion, 0) as nvarchar) + '.' + cast(coalesce(BuildNumber, 0) as nvarchar)

    from BuildVersion

     

    declare entityCursor cursor for

    select LogicalName

    from Entity

    where IsIntersect = 0

      and IsSecurityIntersect = 0

      and IsLookupTable = 0

      and IsAssignment = 0

      and LogicalName not like '%activity%'

      and LogicalName != 'activitypointer'

    order by 1

     

    -- write the top-level schema tags and namespace information

    insert @view (value) values ('<?xml version="1.0" encoding="utf-8" ?>')

     

    insert @view (value) values ('<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"')

    insert @view (value) values (' targetNamespace="http://www.microsoft.com/mbs/crm/schemas/2004"')

    insert @view (value) values (' xmlns:tns="http://www.microsoft.com/mbs/crm/schemas/2004"')

     

    insert @view (value) values (' elementFormDefault="unqualified" ')

    insert @view (value) values (' attributeFormDefault="unqualified" >')

    insert @view (value) values ('')

    insert @view (value) values (' <xsd:import namespace="http://www.w3.org/XML/1998/namespace"')

    insert @view (value) values (' schemaLocation="http://www.w3.org/2001/xml.xsd" />')

    insert @view (value) values ('')

     

    insert @view (value) values ('')

    insert @view (value) values (' <xsd:annotation>')

    insert @view (value) values (' <xsd:documentation xml:lang="en">')

    insert @view (value) values (' Copyright (c) ' + cast(year(getutcdate()) as nvarchar) + ' Microsoft Corp. All rights reserved.')

    insert @view (value) values (' DO NOT EDIT - Schema automatically generated ')

    insert @view (value) values (' Built on : ' + cast(@buildDate as nvarchar))

    insert @view (value) values (' Version : ' + cast(@buildNumber as nvarchar))

    insert @view (value) values (' </xsd:documentation>')

    insert @view (value) values (' </xsd:annotation>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:simpleType name="uniqueidentifier">')

    insert @view (value) values (' <xsd:restriction base="xsd:string">')

    insert @view (value) values (' <xsd:pattern value="[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}" /> ')

    insert @view (value) values (' </xsd:restriction>')

    insert @view (value) values (' </xsd:simpleType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="keyType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:string">')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="principalType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:string">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string" />')

    insert @view (value) values (' <xsd:attribute name="type" type="xsd:int" use="required" />')

    insert @view (value) values (' <xsd:attribute name="dsc" type="xsd:int" />')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="ownerType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:string">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string" />')

    insert @view (value) values (' <xsd:attribute name="type" type="xsd:int" use="required" />')

    insert @view (value) values (' <xsd:attribute name="dsc" type="xsd:int" />')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="customerType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:string">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string" />')

    insert @view (value) values (' <xsd:attribute name="type" type="xsd:int" use="required" />')

    insert @view (value) values (' <xsd:attribute name="dsc" type="xsd:int" />')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="lookupType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:string">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string" />')

    insert @view (value) values (' <xsd:attribute name="type" type="xsd:int" use="required" />')

    insert @view (value) values (' <xsd:attribute name="dsc" type="xsd:int" />')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="picklistType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:int">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="booleanType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:boolean">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="moneyType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:float">')

    insert @view (value) values (' <xsd:attribute name="value" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="numberType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:int">')

    insert @view (value) values (' <xsd:attribute name="value" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="decimalType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:float">')

    insert @view (value) values (' <xsd:attribute name="value" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="floatType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:float">')

    insert @view (value) values (' <xsd:attribute name="value" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="dateTimeType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:dateTime">')

    insert @view (value) values (' <xsd:attribute name="date" type="xsd:string"/>')

    insert @view (value) values (' <xsd:attribute name="time" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="statusType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:int">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="stateType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:int">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    -- open the cursor

    open entityCursor

    fetch entityCursor into @entityName

     

    while @@fetch_status = 0

    begin

      insert @view (value) values (' <xsd:complexType name="' + @entityName + '">')

      insert @view (value) values (' <xsd:sequence>')

     

      declare attributeCursor cursor for

      select Attribute.LogicalName,

      case

        when AttributeTypes.XmlType in ('dateTime.tz', 'datetime') then 'tns:dateTimeType'

        when AttributeTypes.XmlType = 'Boolean' then 'tns:booleanType'

     

        when AttributeTypes.XmlType = 'picklist' then 'tns:picklistType'

        when AttributeTypes.XmlType = 'state' then 'tns:stateType'

        when AttributeTypes.XmlType = 'status' then 'tns:statusType'

     

        when AttributeTypes.XmlType = 'primarykey' then 'tns:keyType'

        when AttributeTypes.XmlType = 'customer' then 'tns:customerType'

        when AttributeTypes.XmlType = 'lookup' then 'tns:lookupType'

        when AttributeTypes.XmlType = 'owner' then 'tns:ownerType'

     

        when AttributeTypes.XmlType = 'uuid' then 'tns:keyType'

     

        when AttributeTypes.XmlType = 'timezone' then 'xsd:int'

        when AttributeTypes.XmlType in ('integer', 'int', 'bigint', 'smallint', 'tinyint') then 'tns:numberType'

        when AttributeTypes.Description = 'money' then 'tns:moneyType'

        when AttributeTypes.Description = 'decimal' then 'tns:decimalType'

        when AttributeTypes.Description = 'float' then 'tns:floatType'

     

        else 'xsd:' + AttributeTypes.XmlType

      end

      from Entity join Attribute on (Entity.EntityId = Attribute.EntityId)

      join AttributeTypes on (Attribute.AttributeTypeId = AttributeTypes.AttributeTypeId)

      where Entity.LogicalName = @entityName

        and (Attribute.ValidForReadAPI = 1 or Attribute.ValidForUpdateAPI = 1 or Attribute.ValidForCreateAPI = 1)

        and Attribute.AttributeOf is NULL

        and Attribute.AggregateOf is NULL

      order by Attribute.LogicalName

     

      open attributeCursor

      fetch attributeCursor into @attributeName, @typeName

     

      while @@fetch_status = 0

      begin

        insert @view (value) values (' <xsd:element name="' + @attributeName + '" type="' + @typeName + '" />')

        fetch attributeCursor into @attributeName, @typeName

      end

     

      close attributeCursor

      deallocate attributeCursor

     

      insert @view (value) values (' </xsd:sequence>')

      insert @view (value) values (' </xsd:complexType>')

     

      fetch entityCursor into @entityName

     

      if @@fetch_status = 0

      begin

        insert @view (value) values ('')

      end

    end

     

    close entityCursor

    deallocate entityCursor

     

    insert @view (value) values ('</xsd:schema>')

     

    select value

    from @view order by idvalue



    Bonus if you've read this far. To get collection classes too, add some more SQL like this before the second fetch, and make sure you add a column for the collection name.


    if @collectionname is not null

    begin

      insert @view (value) values ('')

      insert @view (value) values (' ')

      insert @view (value) values (' ')

      insert @view (value) values (' ')

      insert @view (value) values (' ')

      insert @view (value) values (' ')

    end

     

     

  • Michaeljon Miller

    Why learn applied formal methods?

    • 1 Comments

    I’m just about finished with the coursework portion for my MSE degree and figured my last quarter should have some fun classes instead of the “usual” ones. I’m taking a course called Applied Formal Methods. It’s all about writing specs using a formal notation, refining them until we get to a “program” and proving, along the way, that the program will be correct out of the box. There’s a ton of theory in this class, loads of unusual notation, not a small amount of math, and plenty of theorem proving.

    So far we’ve been through basics proofs of if..then..else, while..loops, loop breaks, and for..loops. The question has come up several times in class and over a private email thread about how this course can a) be useful, b) be applied, and c) make it into the “real world”.

    I've been thinking about this class for the last few weeks trying to come up with an answer to that question about how this stuff helps us design better programs. I think I've arrived at an answer only to realize that the answer was sitting right in front of me the whole time.

    But first, a little background information to set the stage. Many of us took an Introduction to Programming or Principles of Programming Languages class some time as an undergraduate. There's a good chance that the tactics which you learned in that class never appeared to have any relevance to the rest of your undergrad work. [Insert any junior high student comment about how you’ll never use calculus or geometry in real life so there’s no reason to learn that either.] In fact, you might have thought at the time that none of what you were learning would ever be useful once you started designing or writing software.

    Let's look at those topics for just a minute and see if they've be applicable to your career.

    • Abstraction building using procedures: expressions, the environment, scope, procedure application, black-box abstractions, recursion, growth and cost, procedures as data
    • Abstraction building using data: sequences, structures, sets, encoding, optimizations
    • Modularity, scope, state: variable scoping, assignment, volatile data, simulators, data structures, streams and infinite data structures
    • Abstraction: language evaluators, data as programs, programs as data, lambdas, closures, interpretation order, and programming models (logic, functional, imperative, declarative, applicative)
    • Machine architectures: register machines, routines, stack frames, allocators, garbage collection, tail recursion optimizations

    So, given all that theory, what did we take away from it? The simple answer is that, although we might not feel we use these skills every day, we do use them. We use them without knowing that we use them, and they make us better designers and programmers. Learning that theory, even though we don't write a read-eval-print loop [see the Wizard Book if you don’t know what this is] every day, we know that it's the underlying technology on which interpreters, scripting languages, and debuggers are built. Although we don't implement red-black trees ourselves we know their performance characteristics and how they're implemented which allows us to make informed decisions about which data structures are best for a given application.

    I did my undergraduate work long enough ago that we used LISP as the design and implementation language. I always wondered why I'd want to know LISP. Everything I'd seen up to that point had been BASIC, Pascal, C, Algol, COBOL, FORTRAN, or some other block-structured procedural language. I couldn't get my head around how LISP was going to help me write better C programs. Turns out I wasn't paying enough attention at the time and didn't realize that the concepts being taught are universal. They apply to every language, every program, every situation.

    Formal methods are in the same boat. You may not directly apply the notations. You may not “prove” that a given algorithm meets a given requirement. You'll probably never find yourself doing formal refinement of a specification. But, you will start to think differently about how you write programs. You'll subtly change the way you think about requirements. You won't ask the customer to rewrite the requirements using CSP or Z or any of the other formal notations. But, when a customer asks you to implement a given requirement you'll stop for a minute and ask yourself if that requirement is strong enough to be implemented. Then you'll work with the customer to refine the specification until you both agree that it can be implemented, it can be tested, and it meets the customer's needs.

    This isn't about learning a new technique for writing programs. It's about changing how your brain works when you start designing algorithms. Just like LISP teaches us how to rethink program constructs, formal methods teaches us how to rethink coding constructs. This is about adding two things to your toolbox: new tools and the skills to use them. (A few other useful tools are LISP-like languages, like Scheme, and functional languages like Haskell. I spent Winter break last year teaching my Haskell because I wanted to add something new to the toolbox.)

    I'm waiting for the “formal methods” versions of the following papers. When that happens, formal methods will be the thing to learn. Again, not because we want to directly apply the “languages”, but because the concepts make us better.

    Beating the Averages, Paul Graham, http://www.paulgraham.com/avg.html

    How to Become a Hacker, Eric Raymond, http://www.catb.org/~esr/faqs/hacker-howto.html

  • Michaeljon Miller

    Should CRM services return an error for invalid attributes?

    • 6 Comments

    It turns out that I was always working with an altered version of the CRM platform which silently was ignoring invalid-for-create and more importantly invalid-for-update attributes. Why is this important? Well, my recommendations for creating a friendlier programming environment were missing a critical step which isn’t available to “outside” developers. This means that creating classes from the XRM entity XSD will only work for a subset of the core scenarios.

    If you’re using the classes for data binding operations then you’ll quickly realize that a READ-EDIT-SAVE operation won’t always work. In fact, any time you run the READ-EDIT-SAVE loop on the same instance things will break. There are two options available for working around this problem. One of them puts the burden on the developer and still doesn’t solve the R-E-S scenario: create different schemas for each of the Read, Create, and Update operations, then create corresponding classes to use them. This doesn’t work well because you now really need to pay attention to which attributes are applicable. The second option is for us to take a design change (which probably won’t make into CRM 1.2, but it might be early enough to get it into CRM 2005) that makes the platform silently ignore “extra” attributes instead of returning an error.

    Obviously I’m leaning toward the latter solution where we fix the problem… er, where we change the design. This isn’t a done deal though and there are strong feelings on both sides of the issue. So I’m asking the CRM development community (at least folks who read the stuff I write) for your opinion. Is it better to silently ignore attributes that don’t make sense for the operation or would you prefer the current behavior where we return an error?

     

  • Michaeljon Miller

    Why duplicate detection is hard

    • 4 Comments

    In a past life (I'm fairly certain it was a past life because there are days when I'm sure I'm paying for it), I worked on a pair of very large-scale data-cleansing systems. They will go unnamed, except that I'll refer to them as System DBS and System ES. Both systems had a specific set of goals and in the case of DBS a very specific target problem domain. The goals were quite simple:

     

    • Acquire data from a number of related applications (where "related" is a very loose term meaning that the owning company for each application was effectively the same, but that otherwise they supported different business functions).
    • Define a common schema that captures the union of all the source data.
    • Define the set of candidate keys that lets you identify an item's source, its original key, its common key, and its goal key.
    • Construct from that data a common union of all interesting elements. That is, load it all up.
    • Apply a forward-chaining rule system over that data to coalesce duplicate records, identify "bad" data, any find missing data.
    • Push that nice clean data back out into the bad dirty world from which it came (but put it back nicely)

     

    Given that DBS was specifically designed to run over data from a given problem domain (let's call it telecom data) one might assume that the problem was well-constrained. If one did assume that one would be very wrong. So, a small team of developers set out to generalize the problem space to cover different domains and designed ES as a result. ES followed the same path as above only in a very general way and without the overly complex rules engine (12 years later I think we might have been able to use that rules engine, but at the time our distaste for it was clear in the ES design). As an aside outside of parentheses this was called the U model mainly for the shape that the model took on while we drew it on the whiteboard.

     

    So, what's the point of that history lesson and what does it have to do with duplicate detection. Well, remember that I mentioned that the primary domain was telecom. That means we covered concepts such as customers, addresses, telephone numbers, physical plant data (there are a lot of little pieces that go into getting telephone service over a land line), bills & invoices, and payments. In all there were some 30 different systems involved in sourcing this data to our engine. One the first things that needs to happen in the DBS problem space is that a set of candidate keys need to be identified that work across all systems, or at least across enough systems such that in the end all systems can be logically linked. In the telecom world that was the phone number.

     

    In the U.S. (actually in the North American dialing area) telephone numbers are 10 digits long and always follow a very specific format. I won't go into the formal names for those various groups of digits or even why there are groups, but let's just say that each of the groups can become part of a key. Nearly every installation of DBS was in the U.S. so building a telephone number parser wasn't too terribly difficult. You're either looking for 7 or 10 digits (unless you run across a PBX in the data and then you have to start messing about with extensions). Well, the big DBS installation I worked on, and what drove much of the ES design, was not based in the U.S., but was instead in another country with very different telephone formats. Some places used 4 digits, some 5, some 7… you get the point. The plan was to configure and run DBS to first dedup that data so we could convert the whole country to 10 digit dialing. A secondary goal, once the customer realized what we could do, was to dedup the whole lot of data to see what we could find (did you know that many times the phone company doesn't know that your home already has a connected telephone line so they sent a technician out with new gear to hook it up?).

     

    I can hear you now: "Get on with the discussion and tell us why duplicate detection is hard."

     

    Remember that I mentioned the step about identifying a candidate key? Well, in the case of a phone number it's fairly simple (let's make some additional simplifying assumptions that phone numbers are never reused, each person has only one phone number, and numbers never move from person to person). Once you see a phone number in a normalized format you can then query over the set of existing data looking for other instances of that phone number. In a live RDBMS that can be done using a unique key constraint over the normalized phone number column that will throw back an error when an insert or update attempts to violate that key. In our simple world this works every time because when you get an error on an insert you know that you have either a duplicate record or an error in the key. For updates you know you have an error in one of the keys of the updated record or in at most one existing record in the database.

     

    Now, let's extend this from our idealized phone number world to something that's more CRM-ish. A phone number is not a reasonable candidate key because it does change over time, two people can share it, and many people have many numbers. A solution to this problem is to identify a new candidate key. One approach is to construct a key from various bits of useful information. For example one might use some normalized elements of a contact's name, a phone number, possibly an email address, and their home address. Once we've extended the key to cover enough elements to guarantee uniqueness (which is not possible and is left as a proof to the reader) in our problem space we will invariably run into the case where that key isn't wide enough.

     

    Let's see what happens when we insert a new record. First, we construct / synthesize a candidate key and put it into one of the columns in the INSERT statement. Then we fire the statement at the database and wait for an error. Let's assume we get a key violation back so we have a few options: we can change the key, we can ask the data supplier what to do, or we can punt. If we change the key automatically then we've simply ignored the duplicate detection problem and we might as well not have done any of this work. Same thing with punting except that it's overly harsh on the other side: the data doesn't go into the database.

     

    We might ask the user what to do. Well, simply telling them that their just-entered data would create a duplicate record in the system and therefore must be in error wouldn't be particularly useful. How would they know what part of it is in error? How would they even know what to do with a duplicate. One thing that DBS and ES did was return the new record and the existing duplicate(s) in a nice bit of dynamic UI so that the user could see both records essentially side-by-side and make a judgment call. This worked for our solution because we specifically engineered it so that there was a headless service running but a staff of "Error Resolution and Correction Clerks". That is, people were sitting in the dark waiting for bad data to pop up on their screen, they'd make a call based on the original data, the duplicate data, and occasionally the data from the source system.

     

    Let's say we do something like the first approach where we simply return the "offending" records and the new record and let the user decide. Then, the user decides that these two records are actually different from one another but that the data is 100% correct. In this case the user or the system could mar the record in such a way that it's no longer considered a duplicate and complete the write operation. What just happened here? Well, the candidate key for the new item will no longer raise an error when it's the cause of a duplication because that key is different. We could mar the data in a predictable way so that the key stays intact but so that there's a "larger" unique key over the data, but again that wouldn’t cause the insert to fail.

     

    The next option is to use a unique key over the candidate key plus some invented data (invented in a predictable way that is) and query the data on the candidate key before attempting an insert. Now we're getting somewhere. We allow duplicate candidate keys but invent a wider key that guarantees uniqueness (see above for details on the widening proof). But we still haven't solved the problem because we don't have a reasonable way to verify that a duplicate is really a duplicate.

     

    This all gets horribly complex when you're dealing with multiple record types or subclasses of types (think of the customer case in MS-CRM where "customer" might mean Account or Contact. This means you need a candidate key that crosses type boundaries and that you have a way to reconcile duplicates across types.

     

    Anyway, that's why duplicate detection is hard. Note that I didn't say it was impossible, just hard.

     

  • Michaeljon Miller

    What's with the CRM security descriptor?

    • 0 Comments

    In response to a number of questions I'm seeing on the Microsoft CRM newsgroup I've gone back through the archives and dug this up. I hope it helps.

    There's been lots of speculation around the security descriptors in Microsoft CRM, and none of it is quite right. The “real” story behind the SD columns is talked about in the CRM architecture paper a bit. They really are NT security descriptors and in theory you can manipulate them using standard NT functionality. But... we use some of the access bits differently than they’re doc’d in MSDN and we’ve added some of our own.

    As for the “hash” rumor, and the “encrypted” one too – there’s no such thing. We store binary data in the database in base64 encoded text fields. That means that attachments, sales lit, and security descriptors are really binary data wrapped up in standard encoding. Somewhere around here I had a routine that would scan through a table, pull the descriptors, convert them back to the binary format that NT likes, and dump them to the console. It’s an interesting application for learning, but it’s not enough information to figure out which ACEs go into the descriptor itself.

    When we tweak an object (create, update, share, assign, etc.) we recalculate the SD values based on the instance’s location in the organization and on roles of security principals somehow related to it. For instance, a sales manager with DEEP READ access to accounts will probably have an ACE in the SD by way of the role membership that granted her the DEEP READ access.  Explicit share means that the principal gets an explicit ACE. Implicit means the ACE came from a group (role, team, business, …).

    But, don’t go thinking you’ve got it all figured out yet. You would still need to know how object hierarchies affect the SD value. That’s where this thread started. We have the concept of a ‘child entity’ which is essentially unsecure on its own. This means that we need to get the access rights from somewhere, so we pull it from the ‘parent’ instance. You can find these types by looking through the metadata for entities that don’t have security descriptors but still have security behavior and APIs.

    Oh, and the idea of creating an instance through the application or API and copying the SD for later SQL inserts will only sometimes work. Actually, it’ll probably not work more often than it will. The reason is that there’s some additional information in the database to track access to entities.  It’s not always kept up to date, and it’s not always applied to all entities, but it’s there nonetheless. So, if you try the trick of copying a known SD value when doing a “data migration” at the SQL level, remember caveat emptor.

    Hope that clears up some of the fog.

     

  • Michaeljon Miller

    One of those irritating technical things

    • 5 Comments

    I was watching my local educational access channel this morning while walking on my treadmill. I thump pretty hard when I walk so I turned the TV up. That’s when I really started thinking about the volume control feedback. Why the hell does the volume go from 0 to 63? As a computer-savvy person I can sort of understand it, but come on now, 0 to 63? One of our televisions goes from 0 to 50. Ok, 50 is a ‘round’ number (at least to us who count using base-10). I don’t like 0 to 50 and would personally prefer 0 to 100 (still takes one byte of storage if that’s the problem) because then I might translate that to a percentage. But, ick. Is this worse than a stereo that goes to 11?

  • Michaeljon Miller

    More fun with XSD

    • 0 Comments

    One more old post making its way to MSDN.

    Torsten Schuster asked about the XML serializer, but unfortunately it was pretty buried. I saw it, but I'm not sure anyone else did. It sounds to me like another case of old CRM XSD and the VS XSD tool, but I'm not really sure.

    From what I gather the platform and APIs are behaving correctly. For example, on a sales order retrieve, the following column set can be passed in.


    <columnset>

        <column>salesorderid</column>

        <column>ordernumber</column>

        <column>name</column>

        <column>customerid</column>

        <column>customfield</column>

    </columnset>



    Which should (and in this case, does) result in the following XML.


    <salesorder>

        <salesorderid>{CA033A28-25FD-47E4-A98A-BDDF134B11F3}</salesorderid>

        <ordernumber>ORD-01001-54ZQ8T</ordernumber>

        <name>Test</name>

        <customerid name="STM Ltd." dsc="0" type="1">{3E8CDBD0-FD2E-409D-BB8D-39870AB689C1}</customerid>

        <customfield>stm_4711</customfield>

    </salesorder>



    The question is about the "customerid" element and why the serializer isn't pulling it into the hydrated object. I can only guess, but it sounds like the XSD doesn't have a correct definition for "salesorder" nor does it have a definition for "customerid".

    Ideally, the sales order XSD should have a "customerid" element of type "tns:customerType" which references this XSD


    <xsd:complexType name="customerType">

        <xsd:simpleContent>

            <xsd:extension base="xsd:string">

                <xsd:attribute name="name" type="xsd:string" />

                <xsd:attribute name="type" type="xsd:int" use="required" />

                <xsd:attribute name="dsc" type="xsd:int" />

            </xsd:extension>

        </xsd:simpleContent>

    </xsd:complexType>


    I can't guarantee that the VS XSD tool will cope well with the XSD that I talked about earlier. Although making the XSD tool deal with it is fairly trivial, I still prefer the other code generator.

    Like I said a while back, I can't support any of this stuff, but I can lend guidance on occasion. Hopefully this information is enough to get things moving again. If not, well, maybe someone else can chime in.

     

  • Michaeljon Miller

    Why MS-CRM isn't a CRM product

    • 1 Comments

    For the longest time I've held the belief that the underlying infrastructure behind MS-CRM was there to support arbitrary business applications. It's funny, for the last six months I've been betting my career on that belief, and even more today, I truly believe that the core value proposition is that CRM is so extensible that its CRM-ness becomes secondary to how and why people buy it.

     

    I've been talking to a number of customers and partners who have purchased CRM not for the CRM aspects at all (although I suspect they end up using quite of bit of the functionality) but for the platform components. It seems to have come full circle - the CRM product was, a very long time ago (in industry terms) designed to be the platform on which bCentral applications were based. After a short segue into on-premise core CRM functionality where the extension capabilities were disabled or hidden, the product is really progressing into a true platform.

     

    It'll be interesting to see what happens over the next two releases. One that's been talked about is called Titan and that's the one that I think of as the core platform. I'm looking forward to seeing what that team can provide to the rest of Microsoft, and possibly to the industry, in terms of platform. Watch out SharePoint, there's a new kid in town and that kid knows all about business application requirements.

     

  • Michaeljon Miller

    When software architecture isn't like building architecture

    • 0 Comments

    We've all been through the talk about how building software is like building buildings. That somehow there's a correlation between creating a software model of reality and a physical model. I've even given presentations and taught course material that made comparisons between how we build buildings from the ground up with a plan and that we should do the same thing with software.

    I'm sure we've all heard the flip-side of the same discussion which says that software architecture is somehow very different from building architecture because software is mutable. That is, if we get parts of it wrong today we can go back and fix it.

    For the most part both of those arguments seem to hold. We want to build software with a plan and on a stable base. We build buildings that way and things turn out well. Right?

    I noticed a difference last night when I received an invoice for "additions" which were made to my house. For those of you who don't know, I'm in the middle of a huge remodel project; this invoice was, I had assumed, my monthly "catch-up" payment against the original bid.

    So, what's different between the two worlds. Well, in both software and buildings estimates can be incorrect. We don't always have all of the information we need so we make guesses. That's why we call the estimation process an estimate. It's not an exact science. At least it's not in the software world.

    We've been building homes for many, many years now and we know a priori what's going to go into their construction. In fact, most building estimates are extremely close, which is why I was making monthly payments against the estimated cost and schedule. Well, I realized last night that it's entirely possible to have an immediate 20% cost overrun building a house (or remodeling one as it were). Why did this happen? Well, turns out that the architect hadn't done all the planning that typically goes into a house remodel plan -- he forgot three windows and ALL OF THE LIGHTS.

    What's this got to do with software? Well, in software we learn things as we build the product. We do this because it's not possible to know everything about the product before we start building it. Cost overruns happen, and when there's doubt about the cost to build a feature developers even over-estimate to cover their asses and their customer's asses. Building architects never over-estimate. Ever. Not once. They always figure out everything they need to put into the project and everything "new" after that is a change order. They can get very good estimates this way and everybody comes out happy in the end. Unless the architect forgets ALL OF THE LIGHTS.

Page 1 of 4 (85 items) 1234