Posted by Troy on July 1, 2010 under Business Intelligence, IT, Microsoft, SharePoint, Web | 
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
- 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
Posted by Troy on June 7, 2010 under Microsoft, SharePoint, Web |
In order to see the Master Page menu option in the “Look and Feel” Sites Settings section in SharePoint 2010, you have to activate the SharePoint Server Publishing Infrastructure. There are two features that need to be activated in SharePoint
- SharePoint Server Publishing Infrastructure
- SharePoint Server Publishing
These features must be activated at the web site level and not by using the Central Administration Web Application. This can cause some confusion because these settings are available in the Central Administration tool.
First, navigate to the website that where you want to activate the Publishing feature. Next, Select: Site Actions>Site Settings
Now Select, Site Collection Features from the Site Collection Administration section:
In this section you want to activate the SharePoint Server Publishing Infrastructure
Go back to the Site Settings page and select Manage Site Features from the Site Actions section. This time you need to activate SharePoint Server Publishing.
Once this is activated the Master Page option will be available in the Look and Feel section for the Site Settings:
Its important to remember that this must be set up at the site level.
Troy
Posted by Troy on June 5, 2010 under Business Intelligence, Microsoft, SharePoint, SQL Server |
Business Intelligence is a core part of SharePoint 2010. Its much more than just a content management system. SharePoint 2010 has the following new features:
Business Connectivity Services
Central Administration Redesign
- Interface is much more intuitive
- UI now uses the well known Ribbon Toolbar
Claim-Based Authentication
Health Monitoring
Sandboxed Solutions
- Deploy solutions onto the Farm or into a sandbox
- Sandbox is a restricted environment which can be used to control and limit resources for a solution
- Some solutions may not work in a sandbox and are better suited for a Farm deployment
- http://technet.microsoft.com/en-ca/library/ee704543.aspx
If you are planning on installing and setting up a SharePoint site I recommend checking out the following training videos from Microsoft:
Installing SharePoint 2010 (54 minutes):
http://technet.microsoft.com/en-ca/sharepoint/ee518665.aspx
Setting up Your First Web App (24 minutes):
http://technet.microsoft.com/en-ca/sharepoint/ee518671.aspx
I reviewed these videos and had no problems setting up a couple of web applications. I don’t recommend setting up a VM (it must be 64-bit for one thing) to run on your laptop or desktop. You will want to create the VM on a server and assign it at least 4 GB of memory. This requirement may increase if you are using a Full SQL Server Stack (Analysis Services, Reporting Services etc.) for a Business Intelligence evaluation.
My next little project will be to use Business Connectivity Services and build a simple application using external data from SQL Server 2008.
Troy
Posted by Troy on October 29, 2009 under .net, csharp, Microsoft |
I have been creating an application for work over the last couple of days which requires ADO.Net. I work in the Business Intelligence field so I do not code verify often unless I need a custom script for an SSIS package. Most of the Microsoft BI products just require a solid understanding of SQL or MDX and of course the customers business requirements. While creating the application I ran into the following error when updating the databases with changes in the dataset. I am using Visual Studio 2008 Express Edition and the database is a Compact Database.
I only received the error when I tried to update a record in the database. If I added a new record (INSERT) and saved my changes the application worked fine. Below is a screenshot of the simple app:
When you hit the save button it executes the following code:
try
{
this.Validate();
this.rulesBindingSource.EndEdit();
this.rulesTableAdapter.Update(this.ruleEngineDataSet.Rules);
MessageBox.Show("Update successful!");
}
catch (System.Exception ex)
{
MessageBox.Show("Update failed:\n" + ex.Message + "\n" + ex.StackTrace );
}
I placed a breakpoint on: this.Validate(); line. When I ran the program the program failed on: this.rulesTableAdapter.Update(this.ruleEngineDataSet.Rules);. Below is a print screen of the message:
The issue is with the TableAdapter. There was no UpdateCommand (which is mentioned in the error message) or DeleteCommand for the TableAdapter:
Create a new UpdateCommand by click (new) in the dropdown box. You can use the query designer to create the update statement for the CommandText property:
Once the the CommandText property was set for the UpdateCommand the program worked. Below is a link to the issue found on msdn forums:
http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvcs/thread/6d4de721-7571-49ca-a1fc-8689ec19a94e/
Troy
Posted by Troy on July 12, 2009 under Blogging, Microsoft, Web |
In yesterday’s post as I was walking through the Windows Live Writer configuration and there was a step that allowed you to add your blog to your Window’s Live site (you need to have a Windows Live account for this). When I tried sharing my WordPress site it failed.
The reason it failed is that blog was not set up as a feed. To fix the issue I used feedburner to set up the blog as an RSS 2.0 feed. Once it was set up I was able to share my WordPress blog on my Windows Live Profile page:
The area highlighted in yellow shows my last post.
Troy
