Friday, May 6, 2011

How to connect two or more Performancepoint Filters to Scorecards and Reports

In this post I am not going to introduce a new thing but I would like to demonstrate a few important steps, that might mess around you and could delay your developments. That is how to make connections among performancepoint filters, scorecards and reports.

Usually connecting one filter to a report and scorecard might not a difficult task to you. Therefore I am forcusing on how to connect two or more filters to both reports and scorecards.


The scenario I have taken here like this,
  • I have two performance point filters for Month and Year.
  • I have a Scorecard which displays few KPIs.
  • I have a Report with some tables and charts.
You can do this in two different ways.
  1. The most common method is create a dashboard in performancepoint designer and deploy it to a sharepoint site..
  2. The next method is create dashboard layout in sharepoint itself, and insert performance point content as webparts.
Eventhough above two methods are seems to be two different approaches, the connection settings are almost same. But I hope to demonstrate above two methodologies. I'd like to guide you through images, since this not very much technical related thing.

To make connections between reports and filters, goto the connections in the report and create a connection to each of the report parameter.





To create connections inbetween scorecards and filters, do the same thing to the scorecards as shown below.







I am suggesting here to connect filters to the EndPoint_Page of the scorecards. If you are using sharepoint 2010, EndPoint_page cause for disabled decomposition tree. (To get decomposition tree right click on the scorecard and select decomposition tree.) To enable decomposition tree install the following hot fix to the sharepoint server.
http://support.microsoft.com/kb/2496951

If you have done correctly, your all connections would work well. Even this is not a technical article, I hope this might helpful to you when you are get stuck with selecting proper configurations in performancepoint.

Wednesday, May 4, 2011

SSIS package variables cannot update and use inside a dataflow task

In this article I am going to introduce an alternative solution for a common problem in SSIS. That is updated values of a SSIS package variables cannot use inside of a dataflow task. I found this problem when I was going to Increase the ErrorCount, if any missmatches found during the lookup. A sample scenario is demonstrated below.


In the above scenario, I have used Row Count component to get the row count of the lookup no match output. I used variable called Count to store the row count. In  the script component I increment the ProductErrorCount variable if row count (value of the Count variable) greater than zero. At the end I found that no count had been updated, during the execution of dataflow task. Therefore above method cannot be used to get the row count of a specific path.

The reason for the above problem is SSIS package variables does not update during the execution of data flow task. Those variables get updated once the Data flow task finishes. Therefore we cannot use the updated value of the variable during the execution of a dataflow task.

Due to the above error we cannot change the value of a variable within the Process method of a Script component. If you try to change the value of a variable, you'll get the following error.


Eventhough you could change the variable values within pre-execution or post-execution methods, it only take effect to the value of the variable after the execution of the data flow task.

Then I use the following alternative method to get the row count without using package variables.

I create a grobal variable called Count inside the script component. Then I increment its value within the process method. If script component read hundred records, then Count global variable incremented up to hundred at the end of the process method. Then In the post execution method I incerement the package variable ProductErrorCount, so I can use its value at the Control flow.  

I hope the above method might helpful in your scenarios. But this is only a alternative solution, Its not a correct way to solve the problem. If anybody found any good method please let me know. Your comments are highly appritiated.