SharePoint24x7 It's all about SharePoint.

13Jun/110

Get Ready for the SEA SharePoint Conference 2011

Posted by Joy

SEA SharePoint Conference 2011

SEA SharePoint Conference 2011

I just received the good news about the SEA SharePoint Conference 2011. Plans are underway for SEA SharePoint Conference and I'm very excited to go this year as well.

If you were there last year, I assume you have already decided to come this year and let me help you to book your calendar. Go ahead and book 08th – 09th November for the greatest SharePoint Conference in the region. If you didn't get the chance to be there last year, here you have got another great opportunity. In nutshell, SEA SharePoint Conference is the largest gathering focused for SharePoint Products & Technologies which runs over 02 days to provide great technical/ business sessions delivered by reputed speakers.

Come and join us to know more about what you can do with SharePoint.

Keep your eye on http://www.sharepointconference.asia/ for more details.

16Apr/110

Article – Business Connectivity Services (BCS) Part IV – Creating an External List

Posted by Joy

Last article demonstrated how to create an External Content Type (ECT) using SharePoint Designer 2010 and set various options such as Limit Filter and Wildcards.

In this section, we will use the same ECT we created earlier and combine it with External List concept to surface the Employee data coming from the LOB system.

There are multiple ways of creating an External List and connecting it with an existing ECT to surface data within SharePoint. I will discuss the 02 most popular mechanisms.

Creating an External List using SharePoint Designer 2010

This is the easiest way in which you can create an External List and connect it with an ECT at the same time. Let's create an External List using SharePoint Designer 2010.

Exercise 1 – Open an existing ECT

Let's open the NorthwindEmployees ECT we created last time.

  1. Fire-up SharePoint Designer 2010 by navigating to Start -> All Programs -> SharePoint -> Microsoft SharePoint Designer 2010.
  2. Click Open Site to bring up Open Site dialog box.
  3. Enter the URL of the SharePoint site we created the ECT for Site name: and click Open.
  4. Select the External Content Types from the Site Objects pane and wait until it retrieves all the existing External Content Types. After a few seconds, you will see the ECT we created last time.

    NorthwindEmployees ECT

    NorthwindEmployees ECT

  5. Click the NorthwindEmployees ECT from the right-hand side External Content Types pane to open the ECT and its properties.

    ECT Summary Page

    ECT Summary Page

Exercise 2 – Creating an External List using SharePoint Designer 2010

  1. Observe the ribbon and its options for the ECT.
  2. From the Lists & Forms group in the ribbon click Create Lists & Forms button.

    Ribbon for ECT

    Ribbon for ECT

  3. Create Lists & Forms for Northwind Employees dialog box will show up for you to configure the new External List. Provide a name for the new External List by filing List Name: field. I provided "Employees List" for this example.
  4. Optionally you can select Create InfoPath Forms check-box to generate InfoPath for all the list view pages (Read List.aspx, DispForm.aspx, etc…) instead of creating ASP.NET pages.

    Creating the External List

    Creating the External List

  5. Click OK to create the External List. After few seconds, you will see the section External Lists in the ECT summary page with the new External List we created.

    Creating the External List

    Creating the External List

Creating an External List using Web Browser

This way we can create the External List by using standard web browser application and using options in the SharePoint site.

  1. Open your web browser and navigate to the SharePoint site which we created the ECT.
  2. Select Site Actions -> More Options… to open Create dialog box.
  3. Select the External List from the List Templates section and click Create button.

    Creating the External List

    Creating the External List

  4. Provide a name for the External List by filling Name: field. I provided "Northwind Staff" for this exercise.

    Creating the External List

    Creating the External List

  5. In the Data source configuration section, click "Select External Content Type" button to browse all the ECTs available in the current site collection.

    Select the ECT

    Select the ECT

  6. Select the Northwind Employees ECT from the External Content Type Picker dialog box and click OK.

    Browse for the ECT

    Browse for the ECT

  7. Now you will see that Data source configuration is filled with the ECT information automatically and click Create button to create the External List.

    Creating the External List

    Creating the External List

View LOB data using the External List

Now that we have created an External List, or may be 02 if you tried both the options, it's time to look at the way it surfaces external data within SharePoint. It's very easy to do that.

  1. From the Quick Launch, select the name of the list you created.
  2. Try viewing individual Employee items, updating Employee records, deleting Employees records and inserting new Employee records using SharePoint External List.
External Data coming through the ECT & External List

External Data coming through the ECT & External List

Displaying an item with InfoPath Form

Displaying an item with InfoPath Form

Editing an item with InfoPath Form

Editing an item with InfoPath Form

Inserting a new item with InfoPath Form

Inserting a new item with InfoPath Form

14Mar/112

Article – Business Connectivity Services (BCS) Part III – Creating an External Content Type (ECT)

Posted by Joy

Here you get the long awaiting Part III of my BCS article series. Having discussed what BCS is, terminology and the solution types, it's time to create a simple solution. Let's create a simple solution which brings/ surfaces data coming from a SQL Server database within in a SharePoint site.

Scenario

Northwind has implemented an intranet portal using SharePoint Server 2010 and it is becoming very popular among the business users/ information workers. One of the business users has learnt about the powerful capabilities of SharePoint 2010 release in the area of integrating with legacy systems. He sends an official email to the IT department of Northwind inquiring about the possibility of integrating their LOB system with SharePoint so that they can manage their Contacts, Prospects, Leads, etc… efficiently using their intranet postal interface.

Solution

IT Manager has realized the importance of the request and now he needs to deliver the solution using BCS which is part of SharePoint 2010. IT Manager decided to develop a simple solution using SharePoint Designer 2010 to make it simple and fast for both business users and the developers.

I navigated to SQL Server Management Studio (Start -> All Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio) and expanded the Databases node. I further expanded the Northwind database and its Tables folder to see all the tables available.

As the 1st step, I decided to provide access to Employees data available in Northwind database using SharePoint 2010 site. I executed the following SQL statement to see all the data available in the Employee table:

Employees data in SQL Server

Employees data in SQL Server

Let's develop our 1st BCS solution to integrate Employees data into a SharePoint 2010 site.

Exercise 1 – Creating an External Content Type (ECT)

  1. Fire-up SharePoint Designer 2010 by navigating to Start -> All Programs -> SharePoint -> Microsoft SharePoint Designer 2010.
  2. Click Open Site to bring up Open Site dialog box.

    Opening your SharePoint site in SPD 2010

    Opening your SharePoint site in SPD 2010

  3. Enter the URL of the SharePoint site for Site name: and click Open.
  4. Select the External Content Types from the Site Objects pane and wait until it retrieves all the existing External Content Types.

    External Content Types in SharePoint

    External Content Types in SharePoint

  5. Select the right-side panel to activate the Ribbon menu options and click the External Content Type from the New group in the ribbon.

    Creating a new ECT

    Creating a new ECT

  6. You will get the New external content type tab/ page and that's where you will configure your External Content Type to communicate with the Northwind database.
  7. Let's first fill the details for the External Content Type Information section. Place cursor for the Name and provide a meaningful name for the External Content Type.

    External Content Type Information

    External Content Type Information

  8. Press Tab to navigate to next field and you will see Display Name is populated automatically based on the Name field. Place the cursor and change the Display Name if you wish. I gave a space in-between to read as "Northwind Employees" which is easy to read.
    External Content Type Information

    External Content Type Information

    External Content Type Information

    External Content Type Information

  9. Select an appropriate option for the Office Item Type. If you are planning to surface data within Office applications such as Outlook you need to specify the Office Item Type. If you have no plan to surface data inside Office applications you can keep the default value which is Generic List for the Office Item Type. You can use Appointment as the Office Item Type if the underlying data coming from the LOB system need be shown in the Calendar pane in Outlook. If you are brining data which can be shown in the Contacts pane in the Outlook such as Employees, Customers, Students, Dealers, etc… then you can use Contact as the Office Item Type.

    Select Office Item Type

    Select Office Item Type

  10. For this demo select the Contact as the Office Item Type.
  11. Next select whether you want to have the Offline Sync for external list Enabled or Disabled. For this demo, keep the default value which is Enabled.
  12. After finishing the defining External Content Type Information, your screen will look something similar to the following.

    Creating a new ECT

    Creating a new ECT

Exercise 2 – Defining External Content Type Operations

  1. Click the link Click here to discover external data sources and define operations link in the External Content Types Operations section to define the external system information and the operations.

    Defining External System Information

    Defining External System Information

  2. You will be taken to the Operations Designer for the Northwind Employees External Content Type. Click the Add Connection button to bring the External Data Source Type Selection dialog box.
    ECT Operations Designer

    ECT Operations Designer

    Data Source Type Selection

    Data Source Type Selection

  3. Select SQL Server as the Data Source Type and click OK to bring up the SQL Server Connection dialog box.
  4. Fill up the SQL Server Connection dialog box as shown below by providing Connection Properties and click OK:
    1. Database Server: - provide the name of the SQL Server. In my case, I'm providing (local) since the database is hosted in my local machine.
    2. Database Name: - provide the name of the database. In this demo we are connecting to Northwind database.
    3. Authentication – select what authentication mode to be used. Select Connect with User's Identity for this demo and other options for this are:
      1. Connect with User's Identity – this will take the currently logged-on user's identity for the authenticating to the external system.
      2. Connect with Impersonated Windows Identity – this allows us to have a common windows account setup for authenticating to the external system and all the requests goes through this account just like using a Service Account.
      3. Connect with Impersonated Custom Identity – this helps us to setup Secure Store Services (SSS) and configure Single-Sign-On to authenticate to the external system. With SSS, we can create and configure different Secure Store Target Applications and map the login credentials between SharePoint and the external system. If we are going to this option, make sure to create and configure the Secure Store Target Application beforehand and provide the Target Application ID for the Secure Store Application ID: field.
  5. Defining SQL Server Connection

    Defining SQL Server Connection

  6. Data Source Explorer will show up Northwind data source and expand it to see all 03 possible way of communicating with the external system:  
    1. Tables – allows us to directly connect with a table object in SQL Server.
    2. View – allows us to connect with a view instead of a table.
    3. Routines – allows us to connect with a Stored Procedure in SQL Server instead of a table.

      Data Source Explorer

      Data Source Explorer

  7. Expand the Tables node and then you will see all the tables from the Northwind database. Expand Employees table and then Columns node to see how SharePoint Designer 2010 has retrieved schema information from the underlying database system.

    Data Source Explorer Expanded

    Data Source Explorer Expanded

  8. Right-click the Employees table and select Create All Operations to fire up the All Operations wizard. This wizard will guide us on configuring what information we need to bring to SharePoint and how they get mapped to Office properties if we have decided to do so, etc… If you don't wish to provide all the CRUD-Create, Read, Update, Delete operations to the external system through SharePoint, you need to select the appropriate options from the below screen. If you want to provide only the Read operations, generate only Read Item and Read List operations.

    Generating CRUD Operations

    Generating CRUD Operations

  9. Click Next in the welcome page of the All Operations wizard.
  10. Parameters Configuration page will show up next and as the 1st step we need to select what are the columns we need to bring into SharePoint. Best practice is to avoid all the optional data fields and select only the mandatory fields to minimize the performance issues. As you can see below, I have selected the fields which are interest to my demo and make sure you double-check with your requirement.

    Selecting Fields for ECT

    Selecting Fields for ECT

  11. Next we need to setup properties for each column and select the column from the left-side Data Source Elements pane and set the properties from the right-side Properties pane. Select the LastName from the Data Source Elements and change the Display Name:, Office Property: & Show In Picker: properties.

    Setting Properties for Fields

    Setting Properties for Fields

  12. Repeat the same for the rest of the fields except selecting the option Show In Picker:. Leave Show In Picker: as unchecked.
  13. Click Next to bring up the Filter Parameters Configuration page. Click Add Filter Parameter to add a filter.

    Filter Parameters Configuration

    Filter Parameters Configuration

  14. As a best practice, we will add a Limit filter to limit the no of records travels from the external system to SharePoint. Select the newly added filter from the left-hand side Filter Parameters pane and click (Click to Add) from the Properties pane to configure the Filter:. This will brings up the Filter Configuration dialog box. Provide a name for the New Filter field. Select Limit for the Filter Type: and click OK.
    Setting up Filter Parameter

    Setting up Filter Parameter

    Setting up Filter Parameter

    Setting up Filter Parameter

  15. Once you are back in the Filter Parameter Configuration screen, give a value to Default Value: to provide a default value to the Limit filter and press Tab. I gave 100 and you can provide a reasonable value.

    Setting up Filter Parameter

    Setting up Filter Parameter

  16. Next, let's add a Wildcard filter parameter to provide users with wildcard searching capability against Last Name value. Click Add Filter Parameter button again and select the LastName for the Data Source Element: from the Properties pane. Click (Click to Add) link to configure the Filter:. Configure the Filter: as shown in the following screen and click OK.

    Setting up Wildcard Parameter

    Setting up Wildcard Parameter

  17. Click Finish to finish the Filter Parameter Configuration wizard.
  18. When you are back in Operation Designer, you can see External Content Type Operations section has 05 operations generated for us to use.

    External Content Type Operations

    External Content Type Operations

  19. Click Save to save the External Content Type to the Site Collection.

    Save the ECT

    Save the ECT

  20. Verify that the new External Content Type is created by navigating back to External Content Types form the Site Objects pane.

    After Creating the ECT

    After Creating the ECT

Now that we have created and configured our External Content Type to communicate with Employee data in Northwind database in SQL Server and we are ready to integrate it with SharePoint 2010 site.

In my next article, I will discuss the process of creating an External List and how to surface Employee data within SharePoint site coming via NorthwindEmployees External Content Type.

10Feb/112

Article – Business Connectivity Services (BCS) Part II – Terminology & Solutions Types

Posted by Joy

In the previous article, we discussed what BCS is, the evolution of BCS, investments areas in BCS in SharePoint 2010 release and BCS features by SKU. In this article we are going to get familiar with the BCS terminology which will help us going forward and will learn about the different solution types we can deliver with BCS.

BCS Terminology

It's always better to get the right terminology in place when we deliver solutions using BCS. They are:

  • Business Connectivity Services (BCS) – the umbrella term Microsoft uses to name this set of technologies which help us surfacing business data from external systems such as SAP, Oracle, Siebel, PeopleSoft, Dynamics, etc…
  • Business Data Connectivity (BDC) – the plumbing components which provides the runtime for both server and the client. This provides the actual connectivity runtime for SharePoint and Office clients.
  • External System – the legacy system which hosts business data. Examples are, SAP, Dynamics, Siebel, PeopleSoft, SFDC, etc…
  • External Content Type (ETC) – the building block of the BCS solutions. It's a reusable collection of metadata and it encapsulates connectivity information, data definitions and behaviors. This is stored in a dedicated database called Business Data Connectivity service database.
  • Model – the XML representation of the actual external data business entity such as Customer, Product, Order which BDC will transport using ETC.
  • External List – brand-new list template which provides same look and feel of the SharePoint lists and libraries along with support of CRUD operations against business data in external systems. These lists don't live in Content Database and have tighter integration with Office clients such as SharePoint Workspace and Outlook.

BCS Solution Types

Solution we deliver with BCS can be categorized in to 03 main areas and they are:

  • Simple solutions
  • Intermediate solutions
  • Advanced solutions

Following diagram will help us understand the same.

BCS Solution Types - Source: Microsoft

BCS Solution Types - Source: Microsoft

Simple solutions

Simple solutions are the solutions which leverages out-of-the-box capabilities of BCS using SharePoint and Office. This type of solutions does not require code and can be implemented by business/ power users. This helps organizations to free developers and IT Pros from having to build such solutions but IT Pros can still have the control over the actions performed by these solutions.

An example would be a power user creating an External Content Type (ETC) using SharePoint Designer 2010 to bring Employee data from HRS system and surface the same information using External List. Further end-users can take employee data offline by syncing the external list with either Outlook or SharePoint Workspace.

Intermediate solutions

Sometimes these types of solutions are referred as intermediate declarative solutions. That is mainly to indicate that even in this category we don't have to write any code.

An example would be adding a workflow to the same earlier employee data set we brought from the HRS system using External Content Type (ETC) and External List and adding Custom Actions to appear in Edit Item menu, customizing the look and feel of the View, Edit, New Info-path forms generated out-of-the-box.

Advanced solutions

Sometimes these types of solutions are referred as advanced code-based solutions. It clearly showcases the message of level of coding required to provide such solutions.

An example would be adding code-behind for Info-path forms to provide advanced validation, extending out-of-the-box Business Data web parts to provide advanced functionality, creating .NET assembly connector to aggregate data from multiple external systems. Or it can be an end-to-end solution in which you build the custom .NET assembly connector to bring data provide advanced validation/ business rules on the fly and display data in a custom user interface component.

These types of solutions are created using Business Data Connectivity Model project type in Visual Studio 2010. Visual Studio 2010 provides bunch of nice designers and skeleton code for developers to rapidly develop BCS solutions.

9Feb/110

Article – Business Connectivity Services (BCS) Part I – Introduction

Posted by Joy

Business Connectivity Services (BCS) is not something new in SharePoint 2010. This has been there from the SharePoint Portal Server 2003 (SPS 2003) and through Microsoft Office SharePoint Server 2007 (MOSS 2007). I assume everyone is familiar with the Business Data Catalog (BDC).

What is BCS?

Simply put, BCS is a platform feature of SharePoint 2010 which allows us to integrate business data from legacy/ Line-Of-Business (LOB) systems such as PeopleSoft, SAP, Siebel, etc… This integration technique allows us not only to surface business data but also to write-back to business data systems.

Evolution of BCS

Microsoft first introduced BDC in year 2003 and it was delivered as a premium feature as part of the Enterprise CAL. In this 1st release of BDC, Microsoft provided the capability only to surface LOB data within SharePoint. Also the implementation semantics were very primitive compared to other related technologies. What was meant by that is integration between SharePoint and LOB data required intensive coding exercises.

Integration Options in SPS 2003 - Source: Microsoft

Integration Options in SPS 2003 - Source: Microsoft

As you can see from the above figure, developers were required to write lot of codes to surface LOB data within SharePoint. Following was the feedback we received from both customers and System Integrators (SIs):

  • Need a way to reduce the integration code – it required intensive ADO.NET code to implement the solutions.
  • Much deeper integration of data into the places users work (Office System) – ability to surface data in Office clients was very limited though Office is the familiar tool among Information Workers.
  • Improve structured data search – though we could surface data with BDC, we were not able to search same information via SharePoint Search.
  • Need a way to centrally manage security, auditing and connections – there was no single screen in which we can manage all the aspects of the BDC solutions.

Based on the above feedback we received Microsoft did some enhancements to the BDC infrastructure and provided the 2nd version of it with MOSS 2007. With BDC in MOSS 2007, we were able not only to surface data but also to write back to LOB systems. But to be honest, write back was very reliable and promising with SQL Server back-end and with other database management systems, developers had to take a challenge most of the time.

As you can see from the following figure, implementation semantics shifted from intensive code to declarative fashion. This eliminated the need of writing intensive code.

Integration Options in MOSS 2007 - Source: Microsoft

Integration Options in MOSS 2007 - Source: Microsoft

Following is the feedback we received from both customers and SIs for the 2nd release of BDC:

  • Read is nice, Write back should have been more powerful – though Microsoft introduced write-back facility, it was at a very primitive level and didn't meet the industry standards. In short, lot of developers didn't even want to consider BDC as a way of write-back mechanism to LOB data.
  • Lack of tooling support , need an easier way to create, debug and maintain application definition XML files – though implementation mechanism shifted from .NET code to XML declaration, there were no rich tools provided by Microsoft to help developers to generate the required XML. Developers had to either write it manually or invest on 3rd party tools such as BDC Meta Man.
  • We wish it was baked into the platform – BDC was offered as a premium feature with SharePoint Enterprise CAL and lot of customers had to go all the way up to Enterprise to get this feature when they could have easily settled with WSS 3.0 for rest of the things.
  • Wouldn't it be nice to have offline capabilities – BDC didn't support Information Workers to take data offline and manipulate them on the go and sync them back to the server when they are back online. BDC couldn't cater roaming users.

Based on the above feedback we received for the BDC, Microsoft decided to re-engineer the entire technology to cater all of the customer feedback. Following is the architecture of new BCS technology:

Architecture of BCS in SharePoint 2010 - Source: Microsoft

Architecture of BCS in SharePoint 2010 - Source: Microsoft

Based on the feedback on 2007 release, Microsoft has invested in 03 main areas in this release of BCS and they are:

  • Presentation
  • Connectivity
  • Tools

Presentation

Microsoft has enhanced the end-user experience of working with external data by investing heavily on SharePoint 2010 and Office 2010. Some of the main features of the presentation investments are:

  • External List – this is a brand-new list type we see in SharePoint 2010 and this list template allows us to create a new list associating with an External Content Type (ETC). External Lists looks and feels like any other normal list in SharePoint 2010 and this list allow all the CRUD operations against the external system.
  • External Data Column – in MOSS 2007 we had a column type called Business Data Column and this has been re-engineered in this release to form the External Data Column. We can add a new column to an existing list or library based on this to bring data from an external system.
  • External Data Web Parts – SharePoint 2010 provides 05 web parts named Business Data Item, Business Data List, Business Data Related List, Business Data Actions and Business Data Item Builder that can be added to any SharePoint page to display external data.
  • Enterprise Search – SharePoint 2010 Search allows users to search data available in external systems within the SharePoint Search Center.
  • Office 2010 – SharePoint Workspace (SPW) and Outlook clients provide the capability of connecting to External Lists and take external data offline and sync them later. Word client allows creating or editing documents with data coming from lists and libraries as external data columns.

Connectivity

BCS provides both read and write capability with the new connectivity infrastructure. This facility has been extended to Office clients as SharePoint Workspace, Outlook as well. New connectivity infrastructure allows reading/ writing capability to ADO.NET databases, WCF/ Web Service Services and any other custom data sources through .NET assemblies.

Main player in this connectivity infrastructure is the Business Data Connectivity (BDC) which has 02 implementations for the Server (SharePoint) and Client (Office) and they are almost identical.

Tools

This is one of the major limitations in the 2007 release of BDC and in this release BCS provides an integrated tooling experience that scales from simple no-code solutions to complex code-based solutions including powerful packing and deployment. The tools provided are:

  • SharePoint Designer 2010 – allows us to bring data from Databases and WCF/ Web Service Services. All the CRUD operations can be generated using a simple wizard. Allows surfacing external data within SharePoint and Office.
  • Visual Studio 2010 – allows us to aggregate external data from multiple external systems by creating a .NET assembly connector. Allows us to execute custom business logic/ rules. Surface external data into other Office applications which are not supported out-of-the-box using VSTO.

BCS features by SKU

Though BCS is a part of SharePoint platform, depending on the SKU, the features of the BCS varies. Following table will show how BCS features vary depending on the SKU:

BCS Features by SKU - Source: Microsoft

BCS Features by SKU - Source: Microsoft