SQL Server 2005 Business Intelligence Features in Micorsoft Office SharePoint Server 2007

Microsoft Vision and strategy for the next release of Business Intelligence solutions is Complete and integrated offering, widespread deliver of intelligence through office and gradable and affordable business intelligence.

Business Application Platform

Business Application platform includes servers for Integration services (SSIS), Analysis Services for OLAP Cubes and Analytics (SSAS) and Reporting Services (SSRS) for enhanced database reporting application.

End User Tools (Office)

Microsoft Office acts as an end user or client application for the business application platform, Excel is used as a layer to show Analysis data in Pivot tables and charts.

Analytic Tools

These tools include Microsoft Business Scorecard manager to show valuable information graphically.

Analysis Service

AS is part of SQL Server 2005provides us rich multidimensional data that can be processed to be used in SharePoint Server 2005. Tithe data can be used to show KPI and Charts

Excel and SharePoint Server

Office Excel provide as an easy to use interface to analyst data trends and use conditional formatting to make sense of data and see the trends of data. Pivot tables can be used to interactively work with data and use it as per the requirement. The report center in Office SharePoint Server 2007 is used to publish the reports and excel document build to analyze data that is collection from SQL Server 2005.

SQL Server 2005

SQL Server is Suit of 5 Server. Which perform different services, these services includes

  • Relational Databases is standard service provide by SQL and mostly known.
  • Replication Services
  • Notification Services: Subscription to data changes
  • Reporting Services, Reporting Authoring and using Environment
  • Analysis Services, OLAP and data mining
  • Integration Services provide with different databases.

SQL server is a comprehensive integrated data platform that provides data to other applications like Visual Studio.NET 2005, so developers can leverage the functionality by sitting at single place. Microsoft Office provides various features that can use any of the services provides by SQL. SharePoint Server 2007 can also use these features as well as there are other third party applications that can be used to manage the SQL data. SQL Server provides the entire tools requirement to manage all the services provided in it.

SQL Business Intelligence

There are 3 components to analyze your data


Data can came from multiple places so you need to integrate the data using the ETL tools.


Perform custom business logic and applying different patterns of Data mining


Dashboards, SQL Server Reports, Pivot tables and Charts

Integration Services

Integration services provide us the capability to integrate data from different data sources. It is a new name for old DTS tools. Users can use the UI tools to create business logic and apply it on the data collection from different sources. Users can debug the data.

Analysis Services

Provide dimensional model that integrated Relational and OLAP Views. It is a fast view to analysis the data, Analysis services provides proactive caching that enables auto discovery of new transactions and provide KPIs and MDX to customize data is different.

Reporting Services

A great Authoring and publishing environment that can be used to build reports by integration with VS.net 2005 and other tools.

Ten Must Have Tools Every Develoer should download – Article

Old one but good one

James Avery


This article discusses:

  • NUnit to write unit tests
  • NDoc to create code documentation
  • NAnt to build your solutions
  • CodeSmith to generate code
  • FxCop to police your code
  • Snippet Compiler to compile small bits of code
  • Two different switcher tools, the ASP.NET Version Switcher and the Visual Studio .NET Project Converter
  • Regulator to build regular expressions
  • .NET Reflector to examine assemblies

more here

CAML Generators

Stramit’s SharePoint Blog

While working with SharePoint it is often require to Query items using CAML.  I thouhg it would be nice to share the tools to load CAML Dynamicaly.  So here is the tool.  The latest one is for SharePoint Server 2007.  the Download links are also given.

Here the 2007 version !

  • Use only Web services from SharePoint 2007
  • use current or custom credentials
  • list of the different list and views (hidden or not) of the selected site
  • auto capture of the CAML source of each component : list / View / Fields / Query
  • get a full CAML list of each column of a list
  • the cAML is view by 3 ways : string, formated indexed XML string and treeview style
  • each CAML code can generate a stringbuilder version in VB or C#
  • the query can be directly execute and test in a result tab : gridview / xml string or Treeview Style

Here’s some screencap
Install process :
You just need the .Net framework 2 🙂


CAML for SharePoint 2.0

Patrick Tisseghem’s Blog [MVP SharePoint]

U2UCamlCreator.exe starts the query builder application. You can set the credentials and also now choose the mode you want to work in.

Getting only the fields you need

V1 of the CAML Query Builder retrieved always all of the fields from the list. This version here allows you to select the fields you want to get returned from the SharePoint site. Just right-click on the list you want to work with in the treeview. Note that this only works now for the Web services mode.

Using the View Fields tab and the listboxes you can then define the fields you want to work with. Clicking on the Query tab allows you to define the query then as with the V1 version.

And of course, you can test it again.

Dynamic Parameters

If you want to have the filtering value dynamic, you can now insert in the value field the name of a parameter enclosed between []. If you follow my steps, you can click now in the treeview displaying the results of your actions on the Model node.

The name of the parameter is free, just use the brackets so that the CAML girl knows that you want to go dynamic.

In the test pane, you can then give a value to this parameter. Move to the second tab and click parameters. The Get Parameters button can be used to fill up the datagrid with your parameters.

And of course, you can test again your query.

Executing the Query in your Code

The CAML builder allows you to store the query as a file. Just use the File | Save File menu item. Once it is saved, we can use the server component of the CAML builder to have this query executed within our code. Just like the previous tutor, I have a small ASP.NET user control that I am going to host in the smartpart. Except now I have a text box where we can enter a search string.

Follow the steps discussed in the previous tutor to set the reference and then create an object of the CAML Helper.
You need a reference to the following assemblies:

  • U2U.SharePoint.CAML.dll
  • U2U.SharePoint.CAML.Server.dll

Since we are having one parameter, you need to first create an array of CAMLParameter objects and add to it one CAMLParameter object.
Just like before, the ExecuteQuery method returns you a ADO.NET DataTable you can immediately bind to your DataGrid.

U2U.SharePoint.CAML.Server.CAMLHelper helper =
     new U2U.SharePoint.CAML.Server.CAMLHelper
U2U.SharePoint.CAML.CamlParameter[] pars = new U2U.SharePoint.CAML.CamlParameter[1];
pars[0] = new U2U.SharePoint.CAML.CamlParameter("Model",TextBox1.Text);
DataTable dt = helper.ExecuteQuery(pars);
DataGrid1.DataSource = dt;

The result can be displayed in the Web part:

You can also use the CAML Query Builder now to construct your query string to do update/new/delete operations via CAML




Using Site Columns in WSS 3.0 Part III

Reusing the Existing List Columns as Site Columns

Creating a Site Colum and Using it in Any List

>Go to Site Settings

>Under the Galleries

section Click on Site Columns


You will see the build in columns provide by SharePoint out of the box

Click Create

>Enter the column Name as Customer

>Select the type as Lookup (Information already on this site)

>Select the Xavor Group from the drop down to identify the item easily.

>Enter Description if you want

>Make it a Required Column

>Select the Customer List from the Get information from dropdown.

>Select the Column you want.  E.g. Customer in this Case

>Click OK

Its Now added to the list

Using the lookup site columns in any other list

>Go to Site settings and create a Document Library

>Name the Document Library as Customer  Proposals

>Select the Document type as Word Document

>Click Create.

Add Custom column to Customer Proposal Document Library

>Go to Customer Proposal Document Library

>Click on Site Settings

>Under the Columns Section Click Add from existing site columns

>Select Xavor from the Group dropdown.

>Select the required column and click add (Customer)

>Click OK


Using the Lookup column

>Navigate to Customer Proposal Document Library

>Click Upload to upload a Document



>Click Browse button to Select the document

>Select a Word Document

>Click OK

OR Create a New Document




>Document Information panel will show the customer name

.  If you do not select any field.  It will show error message.

>Select a Customer Name and Click Save as or Retry save under the information panel.


To set the property while uploading a document to the document library

When you will upload any document to the document library the following dialog will appear.

>Select the required Column and >click OK


You can also edit the above mentioned values using the dropdown list by hovering mouse over the document in the document library and select Edit properties from the dropdown list.



Site columns provide you’re a flexible method of storing information on the parent site which can be distributed across the sub sites in the site collection.  A great thing about site column is that you can later update or delete the values which can be forward to every list using the columns or can be set that later lists and libraries will use the updated column but old ones will use the old values.  This removes a lot of problem faced by knowledge workers when they wanted to share list data within their sub sites.  It was not possible without custom code.

I have tried my best to explain each and every step.  Please do provide me feedback in any case.

Thanks and Regards,


Using Site Columns in WSS 3.0 Part II

    Using Site Colums in WSS 3.0 Part II

>When user clicks OK after enter the basic information Mailing address will be automatically generated.


Creating a Site Colum and Using it in Custom List

1.       Go to Site Settings

2.       Under the Galleries section Click on Site Columns


>You will see the build in columns provide by SharePoint out of the box.

>Click Create



>Enter the title as Business Type

>Select the type as Choice

>Create a New Group to group the custom column easily




>Enter the Description

>Make it Required Field

>Enter the Choices for the column

>Select the Display style

>Enter the default choice and

>Click OK


Newly Created column will be added to the gallery


Add Custom column to Customer List

>Go to Customer List

>Click on Site Settings

>Under the Columns Section Click Add from existing site columns

>Select Xavor from the Group dropdown.

>Select the required column and click add

>Click OK


Selecting Values for newly Created Site Columns


List after Selecting the site column values

Next See Part III