SharePoint: Reference Data Lookup

In my SharePoint Common Reference Data Management post, I outlined several options for how to maintain and use common master data as reference data across site-collections:

• Replicated Lookup List
• Remote Lookup List
• External Database Lookup
• External Hybrid Lookup List

I've made some figures showing the four reference data designs:
Lookup metadata and content.pdf

Note that if you plan to use custom lookup columns in your site content types to get a controlled vocabulary for your taxonomy, you must test and verify that the lookup columns can:

• be used as site columns
• be filtered to show a subset of lookup values
• work in the DIP panel - fully, downgraded or not at all
• reference data in other site-collections
• be replicated across site-collections

A lookup column that can be filtered is useful as it is better positioned to support an evolving set of lookup values, e.g. hide values that become expired. Such a lookup column can be configured to work exactly as a choice column and still contain a centrally controlled vocabulary value-set.

SharePoint: Common Reference Data Management

There are two kind of common reference data typically used in SharePoint:

1. Data that is native to the portal
2. Data that provided by external systems such as CRM, ERP, SCM

The usage and maintenance of the common reference data differs by their information type classification as described in later sections. Doing a thorough information architecture analysis is important for this aspect of SharePoint also.

The sharing of common data across multiple site-collections will also affect how native data can be stored, accessed and maintained.

External Common Data

External data used in SharePoint should not be imported into SharePoint lists; but must rather be used as pure reference data, and be maintained in their native systems. This is to avoid replication as much as possible due to the extra implementation and operational effort each added replication requires.

Use the web-parts provided by the external system to view the external data. Usage of the external data as lookup columns in lists is restricted to either:

• Plain text fields with lookup only in InfoPath DIP form
• Custom developed lookup columns
• Third-party lookup columns
• Use of the MOSS BDC

The “Business Data Catalog” (BDC) is the standard MOSS mechanism for utilizing external data in MOSS, but this will require MOSS enterprise edition. Note that the BDC "Business Data" column cannot be used as a site column and hence not be part of a site content type.

Suggested third-party components:

• Bamboo external data sources: MashPoint
• Bamboo external lookup column: Business Data Column

Always test and verify that custom columns can be used as site columns if you plan to use them in site content types. Also test and verify that custom columns will work in DIP when used in a document content type, even if only added to list content types.

Native Common Data

Native reference data should be stored and managed in SharePoint lists. The advantage of using SharePoint lists is that you get a data management UI for free: just use standard SharePoint to maintain the data. The downside is the need for replication of shared data across site-collections that use the reference data, as OOTB lookup columns are restricted to a single site-collection.

The common data lists will be used as reference data sources for site columns of type “lookup”. This will ensure that common data is based on native SharePoint aspects, possibly enhanced with third-party SharePoint components.

It is recommended to create the reference data list and the corresponding lookup site column at the root site of the site collection, as the lookup column can then be used in lists in any sub-site to lookup the reference data (cross-site lookup).

Externalized Native Common Data - External/Hybrid

Sharing common data in SharePoint lists across site-collections is not trivial. It must be considered if native common reference data should be externalized to a custom database and be treated as external data. The downside is that an application must then be implemented to allow for maintaining the externalized data. Data-aware tools such as InfoPath, Excel or Access can be utilized as the data maintenance front-end.

A variant of the external database is to use SharePoint itself as the external database, then you get the best from both alternatives (hybrid): a real master for the common data and the SharePoint UI for free. Use either a BDC lookup column or a third-party lookup column that works across site-collections.

Externalizing the native common data to a separate database will require more work on backup/restore. Using the SharePoint database avoids this.

Suggested third-party components:

• Bamboo lookup column: Selector
• KWizCom remote list viewer/editor: Remote List Viewer web part

Always test and verify that custom columns work across site-collections.

Replicating Common Data across Site-Collections

Native common data will be managed centrally in a master site collection, replicated to multiple target site collections. There will be one master list per target list type. Lists in sites in the target site collections can then reference the target common data lists using columns of type “lookup”.

Note: always prefer using externalized native common data instead of replication.

There are two options for how to maintain and replicate the shared data:

A) Overwrite data in targets on replication, i.e. data must only be entered in master as any updates to data in target lists will be lost on next replication. Updating target lists must be prohibited.

B) Two-way data replication to allow for updating data in targets in addition to master. Conflict resolution rules must be configured so that the master wins any auto-resolvable conflicts.

Which option to use for a list depends on the classification of the information asset that the list contains. E.g. country and product lists need not be updated except from in the master; while it is critical for the business that project information assets like contacts can be updated and used immediately.

Note that there will be latency in the replication of changes for both option A) and B). This latency can be configured. The impact on this replication latency must be analyzed based on the information types identified as common portal data. Different information types will have different tolerance for data staleness.

For option A) it must be considered to use a remote list viewer/editor in relevant sites to allow for editing master lists “remotely” from target sites.

Suggested third-party components:

• KWizCom remote list viewer/editor: Remote List Viewer web part
• Echo 2007 Content Manager
• DocAve5 two-way Replicator

Always test and verify that custom components such as web-parts and column types can be replicated.

SharePoint Lookup Column Types

Note that the standard “lookup” column is not a many-to-many relationship; it is just a choice list with a multi-select option. There are several third-party lookup types available to alleviate this limitation.

Suggested third-party components:

• KWizCom many-to-many list item relationship: Dual Lookup Field Type
• Bamboo one-to-many relationship: List Integrity
• Bamboo master-detail lookup column: Linked Selector

Always test and verify that custom columns can be replicated if used in lists and content types.

Codename "Bulldog" - Master Data Management

Microsoft last year acquired the Stratature +EDM master data management (MDM) platform. It will be incorporated as part of the next version of SharePoint, however only a few details are public. Read the roadmap here.

Information Models: Business Process or Forms-over-Data

Joe McKendrick has a post about the need for doing data governance today:

If data governance is inadequate — information is outdated, out of sync, duplicated, or plain inaccurate — SOA-enabled services and applications will be delivering garbage. That’s a formula for SOA disaster.

He links to the XML to the rescue: Data governance in SOA article by Ed Tittel, which describes how having a common model and doing data governance is imperative for SOA success. The article conveys the same concepts as chapter 4 'XML: The Foundation for Business Data Integration' in David Chappell's seminal book Enterprise Service Bus. David describes the need for having a common XML data format for "expressing data in messages as it flows through an enterprise across the ESB". This common XML format is a specialization for the common data model (CDM) pattern from the classic book Enterprise Integration Patterns by Gregor Hohpe/Bobby Woolf.

I've written many times about the importance for having a common information model (CIM) for the domain objects (business entities) that your data services operates on, and the need for applying master data management (MDM) on those data. David Linthicum and I agreed on this last year and still do.

What I've written even more often about, and what most canonical models do not encompass; is that the data expressed in the messages conveying business events is not simply CIM entities, but are rather projections of one or more of the domain objects. In addition, the messages covers more than just activity data, they also contain queries, notifications (events) and commands.

E.g. an insurance clerk triggers a business process by registering the real world event "customer has moved" using a projection of customer and address data, not the complete customer aggregate. This action event message triggers a recalculation of the insurance premium due to the relocation of the customer, in addition to updating the customer record - i.e. an underlying business process is executed.

In a traditional forms-over-data application, the whole customer aggregate would be used - but this style would not make for services that can be easily composed to support business process management efforts. The business event data requirements must be simple enough to make composite services possible, including semantic data mediation and integration. In addition, process tailored data make it easier for any involved human operator that have to enter or act on the data. After all, BPM is not all about automation, and human tasks will be central in driving the processes for quite a while.

We need to move to an information model that allows for semantic business process integration, not just semantic business data integration. That is why a business process information model (BPIM) is required, modeling the messages for the action, query and notification events that drive the business processes. Design the BPIM based on the CIM, ensuring that the model is canonical for each process domain.

The BPIM is based on the CIM as each service domain model is just a projection of the common data model, and the projection metadata should be goverened as any other SOA artifact.

SOA Information Models and Services Compositions

I've written quite a lot about having a model for the information needed for semantic business process composition in service-oriented solutions, two of the most read and commented are SOA Canonical "Data" Model and Ontology for Business Processes, SOA and Information Models.

The process composition information model is focused on the business events, messages and documents and the semantics of the information that the model encompasses. The model is targeted at the orchestrations level in the ontology, not at the services level; as shown in the figure (click to enlarge):



The service classification scheme has the "hierarchy" taxomomy form. It is not a scientific hierarchy nor a tree form, thus there is no "is using" or "is dependent on" relation between the services - it is just a classification scheme. There can be no dependency between services in SOA except for composite services realizing a business process by consuming the actual services. Taking a dependency on another service will break the central "services are autonomous" tenet of SOA.

A note on the splitting of the service categories: this is a pure metadata ontology based on the taxonomy for categorizing services, it is not a split based on technolog or, teams, and surely not on layers. A service should not call on other services, making a service dependent on another service will lead to repeating the failure of DCOM/CORBA in distributed systems; using web-services and calling it SOA solves nothing. Use the active service pattern or EDA to ensure that your services are truly autonomous and independent of the availability of other services. Isolate service dependency to the composite services, or even better to the consumer mashups, only.

The "business process information model" (BPIM) is what models the business events and documents in the composite services level (orchestration / conversations / sagas). I like to use a domain model as it by definition focuses on the concepts of the problem domain (the business process information artifacts) rather than just the data of the domain. The information model must comprise the different message types for the action, query and notification events that drive the business processes. The model must be canonical for its domain to ensure that the format and semantics of the model is consitent within the domain.

There is some confusion on the difference between the process composition BPIM and the EAI "canonical data model" (CDM), plus the similar concept Common Information Model (CIM). As can be seen from the comments on the two postings and some blog reactions like SOA doesn’t need a Common Information Model, this has made it harder to convey the difference between the process composition information model and the more data-oriented models.


A CIM should be used for the resources data model - which the BPIM is related to. Design the BPIM based on the CIM, ensuring that the model is canonical for each process domain. The data in the event document for the real-life action "CustomerHasMoved" is not a complete "Customer" resource entity, it is a message including minimal "AddressChange" data. This data will typically contain both actual event data combined with reference keys to the resources such as customers and addresses in the different service domains that the composition comprises. 

The message data is of course associated with the CIM of the underlying resource services. The BPIM message types are projections of CIM objects; in fact, they are projected compositions of the referenced resources - not just simple compositions of resource objects. These projections must be kept as small as possible, preferably just the resource keys, in addition to event details such as a booking reference number. Keeping the event document data model small has the nice side-effect of making the schema versioning a bit simpler.

The CIM should be the starting point of your modeling efforts, as the documents in the process compositions is based on the service domains. Having Business Process Modeling Notation (BPMN) diagrams can help in modeling, as BPMN encompass process, events, messages and business documents, covering actions, queries and notifications. Other process modeling diagrams can be used, just ensure that the events, messages and data are captured in the model.

It is still important to recognize that having a "one true schema" for process composition or SOA in general is not a recommended practice, hence the term "domain" in the naming. Rather than trying to enforce a common model across all service domains, federated models should be used. When composing services across the domains, mediation and transformation of messages will be required - here a service bus with such capabilities would come in handy.

A thing to recgonize is that the overall model incorporates several "flows": process flows, event flows, message flows, and data state flows (resource lifecycles). Typically, only the process flow is clearly shown in diagrams, while e.g. message flow is only represented by receive and send ports. See this interview with Gregor Hohpe on Conversation Patterns about how more than just the process need to be modeled.

For an excellent primer on architectural aspects of SOA and the proliferation of acronyms, I recommend reading Architecture requirements for Service-Oriented Business Applications by Johan den Haan. Note that he does the classical simplification of focusing on business processes without including a BPIM; it is important to separate the process composition model from the resource model, just as business processes are separated from resource lifecycle processes. The business process events, documents and messages are not just collaterals of the BPMN modeling process, they are important artifacts for semantic mediation in composite services.

[Business Process Integration: "e-Business: organizational and technical foundations", Papazoglou, M. P. & Ribbers, P. (2006)]

Ontology for Business Processes, SOA and Information Models

I have for a long time promoted that you should apply a specific kind of information model (the business process information model - BPIM) when composing services into business processes in service oriented solutions. A BPIM models the messages for the business events that drive the business processes. The main goal of this information model is to enable coordinated loose-coupling (semantic mediation) between the services at the composition level.

There has been a lot of discussions on how the BPIM relates to SOA services and the common information model (CIM), and how the BPM process layer relates to the SOA process layer. In the following ontology I have tried to model the relationships between the documents and the domain objects, Shy Cohen’s service taxonomy & ontology, and Jean-Jacques Dubray’s SOA+BPM ontology. 

The service classification scheme has the "hierarchy" taxomomy form. It is not a scientific hierarchy nor a tree form, thus there is no "is using" or "is dependent on" relation between the services - it is just a classification scheme. There can be no dependency between services in SOA except for composite services realizing a business process by consuming the actual services. Taking a dependency on another service will break the central "services are autonomous" tenet of SOA.

Click to enlarge the ontology:

Note how there are processes at two levels in the model: the event-driven resource lifecycle processes for domain objects (bottom), and the human-driven business processes realized as composed services (top). These two levels are separate bounded contexts with well defined mappings between them: the business process domain and the resource lifecycle domain.

The BPIM is applied at the business process domain level (orchestrations / composites / sagas), while the services operate on the domain objects in the CIM to manage the lifecycle of these resources in accordance with the business events.

Note that the BPIM is related to, and is a subset of/reference to, the resource domain objects in the common information model (CIM). In addition, the messages covers more than just activity data, they also contain queries, notifications (events) and commands. This in like a mail order paper form that contains e.g. some customer data and references to product data. The word "subset" is central, e.g. the “customer has moved” action event has a BPIM document that contains the data pertinent to that specific business process event, not the complete schema of the customer domain object. The resource process service used to act on the “customer has moved” event does of course operate on the actual domain object, but this is invisible to the human workflow process.

The business process messages (action, query, notification) driving the composite services and the resulting business events are central artifacts when designing the services and the information model. I am a strong believer in designing a service-oriented solution by applying "EDA style" thinking to avoid missing out on the events and their documents due to traditional “invoke operations” SOA thinking, getting too much focus on business process flow.

An aspect not shown in the model is that the need for agility increases towards the top, while the cost of change increases towards the bottom. This is an important argument for having processes at two levels; it allows you to contain the frequent changes to the business processes (mashup style compositions) and composite services rather than having to constantly make changes to the services themselves, which could become very costly as the number of service subscribers increases over time. This is something that your business people should readily appreciate.

Design your system to have flexibility in the flow of the business processes and composite services. The diamonds of your business process are the business decisions, and this is where you should implement a business rules engine (BRE) mechanism. Process flow logic is not service business logic, avoid leaking domain logic into the orchestration layer at all cost.

Finally, the model show how claims relate to services at all levels. I have done this to show how a claims-based security model is a cross-cutting concern throughout a service-oriented solution. I strongly recommend designing in process claims right from the beginning, as this makes it easier to learn how access control relates to the business processes and solution domain.

Master Data Management (MDM) relates to the "Resources" in the above ontology. It is imperative that you apply a MDM strategy to your enterprise resource domain objects to avoid inconsistencies and multiple versions of the truth.

I hope this ontology makes my viewpoint on business process driven SOA clear to you. Feel free to comment on this model, and also check out the six figures in JJD’s ontology for models showing different perspectives of resource lifecycles & business events, BPEL, BPMN and human tasks.

See also this related post.

SOA: Canonical "Data" Model

An important topic when designing service oriented systems is how to enable different services to share semantics to be able to be composed into working solutions. Jack van Hoof has written a good article about this: How to mediate semantics in an EDA. A few weeks later, Nick Malik posted another good read about this topic: Canonical Model, Canonical Schema, and Event Driven SOA. Read Jack's post first.

They both talk about using a canonical data model (CDM) as the Esperanto / Babel fish to map between the format and semantics of the disparate systems taking part in a SOA solution. Note that CDM is not about having a common data model (EAI CDM) or a shared database across all systems in an enterprise, don't get fooled by the "data" in the term 'canonical data model'. CDM is about not making everybody have to speak English, but rather having CDM translators for each native system.

Btw, Gregor Hohpe sometimes use the term 'canonical domain model' on his blog, while using the term 'canonical data model' in the book "Enterprise Integration Patterns" [Hohpe/Woolf CDM pattern (355)]. I think it is better to talk about the business domain rather than about "data", as this help focusing on the business processes rather than databases and other technology. You'd be surprised how many biz people concern themselves with how the data model looks - maybe a leftover from the client-server days, to show that they know what an ER-diagram is? Focus on designing a business process information model (BPIM) for each business process domain.

Trying to enforce a One True Schema across your services (everyone has to speak English) is not a viable path, and it is also a recipe for future maintenance hell. Making every service contract depend on the One True Schema will make it impossible for the services to evolve separately, they will no longer be autonomous. A simple change to e.g. the order entity will cause a ripple effect through all referring services. This is where the business process information model comes into play, it allows you to version and evolve the services independently of each other.

The Canonical "Data" Model concept is also sometimes referred to as a Common Information Model (CIM). Both the business process information model (BPIM) and the data focused CDM/CIM models has the same goal: mediation og semantics. However, they are not the same as the two other models are both variations of the common data model approach. The business process information model is about semantic business process integration, not just only semantic data integration.