Building BI Application’s with SharePoint 2010
Posted by Troy on July 1, 2010 under Business Intelligence, IT, Microsoft, SharePoint, Web | 
Be the First to Comment
SharePoint 2010 is an excellent Platform for creating enterprise applications that leverage your current Business Intelligence infrastructure and operational systems. Of course your not going to build mission critical applications like POS or Supply Chain Ordering system but what about tracking weekly sales and expenses or building a pricing model that requires data from multiple data sources. Using a combination of SharePoint 2010 and the Office 2010 products it is possible to create robust enterprise applications with a reasonable amount of effort.
Business Connectivity Services (BCS) in SharePoint 2010 and SharePoint Designer 2010 is what makes these applications possible. Sure you could build your application using native SharePoint list but this approach is limited and does not allow you to leverage existing systems like your corporate data warehouse. Below is a simple diagram which show how BCS allows business user’s to access external data:
The best way to learn about how these tools work is to work through a practical example. Over the next few blog posts I will walk through how to create a simple sales forecasting application. I am assuming that you have access to a desktop machine or server with at least the following software:
- SQL Server Express 2008 R2
- Adventure Works DW R2
- SharePoint 2010 Foundation Server
- SharePoint 2010 Designer
In this scenario the business users want to create a weekly forecasting model which will forecast Internet Sales by Territory by Product Category. Execute the following to create a forecasting table in the Adventure Works DW R2 database:
select fcst.* into dbo.WeeklySalesForecast from ( select dt.CalendarYear,'Week ' + convert(nvarchar(2),dt.WeekNumberOfYear) WeekNumber , dt.MonthNumberOfYear, dt.EnglishMonthName, ter.SalesTerritoryGroup, ter.SalesTerritoryRegion, cat.EnglishProductCategoryName Category, sum(fct.OrderQuantity) LastYearQuantity, sum(fct.SalesAmount) LastYearAmount, null ForecastQuantity, null ForecastAmount from dbo.FactInternetSales fct inner join dbo.DimDate dt on fct.OrderDateKey = dt.DateKey inner join dbo.DimProduct prd on fct.ProductKey = prd.ProductKey inner join dbo.DimSalesTerritory ter on fct.SalesTerritoryKey = ter.SalesTerritoryKey inner join dbo.DimProductSubcategory sub on prd.ProductSubcategoryKey = sub.ProductSubcategoryKey inner join dbo.DimProductCategory cat on sub.ProductCategoryKey = cat.ProductCategoryKey where dt.CalendarYear = 2008 group by dt.CalendarYear,dt.WeekNumberOfYear, dt.MonthNumberOfYear, dt.EnglishMonthName, ter.SalesTerritoryGroup, ter.SalesTerritoryRegion, cat.EnglishProductCategoryName) fcst
This will create a table that can be used for building the BCS. In this post we are going to simply create a SharePoint site using SharePoint Designer to host our SharePoint application using SharePoint Designer 2010. Open SharePoint Designer 2010 and open the Main Site. In my case my main site is on my laptop and can be accessed at http://localhost. From the Site Actions menu select “New Site”:
We are now going to create a new Site called Ops Forecast (http://localhost/opsforecast) use the Team Site template:
- Title: Ops Forecast
- Url Name: opsforecast
Now click Create and the site will be created. In the next post we will build 2 stored procedures and create an External Content Type which will be used as a list to view the forecast data in SharePoint.
Troy
