First Impression of the Business Data Catalog Definition Editor

One of my recent projects involved deciding where to place the results from a database. The database maintains cataloging information on a collection of works housed in the library on the campus where I work. The collection contains around 40,000 entries. While this isn't necessarily a huge list, I determined that a SharePoint list would probably carry too much overhead. This lead me to the Business Data Catalog, and thus my first project to work with the BDC Definition Editor that ships with the latest MOSS SDK.

After installing the application (located in C:\Program Files\2007 Office System Developer Resources\Tools\BDC Definition Editor), I began the exploration of the tool. I started out by adding a ne LOB system. The interface that appeared allowed me to either specify a database connection or a web service connection. This is where I register my first complaint with the tool. I had to provide a complete connection string. This would have been a good place to include a wizard that would help specify the connection options, etc.

Once I provided the connection string and connected to the database, my experience began to improve significantly. I was able to select tables and views to include as BDC entities just by drag-and-drop operations. The BDCDE also recognized relationships and allowed me to choose to import these as well. Once my entities were defined, I just simply clicked on OK and the BDCDE created the bare essentials for each entity. This included both a SpecificFinder and an IdEnumerator.

At this point, I decided to see what functionality was provided without any modifications. I saved the definition and then jumped into my SSP to import the definition. My test environment wasn't set up correctly, so I had to spend a little time tweaking permissions -- but once this was done I could easily view the profile page for each entity. They were working without an issue. However, since no Finder method was created by default, I couldn't use any of the entities with the BDC web parts.

Jumping back into the BDCDE, I created a finder method and was surprised to find there is no way to copy an established parameter to another method. The only difference between the specific finder method and the finder method was the lack of input parameters, so I would need to recreate the return parameter. With a little creative thought, I decided to open the XML definition in Visual Studio. I located my return parameter and copied the xml nodes and placed them in the Finder method. I then imported the definition back into the BDCDE, saved the definition and tested it using SharePoint. I now had a working finder and could return a BDC list.

The next experiment was to create filters. This is where being able to move a parameter node up or down would be very beneficial. I created my filters and tied them to parameters. However, when I attempted to Execute the Finder instance, I received an error message "Could not find exactly 1 Member with the name 'xxxx' Parameter Name: fieldName. After some experimentation, I discovered that the In parameters must be specified prior to the Return parameter. So, back to Visual Studio with the XML definition. I moved the parameter via copy and paste. After I loaded the definition in the BDCDE, I was able to execute the method and filter the result set.

Overall, I'm very impressed with the functionality included with this tool. It has significantly increased my productivity in creating BDC definitions for use in my SharePoint environment. It sure beats attempting to author the definition by hand! However, there are some functions that are missing or should be included in the next iteration to help this become a powerful tool. This includes the ability to clone a return type, move parameters, specify filters later in the development and auto-versioning the file. If you can't afford to purchase BDC Metaman, this is an excellent alternative!

Querying a Web Service with InfoPath 2007

One of the things that I have found very useful with InfoPath 2007 is the ability to query web services for data. In one of my recent projects I was asked to have an employee enter their ID and return some specific information about them to be used in an InfoPath form. The group involved wanted to make sure that the name of the employee was the official name found in the HR database.

So, my first thought was, great, I'm going to have to create a custom web part or field type for SharePoint -- then I discovered that InfoPath has the capability of querying web services. This changed my game-plan significantly. So, without further explanation of the project, here are the steps taken to query a web service using InfoPath.

Step One - Create The InfoPath Form

I begin my process by laying out the required data-source elements. The group wants to return the official name of the employee based on their employee ID. So, I create my data-source with the following nodes:

I now add my fields to the InfoPath form using the Layout task-pane. For the purposes of this demonstration, I have removed the additional data elements.

Because I don’t want the employee changing the first name and last name fields, I am going to set these both to read-only. This is accomplished by right clicking on the field and choosing Text Box Properties. One the display tab, you’ll see the read-only checkbox.

Now that these items are ready only, I’m ready to move on to step two.

Step 2 – Create the data connections

From the menu, I choose Tools -> Data Connections.

I currently don’t have any data connections defined, so I will need to add my data connection. In this situation, I already have a web-service that connects to my HR database. I just need to connect InfoPath to the web service, so I click on Add.

In the dialog that appears, I choose to create a new connection to receive data.

In the list of options presented, I select from a web service. I am prompted to enter the URI for my web service or to use UDDI to locate a web service. After you have selected the web service, you will be presented with all of the available methods. For my example, I will be choosing the method to return an employee by id as XML data.

The next two screens present the available parameters and ask me to fill them out. The first screen is used to gather the schema of the returned data. The second is to create a default value. This method expects a single employee id formatted as an integer. In my situation, I set both of these to my employee ID (mainly because it’s the only one I know from memory).

Now I’m asked by InfoPath if I want to store a copy of the data in the template. This will be based upon your situation, so I have no recommendation. Since I’m returning a single record, it almost seems pointless to store the record with this template, so I have chosen not to store any data.

Finally, you’re asked for a name and if you want to automatically retrieve data when the form is opened. Set these values and click Finish. I have now created my data connection to retrieve employee information. You should be able to change your data source on the data source task pain to explore the returned schema.

Step Three – Linking Everything Together with Rules

Now that I have successfully connected to my web service, I need to create a rule that will populate the name based on the employee ID typed into the employee ID field. Begin by right clicking on the Employee ID field and choosing Rules.

The trick to getting this to work is to use the Employee ID field to pass the required parameter to the web service and re-querying the web-service with the new parameter. So, I need to create a new rule that will take care of this functionality for me. From the rules dialog, I click on Add. To help me clarify the rule later, I call this rule Set Employee Name.

I create a new action to set a field’s value. This is where some of the magic will happen. For the field, I click on the Browse Icon and select my web service from the data source drop down. You should see query fields and data fields as nodes in the tree. Open the query fields until you find your parameter. Click on the parameter and click OK to set this value.

For the value, I set this to the current node by selecting the function icon and choosing to insert a field or group. Make sure your main data source is selected and choose the appropriate field. In my case, this is the employee ID field. Once these bindings are done, I click OK.

I now need to add an action to query the web service, so I click on Add Action and choose Query using a data connection in the action drop down. Since I only have one data source, I choose the only available data source and click OK.

Now, the final step is to add an action to set the fields of my main data source to the retrieved values from the web service. I click on Add Action and choose the action to Set a field’s value.

This time, the field will be FirstName from the main data source and the value will be the associated value from the web service. I add one more action to set the last name. Now I’m ready to test it out to make sure it works as expected, so I click OK until I am back to the main form. I now click on preview and test the form to make sure it is able to retrieve the name from the web service and populate my read-only text boxes.

When I enter my employee ID, I find that my name is automatically populated back into the form.

Developing Search

Tonight I attended the DFW SharePoint Users Community meeting where Bill English presented on building and cataloging search for SharePoint Server 2007. Here are some key thoughts that were revolutionary to me and I think should be considered in any implementation of SharePoint. Many of these items will be focused more on the administration and deployment of SharePoint, but as developers, there are times we may want to consume search results from SharePoint in our solutions. We can have more confidence of the results our solutions provide to the user if the search architecture has been well defined. According to the statistics that Bill shared, our users spend at least 30% of their time just trying to find stuff. For information workers, this is time spent searching file shares, the internet, various in-house systems and SharePoint. The problem doesn't just go away by introducing SharePoint. If you don't have an organized approach to search within your SharePoint implementation, you will end up with the exact same problems currently experienced by your users. A simple concept that we sometimes miss is the fact that as we have more items included in our search indexes, the relevancy of our search results begin to decrease. This is because the more often a word is used causes it to become more general in nature. An example presented was the term horn, horn and horn. They can all be very different -- did you mean a car horn? A horn on an animal? Or a musical instrument? As you can tell, a general search for horn will return all of these items. Even by using keyword searches, we can only improve the situation slightly. Best bets can also help, but they do little to increase relevancy. So what are we to do? Bill suggested the focus of your MOSS implementation should not just be taxonomies or hierarchies, but also findability. This is basically defining how you want your end users to be able to find information using SharePoint. This will also help drive your taxonomy plan as well. Here's a suggested plan that was presented:

• Define Content Types
• Group content types into lists
• Group Lists into Sites
• Group Sites into Site Collections

One of the other aspects that we tend to ignore in SharePoint is the capabilities of social networks. Users will migrate to finding items by using the least amount of effort. If they know someone that might know where information is located, they may query that person for the location. I find this is the case within my user community. Individuals in my department are continually coming to me to ask me where I stored something in SharePoint -- and I usually know right where it is.

MOSS integrates some key social networking components through the use of My Sites that can assist our users in finding others who might have the information they are seeking. User profiles allow us to define metadata on a person, much as we would define metadata on a document or list item. The metadata is the profile properties defined by the SharePoint User Profile Administrator. This includes properties like skills, responsibilities and even the user's group membership. Users of the SharePoint can also use the colleague web part to build relationships to the people that contain valuable information needed for their tasks.

All-in-all, this meeting was very good and thought provoking. I'm taking the notes back to my team tomorrow to begin discussing what we might be able to do to improve our search within SharePoint.

SharePoint Solutions Blog: SharePoint Designer Workflows: How to Tell Which Fields Have Changed

I would have never considered this possibility, but this is a very helpful tip! SharePoint Solutions Blog: SharePoint Designer Workflows: How to Tell Which Fields Have Changed

Requirements Gathering

One of the issues that we face as developers is that of gathering the end-user requirements. Many of us have spent countless hours in interviews with users who describe their solution in terms of what they want to see, leaving us to figure out how to merge this with our various development tools. In most cases, I have found this leaves a very large “grey” area where the developer and user are no longer able to communicate. The developer is focused on the implementation and code while the user is focused on usability and customization – and we’ve all been faced with the challenge of having to revamp our solution because the user forgot one key requirement.

In my recent projects, our team has been piloting the use of Wiki’s to help with the gathering of requirements. Basically, we will conduct an initial interview with the stakeholder(s) of the project. In the initial interview, we are only focusing on the identification of the problem. No solutions or possible solutions are ever mentioned. Our focus is to see what the stakeholder is facing in his day to day operations, so we will ask questions based on the current problem – not the expected solution. We will also use this meeting to gather the overall goals in measurable formats. For example, our goals might be something like:

• We would like to reduce the amount time required for data entry
• We need to be able to determine how many widgets we sold in a specific quarter
• We would like to be notified anytime we only have less than 10 items in stock
• We want to be able to view the details of any specific applicant and handle correspondence in a central location

In most cases, the users are much more capable of defining their intended goals, especially when a technology solution has not even been mentioned. This will allow your team to both define the problem and have measurable goals to reach as you begin developing the requirements.

After we have gathered the initial information from the stakeholder(s), we provision a Wiki site on SharePoint and invite all of the stakeholder(s) in as contributing members. Our notes are condensed and entered into the Wiki’s home page. We then set each of the goals as a separate Wiki page and begin fleshing out requirements based on the individual goal. Each goal page has sections for data, functions, reporting, communication and security. Each requirement will be listed in one of the sections.

As the requirements are attached to each goal, the stakeholder(s) are invited to revise or make notes on each requirement. This way, the developer and stakeholder are able to work in an evolving environment that minimizes the number of follow-up and analysis meetings that would normally be conducted, and all of the notes are organized by goal.

Once a good collection of goals have been established and further defined, we take the wiki pages and flesh out an official requirements document. This document helps to define the scope of the project, the need and impact of each requirement and an overall plan that both developers and stakeholder(s) can use to better understand the document. If there are places in the document that need further definition, it’s back to the Wiki for clarification of the requirements.

The official requirements document contains the following information, and becomes the basis of the project:

• Data – What are the requirements for working with data in the solution? How will it be accessed? How will it be stored?
• Functional – What are the major functions that need to be accomplished by the solution? (This is usually the list of goals provided by the end user)
• Reporting – What type of reports will need to be provided? Will custom reporting need to be available?
• Communication – Are there any needs to communicate about the items in the solution? How will this be handled?
• Security – What type of security is needed for the solution? Will there need to be multiple roles? Who will need access? Who doesn’t need access? How do we comply with regulatory requirements?
• Software – Is there any development tools or additional software that will need to be used or obtained? Will we need to use SQL Server? How about SharePoint?
• Hardware – Will this solution need any additional hardware? Do we need a new server? What about a router or switches? Can we get that color printer we’ve been dreaming about?

Additionally each requirement is given a classification of Mandatory, Required or Desired. Mandatory requirements must be present for the solution to function. Required requirements are needed, but the system will still be able to function if they are not present. Desired requirements are nice to include, but they are not needed for the system to function. With the sign-off of the requirements document, the stakeholder understands that any non-critical changes are to be included as a future version of the solution.

As I stated at the beginning of this post, we are just now beginning to implement this strategy. For the projects that have been included in this pilot, we are finding that both the stakeholders and the developers are able to bridge the communication gap more effectively. Additionally, when the requirements document is finalized, the overall creep of scope is minimized, leaving both the end user and the developer on friendlier terms with one another!

I would be interested to know if anyone has tried something similar in their endeavors with SharePoint. Please feel free to comment!