Data architecture considerations and guidelines for retrieving SAP data via Duet Enterprise into SharePoint

In Duet Enterprise FP1 it is relatively easy to generate a Gateway Model, as long as your SAP data sources live up to the required constraints. The challenge and intellectual work transfers to the data architecture, for deciding on and defining the proper data service interfaces given the application context.

This blog is earlier published on SAP Community Network Blogs

Real-time retrieval versus Search indexing

Bringing SAP data into SharePoint based front-ends can take different forms. You can unlock the SAP data real-time via ExternalList, to display and even edit the SAP data via the familiar SharePoint List UI metaphor. If the row-based List format doesn’t fit, you can develop a custom SharePoint webpart. You can also access the SAP data via the extensive SharePoint Search architecture and capabilities. With the improved Enterprise Search in SharePoint 2010 [or FAST if also purchased by customer organization], more and more new applications will be architected as search-driven. In context of SAP data think of searching from SharePoint context for a certain Supplier administrated in PLM, a Customer in CRM, et cetera.

Duet Enterprise can facilitate all of the above scenarios, in combination with the strengths of the SAP and SharePoint landscapes. However, the different nature of these scenarios may well result into different approaches for the data integration architecture. In the case of real-time retrieval of SAP data to render into SharePoint, it is advisable to limit the amount of data retrieved per SharePoint - Gateway - SAP backend interoperability flow. Don’t put unnecessary SAP data on the line that is not being used in the front-end: only include the fields of the SAP data that will be displayed in the UI and are relevant in this application context for the end-user. Search however has a different context. It is feasible to search on any of the field data of the SAP entity. This requires that all that SAP field data must be indexed upon SharePoint Search crawling time. So here it is advisable to not limit the amount of SAP data retrieved, but instead retrieve as much of the SAP data that expresses some functional value.

What if the same SAP data is to be unlocked both via SharePoint ExternalList, as via SharePoint Enterprise Search? The data integration architectures of these are thus contra dictionary: reduce the retrieved SAP data versus give me all. Well, nothing prohibits you from constructing multiple data integration pipelines, tuned for the different scenarios. For Search, it’s Gateway Model returns in the Query method all fields that contain functional value. And for the real-time ExternalList, that Gateway Model returns in the Query method only those fields that will be rendered as list columns.

In Duet Enterprise 1.0, constructing the Gateway Model takes considerable time. This is an obstacle for constructing multiple Gateway Models with different data representation / signature, as it can easily double your Gateway mapping effort and time. Luckily in Duet Enterprise FP1 it is relatively easy to generate a Gateway Model, as long as your SAP landscape data sources live up to the required constraints.

Complex SAP data source structure

If the SAP data is conceptually a flat structure, it is sufficient to have a single Gateway Model to retrieve the data into SharePoint context. However, we all know that SAP (or, business) data is typically of a more complex structure: hierarchical with multiple child entities. When you want to retrieve such a structure into SharePoint, you basically have 2 options.

The first is to flatten the structure. This approach suffers from some disadvantages. In case of multiple occurrences of the same child type (e.g. Ordered Item); how many of them should you include in the flattened representation? All, or an arbitrary limited number? Also, for the end-user a flattened structure can be conceptually wrong and counter-intuitive: Ordered Item information is of different functional level as the Purchase Order information.

The second option is to maintain the hierarchical SAP structure within the Gateway Model architecture. For this you need to generate a Gateway Model for the parent SAP data entity, as for each type of its child data entities. At SharePoint client-side you associate the 2 resulting External ContentTypes, to establish the parent-child relation. SharePoint BCS respects the association between the External ContentTypes when it operates on the data. In case of SharePoint search, the child-associations are crawled in the context of the parent data, and either the parent or child SAP data entity will appear in search results. The BCS Profile page of a parent data entity renders also the data of its child entities.

In case of real-time retrieval, you can apply the BCS Business Data webparts: add to the SharePoint page both a Business Data WebPart for the parent entity and a Business Data Related List WebPart for the child data entities. The latter will display the child SAP data entities of the parent SAP data entity that is selected in the Business Data List web part.

Mind you, the user experience of this setup is not always intuitive. In such case, it can prove better to build an own custom presentation. An example of this is outlined in Working with complex SAP business entities in Duet Enterprise.

Unlocking SAP data via Duet Enterprise Feature Pack 1 in more agile approach

This blog is earlier published on SAP Community Network Blogs

A major bottleneck in applying Duet Enterprise 1.0 is the time it takes the SAP + Microsoft development team to unlock SAP data via Gateway to SharePoint. You have to define the service interface in ES Builder, create a proxy in transaction SPROXY, and next via transaction SE38 realize the GenIL model. The latter requires a lot of handcrafting ABAP code to do the mapping from Gateway runtime context and data representation to SAP backend, and vice versa. Code that follows a pattern, so a good candidate for code generation. This was a major pain point experienced by us when initially applying Duet Enterprise 1.0 within the Ramp-Up in 2010, and with emphasis reported back to the Duet Enterprise product team.

In the coming Feature Pack 1 version, the Duet Enterprise product team has evidently got the message. FP1 comes with multiple generator tools to ease and speed-up the realization of the internal Gateway Model (new name for GenIL model) for your application scenarios. Handcrafting is largely eliminated and replaced by full-automatic generation of first the mapping code to unlock the SAP data via NetWeaver Gateway 2.0, and next the required SAP Gateway service proxy, plus the SharePoint BDC model. The latter can be handed over to SharePoint side to import the External ContentType definition into Business Connectivity Services. Something that took us with version 1.0 several days to set things up at both SAP as SharePoint side, now is done in matters of minutes. Very appreciated side-effect of this is that it enables agile development: if the initial Model does not fit the requested application context, just change the mapping in the tooling and regenerate. With handcrafting approach you would loose a lot of elapse time here rearranging and testing your own mapping code.

Of course not all now suddenly comes for free. Before you start the Gateway Model generation, you still first must think about the data integration architecture to achieve your application scenario. An action that involves and requires consensus of both the SharePoint and SAP backend architects plus developers. The usage of the FP1 / Gateway 2.0 generation tools itself put some constraints on the SAP backend data sources; BOR, RFC or Dynpro Screens. Basically it comes down to it that the backend data entities must provide at minimum both a ‘Query’ and ‘ReadItem’ operation, and also ‘Create/Update/Delete’ operations for update scenario via SharePoint.

What if the available SAP backend entities do not self satisfy the requested integration pattern and/or the generation constraints? Even then, it is far easier and better manageable to realize a custom wrapper RFC within the ERP level that does satisfy the pattern + constraints, than apply the manual Gateway Model code crafting. Spoken from own experiences…

Read content of uploaded file within ItemAdding method

In an application it is required to validate the file content before allowing the upload into a document library. SharePoint 2010 enables this via the ItemAdding synchronous SPItemEventReceiver method. Problem is however that you cannot access the uploaded file through the SPItemEventProperties.ListItem object. The item is at runtime of ItemAdding not yet created in and thus not available via the list. Via blogpost Getting file content from the ItemAdding method of SPItemEventReceiver I found a code-snippet how to access the file. Last remaining issue was that I received empty result upon reading from the filestream. Debugging I discovered that the Stream position was set to the end of file. Which is logical since the file has been read in order to save it to the content database. By resetting the position I can read the file contents, validate it, and cancel the upload in case of invalid content

public override void ItemAdding(SPItemEventProperties properties)
{
  string searchForFileName = Path.GetFileName(properties.BeforeUrl);
  HttpFileCollection collection = _context.Request.Files;
  for (int i = 0; i < collection.Count; i++)
  {
    HttpPostedFile postedFile = collection[i];
    if (searchForFileName.Equals(
      Path.GetFileName(postedFile.FileName), StringComparison.OrdinalIgnoreCase))
    {
      Stream fileStream = postedFile.InputStream;
      fileStream.Position = 0;
      byte[] fileContents = new byte[postedFile.ContentLength];
      fileStream.Read(fileContents, 0, postedFile.ContentLength);
      ...