Database Option: CURSOR DEFAULT {LOCAL | GLOBAL} - Is CURSOR Global?

Setting the value of this option as GLOBAL has been understood wrongly by many developers. All you have to remember is, this “global” does not refer all connections, it refers the current connection. If either database option or at the declaration of cursor, GLOBAL is set, it means that scope of the cursor is global to the connection. Simply it can be referenced in any stored procedure or batch executed by same connection. If LOCAL is used, it is only for the batch which it was created. See the code below;

-- Parent SP with a cursor

CREATE PROC TestCursorParentSP

AS

BEGIN

    DECLARE @SalesOrderID int

    DECLARE cur CURSOR GLOBAL

    FOR

    SELECT SalesOrderID 

    FROM Sales.SalesOrderHeader 

    OPEN cur

    FETCH NEXT FROM cur INTO @SalesOrderID

    SELECT @SalesOrderID

    EXEC TestCursorChildSP

    CLOSE cur

    DEALLOCATE cur

END

-- Child SP that accesses cursor declared by parent

CREATE PROC TestCursorChildSP

AS

BEGIN

    DECLARE @SalesOrderID int

    FETCH NEXT FROM cur INTO @SalesOrderID

    SELECT @SalesOrderID

END

-- Executing parent sp

-- Get values from both SPs

EXEC TestCursorParentSP

If the cursor is declared as LOCAL, child SP will not be able to access it.

New Prices for Microsoft Certifications Exams

If you plan for doing MS exams, better hurry up. Following certifications are slated for a price increase:

• Microsoft Certified Technology Specialist (MCTS)

• Microsoft Certified IT Professional (MCITP)

• Microsoft Certified Professional Developer (MCPD)

• Microsoft Certified Desktop Support Technician (MCDST)

• Microsoft Certified Systems Administrator (MCSA)

• Microsoft Certified Systems Engineer (MCSE)

• Microsoft Certified Application Developer (MCAD)

• Microsoft Certified Solution Developer (MCSD)

• Microsoft Certified Database Administrator (MCDBA)

See more detail:
http://www.microsoft.com/learning/en/us/certification/cert-pricing-FAQ.aspx

TOP WITH TIES: Have you shown your TOP 10 Customers properly?

Think about a scenario where you have to show your top 10 customers based on their purchases. If the record set is as below;

and if you show first 10 records using TOP 10, have you shown all best customers? See the 11th record, it is same as 10th order, hence he should be added to top-10 list. In this case, top-10 list becomes top-11 list. But we cannot change the query as TOP 11 because it is not always guaranteed that 11th record is same as 10th record. In that case, what would be the way?

The solution is, TOP 10 WITH TIES. It adds additional rows to the resultset if any tied rows for 10th are there. See the code below;

Note that WITH TIES can be only used with SELECT statement and ORDER BY must be used too.

SQL Server 2008 R2 SP1–CTP is available for downloading

Microsoft announces the availability of Service Pack 1 as Community Technology Preview for SQL Server 2008 R2. Here are some of enhancements come with SP1;

• Dynamic Management Views for increased supportability
• ForceSeek for improved querying performance
• Data-tier Application Component Framework for improved database upgrades
• Disk space control for PowerPivot

Here is the link for downloading: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df&displaylang=en

Note that this is CTP, not for installing in live environment.

Does Referential Integrity improve performance?

I believe that everybody knows that Referential Integrity is for maintaining the relationship between two entities; Parent and Child. It makes sure that no child records is exist without a parent. Referential Integrity is implemented with Foreign Keys. The foreign key column can be set as Nullable, in that case it allows to have a child without a parent .

While I was going through my one of my old presentations, I found a slide related to this (yes, it is good to keep old presentations, human forgets things). Let me explain it with a code;

The above code is executed against AdventureWorks2008 database. Note that it gets records only from Detail table even though Header table is joined. You may say that this join is useless because it does not get data from Header. True, but see, though we have linked the header to detail, as per the plan, SQL Server does not access the header. It knows that referential integrity is implemented and existence of parent records is guaranteed, hence it does not need to access the header. Now let’s remove the relationship between header and detail, and execute the same.

Query is same but in this case, SQL Server scans the header table. The reason is it does not know whether records are exist in header for every child, it accesses the header though there is no request for values in header. Can you see, referential integrity is not only for making sure that database is consistence, it improves the performance too.

Ordering Attribute Members of Dimensions in Analysis Services

As each attribute in a dimension can have a key column and optionally name column, attribute can be sorted by either key or name column. Sorting is done with OrderBy property. Once the property is set, attribute is sorted in ascending when it is displayed. This is okay with most cases but in some cases sorting is needed in a different way. Examples cane be, sorting in descending order, sorting by not its key or name, by some other attribute. This post discusses these exceptional cases.

Sort by some other attribute
Good scenario for this is Month attribute in a Month dimension. Usually we set Month attribute’s Key Column as MonthKey and its Name Column as MonthName. If the sorting is set as Key with OrderBy property, months will be ordered chronologically only if you have inserted them in source table in an order. If they are not inserted in an order, months will not be ordered as we want. Again, if OrderBy property is set as Name, months will be displayed in alphabetical order which is not the way we usually want. See the image below.

Note that records are not inserted in an order. Month attribute’s key is set with MonthKey and its name is set with MonthNameWithYear. You can see how they are displayed with OrderBy set as key and name.

In order to have the proper ordering on Month attribute, we need an additional column to be used for sorting. Best candidate is MonthNumberOfYear column. It can be used as sorting key for Month. One requirement for doing this is, have a relationship between Month and MonthNumberOfYear, in other words, making MonthNumberOfYear as Related Attribute for Month. By default, when the dimension is created, wizard adds all the dimension’s attributes as related attributes of the key. In this case, it has been added too. If not, you need to manually add this through Attribute Relationships (a tab) in BIDS’s Dimension designer.

Once the column is set as a related attribute, change the OrderBy to AttributeKey and set OrderByAttribute as MonthNumberOfYear.

Once processed, months will be displayed in an order as we want. There are two things to be considered in this setting. If MonthNumberOfYear column is not required for querying, you can hide it by setting AttributeHierarchyEnabled property to false. Other thing is, Relationship Type between an attribute and related attribute. It can be set as either Rigid or Flexible. If the relationship is fixed over time, not going to change (Type 2), rigid is the best option. If the relationship can change over time (Type 1), type has to be set as Flexible.

Descending Order
Once the sorting is set for an attribute, it is sorted in ascending order. That is the only possible sorting order supported by Analysis Services. If descending order is required, just like the previous one, support is needed from another attribute that maintains value in descending order. For example, if months need to be sorted in descending, MonthNumberOfYear has to be filled with values 12 to 1, not 1 to 12.

Logical Query Processing Phases in SELECT statement

The SELECT statement can be constructed with various clauses as per the requirement. It can be as simple as just a set of columns from a table or as complex as a set columns from multiple tables with aggregate functions. Understanding how SQL Server executes added statements will be worthwhile for constructing the SELECT quickly and correctly as per the requirement. Here is the logical processing order in SELECT statement;

Note that all clauses have not been used with above code. Here is the order of all possible phases;

As you see, it is not the order we have written the query, hence keeping this order in mind is absolutely helpful.

SQL Server could not create a subscription for Subscriber: MSDTC on server is unavailable.

When I configured a subscription for Transactional replication with updatable subscription, the following error occurred.

SQL Server could not create a subscription for Subscriber 'DINESH-PC\SQL2008Instance2'. MSDTC on server 'DINESH-PC\SQL2008INSTANCE2' is unavailable.

I did not want to set the subscription for Immediate Updating mode, so set to Queue changes and commit when possible.

I am aware that MSDTC is used for Immediate Updating (Simultaneously commit changes). Then why I am getting such error? I did not select that option.

The reason is the wizard. When the subscription is created through the wizard, whatever the selection you make, it makes both Immediate Updating and Queued Updating available for subscription. If you do not want both, do not use the wizard, create the subscription manually using stored procedures. Note that you can use the wizard for generating scripts for the whole task without creating the subscription. The generated script contains four stored procedures;

• sp_addsubscription: This adds a subscription to publication and it has to be run in publisher. You need to make sure that @update_mode is set as ‘queued tran’.
• sp_addpullsubscription: This adds a pull subscription to subscriber, hence needs to run in subscriber. Just like previous one, make sure that @update_mode is set as ‘queued tran’.
• sp_link_publication: This is to use with Immediate Updating, hence you do not need to execute this.
• sp_addpullsubscription_agent: This adds a scheduled agent job to synchronize pull subscription with transactional publication. Run it in subscriber.

Once you run everything, you have a subscription with queued updating mode.

SQL Server Updatable Subscriptions: What is the best replication type for this?

Replication is used for copying data and database objects from one location to another on a recurring basis. The topology of replication is vary, it can be a simple implementation with two servers or a complex implementation with many local and remote servers and mobile devices. Servers involved are designated with server roles such as Publisher, Distributor and Subscriber and their responsibilities are publishing, distributing, and finally subscribing published data. In some scenarios, subscribers use the subscription only for reading data. In other scenarios subscribers make modification to subscription (change data) and send back to publisher. Such subscriptions are called as Updatable Subscription. SQL Server provides many ways to implement a replication with updatable subscription. This post discusses some considerations and guide lines for selecting the best replication type with updatable subscription suit for your requirement.

The general overview of Replication can be illustrated as below.

• Publisher: Publishes data with publication.
• Distributor: Distributes data. This server holds metadata, historical data and replicated data for some replication types.
• Subscriber: Receives data from publications. They can make changes to subscribed data too.

Updatable Subscription
SQL Server provides three different types of Replications. They are:

• Snapshot replication
• Transactional replication
• Merge replication

Changing data at subscriber and sending changes back to publisher facility is not available with all types. Transactional replication and Merge provides facilities for it.

Transactional Replication is mainly used in Server-to-Server scenario. Subscribers are initiated with a snapshot of publication and then transactions occurred are sent to them in incremental manner. This replication type allows subscribers to update data and send back to publisher. If it is required, Transactional Replication with updatable subscription option is available at creation of publication. This works in two ways:

• Immediate Updating: Uses MSDTC for sending data from subscribers to publisher and applying. It is done through triggers added in published tables in subscription.
• Queued Updating: Changes are sent to a queue. The Queue reader agent pull changes and apply to publisher. It is done with triggers too.

Read more on these two at: http://msdn.microsoft.com/en-us/library/ms151794.aspx

Now, where we can use Transactional replication with updatable subscription? Here are some guidelines for selecting this as your replication type:

• Scenario is Server-to-Server.
• Number of subscribers are less than 10.
• Subscribers update data infrequently.
• Subscribers, distributor and publisher are connected most of the time (For Immediate updating, regular connection is required as update happens synchronously).
• Subscribers need all changes, not the last change (Eg. if there are 100 transactions for a record, all are required by the subscriber, not the final state of the record)
• Expect less update conflicts with queued updating. This type does not have rich facilities for handling conflicts.

**Note that MSDN says that this feature will be removed in a future version of SQL Server, so it is not advisable to use this if you plan to upgrade your system with future versions.

Merge replication is mainly for Server-to-Client environment. This allows subscribers to make modification on replicated data and send them back to publisher. Use following guidelines when selecting Merge as your replication type for updatable subscription:

• Scenario is Server-to-Client.
• Many number of updating subscribers (Merge is designed for this).
• Expect conflicts (Good support on conflict detection and resolving)
• Subscribers need the final state of the change, not all transactions.
• Subscribers, distributors and publisher are not always connected.
• Subscribers need a portion of publication, not the entire one (This is done with dynamic filters in publication).

Okay, you can consider given guidelines and select the best type for your requirement. How about Peer-to-Peer replication? Can we use it too for handling this?

Peer-to-peer replication is scale out and high availability solution. It maintains copies of data across multiple servers, referred as nodes. Its foundation is Transactional replication and works in similar manner. The main difference is, nodes are not designated as publisher or subscribers, all are publishers and subscribers. Here are some guidelines for selecting it:

• Requires high availability (with other types, if publisher goes down, replication topology is down too).
• Less number of nodes (would be better to have 10 or less).
• Filtering of publication is not required.
• Do not expect much conflicts.

Self-Service Business Intelligence with SQL Server 2008 R2 – Speaking engagement in SQL Server Sri Lanka User Group

The February meeting of SQL Server Sri Lanka User Group meeting was held on last Wednesday and sessions were done by Dinesh Asanka and me. My session was on Self-Service Business Intelligence with SQL Server 2008 R2, focusing on PowerPivot. The agenda for the sessions was:

• What’s new in SQL Server 2008 R2?
• What is Business Intelligence?
• Introduction to New Paradigm
• Introduction SQL Server PowerPivot
• Future Releases
• Q & A

The presentation is available for downloading: http://www.sqlserveruniverse.com/v2/SSSLUG/Downloads.aspx

Guest user account

The Guest user is an account which is automatically created when a database is created. This account is used when a login connects to a database without having a user account created in it. By default, guest is disabled, to enable and disable, use the codes below;

-- to enable

GRANT CONNECT TO GUEST

-- to disable

REVOKE CONNECT FROM GUEST

The recommended way of managing guest account is, have it in disabled status. If you have enabled it for some reason, permission can be granted either using graphical interface or through T-SQL.

-- Give data read permission to guest on Customer table

GRANT SELECT ON Sales.Customer TO GUEST

If guest is enabled in AdventureWorks2008 database and above permission is granted, when a login (eg. Jane) connects to AdventureWorks2008, she will be able to read data from Sales.Customer though she has no user account created in AdventureWorks2008.

Template Explorer in SQL Server

Although this is not much popular among DBEs, it is one of the facilities should be used. The Template Explorer gives us various type of templates for constructing objects. For an example, if you need to create a database using T-SQL, the template of the CREATE DATABASE can be taken from the Template Explorer and altered as you need. This definitely speeds up coding though it discourages remembering syntax.

The Template Explorer can be opened using View –> Template Explorer (or Ctrl+Alt+T) menu. Templates are grouped into three: SQL Server, Analysis Server, SQL Server Compact. Templates in a group are categorized too. For an example, if you want to create a database with multiple file groups, CREATE DATABASE ON MULTIPLE FILEGROUPS in DATABASE  category under SQL SERVER group can be used.

Template can be brought into query window by dragging and dropping or double-clicking. Once placed, need to replace parameter values with your values. Although you can manually do it, easiest way is, using Specify Values for Template Parameters window. It can be accessed using Query –> Specify Values for Template Parameters menu.

You can create your own templates and have them in Template Explorer. Start it with creating a folder or add new ones in given folders (categories). This facility is open through right-clicking on the folder in Template Explorer and selecting New. One thing you need to remember is the way of adding parameters. Parameters should be enclosed with “<” and “>” and should contain three parts: Parameter name, type, value. You can have the same for both Parameter name and value, allowing user to replace the “value” with their value. For an example, parameter for database name would be: <Database_Name, sysname, Database_Name>.

sys.database_files: size column and why 128?

It came up while my class was being conducted. We were discussing of finding the size of files attached to a database and available free space of files. As usual I used my sample codes to show few things, this is one of them:

SELECT name, size/128 size FROM sys.database_files

Now, why 128? It did not come into my mind immediately . Result of being a designer.

Size column shows current size of the file in 8KB pages. It is the number of pages that have been allocated. So, why 128? It is for converting to MB. There are 128 8KB pages in 1MB. Here is my altered code;

SELECT name, size/128 SizeInMB

    , FILEPROPERTY(name, 'SpaceUsed')/128 SpaceUsedInMB 

FROM sys.database_files

-- or

SELECT name, size*8 SizeInKB

    , FILEPROPERTY(name, 'SpaceUsed')*8 SpaceUsedInKB 

FROM sys.database_files

Installing and Configuring PowerPivot for SharePoint 2010

Microsoft PowerPivot is slowly becoming an essential technology for Business Users addressing Self-Service Business Intelligence. It is innovated by Microsoft SQL Server team as a part of Microsoft SQL Server 2008 R2 release and comes in two flavors; PowerPivot for Excel and PowerPivot for SharePoint. I have already written a post on Excel add-in (Creating Self-Service Business Intelligence dashboard with PowerPivot – Part I) which discusses dashboard creation with Excel and PowerPivot. This post discusses about Installing PowerPivot for SharePoint 2010 with following sections;

• Excel and SharePoint
• Installing PowerPivot for SharePoint 2010
• Error: Could not load assembly
  ‘Microsoft.AnalysisServices.SharePoint.Integration.dll’
• SQL Server Analysis Services Installation – VertiPaq
• Deploying PowerPivot solution packages to Web Application
• Other Services required
• Creating PowerPivot Service Application
• PowerPivot Feature Integration for Site Collections
• Configuring PowerPivotUnAttendedAccount for Data Refresh

Excel and SharePoint
Why two technologies? We can consider that Excel is for authoring PowerPivot applications. It allows to create an application with one or many data sources and then facilitates data analysis in a multi-dimensional structure. It does not need a constant connection to used data sources, everything will be stored inside Excel Workbook and if want, data can be refreshed. SharePoint can be considered as a hosting environment for PowerPivot applications. We usually go for SharePoint if collaboration, sharing and reporting are required.

Installing PowerPivot for SharePoint 2010
There are two components need to be installed. PowerPivot System Service and Analysis Services in VertiPaq mode which is a local, in-process Analysis Services engine. Let’s start with Analysis Services.

Installing Analysis Services for PowerPivot is straight forward. I have already set up my SharePoint 2010 farm. It uses SQL Server 2008 R2 that exist in same box. Note that my installation contains full SQL Server product suite, including SQL Server Analysis Services, hence new instance of Analysis Services for PowerPivot will be a named instance. There is a known issue you might face if SQL Server installation is already exist. It is;

Could not load assembly ‘Microsoft.AnalysisServices.SharePoint.Integration.dll’
You get this error if you have a prior installation of SQL Server 2008 R2. Unfortunately it comes at the completion of the installation.

Solution for this is simple. Create a text file as below and save it as Setup100.exe.config.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <runtime>

    <disableCachingBindingFailures enabled="1" />

  </runtime>

</configuration>

Then move the file into %ProgramFIles%\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64. Once the file is placed, you need to start the installation again. For more information, see this: http://support.microsoft.com/kb/2261507 .

SQL Server Analysis Services Installation – VertiPaq
Before starting the installation, make sure that a domain account is created for new Analysis Services instance for running the service. In my case, newly created account for this is PowerPivotAnalysisServices.

Now run the SQL Server 2008 R2 setup. It would be better to run the setup as Administrator (Right click on Setup.exe and select “Run as Administrator”). If you have configured more than one server for the farm as Application Servers, the installation should be done for all servers (Generally we set up two servers one for WFE and another for applications. This installation should be done only on Application Servers).

Once the installation is started, select New installation or add shared features as per the screen below.

Continue with screens until you get a screen which allows you to select SQL Server PowerPivot for SharePoint. Select Existing Farm for Add PowerPivot for SharePoint to (Note that assumption here is Farm is already configured).

Next screen is Feature Selection. It is a read-only screen, you will not be able to change anything on it. Continue with the wizard until you get Instance Configuration screen. You will notice that the instance has already been named ad POWERPIVOT. Do not change it, continue the wizard.

At the Server Configuration screen, set newly created domain account and its password for SQL Server Analysis Services. When you have Analysis Services Configuration screen, make sure that accounts that need to be administrators for Analysis Services are added.

Everything is set now, continue the wizard and complete the installation. Once it is done, go to SharePoint Central Administration –> System Settings –> Manage Servers in this Farm. It shows all the servers in the farm and services running. If you see SQL Server Analysis Services and SQL Server PowerPivot System Service, your installation is successful.

Deploying PowerPivot solution packages to Web Application
Next part is deploying PowerPivot solution packages to Web Applications (to WFE Servers). There are two solution packages. Installation we performed earlier deploys solution packages for Central Administration but not for our web application. Go to SharePoint Central Administration –> System Settings –> Farm Management –> Manage Farm Solutions. You should see two solutions;

• powerpivotfarm.wsp is deployed globally, no need to deploy again.
• powerpivotwebapp.wsp, you can see that it has been deployed to my default web application (http://w2008r2-sp:10000). This is the solution we need to deploy manually to Web Application which suppose to hold PowerPivot data.

Let’s deploy powerpivotwebapp.wsp to a web application. Click on powerpivotwebapp.wsp. It opens Solution Properties. Click on Deploy Solution. Select the web application from the drop-down you need to use under Deploy to? and click OK. Once the Solution Properties page is open, make sure that added web application is listed as below.

Other Services required
Next step is starting all other necessary services required by PowerPivot. Make sure following services are running;

• Excel Calculation Service
• Secure Store Service
• Claims to Windows token Service
• SQL Server Analysis Services
• SQL Server PowerPivot System Service

These services are listed under SharePoint Central Administration –> System Settings –> Manage Services on Servers.

Creating PowerPivot Service Application
This helps to establish the communication between WFE and PowerPivot service via PowerPivot Service Application Proxy. Let’s create it. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Click on New and select SQL Server PowerPivot Service Application.

With Create New PowerPivot Service Application window, set the name as PowerPivotServiceApplication. Do not use any existing pool, create a new one and assign a domain account for it. Set the database server and database name then. Finally select the checkbox Add the proxy for this PowerPivot service application to the default proxy group. Click OK to create it.

PowerPivot Feature Integration for Site Collections
This is appeared under Site Collection Features and must be activated for each of the site collections. Go to your site collection and click on Site Actions –> Site Settings –> Site Collection Administration –> Site Collection Features. When the page open, find PowerPivot Services feature integration for Site Collections and activate it.

Configuring PowerPivotUnAttendedAccount for Data Refresh
PowerPivot unattended account is used for refreshing data in the workbook if user credentials are not exist with the workbook. This account is stored in SharePoint Secure Store Service. In order to continue, you need to make sure that Secure Store Service is up and running, and master key is created. I have already configured Secure Store Service for PerformancePoint. If you have not, see this post for configuring it: http://dinesql.blogspot.com/2010/07/configuring-performancepoint-2010.html.

Let’s open the Secure Store Service Application. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Click on Secure Store Service (or select it and click on Manage) link;

Secure Store Service uses Target Applications for holding accounts. In my case, I have an application set up for PerformancePoint and that is what has been listed. If you have note, nothing will be listed. Once a Target Application is created for PowerPivot, it has to be associated with PowerPivot Service Application. Let’s start creating the Target Application. Click on New for opening Create New Secure Store Target Application. Set ID and name for the application. Appropriate ID and name would be PowerPivotDataRefresh and Power Pivot Data Refresh. Enter a valid email for Contact Email and select Individual for Target Application Type.

Next screen for specifying fields (columns) for the application. Accept the default as we need only user name and password and continue.

Next screen for setting admins for the application. Make sure that you have added the account which is used for PowerPivot Service Application pool. In addition to that, any account need to be an admin can be added. Continue the wizard and complete. Finally you are back on Secure Store Service. You should see the newly created application listed now.

Next is setting credentials for PowerPivotDataRefresh application (or PowerPivot unattended account). Select the check box next to newly created application and click on Set Credentials button. It opens the Set Credentials for Secure Target Application (Individual). Settings for this screen will be;

• Credential Owner: This is the owner of the credential. It will be used by PowerPivot unattended account but this is not the account used as the unattended account.
• Windows user name and password: This is the actual account used as the unattended account.

Click OK. Now we need to associate the application with PowerPivot Service Application. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Find PowerPivot Service Application and click the link (or select it and click on Manage button). This opens PowerPivot Management Dashboard. Click on Configure Service Application Settings in Actions frame. It opens PowerPivot Settings. Find PowerPivot Unattended Data Refresh Account input box under Data Refresh. Type the ID of the application created in Secure Store Service, in my case, it is PowerPivotDataRefresh.

Done. To complete the process, few setting have to be done in Excel Services.

Settings in Excel Services
Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Find Excel Services Application and open it. It opens Manage Excel Services Application. Click on Trusted File Locations.

You will see that SharePoint has been added as a Trusted Location (Address is http://). In order to access a workbook by both Excel and PowerPivot services, this is required.

Get the menu of http:// and select Edit. Change following items in Edit Trusted File Location.

• Workbook Properties: Maximum Workbook Size. Set as you want. Note that maximum file size accepted by SharePoint for both Excel and PowerPivot is 2GB.
• External Data: Select Trusted data connection libraries and embedded under Allow External Data.

Click OK to save settings. Now, You should be able to upload an Excel file that contains PowerPivot data into the web application and see.

Will discuss more on this in another post.

PPS Reports: Integrating Reporting Services Reports with PerformancePoint 2010 Dashboards

The success of Business Intelligence solution can be measured with Visualization of data, hence Visualization in proper manner is important. It is handled through many ways, providing easier access for information to business users. One of the key components in Microsoft Business Intelligence suite is PerformancePoint 2010 which supports visualizing data in number of ways. PPS allows business user to interact with data, organize as he wants and change the content as he wants with its reporting features. Some of reporting facilities available with PPS are Reporting Services reports, Analytic charts, grids, and Decomposition trees. This post focuses on Reporting Services reports, basically, how a Reporting Services report can be added to PPS dashboard.

Microsoft Report Viewer 2008
In order to use Reporting Services report with PPS Dashboard Designer, you need to make sure that Microsoft Report Viewer is installed in your machine. If not, you may face errors like “Unable to connect with Report Server” or “Could not load file or assembly ‘Microsoft.ReportViewer.WinForms’…..”. It is available for 2005, 2008 and 2010 now. It looks like PPS Dashboard Designer works only with 2008 (I have 2008 SP1) but you can try 2010 too. Here are links for downloading it:
Microsoft Report Viewer Distributable 2008
Microsoft Report Viewer Distributable 2008 SP1
Microsoft Report Viewer Distributable 2010

Getting Started
As usual, you have to start with PPS Dashboard Designer. If you have configured Business Intelligence Web Application and site properly with SharePoint 2010, there will a link available for downloading this ClickOnce application (see Configuring PerformancePoint 2010 for more details). Click on Run Dashboard Designer for downloading the designer.

Once open, Save it with a meaningful name. Then select PerformancePoint Content folder in left-pane and go to Create tab in the ribbon. Click on Reporting Services.

Report Setting
You need to set few settings in order to have a Reporting Services report in your dashboard.

Server Mode: This allows you to set the mode of Reporting Server you are going to connect with. If it is hosted in SharePoint, select SharePoint Integrated, else select Report Centre. In my case, it is SharePoint Integrated mode.

Report Server URL: This is the URL of Report Server Web Service which you set of Reporting Services Configuration Manager.

Report URL: This is the exact URL of the report. If it is hosted in SharePoint, make sure that you have given the proper URL (including the extension). If you select Report Centre, you will be able to browse the report and set.

Show toolbar, Show Parameters, Show DocMap: You can set the visibility of these items. Note that Document map will not be available for all reports. It has to be created with either Report Builder or BIDS.

Zoom: Allows you to set the viewing size.

Format: Default is HTML 4.0. You can select other types such as Excel or PDF. Based on the type you select, report will be either shown in the web page or will be downloading to client machine.

Section: This allows you to set the page to be displayed. If you set 3 for this, 3rd page will be open as the default page.

DocMap ID: It is supposed to show the element set with this as the default page. I could not figure it out the way of setting it, for me, it did not work.

Report Parameters: This allows to set parameters default values.

Once settings are done, add a dashboard and place the report, and publish to SharePoint site. SharePoint site can be set with File Button –> Designer Options –> Server –> SharePoint URL. Make sure it is set before publishing.

Here is the published dashboard.