BusinessDataItemBuilder not usable on publishing site

SharePoint BCS out-of-the-box delivers a number of useful webparts to render external data. The BusinessData webparts consistently apply the architecture pattern of data provider and data consumer. A provider-consumer example is that of the BusinessDataItemDetails webpart to render an external data item, connected to a BusinessDataItemBuilder webpart that provides the external data item. The latter applies the 'ID' querystring parameter in the page URL to determine which entity to retrieve from the external administration. A standard example where the 2 webparts are used in this combination are the BCS profile pages.

It appears a sensible approach to use the same webparts combination for rendering an external entity item on a publishing page. However, when opening the page with the 'ID' querystring parameter referencing a BCSidentity:

http://tnvduetenterprise/subsite/Pages/TestSAP.aspx?ID=0aab

SharePoint throws the following error:

PortalSiteMapProvider was unable to fetch current node, request URL: /subsite/Pages/TestSAP.aspx, message: Value cannot be null. Parameter name: key, stack trace: at System.Web.SiteMapNode..ctor(SiteMapProvider provider, String key, String url, String title, String description, IList roles, NameValueCollection attributes, NameValueCollection explicitResourceKeys, String implicitResourceKey) at System.Web.SiteMapNode..ctor(SiteMapProvider provider, String key, String url, String title, String description) at Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapNode..ctor(PortalSiteMapProvider provider, PortalWebSiteMapNode webNode, String key, NodeTypes type, String url, String title, String description) at Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider.get_CurrentNode()

The cause is that the masterpage of publishing site includes PortalSiteMapProvider and PortalSiteMapDataSource. And the latter also reacts on the presence of the 'ID' querystring parameter, but expects a page identification as value instead of the BCS identity.

Justification of the Duet Enterprise XML-Funneling pattern

XML-Funneling pattern as answer for dealing with complex SAP data structures

In the blogpost ‘Architecture Pattern XML-Funneling of complex SAP structures for Duet Enterprise interoperability’, I introduced the XML-Funneling pattern as pragmatic approach to handle the Duet Enterprise limitation regarding complex SAP data structures. This Duet Enterprise limitation has 2 origins:

• The SAP NetWeaver Gateway generator tools are not capable of preservering hierarchical structure; their simple execution model is to flatten the SAP structure in the Gateway DataModel.
• The SharePoint BCS UI-controls are not capable of rendering hierarchical structure; their default rendering model is list/row based.

An often-heard misunderstanding is that Duet Enterprise itself, via the foundational layers SAP NetWeaver Gateway and SharePoint BCS, cannot be used to exchange complex data structures between SAP and SharePoint. This is not true. Gateway can expose complex data structures, but currently then requires you to model the interface and data signatures in ESR Builder, and next manually develop the Gateway Mappers. Although in FP1 less cumbersone as in Duet Enterprise 1.0, it is by far not the preferred approach to expose SAP building blocks as Gateway services. SharePoint BCS as integration layer can consume complex external data structures, but the rendering via standard controls is limited to the root level. In case the child levels also must be visualized, you need to develop a custom view that interoperates with the BCS API. This is doable, although a painpoint is that the SharePoint BCS API is weakly typed, and therefore programming against the API is laborsome and error prone.

I invented the XML-Funneling approach to simplify the development process at the SAP side. By mapping the SAP hierarchical structure to an XML string, the Gateway generator tools can be applied: a string is namely a ‘simple’ type.
On the SharePoint side the XML-Funneling approach does not really make developer life easier. A marked disadvantage is that it requires custom development to deserialize the received XML-structure into a strongtyped data object, before you can next use and visualize the received complex SAP data structure in a SharePoint view.

Trade-off decision whether or not to apply XML-Funneling

A trade-off decision is then whether in the total SAP + SharePoint system architecture and development effort, this front-end side / .NET disadvantage outweights the advantage on SAP side? Is it sensible to abandon the XML-Funneling approach in case of complex SAP data structure? My advice is to still apply the XML-Funneling pattern. Justification herefore are the following considerations.

Starting-points

• SharePoint is the target front-end platform.
• Favour out-of-the-box SharePoint above custom development.
• Application of SharePoint BCS; i) for the connectivity handling to external data administrations, ii) for the out-of-the-box UI controls to render external data, iii) and for integration within SharePoint Enterprise Search and FAST.
• Complex data structure is common within SAP business packages

Are there sensible alternatives for XML-Funneling approach?

WCF + DataContracts as alternative for Duet Enterprise to consume SAP data
First, Duet Enterprise is itself a WCF variant: the SAP data is consumed in SharePoint BCS as WCF service endpoint.
Second, on SharePoint side BCS is the limitating factor wrt complex structures, not Duet Enterprise. What if you bypass BCS and directly consume the Gateway Services as WCF endpoints? The result is that you not only loose the added interoperability value of Duet Enterprise: Single Sign-On, Authorization, Logging and Monitoring. But in addition also all of the native values of SharePoint BCS: standard UI-controls to visualize data (not all exposed SAP data is of hierarchical / complex structure), exposal to offline usage, use in SharePoint Search or FAST context.
Conclusion: this alternative is not sensible; it does not comply with the starting-points.

Expose complex SAP data structures via Duet Enterprise into SharePoint BCS
SharePoint BCS is a generic integration layer, usable for arbitrary external administrations: SAP, Siebel, Oracle, SQL-databases, ... This transparant interoperability capability is supported through a script-like, weakly typed BCS programming model. Whenever you can operate with BCS imported external data through any of the available standard UI controls, this doesn’t impose a problem. The controls abstract the weakly typed BCS programming model. But the standard controls are not able to visualize complex external data structures. For them also in this alternative you are required to custom build a view. Due the weakly typed programming model of the BCS API this is laborsome and error-prone.
Conclusion: this alternative is on SharePoint side an hardly better, programmer-friendly approach for consuming complex BCS consumed data; merely different. At SAP side, the consequence is that you cannot use the Gateway Generator tools/pipeline.

Expose complex SAP data structures as multiple associated simple structures
SharePoint BCS supports the concept of associations between two external content types. As parent-child is a relation, you can use this concept to expose complex SAP data structures through Duet Enterprise into SharePoint BCS.
BCS associations certainly has value, e.g. for master-child data model and UI-formats. But it also suffers from limitations that make it less applicable in other scenarios. For one: the net effect is that to retrieve a single complex SAP structure, multiple invocations from SharePoint to SAP must be done to retrieve the entire structure. And the responsibility for retrieving the entire structure is placed on the front-end side; instead of the data provider side. Another, the standard SharePoint UI controls all operate on a single BCS entity. If in the User Interface Design the ‘child’ data is directly mingled with(in) the ‘parent’ data, the standard controls cannot be used.
A Duet Enterprise design time issue is that the SAP Duet Enterprise generators are focussed on a single Gateway datamodel, and have no support for defining associations between 2 datamodels. Usage of BCS associations requires you to afterwards modify the BDC Model that is generated by BDC Publisher to add the associations. Either via SharePoint Designer, but in my experience you will more often than not need to resort to manual modification of the BDC Model. Which is time consuming, and error prone.
Conclusion: associations suffers from limitations and software architecture + performance drawbacks.

Final thought

Given that you want to benefit from the interoperability values of Duet Enterprise, and that within the exposed SAP business package there are entities with complex structure; XML-Funneling is a feasible approach. Consistent application of the pattern results in recognizable code setups for multiple Duet Enterprise scenarios, on SAP and SharePoint side. And in our experience, it is also possible to develop reusable building blocks for SAP and SharePoint to ease in the application of the pattern.

Execution flow of Duet Enterprise single sign-on

One of the much appreciated Duet Enterprise "plumping" capabilities is single sign-on (SSO) from SharePoint farm into the SAP landscape. This capability is achieved by reusing available capabilities in both SharePoint 2010 and SAP NetWeaver platforms. For SharePoint this is BCS WCF Connector, Secure Token Service application and SAML2 support; at SAP side the existing table for external users 'VUSREXTID' and also SAML2 support.

How does Duet Enterprise authentication and single sign-on work out at runtime moment? The end-user starts at SharePoint front-end, thus authenticated as SharePoint user. The SharePoint authentication can be via any authentication provider. However, if you also intend to use the Duet Enterprise Roles Synchronization capability, this requires Claims-Based authentication. Upon invoking a call via SharePoint BCS through Duet Enterprise connnectivity handling to the SAP NetWeaver Gateway system, Duet Enterprise maps the authenticated SharePoint account onto a SAP accounts. The staps in this user mapping are:

1. The SharePoint authenticated user identity is sent to Microsoft Business Connectivity Services Windows Communication Foundation connector.
2. The BCS WCF connector invokes the SharePoint Security Token Service to retrieve the user token for this SharePoint user identity.
3. The SharePoint Security Token Service returns a token that identifies the SharePoint user.
4. Duet Enterprise SSO handling sends the received token to SAP NetWeaver Gateway, packaged within a SOAP request
5. SAP NetWeaver Gateway uses the received token to identify the external user, and locate in the user mapping table (VUSREXTID) the associated SAP user with this credential
6. The SAP user account that is mapped to the SharePoint user is returned to SAP NetWeaver Gateway.
7. SAP NetWeaver Gateway uses the SAP user account to request access to information in the SAP backend.
8. If the user is authorized to access the information, the requested information is sent to SAP NetWeaver Gateway.
9. SAP NetWeaver Gateway sends the requested information to the Microsoft Business Connectivity Services WCF connector as a SOAP response.
10. The Microsoft Business Connectivity Services connector passes the information to the SharePoint front-end side; e.g. to display via an External List or any of the other BCS Business Data webparts.

For this SSO pipeline to actually work, requires and strongly depends on correct execution of Duet Enterprise configuration of SharePoint and SAP mutual authentication. SAP Gateway must be configured for SAML2 usage, a trust relationship must be configured between SharePoint and SAP, SSL certificates have to be exchanged between SAP and SharePoint. But this is only a one-time, at configuration step. Afterwards you can rely and profit on Duet Enterprise Single Sign-On capability, without need to maintain the supporting code.

Duet Enterprise interoperability versus native WCF service integration

Recently I was at SAP Walldorf exchanging Duet Enterprise experiences with other partners and customers. One of the Microsoft solution architects present of a Finnish IT-consultancy raised the question when you should utilize Duet Enterprise above the plain usage of Microsoft WCF. This same question has (and is from time to time again) also been asked by the Microsoft architects of the organization where I’m involved in 2 SAP / SharePoint integration projects; both utilizing Duet Enterprise.

The question boils down to understand where is the return of investments. When does it make sense to investigate in purchasing and learning Duet Enterprise, instead of connecting via webservices to earlier build custom connectivity? And to be honest, from a Microsoft developer’s perspective the latter is more appealing, because technically more challenging and also familiair approach plus technology.

The valid answer of Holger Bruchelt on this question was that it strongly depends on the size of your SAP / SharePoint interoperability case. If just for a single application context, with no more scenarios in sight on a medium term, it is very hard to make the business case for purchasing, training and implementing Duet Enterprise. Most likely you can better make a dedicated 1-on-1 integration via the known way of Microsoft WCF, and connect direct to SAP webservices (BAPI webservices, or Enterprise Services).
However, when SAP / SharePoint is a strategic issue for the organization; with multiple applications of it on the horizon; Duet Enterprise is a very valid option to consider. The execution of multiple projects makes it profitable to invest the license and implementation costs for the added value of Duet Enterprise as standards-based SAP/SharePoint interoperability foundation.

A derived question is how Duet Enterprise interoperability compares against native service integration. This question is more complex to answer.
Duet Enterprise is (a consumer of) Service Integration. On SharePoint side it relies on the strengths of SharePoint Business Connectivity Services (BCS) to interoperate with the SAP landscape. BCS supports different connectivity-models to external systems and data repositories, including via webservices / = Service Integration. Duet Enterprise is an example of this: it invokes SAP NetWeaver Gateway webservices through means of BCS. For SharePoint developers, BCS can thus be seen as an abstraction of the Service Integration layer. On top of this abstraction, the SharePoint platform includes diverse BCS capabilities to aid the developer in composing the interaction with external systems: External List, Business Data webparts. In addition, the BCS API can be programmed for custom construction.

There are noticable differences between native service orientation and service integration using Duet Enterprise:

• Duet Enterprise enables the entire system chain from provider (SAP Business Suite) to consumer (Microsoft SharePoint); on design time and on runtime moments. At design time, Duet Enterprise can be a significant accelerator to expose SAP data into SharePoint context. This is achieved through the Duet Enterprise design tools that are integrated in both SAP and Microsoft landscape. And by using SharePoint BCS as connectivity layer, the step to develop and provision a separate middleware layer is excluded. For the runtime handling, Duet Enterprise provides out-of-the-box capabilities for interoperability aspects: data retrieval, display and management, Single Sign-On, autorisations, end-to-end monitoring.
• Duet Enterprise is a standard SAP / SharePoint interoperability foundation, designed and maintained by SAP and Microsoft together. The 2 software vendors execute a clear roadmap to further develop their shared product. In this roadmap, the product is aligned with the new developments in SharePoint and in SAP technology landscapes. Further, via the Unite program, the Duet Enterprise ecosystem expands on the basic deliverable with additional content packages. The expectation is that the ecosystem will steadily grow, and also deliver technical and functional components that end-organizations can deploy for custom composition and build of company-specific scenario’s.
• Duet Enterprise lifts on SAP NetWeaver Gateway; Gateway is now a central component in the SAP architecture to expose SAP data and processes to external clients: SharePoint [via Duet Enterprise], but also (and with more emphasis real soon) to Mobile [via Sybase Unwired Platform]. Relying on Gateway enables organizations to expose SAP data + processes to multiple channels, with reuse of the same Gateway services [thus reduce on development and maintenance effort + costs].

Skillset + Toolbox for a SAP / Microsoft interoperability architect

In the role of SAP /Microsoft interoperability Solution Architect, you are typically also the middleman between the SAP and Microsoft counterparts. You work with the Microsoft architect and developers, also with the SAP solution architect, SAP Business Suite business analyst and ABAP developers, as well in the combination. In my experience it is necessary to have a deep enough insight in the architecture and technology of both landscapes, for the optimal and valuable SAP / Microsoft advice in a given customer context. So although not required to program ABAP and/or C# yourself, you need to understand their role and positioning. And that of BizTalk, SAP PI, Microsoft WCF, Enterprise Services Repository (ESR), ABAP Workbench, SharePoint Designer, to name a few.

Personally I also prefer an own workspace in which I can analyse, inspect and work on both sides of the SAP and Microsoft interoperability stacks. My SAP/MS toolbox therefore contains a.o. the following items:

SAP side

• SAP GUI (and thus the whole ABAP development + configuration stack)
• SAP authorizations for backend; inspect BOR, RFC, workflow, application and error logs
• SAP authorizations for Gateway; analyse Logs, SIMGH, …
• S-userid
• (SAP developer key)

Microsoft / SharePoint

• SharePoint UI operated in browser
• SharePoint Designer
• Visual Studio >= 2008
• InfoPath
• SiteAdministrator role on the SharePoint site collection
• Authorizations on the SharePoint farm for BCS, Secure Store
• ULS Log Viewer

SAP / Microsoft interoperability; knowledge of…

• Duet Enterprise
• .NET Connector (NCo)
• Sitrion
• Winshuttle
• BizTalk WCF LOB Adapter
• ES Explorer

Useful others...

• Fiddler
• IE Developer tools
• FireFox + FireBug
• Wireshark
• .NET Reflector
• soapUI
• HttpWatch
• WinDiff

Typical content of my daily workplace; blended SAP and Microsoft tools – Visual Studio, SharePoint Designer, SharePoint UI opened in browser, SAP GUI, ULS LogViewer, ...

Architecture Pattern XML-Funneling of complex SAP structures for Duet Enterprise interoperability

This blog is earlier published on SAP Community Network Blogs

Via Duet Enterprise it is possible to expose SAP data to a SharePoint UI in short timeframes (less than an hour). This agility of SAP/SharePoint interoperability is made possible by combining existing strengths of SAP NetWeaver Gateway (Design Time + Runtime support) and SharePoint BCS. To utilize these strengths in optimum way, your scenario must obey to the following list of prerequisites:

• Usable RFCs or BORs are available in the SAP backend
• Flatten SAP data structure
• SharePoint ExternalList UI metaphore is good enough / adequat for the User Experience (note that you can alter it considerable via a custom XSLT stylesheet)

Duet Enterprise leans on the power of SAP NetWeaver Gateway 2.0 to publish SAP data from backend to external UI channels. Gateway design time tools enable you to quickly generate a Gateway DataModel, next expose this via a generated SAP webservice, and import the SAP data into SharePoint BCS.
Althought Gateway itself allows exchange of complex SAP data structures, the GW tools current do not support that. If you need to expose a complex SAP structure through Gateway, you’re on your own with handcrafting the Backend Operation Proxy, GSDO mappers, Gateway Service Proxy. This is not very attractive; it is laborsome, errorprone, and for every change in the data or service signature you must repeat some or even the whole effort.

An alternative is an approach in which you can still use and rely on the powers of the GW Design Time tools to generate the mappers. As said, the GW Design Tools demand the mapped SAP data is in a flattened structure. This conflicts when your source SAP structure is complex, with nested tables. But you can align them by introducing an intermediair component on SAP side; which semi-flattens the complex structure while maintaining in metadata the complex structure. Outline of this architecture pattern is that you build a custom RFC on the SAP backend that retrieves the complex structure by invoking the existing RFC/BOR, or even invoke that RFC/BOR in a loop or invoke multiple RFCs and/or BORs. The received SAP data is collected into a runtime XML document instance, in which you (re)build + maintain the complex data(object) structure. The custom RFC outputs as single string parameter the OuterXML of the runtime XML document. Such a single string parameter can without constraints be handled by the GW Design Tools to generate the entire Gateway + Duet Enterprise data exchange components: GW Model, GW Service, BDC Model. At runtime the complex structure is derived at SAP business side, funneled into a ‘flattened’ string-representation, and exchanged via Gateway + Duet Enterprise to and from SharePoint UI. Within SharePoint context the received string-representation is interpretated as serialized format of a complex data transfer object (DTO). Either via XSLT-transformation in the standard BCS controls, or via a custom UI-control, the complex structure is rendered on a SharePoint page.

The Xml-funneling pattern can be applied at 2 levels. You can use it to flatten the collection of child SAP entities of a parent SAP entity (returned as nested tables in the RFC output). And you can use it to flatten an entire functional SAP entity; which may be runtime composed by issuing multiple RFC calls.

Impossible to use custom implementation of public BCS interface IEntityInstanceProvider

SharePoint Business Connectivity Services contains out-of-the-box webparts to visualize external business data. All the standard BCS business data renderers utilize the pattern that data selection is delegated to a data provider control. The 2 controls are associated via consumer/provider connectionpoints. BusinessDataDetailsWebPart is one of them. Its standard usage is for BCS profile pages. However, usage of this webpart on itself is not necessarily limited to that context alone. The webpart is selectable in the webpart gallery, and can be added to arbitrair SharePoint webpart page. The challenge is how to connect it to an external BCS entity. The trivial approach is to do it similar as on profile pages; feed it via the provider BusinessDataItemBuilder. The working of BusinessDataItemBuilder depends on an ‘ID’ querystring parameter. However, when that parameter is included in the url of a page with the 2 BCS webparts on it, SharePoint faults with an error message:

System.NullReferenceException: Object reference not set to an instance of an object.
  at Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapDataSource.OnInit(EventArgs e)

This occurs because PortalSiteMapDataSource also reacts on the presence of ‘ID’ querystring parameter, but assigns another and in this context incorrect meaning to the value. When the ‘ID’ querystring parameter is renamed, the error is prevented. However, then the standard BusinessDataItemBuilder no longer reacts on it.

A proper solution seemed to build a custom provider that relies on another named querystring parameter to retrieve the BCS entity item. The BCS architecture has the promise to support this: it heavily decouples via interfaces from the specific BCS implementation classes. But this appears to be a false promise. Microsoft already points you to that via the warning accompanying the public (!!) interface IEntityInstanceProvider

This class and its members are reserved for internal use and are not intended to be used in your code.

Still, since the interface is public; and also the connectionpoints are public; it sounded safe to expect it must work. But when I added both my own IEntityInstanceProvider implemenation class and a BusinessDataDetailsWebPart on a page; it was not possible to connect them; not in the GUI, and also not explicit in custom code. Via Reflector I reverse engineered the cause, to ultimately end up with the following:

Microsoft.SharePoint.WebPartPages.WebPartPageUserException was unhandled
Message=The connectionpoint BDWP Item on g_1e1e7d15_652d_44cd_bc7d_9026382ff33b is disabled.
Source=Microsoft.SharePoint
StackTrace:
at Microsoft.SharePoint.WebPartPages.SPWebPartManager.CanSPConnectWebPartsCore(WebPart provider, ProviderConnectionPoint providerConnectionPoint, WebPart consumer, ConsumerConnectionPoint consumerConnectionPoint, WebPartTransformer transformer, Boolean throwOnError)

and digging deeper:

if (!providerConnectionPoint.GetEnabled(connectionAgentByInterfaceName))
{
    if (throwOnError)
    {
        throw new WebPartPageUserException(WebPartPageResource.GetString("SPWebPartConnection_DisabledConnectionPoint", new object[] { providerConnectionPoint.ID, provider.ID }));
    }
    return false;
}

public override bool GetEnabled(Control control)
{
    bool flag = false;
    BusinessDataWebPart part = control as BusinessDataWebPart;
    if (part != null)
    {
        try
        {
            return part.CanConnect(true);
        }
        catch
        {
            return false;
        }
    }
    if (control is BusinessDataItemBuilder)
    {
        flag = true;
    }
    return flag;
}

Thus: although at the external front decoupled via interface IEntityInstanceProvider, internally BusinessDataDetailsWebPart demands it's provider to be either a BusinessDataWebPart or BusinessDataItemBuilder webpart!! So much for interface decoupling.
Confine to this internal constraint by inheriting either of these classes is also not possible: BusinessDataItemBuilder is sealed, and BusinessDataWebPart is public abstract, but with 4 internal abstract methods.

BusinessDataDetailsWebPart also allows to explicit connect it in code with a BCS entity. Less elegant as via the connectionpoint, but workable. The standard BusinessDataDetailsWebPart can then still be used to render the external data item; no need to custom build an own one (or ‘copy’).

BusinessDataDetailsWebPart busDetailsWp = wp as BusinessDataDetailsWebPart;
if (busDetailsWp.BdcEntity != null && busDetailsWp.BdcEntity.Equals(entity))
{
    string entityInstanceId = ((IEntityInstanceProvider)this).GetEntityInstanceId();
    busDetailsWp.ItemID = entityInstanceId;
}