What are Business Connectivity Services

Business Connectivity Services (BCS) started out as a feature in the SharePoint 2007 product, known at that time as the Business Data Catalog (BDC). The purpose of the BDC was to enable users to read data from external systems. This provided basic connectivity between SharePoint and external databases and allowed for incorporating data from external line of business (LOB) systems and SharePoint. In the SharePoint 2007 product, BDC features were limited to the Enterprise version of the product.


BCS enables users in a similar fashion to read and write data from external systems.  SharePoint 2010 and Office 2010 applications have certain features that will enable them to use external data directly from online as well as offline stores. Developers can build BCS solutions in both SharePoint Designer 2010 as well as Visual Studio 2010 and gain access to rich feature sets for exposing external LOB systems to SharePoint. In the SharePoint 2010 product, aspects of BCS exist in every version of SharePoint, including SharePoint Foundation, Standard, and Enterprise SKUs. Microsoft opening up this rich feature set to the entire product line is a very helpful advance in providing connectivity to all SharePoint users.

Web Parts

Web parts are some of the most versatile components in SharePoint 2010 development. They are a fundamental building block in SharePoint that allow you to reuse component. You can add components to many different pages in a SharePoint site. One of the differences in working with web parts as compared to more traditional styles of ASP.NET development is this component reuse. In ASP.NET development, typically pages are constructed to be somewhat static in that they do not depend on the user to add functional components to the page. Of course, some modular ASP.NET products such as DotNetNuke are the exception to this, but on a large scale, ASP.NET development is a “build once” type of situation.SharePoint 2010 development is not like this. While it is possible to develop application pages and site pages, many times the framework of choice within SharePoint is the web part due to its versatility.

Web parts are typically deployed into web part zones in SharePoint 2010. There are many different kinds of web parts that come out of the box in SharePoint. Some of the standard web parts that are available to use on a SharePoint 2010 publishing site used for extranets include the following:

• Lists and Libraries—Types of web parts showing information stored in standard

SharePoint lists and libraries.

• Documents—A web part listing documents used in pages on your site.

• Images—A web part listing images used in pages on your site

• Pages—Pages created on your site.

• Workflow Tasks—Listing of workflow tasks on your site.

• Content Rollup—aggregation of content in a web part.

• Content Query—A web part for showing rollups of content on subsites, lists, or

specific areas of your web site. Provides easy navigation into areas.

• Relevant Documents—A web part showing a table of documents last modified by

you.

• Summary Links—A web part for adding pertinent links.

• Table of Contents—A web part that contains navigational links to your site.

• Web Analytics Web Part—A web part for tying into web analytics.

• XML Viewer—A web part with which you can provide an XML file and an XSLT

transform file to apply to the XML. Useful for such things as formatting

syndicated content from RSS feeds.

• HTML Form Web Part—A web part for HTML form inputs for sending to other

web parts

• InfoPath Form Web Part—A web part for rendering an InfoPath browser-enabled

form.

• Content Editor Web Part—A web part allowing the user to edit and save rich

content.

• Image Viewer—A web part for displaying a specific image.

• Media Web Part—Allows for embedding audio and video files into a page.

• Page Viewer—Renders another site page in an iFrame.

Picture Library Slideshow Web Part—a slideshow web part that displays all the

pictures in a picture library.

• Silverlight Web Part—A web part container for a Silverlight .xap file.

• Social Collaboration—Content media such as a note board, organization browser,

or tag cloud.

• Site Users—Displays a list of other site users and their status.

• User Tasks—Displays tasks assigned to the current user.

The Publishing Site is a site template in SharePoint that is focused on a particular task. Web parts that are available on a Team Site intranet portal include many more categories, including:

• Business Data—External LOB systems web parts

• Excel Web Access—Exposing Excel documents to your intranet

• Filters—Aggregates or filters from multiple children web sites

• Outlook Web App—Integration with your Exchange mail servers

• Search—Content and people search

• Social Collaboration—content media such as a note board, organization browser,

or tag cloud

These built-in web parts are available on certain types of sites and depending on whether or not different services are set up within SharePoint 2010. There are also tons of web parts that are available from third-party vendors, from CodePlex, and from many other sources for SharePoint 2010.One thing of note that is different about the behavior of web parts in SharePoint 2010 is that in previous products, pages provided a web part zone for you to add your web part into. In SharePoint 2010, there still are zones, but they are rich content zones. In each of these, you can intermix free-form text with images as well as instantiating your web part. The web part itself is stored in a hidden web part zone on the page, and its place in the content is stored in a div tag that contains the GUID identifying the web part.

Using PowerPivot for SharePoint

PowerPivot applications are created using Excel 2010 with the PowerPivot add-in, as

covered in the preceding chapter. After a PowerPivot application is created, it should be

uploaded to the SharePoint PowerPivot Gallery to be shared across the organization.

PowerPivot Gallery

When documents are loaded into a typical SharePoint library, they are presented in a

simple list. This works fine, but wouldn’t it be great if the documents were able to be

previewed in a library visually, like how photos are presented with thumbnail previews?

Well, that is precisely what the PowerPivot Gallery provides for PowerPivot applications.

Although this might seem like simple eye-candy, this has significant benefits for usability

and user adoption. It’s much easier for users to remember an application visually than

simply by the filename. Also, the ability to visually review the applications in the gallery

makes them more engaging, which can lead to increased user adoption.

SILVERLIGHT AND THE GALLERY

The PowerPivot Gallery uses Silverlight for the various presentations. As a result,

Silverlight needs to be installed to use the gallery. In addition, Silverlight is a 32-bitonly

application, so the 32-bit version of Internet Explorer is required, as well.

PowerPivot Gallery

The PowerPivot Gallery offers three different presentations: Gallery view, Carousel view,

and Theater view. These are selectable from the Library ribbon, as shown in. Gallery view: The Gallery view is the most basic of the views and lists a preview of

the file along with a smaller thumbnail view of each sheet in the file. As you hover

over each thumbnail, the primary view changes to show that sheet. Clicking the

primary view or the thumbnail launches the PowerPivot viewer and loads the file

full screen for interactivity.

When viewing the workbooks in the gallery, you will notice two faint icons in the upperright

corner for each workbook. The icon on the right, which looks like a calendar, is the

Manage Data Refresh function. Clicking this icon launches a page where a data refresh

schedule can be created. Currently, the highest frequency that can be set for data refresh is

daily. This can be useful for applications where the data is loaded nightly, because the

PowerPivot applications could then be set to refresh each morning.

When a PowerPivot workbook gets refreshed, PowerPivot needs to take a new “snapshot”

of each sheet for visual presentation in the gallery. The PowerPivotGallery component

initiates this snapshot process automatically, but if a user attempts to look at the gallery

before the snapshot has been updated, PowerPivot places an hourglass icon on the

outdated views as a visual indicator that these views are not yet current. 

Publishing PowerPivot workbooks to SharePoint can be done by either the Upload function

from within the SharePoint PowerPivot Gallery, the Save As option from within

Excel, or the Save and Send option for publishing from within Excel. Although all of these

methods produce the same result (that is, getting the file into SharePoint), the mechanisms

and options used are different. The Upload option is initiated from within the SharePoint PowerPivot Gallery. The Upload function is a synchronous transfer and is the fastest option but also results.The Save As option is initiated from within Excel by selecting Save As and browsing

to the PowerPivot Gallery in SharePoint as the destination. This option uses an asynchronous

transfer, which enables the bandwidth demand to be more balanced, but

takes more time than the Upload option.

. The Save and Send option is similar to the Save As option but enables the user to specify

publishing options, including selecting which sheets of the workbook to expose in

the browser and setting parameter values that may be used in the worksheet.

Although there is no way to force a particular PowerPivot publishing method, each organization

should consider whether there is a preferred method and recommend this to

their users.

Controlling Data Exposure and Spreadmarts

Although the uploading of PowerPivot workbooks is beneficial to an organization, allowing

these workbooks to be downloaded can be very detrimental. Because these PowerPivot

applications contain significant data and may also contain business rules, allowing them

to be downloaded can be risky. In addition, downloading these applications usually results

in multiple copies of various versions of the application, which is a very unmanageable

FIGURE 10.15 Publish initiated from Excel.

182 CHAPTER 10 PowerPivot for SharePoint

scenario. This scenario has been thought of as a datamart of spreadsheets, or a spreadmart.

This can be controlled by preventing users from downloading these applications.

To prevent users from downloading the workbooks into Excel, Excel Services provides a

SharePoint group named “Viewers.” Users placed into the “Viewers” group can still fully

interact with the workbook, including changing the slicer values, but they are prevented

from downloading the Excel file.

Monitoring PowerPivot

One of the biggest advantages of a tool like PowerPivot is the ability to give end users the

freedom to build the analytic applications that they find useful for managing the business.

However, one of the biggest concerns for SharePoint administrators is being able to

manage the effect that these applications will have on the rest of the SharePoint environment

and to plan for future resources.

To meet this need, PowerPivot for SharePoint includes some very useful tools for monitoring

which files are being used and how they are performing. The SharePoint Usage and

Health Data Collection service is used to collect this information, and the PowerPivot

Management Dashboard is used to monitor this information.

Enabling Usage and Health Data Collection

The Usage and Health Data Collection service is new to SharePoint 2010. This service

collects and logs SharePoint health indicators and usage metrics for analysis and reporting

purposes. During the multiserver farm install process, there was a statement about

making sure the Usage and Health Data Collection service was enabled. To do this, follow

these steps:

1. Go to Central Administration and navigate to Monitoring, Configure Usage and

Health Data Collection.

2. Confirm that the Enable Usage Data Collection box is checked, and then check the

boxes for PowerPivot data collection, namely the following:

• PowerPivot Connections
• PowerPivot Load Data Usage
• PowerPivot Unload Data Usage
• PowerPivot Query Usage

3. Confirm that the Enable Health Data Collection box is checked.

Using the PowerPivot Management Dashboard

To launch the dashboard, go to Central Administration and navigate to General

Application Settings, PowerPivot Management Dashboard.

The dashboard is divided into the following sections:

. Infrastructure - Server Health: Provides graphical trend charts for the following

statistics:

• Query Response Times
• Average Instance CPU
• Average Instance Memory
• Activity
• Performance

. Actions: Links to PowerPivot-related settings within SharePoint.

. Workbook Activity: This is a chart of the number of queries by the number of

users over time, but it is more than just a static chart. It enables you to “play” the

chart and watch how the usage occurs over the time span presented. This is useful

for tracking peak activity and determining when additional resources might be

necessary.

. Data Refresh: The automatic data refresh capability of PowerPivot is one of its most

useful features. As such, it’s extremely important to know whether those refreshes

succeed or fail, and if they fail, why. The Data Refresh section contains a list of

FIGURE 10.16 PowerPivot management dashboard.

184 CHAPTER 10 PowerPivot for SharePoint

recent activity, which can be drilled down to see all history for a particular data file.

The Data Refresh section also contains a list of recent failures for troubleshooting.

. Reports: The Server Health and Workbook Activity sections are actually displays

from a PowerPivot application, which is connected to an Analysis Services cube

named ITOps Sandbox. The Excel files that provide that information are listed in the

Reports section as Server Health.xlsx and Workbook Activity.xlsx. Custom reports

can also be created against this cube.

Summary

While PowerPivot enables end users to create powerful analytic applications, the

PowerPivot features of SharePoint enable IT to keep the applications accessible, current,

and organized.

In this chapter, we explored how PowerPivot for SharePoint is installed, how the

PowerPivot Gallery can be used to access and refresh these applications, and how the

PowerPivot Management Dashboard can be used to keep things running smoothly.

Best Practices

The following are best practices while working with PowerPivot for SharePoint:

. Don’t skimp on memory: Because PowerPivot for SharePoint loads the workbooks

into the server’s memory, the application server hosting PowerPivot services should

have plenty of memory. The recommended amount of memory for an enterprise

PowerPivot server is 32GB to 64GB. This will vary from organization to organization,

but the determining factor is the number and size of concurrent workbooks that will

be used.

. Review and set file size limits: Because PowerPivot applications include the actual

data, the files can be larger than what is commonly stored in SharePoint. By default,

SharePoint limits the size of uploaded files to 50MB and Excel Services limits the size

of a workbook to 10MB. These limits should be reviewed and adjusted as necessary

to meet the requirements for the environment.

. In a multiserver SharePoint farm, turn off Excel Calculation Services on the

WFEs: If the instance of ECS on the WFE tries to connect to the PowerPivot application

service, it will fail because the Analysis Services OLE DB driver is not installed

on the WFE.

. Turn off the Excel Web Access warning: Because PowerPivot applications require

Excel Services to query external data, users will frequently be prompted with the

SharePoint Excel Web Access external data warning. In the Excel Services Application

settings, the Warn on Refresh check box should be cleared.

Create and Deploy Timer Job in SharePoint Programatically

What is Timer Job???

Timer Job is a background process that are run by SharePoint. In Central Administration, select Monitoring link from the main page, and then select Review job definitions link under the Timer Jobs section, then you will see the list of scheduled timer job definitions 

How to Create Custom Timer Job??

Problem

Execute a code in SharePoint at every 15 minutes and adds a new record to the Task list.

Solution

Need to create Timer job that will run automatically after every 15 minutes. Following are the steps to create custom timer job definition and deploy it.

Creating Job Definition Class

Step 1

Create an empty SharePoint project from visual studio 2010 or 2012 and give the name TimerJobApplication. You can give any name to this project but here in our example we are using this name. Press OK and select "Deploy as Farm Solution"

Step 2

Add a new class to the project and give the name CustomTimerJob.cs. You can give any name to this class but here in our example I am using this name.

To do this, right click on the project name in solution explorer, select Add -> New Item. Under C# category select Code tab, select Class and give the class name.

TimerJobApplication -> Add -> New Item -> Class

Step 3

Derive this CustomTimerJob from SPJobDefinition class.

You need to add following statements to do this.

Copy Code

using Microsoft.SharePoint;

using Microsoft.SharePoint.Administration;

public class CustomTimerJob : SPJobDefinition

    {}

Step 4

Add following 3 constructors to your class.

Copy Code

public CustomTimerJob() : base() { }

public CustomTimerJob(string jobName, SPService service): 

          base(jobName, service, null, SPJobLockType.None)

{

    this.Title = "Task Complete Timer";

}

public CustomTimerJob(string jobName, SPWebApplication webapp): 

        base(jobName, webapp, null, SPJobLockType.ContentDatabase)

{

    this.Title = "Task Complete Timer";

}

Step 5

Override the Execute() method to do your stuff. Whenever timer job executes, it will run the code written inside Execute() method.

Copy Code

public override void Execute(Guid targetInstanceId)

{

    SPWebApplication webApp = this.Parent as SPWebApplication;

        SPList taskList = webApp.Sites[0].RootWeb.Lists["Tasks"];

        SPListItem newTask = taskList.Items.Add();

        newTask["Title"] = "New Task" + DateTime.Now.ToString();

        newTask.Update();

}

Here in our example, a new record will be added to Tasks list whenever timer job gets executes. You can write your own logic here.

Following is the complete code file for CustomTimerJob class.

Collapse  | Copy Code

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.SharePoint;

using Microsoft.SharePoint.Administration;

using System.Data;

using System.IO;

namespace TimerJobApplication

{

    public class CustomTimerJob : SPJobDefinition

    {

        public CustomTimerJob() : base() { }

        public CustomTimerJob(string jobName, SPService service)

                    : base(jobName, service, null, SPJobLockType.None)

            {

            this.Title = "Task Complete Timer";

            }

               public CustomTimerJob(string jobName, SPWebApplication webapp)

            : base(jobName, webapp, null, SPJobLockType.ContentDatabase)

        {

            this.Title = "Task Complete Timer";

        }

            public override void Execute(Guid targetInstanceId)

        {

            SPWebApplication webApp = this.Parent as SPWebApplication;

            SPList taskList = webApp.Sites[0].RootWeb.Lists["Tasks"];

            SPListItem newTask = taskList.Items.Add();

            newTask["Title"] = "New Task" + DateTime.Now.ToString();

            newTask.Update();

        }

    }

}

Registering Timer Job Definition

Step 6

Add a new feature to register our custom timer job.

To do this, right click on Features inside project in solution explorer and click "Add Feature".

Rename this feature to CustomTimerJobFeature.

Give Scope this feature to "Web Application".

For this, double click on CustomTimerJobFeature and choose scope "Web Application". Now press F4 so it will open property window of feature. Select False in "Activate on Default".

The reason behind selecting False is that we have to give feature scoped web application level and we dont want to activate this for all application. You have to activate this feature for the application for which you want to register timer job.

Step 7

Add Feature event receiver for this feature.

Right click on CustomTimerJobFeature and select "Add Event Receiver".

Write the following code to CustomTimerJobFeatureEventReceiver.cs class.

Collapse  | Copy Code

using System;

using System.Runtime.InteropServices;

using System.Security.Permissions;

using Microsoft.SharePoint;

using Microsoft.SharePoint.Security;

using Microsoft.SharePoint.Administration;

namespace TimerJobApplication.Features.CustomTimerJobFeature

{

[Guid("e6ea0027-1187-419d-b357-306244d0ae37")]

    public class CustomTimerJobFeatureEventReceiver : SPFeatureReceiverimer

    {

        const string JobName = "New Task Timer";

        public override void FeatureActivated(SPFeatureReceiverProperties properties)

        {

            try

            {

                SPSecurity.RunWithElevatedPrivileges(delegate()

                {

                    SPWebApplication parentWebApp = (SPWebApplication)properties.Feature.Parent;

                    SPSite site = properties.Feature.Parent as SPSite;

                    DeleteExistingJob(JobName, parentWebApp);

                    CreateJob(parentWebApp); 

                });

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        private bool CreateJob(SPWebApplication site)

        {

            bool jobCreated = false;

            try

            {

                CustomTimerJob job = new CustomTimerJob(JobName, site);

                SPMinuteSchedule schedule = new SPMinuteSchedule();

                schedule.BeginSecond = 0;

                schedule.EndSecond = 59;

                schedule.Interval = 15;

                job.Schedule = schedule;

 

                job.Update();                

            }

            catch (Exception)

            {

                return jobCreated;

            }

            return jobCreated;

        }

        public bool DeleteExistingJob(string jobName, SPWebApplication site)

        {

            bool jobDeleted = false;

            try

            {

                foreach (SPJobDefinition job in site.JobDefinitions)

                {

                    if (job.Name == jobName)

                    {

                        job.Delete();

                        jobDeleted = true;

                    }

                }

            }

            catch (Exception)

            {

                return jobDeleted;

            }

            return jobDeleted;

        }

        public override void FeatureDeactivating(SPFeatureReceiverProperties properties)

        {

 

            lock (this)

            {

                try

                {

                    SPSecurity.RunWithElevatedPrivileges(delegate()

                    {

                        SPWebApplication parentWebApp = (SPWebApplication)properties.Feature.Parent;

                        DeleteExistingTimerJobFromSite(this.JobName, parentWebApp);

                    });

                }

                catch (Exception ex)

                {

                    throw ex;

                }

            }

        }

    }

}

You can create different schedules as per your requirements. Here we have created SPMinuteSchedule for executing our job at every 15 minutes.

Other schedules you can also use as per your requirements. Following is the list of schedules.

Class Name Recurrence

SPYearlySchedule Runs the timer job once per year

SPMonthlySchedule Runs the timer job once per month on the specified day.

For example, the 15 of the month

SPMonthlyByDaySchedule Runs the timer job once per month on the specified day and week.

For example, the 3rd Friday of the month

SPWeeklySchedule Runs the timer job once per week

SPDailySchdedule Runs the timer job once every day

SPHourlySchedule Runs the timer job once every hour

SPMinuteSchedule Runs the timer job once every n minutes 

where n is the value in the Interval property

SPOneTimeSchedule Runs the timer job once

Deploying and Debugging

Right click on the project name in your solution explorer and select deploy.

After successfully deployed, you need to activate the feature manually because we have set property "Activate on Default" to False.

Open Central Administration and select manage web application.

Select your web application and on ribbon control select "Manage Feature" tab then you will see the pop up window displaying all your application scoped features. Find the feature that we have created here and activate it.

When feature is activated, your timer job will be registered. To see list of all registered timer jobs, Go to central administration and select "Monitoring" section.

Select "Review Job Definitions".

You will be able to see all registered timer job. In this list you will also find our custom timer job listed.

Your code written in Execute() method will start executing at every 15 minutes (or whatever schedule you have set).

Debugging Timer job

Debugging timer job is quite different than debugging normal C# code. You need to attach "OWSTIMER.EXE".

To do this, after deploying the project, whenever you want to debug Execute() method, press CTRL+ALT+P and select the process "OWSTIMER.EXE" and click Attach. You can also open this dialog box manually from Debug menu.

Debug -> Attach to Process

Now, put the break-point at Execute() method so whenever your timer job executes, you can debug the code.

If you don't want to wait for timer job to be executed (15 minutes in our case), for testing purpose you can run timer job immediately from central administration. To do this select timer job listed in all Job Definitions and it will give you option to "Run" or "Disable" job as well as you can also set time schedule.

Important Note

Note that, whenever you change the logic or code inside Execute() method, you must restart the Timer service from Services.msc. If you just deploy without restarting service, it will not take your latest updates. So always have practice to restarting the "SharePoint Timer Service" from Services.msc.

If you still face any problem registering or deploying timer job, try uninstalling your assembly and restart the IIS and then deploy again.

To uninstall assembly, in "Run" type "assembly" and press enter. In list of assemblies, you will see the assembly of your project name. Select that and uninstall by right click.

To restart IIS, in "Run" type iisreset and press Enter.