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].

What is this about?

Everybody knows the importance of MS-Excel, How will it be if we can populate SAP data into the Excel client automatically. Sometimes we feel good if we are able to view a list of data from SAP and update the data from Excel itself. Yes I have taken this case to demonstrate as of how we can populate the data from SAP system into MS Excel.

How are we going to achieve this?

With the Duet Enterprise and Office 2010 suite, now it is possible to build solution that would let you interact with your SAP backend directly from your MS Office products. This solution is build using VSTO packages via Visual Studio. Since we are developing on top of Duet Enterprise, the developer/partner need not worry about security, localization etc., because all these are taken care by Duet Enterprise.

 

What are the tools required to build this solution?

  1. Visual Studio 2010
  2. BCS Artifact Generator
  3. BCS Solution Packaging Tool

 

Use case to explain this article:

Say, Martin, who is using Duet Enterprise is responsible for some CRM accounts. He wants to see the list of CRM activities that are associated with the accounts and responsible contacts. After getting the list of activities, Martin can group those activities as per the status and send that data to his higher official with a chart in excel.

I am demoing this scenario using CRM Activity that is delivered as part of Feature Pack 1 for Duet Enterprise 1.0.

Pre-Requisite for this demo is Feature Pack 1 for DuetEnterprise 1.0.

To acheive this scenario follow the steps mentioned below:

  1. Create a Base solution.
  2. Download the sample code from MSDN.
  3. Edit the code as shown below.

Creation and deployment of base BCS Solution:

    1. Assign the required permissions to the BCS models (CRM Activity) from your central administration as shown below. Please select
           the sub-set of user as per your requirement. Here I would allow all the authenticated users to have access to this solution.

Goto Central admin -> ManageService Application->BusinessData Connectivity Service.


Metadata_Permissions.png

2.  Export the models with all the permission and proxy information as shown below:
Export_BDC.png

3. We’ll use the BCS Artifact tool to generate the artifacts for the downloaded model.

a.      Import the model in the artifact tool and click next:

          Artifact_Generator.png

b.     Click on Generate Artifacts:

          Artifact_Generator_Finish.png

4. Once the artifacts are generated open the BCS Packaging tool:
                    BCS_Packaging_Tool.png

        1. Provide a Solution Name and select the solution type as given in the screenshot above.
        2. Provide the path of the files which are generated in the previous tool(BCS artifact tool)
        3. Provide an output folder for this BCS Packing tool.
        4. Now click on Package which will generate VSTO package.
        5. Once the VSTO package is created successfully, click on Deploy which will deploy the base solution in the current machine.

 

5. Now you may download the code from this MSDN article:
6. After downloading the code extract it to a folder and open the SalesOrderAddIn visual studio solution.

7. Open the SalesDataManager Class and make the changes as shown below as per the CRM activity BCS Model:

Existing code in the Solution:

private const string SalesOrderHeader = "SalesOrderHeader";
private const string SalesOrderLine = "SalesOrderLine";
private const string SalesOrderHeaderFinderName = "SalesOrderHeaderReadList";
private const string SalesOrderSpecificFinderName = "SalesOrderHeaderReadItem";
private const string SalesOrderLineSpecificFinderName = "SalesOrderDetailReadItem";
private const string SalesOrderLobSystemName = "AdventureWorks";
private const string SalesOrderLobSystemInstanceName = "AdventureWorks";
private const string SalesOrderAssociationName = "SalesOrderDetailNavigateAssociation";
private const string SalesOrderHeaderNamespace = "http://intranet.contoso.com";
private const string DependentDataSolutionID = "SalesOrderSolution";
private const string DependentDataSolutionVersion = "1.0.0.0";

 

Changed Code will look like this:

private const string SalesOrderHeader = "Activity";
private const string SalesOrderLine = "empty";
private const string SalesOrderHeaderFinderName = "FindActivityByElements";
private const string SalesOrderSpecificFinderName = "ReadActivityById";
//private const string SalesOrderLineSpecificFinderName = "SalesOrderDetailReadItem";
private const string SalesOrderLobSystemName = "Activity";
private const string SalesOrderLobSystemInstanceName = "Activity";
private const string SalesOrderHeaderNamespace = "SAP.Office.DuetEnterprise.CRMAccount";
private const string DependentDataSolutionID = " Demo";
private const string DependentDataSolutionVersion = "1.0.0.0";
private const string UpdateMethodName = "UpdateActivity";

 

Add a button on the ribbon and on the button click event add a code like this:

private void btnGetData_Click(object sender, RibbonControlEventArgs e)
{
     if (lobData != null)
            {   
               salesOrderTable = lobData.GetSalesOrderHeaderItems();
              
               if (salesOrderTable != null)
              
               {
      
                BindSalesOrderLines(salesOrderTable);

               }
            }
}

 

public DataTable GetSalesOrderHeaderItems()
{
            if ((entitySalesOrderHeader != null)&& (lobInstance != null))
               {
               // Get the default filters
               IFilterCollection filters = entitySalesOrderHeader.GetMethodInstance(
                                             CRMAccountHeaderFinderName,MethodInstanceType.Finder).GetFilters();
              // Execute the FindFiltered method online.              
               IEntityInstanceEnumerator enumerator =
entitySalesOrderHeader.FindFiltered(
                                                       filters,SalesOrderHeaderFinderName,lobInstance,OperationMode.Online);
               SalesOrderTable = null;
               SalesOrderTable = catalog.Helper.CreateDataTable(enumerator);
               // Clear the dirty rows, if any
               changedSalesLineRows.Clear();
               //SalesOrderLineTable = catalog.Helper.CreateDataTable(enumerator,true);              
               if (SalesOrderTable != null)
               {
               SalesOrderTable.RowChanged += new DataRowChangeEventHandler(SalesOrderTable_RowChanged);
                               }
               changedSalesLineRows = SalesOrderTable.Clone();
          }
     return SalesOrderTable;
}

 

You may comment out/delete the code where ever it is not required. Also rename the Class Vriables/members as required.

If you want to update the item from Excel:

1.  Listen to the datatable rowchanged event and add the changed rows in a new datatable.

///<summary

/// Track the row which has been edited by end user

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

void SalesOrderTable_RowChanged(object sender, DataRowChangeEventArgs e)

{
         changedSalesLineRows.ImportRow(e.Row);

}

  2. Add a button on the ribbon and on the click even on the button add this code:

foreach (DataRow changedrow in changedSalesLineRows.Rows)
{
try
    { 
    itemIdentity = new Identity(changedrow[EntityIdentifier].ToString());
     salesOrderinstance = entitySalesOrderHeader.FindSpecific(itemIdentity,
     CRMAccountSpecificFinderName, lobInstance, OperationMode.Online);
         foreach (DataColumn col in changedSalesLineRows.Columns)
           {
try 
    {
     salesOrderinstance[col.ColumnName] = changedrow[col.ColumnName];
           }
                catch (System.Exception ex)
          {
           }
}
           salesOrderinstance.Update();
     System.Windows.Forms.MessageBox.Show("Updated Successfully!!");
     }catch(System.Exception ex)
     {  
     }
}

 

So now the user can get SAP data in Excel and update the same. This sample code can be enhanced further with custom action pane as per the customer requirement.A good starting point to refer could be the MSDNarticle.  Most of the code pieces used in this demo have been taken from this MSDN article which talk about Excel add-ins. Also it explains about each method as to what it does.

This webinar was held on March 27, 2012. The webinar presented how Omnicell, a leading provider of comprehensive, technologically advanced solutions, is using Duet Enterprise to meet the need for centralized and easily accessible customer data while enabling employees to increase efficiency and results.

You can view the recorded webinar here. (registration is required)

The presentation slides can be viewed here. (PDF)

 

 

 

We all know the importance of data in today’s world and we also know most of the data that we come across in our day-to-day life is not very organized. How many time have you received an email that contains an important MS Word document , a MS Excel sheet with important statistics or a Power Point presentation that you need to store in your ERP \ CRM system.

With the Duet Enterprise and Office 2010 suite, now it is possible to build solution that would let you interact with you SAP backend directly from your MS Office products.

For example, I would take the following use case:

Jack receives an email from his colleague which contains an important MS Word document attachment. After reading through the document, Jack feels the need to upload this document against one of the Account that he is handling. We would provide Jack with a way to upload the document directly using

MS Word instead of going to his Duet Enterprise enabled SharePoint site or to his SAP backend.

The solution would be a two-step process. The first step would be deploying the BCS solution (vsto packages) onto the client machine. The second step would be to create and deploy the plugin for MS Word that would read the data to and from the backend.

This demo is based on the CRM Account and Attachment entities that are delivered as part of Duet Enterprise Service Pack 1.

 

Pre-Requisite for this demo is Duet Enterprise Feature Pack 1.0 Service Pack 1.0.

  

Step1: Creation and deployment of BCS Solution

Assign the required permissions to the BCS models (CRM Account & Account Attachment) from your central administration as shown below. Please select the sub-set of user as per your requirement.

Here I would allow all the authenticated users to have access to this solution.

Figure 1

2.      

Export the models with all the permission and proxy information as shown below.

Figure 2

3.      

 We’ll use the BCS Artifact tool to generate the artifacts for both the downloaded models. We’ll use the output from step 4 to create the vsto packages using the BCS Solution Packaging Tool. At the end of this step you will have the vsto package which needs to be deployed on the client machine.

Step2: Creating the MS Word Add-on

With Office 2010 and onward the BCS client is integrated in the Office suite, so as a pre-requisite we need Office 2010 to be present on the client machine.

The code for this sample project can be downloaded from here.

The next step would be to create MS Word 2010 Add-in project as shown below.

Figure 3

(PS: We are using .NET Framework 3.5)

Please add the references as shown in the screenshot below.

Figure 4

 

Once this is done, we would add a user control to the solution. This would be the pane that the user would see with all the controls in it.

 

 

Once this is done, we would add a ribbon control. From here we can pop up or hide our user control.

 

 

Now, that we have all the controls to start off with we will proceed with the coding part.

Firstly, let’s add the user control that we add to the collection of TaskPane in MS Word. For this we would add the following code in the “ThisAddIn.cs” file that was created from the template.

      

        private GetDataUserControl customUserControl;

        public Microsoft.Office.Tools.CustomTaskPane customTaskPane;

        private Microsoft.Office.Tools.CustomTaskPaneCollection

     customTaskPaneCollection;

 

       

 

        private void ThisAddIn_Startup(object sender, System.EventArgs e)

        {

            Load_Pane_Control();

       

        }

 

        private void Load_Pane_Control()

        {

            customUserControl = new GetDataUserControl();

            customTaskPaneCollection =

     new Microsoft.Office.Tools.CustomTaskPaneCollection(

                this.ItemProvider, this.HostContext,

     "Duet Enterprise Document Uploader", this,

     "Duet Enterprise Document Uploader");

            customTaskPane = customTaskPaneCollection.Add(

     customUserControl, "Duet Enterprise Document Uploader");

            customTaskPane.Width = 310;

            customTaskPane.Visible = false;

        }

 

Let’s now prepare to show and hide the control pane as per the user’s wish. For this I am adding a button on the ribbon control. On click of this button we will hide or show our task pane.

 

 

Once this is in place we will write the following code lines in the Ribbon1.cs file.

private void GetDataButton_Click(object sender, RibbonControlEventArgs e)

        {           

            //Open the task pane

            if (Globals.ThisAddIn.customTaskPane.Visible)

                Globals.ThisAddIn.customTaskPane.Visible = false;

            else

            {

                Globals.ThisAddIn.customTaskPane.Visible = true;                             

            }           

        }

 

After this we would check whether the required solutions (vsto packages) are installed or not. The following code line will give the installed version of the solution

                expectedDataSolutionVersion =
                SolutionRegistry.GetCurrentSolutionVersion(
                Constants.DependentDataSolutionID[Index]);

 

Once you find that all the required solutions are present and of the correct version.  We then design our task pane. I would go ahead and add a drop down to show the available object (e.g. CRM Account, CRM Activities etc. In the code attached we are only showcasing CRM Accounts but it can be extended very easily for the other scenarios as well following the same steps.)

Next, we will load all the account that the user has access to.

public DataTable GetAllEntry(BCSDetails bcsObj)
        {
            if ((Entity != null)
                && (lobInstance != null))
            {
                // Get the default filters
                IFilterCollection filters =
                    Entity.GetMethodInstance(
                    bcsObj.FinderName,
                    MethodInstanceType.Finder).GetFilters();
 
                // Execute the FindFiltered method online.
                IEntityInstanceEnumerator enumerator =
                    Entity.FindFiltered(
                    filters,
                    bcsObj.FinderName,
                    lobInstance,
                    OperationMode.Online);
 
                TableData = null;
                TableData = catalog.Helper.CreateDataTable(enumerator);
            }
 
            return TableData;
        }
 

 

Once the user has made all the changes and saves the document,  he can upload the document by clicking on the upload attachment button. The upload function would invoke a create attachment call internally as shown below.

public void UploadAttachment(BCSDetails bcsObj,string AccountId, string Name,string Path)

 {

Identity id;

Globals.ThisAddIn.Application.ActiveDocument.Close( Type.Missing,Type.Missing,Type.Missing);

    if ((Entity != null) && (lobInstance != null)){

      IFieldValueDictionary fieldValueDictionary =  Entity.GetCreatorView(bcsObj.creatorName).GetDefaultValues();

       fieldValueDictionary["AccountID"] = AccountId;

       fieldValueDictionary["DOCUMENTNAME"] =Name;

        fieldValueDictionary["DOCMENT_FILE_DESCRIPTION"] = "This a test upload";

        fieldValueDictionary["DOCUMENTFILETYPEDESCRIPTION"] = "application/msword";

       fieldValueDictionary["DOCUMENTFILENAME"] = Name.Replace(".docx", "") + "_"

+System.DateTime.Now.ToString().Replace("/", "").Replace(":", "").Replace(" ", "")

+ ".docx";

        fieldValueDictionary["DOCUMENTFILESIZE"] = FileLength(Path);

        fieldValueDictionary["BINARYCONTENT"] = ReadFile(Path);

        fieldValueDictionary["DOCUMENT_CREATED_AT"] = System.DateTime.Now.ToString();

         fieldValueDictionary["DOCUMENT_CREATED_BY"] = "UserName";

         fieldValueDictionary["DOCUMENT_LAST_CHANGED_AT"] = System.DateTime.Now.ToString();

          fieldValueDictionary["DOCUMENT_LAST_CHANGED_BY"] = "UserName";

  id = Entity.Create(fieldValueDictionary, lobInstance);

   }     

}

 

If you want to see all the pieces put together then you can download the code from here.

Following on the same lines we can extend the same example for other object like Contact,  Activities etc.  We can go a step forward and even integrate SAP data with other MS Office suite products like MS Excel, Outlook etc.

In future, I would try to publish pug-ins for other offices products.

A good starting point to refer could be the MSDN sites.

Most of the code pieces used in this demo have been taken from MSDN which talk about outlook add-ins.

Hope this provides a starting pointer for you all to start trying out other combinations.

 

Cheers,

Oyshik

Actions

Filter Blog

By author:
By date:
By tag: