Wednesday, November 17, 2010

Programmatically Set Audience to a SharePoint 2010 WebPart

To provide permission to a sharepoint web part for specific SharePoint Group, we have to use AudienceManager as shown below

SPWeb currentSite = SPContext.Current.Site.RootWeb;
SPSite spSite = currentSite.Site;

SPServiceContext sc = SPServiceContext.GetContext(spSite);

AudienceManager am = new AudienceManager(sc);

Microsoft.SharePoint.WebPartPages.SPLimitedWebPartManager mgr = null;
mgr = currentSite.GetLimitedWebPartManager("SitePages" + "/" + page, System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared);
foreach (System.Web.UI.WebControls.WebParts.WebPart webPart in mgr.WebParts)
{
if (webPart.Title == "WebPartTitle")
{
//Get the Existing Audience for the Web Part
string audWebPart = webPart.AuthorizationFilter;
//If there is no Audience for the Web Part just add it
if (audWebPart == "")
{
webPart.AuthorizationFilter = ";;;;" + NewAudience;
}

//If already Audiences are exist for the Web Part then append new Audience
else
{
webPart.AuthorizationFilter = audWebPart + "," + NewAudience;
}

mgr.SaveChanges(webPart);
lblMessage.Text = "Succeeded...";
}
}


If we just set the value for AuthorizationFilter property, then the existing Audiences will be overwritten.
To avoid it first we have to check whether already audiences exists for that web part. If so we have to append it.

The SharePoint Server 2010 framework requires that three kinds of values be separated by a pair of semicolons (";;"). Multiple values for global audiences and SharePoint groups are delimited by commas, and multiple values for distribution lists are delimited by newline characters ("\n"). Therefore in the above example we used ";;;;" to provide valid string to the AuthorizationFilter property.

Friday, November 12, 2010

Retrieve SharePoint 2010 Permission Levels to a ASP.NET DropDownList

Below code shows how we can get the SharePoint permission levels and assign it to a ASP.NET DropDownList (drpPermissions)

drpPermissions.AppendDataBoundItems = true;

SPWeb currentSite = SPContext.Current.Site.RootWeb;


SPRoleDefinitionCollection permissionColl = currentSite.RoleDefinitions;

foreach (SPRoleDefinition permission in permissionColl)
{

drpPermissions.Items.Add(permission.Name.ToString());
drpPermissions.DataBind();
}

Monday, November 1, 2010

Issue with SharePoint Designer 2010...

While working with dashboard pages, a problem was identified.
First a dashboard page was published to a SharePoint 2010 site using PerformancePoint.
That page includes a SQL Server Report and a PerformancePoint Filter was applied for it. After publishing it to the SharePoint Site, the SharePoint page was opened using SharePoint Designer 2010.
Then some modifications for the layout of the page was done and saved it. But when I opened the page in the browser and tried to view the report, that PerformancePoint Filter was not applied for the report.
The issue is when we save the page using SharePoint Designer, the mapping between the report and the filter was lost.
To apply the filter we have to manually do the mapping.

Wednesday, June 30, 2010

Hosting ASP.Net Site in IIS in Windows Server 2008 R2

I am posting this post since I had some problems while hosting a site in IIS in Windows Server 2008 R2.

To add a web site in IIS, right click on Sites in the IIS Manager and select Add Web Site as shown in the below figure.




Then you will get the Add Web Site window as shown below and enter the required details.




Physical Path should be the folder which contains the web site files and it should locate inside the C:\inetpub\wwwroot\wss\VirtualDirectories folder.

Then click Ok to complete it.
But if you try to browse the site, it will give you an error.

To avoid it and browse the web site you need to do two things.
First one is, add the IIS_IUSRS and allow full permission. To do it right click on the site and select Edit Permission as shown in the below figure.



Then go to the Security tab in the new window and add the IIS_IUSRS user and allow Full Control. Then click Ok.

Still you cannot view the page because of an authorization error.

To avoid it you need to do the second thing.
Select the site and you will get a screen as below.



Then double click on Authentication and you can see enabled authentication types. In that Windows Authentication is displayed as Disabled. Right click on it select Enable as shown in below figure.



It will fix the authentication error and you will be able to browse the web site...

Tuesday, June 29, 2010

Increase Virtual Hard Disk Size in Hyper-V

In some situations we may need to increase the virtual hard disk size.
We can increase the size of the disk by using Edit Disk under Actions in the Hyper-V Manager.

Then you will get a window as shown below.



Select Expand from it and click Next.




Then add the New Size as shown below and click Next.




Then you will get a Summary as shown below and click Finish to complete it.




But you cannot use that increased space straight away. When you start the Virtual Machine, previous disk size will be avilable. To add the increased capacity, go to the Disk Management window by right clicking Computer and by selecting Manage. Then right click on the partition and select Extend Volume as shown below.




Then you will get the Welcome screen as shown below and click Next.




Then you will get the Extend Volume Wizard. Give the amount to be increased as shown below and click Next.





After that you will get the final screen as shown below.



Click Finish to add that disk space.

Friday, June 18, 2010

Connect to IBM DB2 Database Using Toad

We can use Toad to explore the DB2 Database Structures. Before using Toad we need to configure IBM DB2. To configure it open the Configuration Assistant as shown in the below figure.


Then you will get a window as shown below. Select TCP/IP as the Protocol and click Next.


After that we have to provide the details of the Host and the Service.


Insert those information and click on Retrieve button to get the Port Number.


Then follow the instructions as shown in below figures.


Then launch the Toad for DB2 as shown in the below figure.


After that create a new connection right click on the Connection Manager and select Create as in the below figure.


Then you will get a window as shown below.


You can see the available databases in the Database alias.


Then provide login details and the category.

Friday, May 21, 2010

Load Data using Change Data Capture (CDC)...

What is Change Data Capture (CDC)

Change Data Capture is a feature of the Database Engine which is available only in SQL Server 2008 Enterprise, Developer, and Evaluation Editions. CDC feature captures insert, update and delete operations applied to SQL Server tables and provides the details of the changes available in an easily consumed, relational format. CDC uses it's own tables to store data related to tracking tables with the same columns as in the tracking table and some additional columns to store important meta data.

CDC with SQL Server Integration Services (SSIS)

There may be a situation where we will need to store the data loaded from ETL Package to another table. That means in a situation where we maintain two tables to keep the same data. In such a situation we can use CDC.
Using CDC, we can load data to one table where CDC is enabled and execute a Stored Procedure to load data to the other table by capturing changed data using CDC.

Enabling CDC

Before enabling CDC for a table we need to enable CDC for the Database.
To that we can execute the below query

USE MyDatabase
Exec sys.sp_cdc_enable_db

Then using below query we can verify whether the CDC is enabled for MyDatabase.

SELECT is_cdc_enabled FROM sys.databases WHERE name = 'MyDatabase'

We can see the result of the above query as 1, which is the value for a CDC enabled database. Otherwise it will give the result as 0.

After that we have to enable CDC for each table. To do it we can use the below query to enable CDC for Table1.


USE MyDatabase
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Table1',
@role_name = NULL
GO

When we enable CDC for a table we can see a table is created under System Tables of the database as cdc.dbo_Table1_CT. Also there are two Table Valued Functions are created as
fn_cdc_get_all_changes_dbo_Table1 and fn_cdc_get_net_changes_dbo_Table1.


Function to Retrieve Change Data

We can write a SQL Function as given below to retrieve change data for a particular period for a particular table.


CREATE function cdc.uf_ChangeData_Table1 (
@start_time datetime
)
returns @Table1 table (
Column1 smallint
,Column2 smallint
,Column3 money
,CDC_OPERATION varchar(1)



) as
begin
declare @from_lsn binary(10), @to_lsn binary(10)

select @from_lsn = sys.fn_cdc_increment_lsn(sys.fn_cdc_map_time_to_lsn('largest less than or equal',@start_time))

select @to_lsn = sys.fn_cdc_get_max_lsn()


insert into @Table1
select
Column1,
Column2,
Column3,
case __$operation
when 1 then 'D'
when 2 then 'I'
when 4 then 'U'
else null
end as CDC_OPERATION
from
cdc.fn_cdc_get_net_changes_dbo_Table1(@from_lsn, @to_lsn, 'all')

return
end
go


In this function only the start time is given and get all the change data after that.



Stored Procedure to Execute the Function and Load the Data to the Table

Stored procedure was written to execute the earlier function to get the change data for a particular period and load those data to the destination tab. Stored Procedure is as below and it takes the input parameter as the start time and the required table.

CREATE PROCEDURE dbo.LoadChangedData (
@start_time DATETIME
,@table VARCHAR(50)
)
AS
BEGIN

IF (@fact_table='Table1')
BEGIN
MERGE INTO dbo.Table2 AS dest
USING (SELECT Column1,
Column2,
Column3 FROM cdc.uf_ChangeData_Table1(@start_time)) AS src

ON (src.Column1=dest.Coulmn1 AND src.Column2=dest.Coulmn2 )
WHEN MATCHED AND (src.CDC_OPERATION='U')
THEN
UPDATE SET dest.Column3=src.Column3

WHEN MATCHED AND (src.CDC_OPERATION='D') THEN DELETE

WHEN NOT MATCHED AND (src.CDC_OPERATION='I' OR src.CDC_OPERATION='U') THEN
INSERT (Column1,
Column2,
Column3 ) VALUES (src.Column1,
src.Column2,
src.Column3);

END
END


This stored procedure will do the insert, update or delete operations to the destination table considering the operation type.


Combining With the ETL

We can add a Execute SQL Task to Data Flow after OLEDB Destination component in the Data Flow and Execute the LoadChangedData stored procedure by providing required two parameters. We need to have the start time as the time before inserting the values to the first table. That means before the OLEDB Destination component.

This procedure will load the inserted data to an another table.


Friday, May 7, 2010

How to Combine Two Fact Tables in SSAS...

In SQL Server Analysis Services Cube, there may be a situation where we need to combine two related fact tables.
This kind of a situation may occur due to the same fact having different dimensions. For an example if we consider two dimensions as Branch and Region, in some areas Sales may happen just only in Regions and sometimes it may go to the level of Branch.

In such a scenario in the Data warehouse we need to have two different tables for those two type of sales like dbo.FactSalesRegion and dbo.FactSalesBranch.
But for analysis purposes we should have all the sales as one. Due to that we need to create one Fact by combining those two fact tables in the data warehouse.

We can do it in the Cube by creating a new fact table using Named Query.
Open the Data Source View by double clicking it in the Solution Explorer. Then right click on the design area and then select New Named Query.
Then provide a name for the combined fact table like FactSales and enter the SQL Query to get the union of dbo.FactSalesRegion and dbo.FactSalesBranch tables.

The query will looks like as below.

SELECT RegionKey, NULL AS BranchKey, Sales
FROM dbo.FactSalesRegion
UNION
SELECT NULL AS RegionKey, BranchKey, Sales
FROM dbo.FactSalesBranch

In the above query we add the non existing columns for each table as NULL and Union those tables, since those two tables which we get the union should have same columns.
The above query provides a new fact table as FactSales which has total sales.
In some complex situations this scenario helps to design the data warehouse.

There are few modifications to be done for the Cube to load data, otherwise it gives errors while processing due to those null values.
We need to modify some properties in those dimensions (in this example Region and Branch dimensions) and also need to change some settings in the dimension usage.

Double click on one of above dimension (assume DimRegion) in the Analysis Services Project. Then select the key (RegionKey) from Attributes and go to properties. In that expand the KeyColumns property and change the NullProcessing property to UnknownMember. After that you will see Key is underline by red color. To solve that issue, select DimRegion from Attributes and go to properties. In the bottom of that property list you can find a property as UnknownMember. Change the value of that property to Visible.

Then you should change some settings in the Dimension Usage area of the Cube. Double click on the Cube in the Solution Explorer and click on Dimension Usage tab. In that you can see the facts and the dimension used for those facts.
Select the RegionKey and click the button in the Right side to get the Define Relationship window. Click on Advanced button and you will get the Measure Group Bindings window.
Under Relationship change the Null Processing of the RegionKey to UnknownMember. Need to do this for all the facts which uses the dimension.

After that you can process the cube and load data.
Hopes this will help you as well...

Tuesday, May 4, 2010

Some KPI Values and Indicators are not shown in a PerformancePoint Scorecard...

While creating some scorecards for BI application, I faced a strange problem. That is, in that scorecard values and the indicators are not shown for some KPIs and others are ok.
The approach I used to create the scorecard is to Import SQL Server Analysis KPIs and after that add some KPIs from PerformancePoint. But the values and the indicators for the KPIs which are created by the PerformancePoint were not shown as shown in the below figure. PPSKPI is the KPI created in PerformancePoint.



I checked the procedure to create that scorecard and KPIs, and found it is ok. Finally I was able to find the reason for that issue. It is a very simple reason.
The reason is that the column names of those two KPI types are different.

For the KPIs which were imported from Analysis Services, those column names are Value and Goal and Status. KPIs which were created from PerformancePoint, those column names are Actual and Target.
When those column names were changed to Value and Goal and Status, the problem was solved...

Monday, May 3, 2010

Setting Up BI Server for Application Developments

Before start development for a Business Intelligence (BI) application, we need to have a proper environment. For that we should need to have a server which has the Windows Server version. For this post I'm using 64-bit Windows Server 2008 R2 installed server.

Server should has
  • .Net Framework 3.5
  • Internet Information Services (IIS)
  • Microsoft SQL Server 2008
  • Microsoft Office SharePoint Server 2007
  • PerformancePoint Server 2007
In the server we should configure it to install .Net Framework 3.5 and Internet Information Services (IIS).
To install IIS, go to the Server Manger and then select Add Roles as shown in the below figure.



Then you will get the Add Role Wizard as shown below



Click Next and then you will get the window to select Server Roles.
Put tick in the check box of Web Server (IIS) to install IIS.
Then click Install to install the selected roles.

After that Install the Microsoft SQL Server 2008. Then install the SQL Server 2008 Service Pack 1.
Then Install the Microsoft Office SharePoint Server 2007. In that select the Basic installation and after installation completes, configure it.

Before installing PerformancePoint Server, we need to install Ajax and some patches for SQL Server.
Those patches are list down below (for 64-bit computer).
  • sqlncli_x64
  • SQLServer2005_ADOMD_x64
  • SQLServer2005_ASOLEDB9_x64
  • SQLServer2005_XMO_x64
  • SQLSERVER2008_ASOLEDB10
You can get those from the Feature Pack for Microsoft SQL Server 2005 - December 2008 release in the http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en link.

Finally install the PerformancePoint Server 2007.

Server is ready for the developments related BI applications...

Saturday, May 1, 2010

Saving Changes is not permitted for a SQL Table...

When you try to save a change to a table in the SQL Server Databse, sometimes you will get a error message saying "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created..." as shown in the below figure.

Due to that you will not be able to change database tables.

You can solve that issue with just a one click. You can find Tools -> Options from SQL Server Management Studio and you will get a Window as shown below. Select Design and under that you can find there is a option saying Prevent saving changes that require table re-creation is selected as shown in the below figures.

Just remove the mark of it and try to save your changes. You should be able to save it unless there are no other issues....


Friday, April 30, 2010

#Error in SQL Server Report...

When we run a SQL Server report, it gives an #Error for text field which computes the summation.
In that report we get the values from an SQL Server Analysis Service Cube and get the summation of those values considering the group.
In the Data warehouse we used the data type for the taken field as money.

Finally I was able to find the reason for the error occurs during the summation.
In the cube, we assign the value 0 for the field if it is null. Problem arises due to it.
When we change that to 0.00 instead of 0, it works.
I don't know any exact reason why it happens like that. It may be due to some problems while converting to the data type it may convert it to different data type like int and it gives errors while summation.
Anyway I think this will help you as well...

Add Row Number to a SQL Report contains Groups

We can add row number to a report which has groups by adding below expression to Row Group of the Row Number column

= RunningValue(Fields!CountColumn.Value, CountDistinct, "MyDatasetName")

To add Row Number to the Details Group, select the field of the Row Number in the Details Group and add the below expression.

= RowNumber(Nothing)


Friday, March 26, 2010

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...

Thursday, March 25, 2010

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.

Tuesday, March 16, 2010

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 http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en 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 http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en 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...

Saturday, March 6, 2010

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...

Friday, February 26, 2010

SQL Server 2008 Installation Failed....

While trying to install SQL Server 2008 in Windows XP, I got a error in "System Configuration Check"
The error is Rule "Performance counter registry hive consistency check" failed. The performance counter registry hive is corrupted.
The problem occurred since the installation trying to read the above register value and does a calculation on Last Counter and Last Help.
We can solve this issue by changing the those Last Counter and Last Help values

Type regedit in the Run and press Enter. It will open Registry Editor window.




Open the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009.
Then you will get a screen as shown in below figure.



Double click on Counter record to get a window as shown below.



Then scroll to the second last line and you will see a number like 3422 as shown in the below figure and remember it.


This is the Last Counter number that the system uses.

Then do the same thing for the Help record too and it is the Last Help number that the system uses.

Then go to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib. The screen is shown below.



Double click on Last Counter and you will get a window as below.



Then select Decimal radio button and put the value taken from the earlier Counter record for the Value data field.
Do the same thing for Last Help which is in the same window.

It will solve the above mentioned issue and do the installation again.
You will be able to pass from that System Configuration Check...