2.90:CMIS Lookup (Lookup Specification)

Performing data lookups on CMIS sources like SharePoint can be extremely powerful in your data integration endeavors.

A base Content Model for use with this article can be found here. It is not required to download to understand this article, but can be helpful because it can be used to follow along with the content of this article. This file was exported from and meant for use in Grooper 2.9

About

Grooper uses the CMIS protocol to connect to a variety of Content Management Systems. This connection can be used to integrate data in powerful ways by allowing the collection of one (or sometimes many) fields of information within a model in Grooper (let's call it Field A), then leveraging that extracted data point against the column of like information in the Content Management system (called Column A) to then pass back other desired fields from other columns in the same row of information.

How To

The most basic thing to understand about performing a lookup in Grooper is what the "lookup field" is and what the "target field(s)" is(are). The lookup field is the trigger, and the target fields are the result.
Put another way, the lookup field is what will be populated with information from an Activity like Extract (a user could also manually enter data into the field). This field will then be "bounced" off of the source of data to supply the remaining fileds, or the target fields, with information

!	Some of the tabs in this tutorial are longer than the others. Please scroll to the bottom of each step's tab before going to the step.

Understanding the SourceUnderstanding the Content ModelConfiguring a CMIS ConnectionConfiguring the CMIS Lookup

Understanding the Source

Perhaps the most challenging aspect of explaining how to leverage this functionality is the fact that it requires access to a CMIS repository (in this case a SharePoint document library). One was created for the sake of this article, but unfortunately cannot be shared externally. The scope of this article also does not allow explaining setting up a SharePoint document library, as that's an exhaustive topic on its own. Note that CMIS Lookups are not limited to SharePoint, that just happened to be the content system chosen for this article.
Given that, it will be assumed you have access to a source that Grooper can connect to via a CMIS Connection.

The below image is showing a SharePoint document library with a single document in it and several columns added and populated with information.

Understanding the Content Model

The Content Model used here is very simple. It has no Content Types or any extraction setup. None of that is necessary to setup and demonstrate the lookup. It simply has a Data Model with several fields.

Configuring a CMIS Connection

This configuration is specific to this article. While aspects of it can apply to any configuration, it's worth noting that this is not THE way to configure a CMIS Connection. This will be configured for a SharePoint environment. Note that the URLs in the image cannot be connected to externally, so attempting to copy this configuration verbatim will give you errors. This is meant as a guide, and will require an actual SharePoint environment on your end to be established, and real URLs from it supplied.

Expand the node tree to the Infrastructure folder and in the CMIS Connections folder create a CMIS Connection object. Set the Connection Type property to SharePoint. Fill in the Base URL property with the URL for the SharePoint site you're wanting to connect to. You can be as granular with this as you want. What this means is you can set the URL to a specific site, and have the CMIS Connection object pertain to that specific site of the SharePoint environment (https://hydra.sharepoint.com/sites/ChiliConToso), or, you could set the URL to the base SharePoint URL and manage all sites from one CMIS Connection object (https://hydra.sharepoint.com).
The Authentication Method property can only be set to SharePoint OAuth when configuring a SharePoint connection. When you click the Authentication property the first time you're configuring a CMIS Connection object with the Connection Type property set to SharePoint a window will pop up to allow the entering of credentials for your Office 365 account. To log out, click the ellipsis button on the property. Set the Enable Library Types property to True. There are two main Content Type objects within a CMIS Repository object: Document and Folder. These have sub-libraries within them that have content specific to their type. Setting the Enable Library Types property to True will allow the creation of CMIS Content Type objects based on these sub-libraries. This must be done in order to connect to the specific library and have visibility to its writable properties. Writer's note - This statement is true if you plan to complete steps 10-13 below and leverage that specific object for import or export. Given the note there states that steps 10-13 are not needed for this CMIS Lookup, this previous statement is a bit erroneous.
With the previous settings saved, you can now click the List Repositories button. Having clicked the previous button, there should be a repository (there may be multiple in your environment) in the Repository List view. Clicking on it will allow the Import Repository button to be clicked. This will change the red dot on the icon to green, meaning there's an active connection to this repository. It will also create several objects in the Grooper node tree: CMIS Repository ojbect, Content Types folder object, and two CMIS Content Type objects - Document and Folder.
Select the CMIS Repository ojbect. Expand the tree of the Document Content Type object within the Content Types view. These libraries, or sites, would not be available if the Enable Library Types property were not set to True. Select the Content Type object that relates to the library/site you want to connect to that has the writable properties you want, then use the Import Types drop-down menu and select Entire Branch. This will be different for your environment, but notice the Content Type Properties list view below. This lists the Writable Properties, like the ones highlighted, and also gives other important information about thier configuring like the data type of the columns. With the desired Content Type imported, a new object representing it will exist in the node tree.	Writer's note - Having created this article and gone back through it, I now realize steps 10-13 here are not absolutely necessary to configure the CMIS Lookup. It will not impede one's ability to do so, and this is valuable information otherwise, as you can use the object created in these steps to map import and export properties for other activites. Feel free to not do steps 10-13 for the CMIS Lookup.

Configuring the CMIS Lookup

With an established connection to a CMIS repository, the CMIS Lookup can now be configured. This is done on a Data Model.

Select the Data Model. Select the Lookups property then click the ellipsis button. This will bring up the Lookup Specification Collection Editor window. In this window click the Add drop-down menu and select CMIS Lookup.
Select the CMIS Lookup ojbect in the list. Select the Repository property then click the drop-down arrow to get a mini-node tree view. Select the CMIS Repository object you wish to perform a lookup against.
Select the CMIS Query property then click the ellipsis button. This will bring up the CMIS Query window. Start by beginning to type... SELECT ...in the editor. The intellisense will pop up a menu that will let you complete the command by pressing tab (or enter, or double clicking the value). You can also just continue writing it out. A CMIS Query has syntax almost just like a SQL Query, so if you're familiar with that this part will be quite simple. Grooper's intellisense for these queries is quite good, however, so even someone with little-to-no experience with SQL Queries will be fine. This is also as good a time as any to remind you to use the Grooper Help: that easily glossed over area with the medium gray background underneath nearly everything you select. CMIS Query is a great example of the Grooper Help as it goes a long way to help you get a result, including telling you what to type, and examples to try.
Having completed the... SELECT ...command, press the space bar to move the cursor forward one space. In doing so the Grooper's intellisense will kick in again and push you in the direction of the... FROM ...statement. Not only that, but having pointed to the CMIS Repository object, the intellisense has an awareness of Content Types within the repository. Select the site that contains the writable properties you're looking to leverage for the lookup. All you SQL sleuths out there might argue, "HEY! You haven't told it WHAT to select, so you shouldn't write the FROM yet!". Estute observation, however, Grooper's smarter than you think. In writing the... FROM ...statement first you've given Grooper awareness of what CAN be selected. This is made clear in the next step.
If you allowed the intellisense to complete the... FROM ...statement for you, it will move the cursor back in front of the... SELECT ...command. This is important because you can now properly flesh out WHAT you are selecting. By pressing the spacebar (after it moved back to in front of the... SELECT ...command) you will now get more intellisense functionality. The intellisense menu will have awareness of the columns of information available in the SharePoint library you selected in the... FROM ...statement. Here you will be choosing the target fields of the lookup. Select an item from the intellisense list.
Having completed the previous selection, type... , ...followed by a space. In doing so you will activate the intellisense again and be able to make another selection. Notice the previous selection is no loner in the list. Repeat this process as many times as necessary to select all target fields.
Here, with... EMail_Address ...we have an example of a column in the SharePoint library being named differently than what is in the Data Model. In order for this to match up, we have to tell it what the column will be used as. To do this, after making a selection, but before the next... , ..., press spacebar and once again see the intellisense kick in and know you want to make an... AS ...statement. Complete the... AS ...statement and press space again, and ... you guessed it ... more intellisense. The values listed here are data elements from the Data Model. By completing... EMail_Address AS 'EMail' ...we've allowed for dissimilarly named elements to sync up.
Having completed the entire... SELECT ...statement (again, this was for the target fields) we can now write the... WHERE ...statement (to define the lookup field). To do this move the cursor to after the completed... FROM ...statement and press the spacebar. Select the WHERE option from the intellisense menu.
Following the... WHERE ...command press spacebar and select the column that will function as the Lookup Field.
Type... = ...immediately after the previous text (no spaces) and select from the intellisense menu the data element from Grooper to marry this too. In most cases it will be named the same. This variable is dynamically populated when the query is sent across for the lookup. SQL can't look at a Grooper field, but it can look at variables. Therefore a dynamically generated... @Employee_ID={value of grooper field} ...is needed to allow the lookup to function properly. The completed query used here looks as follows: SELECT First_Name, Last_Name, Phone_Number, EMail_Address AS 'EMail', Gender, SSN, IP_Address, Street_Number, Street_Name, City, State, Postal_Code AS 'ZIP' FROM sites/Chili con Toso/Shared_Documents/Document WHERE Employee_ID=@Employee_ID When finished writing the query, click the OK button to close the window.
Back in the Lookup Sepcification Collection Editor the Lookup Fields and Target Fields properties are now dynamically populated based on the CMIS Query that was written. These properties will usually work fine if defaulted, but they cover circumstances outside basic situations. The Grooper Help does a good job explaining what they're for. Click the Test Lookup button to test your configuration.
Enter a valid value into the Lookup Field A successful lookup will populate the columns in the Lookup Results list view with information.
You can also test your results by entering a value in the Data Field that is the Lookup Field in the Data Model Preview. Enter a value, and press tab to leave the field. Leaving the field fires the lookup, and will, as a result, populate the remaining fields.

Version Differences

Prior to Grooper 2.9 the CMIS Lookup functionality did not exist.