Create PerformancePoint Filter and Set a Default Value for the Filter

We can create a Filter for a PerformancePoint page easily. When you open the Dashboard page in PerformancePoint Dashboard Designer, you can see the Filters tab. Just click on it and you can see Filters there. Then click on New Filter as shown in the below figure.

Follow the wizard by providing a name and the data source, then you will get a screen to select members for the filter as shown in the below figure.

In the shown figure Filter members are already selected. But you can set the default value for the filter while selecting the filter members. To do it, just select the member value and right click on it. Then you will get a screen as shown below.

Select Set as Default Selection as shown in the above figure and after that you will get a screen as below showing the default selection.

Then just follow the other steps to add the filter...

Redirect to a Different page from a Web Part Zone in SharePoint

Recently I had a problem with redirect to an another SharePoint page.
The problem was when I try to click on a Report publish from the PerformancePoint, it opens the redirected page in the same zone, not in the same window.
To overcome that I used a javascript. The approach I used was, have .aspx page which contains the javascript to redirect to the requested url and call that .aspx by passing a parameter when the report is clicked. The aspx page is located in the Root of the PPSMonitoring site. You can find the path of the Root folder by right clicking on the PPSMonitoring site in IIS and select Explore as shown below.

Then add the redirect.aspx page (according to my sample) which contains the below code.

What this javascript do is it gets the parameter passed by the clicked page and set the redirected url. Then it set the parent page url as the redirecting page url. The reason to set the parent url is that when the request comes from a Zone, it consider the Window as the Zone. Therefore we have to set the redirecting url to the Parent page url.

Warnings while Configuring PerformancePoint Server 2007 in Windows Server 2008 R2

While configuring PerformancePoint Server 2007 in Windows Server 2008 R2, I got two warnings even though those patches for the SQL Server were installed.
Those warning are
  • SQL Server Native Client 9.0 (SP2) is missing
  • SQL ADOMD.NET 9.0 (SP2) missing
Those patches were installed using the Feature Pack for Microsoft SQL Server 2005 - February 2007 release in the link.

Intalled patches are
  • sqlncli_x64.msi
  • SQLServer2005_ADOMD_x64.msi
  • SQLServer2005_ASOLEDB9_x64.msi
  • SQLServer2005_XMO_x64.msi
I got warning for the sqlncli_x64.msi and SQLServer2005_ADOMD_x64.msi.

I was able to resolve that issue by installing the sqlncli_x64.msi and SQLServer2005_ADOMD_x64.msi from the Feature Pack for Microsoft SQL Server 2005 - December 2008 release in the link.

It is better to install others from this release too...

Enabling Java scripts and Ajax in IE for Windows Server 2008 R2

I had a problem while run the PerformancePoint Server 2007 Dashboard Designer using Internet Explorer in Windows Server 2008 R2. That is the Run button and the Go button are not visible for the Dashboard Designer page.
Also I had some problems for the SharePoint Server 2007 in the same OS and with the same browser. In sharepoint, those javascripts were not working.

Those errors occurs due to the security settings provided by the OS.
To solve those issues go to the Server Manager.
Then as shown in the below figure, you can find Configure IE ESC under the Security Information.

Click on it and select Off form those selections as shown in the below figure.

Then click Ok and check your sites.
It will works...

Data Cleansing with SSIS

Data cleansing is the process of detecting and correcting (or removing) corrupt or inaccurate records from a record set, table or database. For an example correcting misspelled records from a given record set.

Below diagram shows when data cleansing is required.

To avoid those situations and to have consistent, relevant and accurate data, data cleansing is required.

Stages of the data cleansing process is as shown below.

As shown in the above figure, before data cleansing, Data Quality Testing need to be done.
After that data cleansing is done by parsing, data transformation, duplicate elimination and by doing some statistical analysis.
The final output of the data cleansing process will be accurate, consistent and relevant data.

SQL Server Integration Services(SSIS) is providing the facility to implement data cleansing processes.
There are some components which can be used to perform data cleansing operations provided by SSIS.
They are
  • Lookup
  • Fuzzy Lookup
  • Fuzzy Grouping

Below diagram shows how we can use those components to achieve data cleansing for a given record set.

In that example a simple scenario is considered. For a given sales record set, it checks whether the customer exists. If not it enters the customer record by considering it as a new customer.
There may be situations, that the customer name is misspelled. SSIS Fuzzy Lookup component can be used identify that kind of situations.
Also when new customer records are inserted, duplicate records may exists. To prevent inserting duplicate records, Fuzzy Grouping component can be used.

I'll discuss how Fuzzy Lookup and Fuzzy Grouping works in another post...

Automating Excel Report Refresh...

The requirement is to refresh an Excel file in the SQL Report Server by running the Macro in the Excel file. In this post, I'll summa...