Fetching Line Of Business Data into front end MOSS site using Business Data Catalog Approach

In this blog i am trying to develop a simple application built upon Business Data Catalog (BDC) feature of MOSS 2007.Most of the coding-savvy developers will always prefer to go for custom web part development to fetch/store data in database over looking the out-of box features provided by MOSS like the BDC one.The idea behind MOSS BDC is whatever data we can acces using web service or ADO.Net we can bring the same into MOSS sites using this Business Data Catalog.
I am concentrating fully on first time BDC application developers focussing on steps to create simple application so kept the functinality to the lowest minimal possible like fetching SQL table to onto a BDC web part in my site and applying filter over it.

Let us assume that we have simple SQL table named EmployeeTable containg employee information.

Pic : EmployeeTable Schema








Pic : EmployeeTable Sample Data
Now this the data which will be brought into the site .Create XML File like below and this is the crucial one as i am putting all my connections,logics,implementation here. I have tried to put as many comments as posible in between to understand all nodes and its relavance . Also i had split the XML codes into many snapshots for better visibility purpose.Please copy all these XML snapshot contents into athe XML file creted by you to get the complete XML file for our application.

Pic: Snapshot 1 of XML File

Pic : Snapshot 2 Of XML File

Pic : Snapshot 3 Of XML File

Pic : Snapshot 4 Of XML File







Pic : Snapshot 5 of XML File







Pic : Snapshot 6 of XML File









Pic : Snapshot 7 of XML File










Pic 8 : Snapshot of XML File









Pic 9 : Snapshot of XML File
Ooff thats the end of XML code snippets .Please excuse for the bad formatting.Now save the completed xml file to any location.

Now the work starts with Share point server. Go to the cental Administration and then Shares Service Provider Home. Click Import Application Defination available under Business Data Catalog.










Browse to the XML file which you created in the top ,keep the default option as it is and click Import .













As our XML file is correct it will get imported successfully,if it contains errors we will be stopped and getting errors in the next screen which needs to be corrected to go furtther.








Click OK, we will get the summary view of our BDC application which got imported and ready to use in our moss site.












Open your favourite moss site.Click Site Actions ->Edit Page. Clieck Add a Web Part in the zone you want to put the web part.Select the Business Data List Web part










Click OK. Now our Business Data List web part gets added to the site.Being in the Edit Mode click "Open the tool Pane" hyperlink available inside the added web part.
Click the "Browse" option which will open up one more" Web page Dialog" as shown below













Select the BDC application we created ,click OK everwhere to get the page loaded with the BDC web part.








Web part is ready to use now . Remeber that comparison is the filter we difined in our XML file level and EmpID is the parameter to filter/compare. Now filter with EmpID to fecth the data from our line of business system which is SQL table in our case.Click Retreive data link to get different data based on parametres passed (with EmpID = 1)










Observe the retrieved data in the "EmployeeIdentifierList" and cross check with the database.
One more view with another parameter (with EmpID = 2)











In the above article i have used only "EmpId" as parameter and "is equal to" as filter.We can add more parameters and more filter criteria's just by fine-tuning the XML file whcih we have developed for the BDC demo.

Indexing Column approach to Manage large list and libraries in MOSS 2007

When the number of items in a list or library approaches two thousand, the performance of the list or library may begin to slow down or adversely affect other areas of the site. The most common example of a user action affecting performance occurs when a user creates or accesses a view of the items in a very large list or library. When defining a view, you have the choice of returning all the items or filtering a subset of items based on a particular column. Any time the total number of items in a list or library is very large, it is important to limit the number of items that a user is working with at one time. This involves filtering the total set of items to a smaller number that is more manageable for the end user and for the database.
To improve the performance in these scenario’s with a large list or library,we can use indexing feature over a column.From a list or document setting page,we can add index to any column.Doing this does not actually create a physical index in SQL server.Instead it creates a table with integer ID of list item or document and value of the indexed column.
Lets create a simple custom list named “Indexed List”.

Go to settings ->List settings.Under columns header click Indexed Columns.

Select the columns which you want to index and click OK.
Add some items to the list.










Now let’s look at how these things are stored in content database. First locate the content database of your web application. In the content database AllLists is the table where all List information is saved.In that find the Indexed List we created just now.














In the same content database, AllUserData is the table that stores all items in any list of the SharePoint site. For backward compatibilty, a SQL View with the name UserData exists that returns data from AllUserData.In this table query for only the data corresponding to our indexed list .













This AllUserData table schema makes it impossible to define database column indices on the custom columns of a SharePoint list since every list is stored in this table and every list would have different columns with different index settings.



The SharePoint Content database schema doesn’t allow indexing in the AllUserData table itself. To overcome this limitation the NameValuePair table was introduced.Hence as described in the beginning Once we decide to index a column in SharePoint list we can see that in the content database the table NameValuePair has been filled with the values of the columns that we have indexed.













Here we can see the all the list item data for which indexing were applied. In the result set row 4 – 8 (last 5 rows) were corresponding to our indexed list. First 3 were corresponding to some other list in the site collection for which the indexing was applied.

The table will be filled with the current column values once we decide to index a column. When editing, deleting or updating values in SharePoint list “Indexed List”– the changed values of the index column will also be updated in the NameValuePair table as shown below (Observe FieldId Column)

1) When indexing was applied for “Created” Column















2) When indexing was applied for “Modified” column






















After we index a column in share point list, we can add it to a new view as we create the view and then use the indexed column to filter the view. Before creating a view, we can add more columns to enable more flexibility for sorting, grouping, and filtering.

With the indexed column in hand, for lists and libraries with large numbers of items,we can access the items with a mix of views and search


1) Recently changed To create a view of only the items that have changed in the past week, n index the Modified column and then apply the filter Modified (Indexed) is greater than [Today]-7.
2) Discussion board updates To create a view of only the discussions that were updated in the past month, index the Last Updated column on a discussion board, create a new Subject view, and then apply the filter Last Updated is greater than [Today]-30.
3) Due today For lists or libraries with a Due Date column, index that column and then apply the filter Due Date is equal to [Today].

4) New items To create a view of only those items that were added in the past week, index the Created column and then apply the filter Created is greater than [Today]-7.
5) My items To create a view of only those items that a particular user added, index the Created By column and then apply the filter Created By is equal to [Me].

Points to be considered when we go for indexed column approach

1)Each additional column index consumes extra resources in the database. Therefore, add indexes only to columns that will be used actively in views on the list or library.
2) When we define a view in a list or library with lots of items, it is important to use a filter that will return no more than two thousand items.
3) Only one indexed column can be used in a view filter. We can filter on many different columns, but it is important that the first column that is used to filter the view has an index and that it sufficiently reduces the total number of items returned.