Saturday, June 25, 2011

Configuring SharePoint 2010 FBA

I have not been making good acquaintance with SharePoint 2010. Time to strengthen my relationship with my old pal…

Just few days back I were tasked to estimate some effort in building a simple extranet website. SharePoint 2010 is one of the option considered. But the user login database has to be separate from the organisation’s AD. Therefore, Form Based Authentication will be required. Now, setting up FBA in in SharePoint 2010 is different from MOSS 2007. So a refresh on that skill is required, by trying it out myself on my SharePoint 2010 test servers.

First step is to start with getting FBA to work in the most simplest form possible. That means making use of the good old ASP.NET Membership database which comes with .NET 2.0. That part of the work are still the same, but the similarity ends there. After reading a few blogs that doesn’t help me much, I found this blog entry from Donal Conlon, which provided me the best step-by-step I saw so far, which allow me to successfully get the FBA work on my SharePoint test servers. Thanks a lot!

Also, I found that CodePlex has some webparts for FBA user management.

And, some more in-depth Membership, Role and Profile stuff from 4GuysFromRolla, which provide some good foundation refresh for me as well.

Prevent SSRS Parameter Auto Postback, again…

In my last entry (wow, that was over a year ago...), I was troubleshooting some SSRS parameter post back bug that was never fixed in SSRS 2005, and manage to worked around it.

In my new assignment, me and my team are building SSRS 2008 R2 reports. The new version of SSRS 2008 R2 now has better control on whether changing a parameter causes postbacks to the server or not. This can be set at per report parameter level, via the advanced properties:

image

However, long time enemy came back again to haunt me and my team. Even if the above option were set to Never refresh, the postback is still happening. After some pocking around on and off for several days, finally we found out the reason.

We discovered that some other reports which has some parameters that does not have default values set, does not perform postback when its value were modified. The default values were set directly by typing in the default value, as per below screenshot.

image

Question is, should we enter the “Label” name here, or the “Value” value here? (Label is what the user sees, Value is what is used as the parameter value at the back to pass into SELECT statement). Anyway, it doesn’t matter. As long as you set a default value like this, it will perform a postback.

To solve this problem, we can up with a workaround (yes, another one. When will MS solve this once and for all?). We create a Dataset to return desired single default value (e.g. DefaultCountry Dataset). The Dataset doesn’t even need to query the database, as long as it returns the default value that you want (e.g. SELECT ‘MY’ as CountryName). Then, use it to set the value to the Parameter as the default value.

image

With that, no more auto postbacks when selecting report parameters.

Tuesday, March 2, 2010

Prevent SSRS Parameter Auto Postback

For some reason, the SSRS 2005 report that I’ve deployed on MOSS 2007 perform auto postback whenever a particular drop down parameter value is changed. It’s just a normal text drop down parameter with a default value. Some online post says not to use the “=SomeText” syntax, but mine is just the text directly set as the default value.

 

Luckily my colleague gave me the tip that the sequence of parameters might cause this behavior. Although there are no dependency between all my parameters, he advised me to put parameters that are dynamic at the top first, follow by the static ones at the bottom. That solved my problem!

Friday, January 15, 2010

Pivot/Unpivot or Transpose in SQL 2005 and SSIS 2005

Lately I’ve been working on a project to bring a suite of Excel Reports to the new age, via SSIS and SSRS. (yeah, boring stuff, not mentioning the times spent to understand the user actions, VBA macros, cell formulae etc, such a pain!!!). Anyway, one of the existing manual actions from the users of the Excel which I need to emulate in the new system is the transpose copy paste action. At last, something that I haven’t done before and it’s interesting to me, in a sense.

After some research, there’s actually quite a lot of way to achieve the result, either using SSIS Data Flow task of Pivot/Unpivot, or just pure Transact-SQL approach.

The SSIS Method

Can’t explain better than what this blog has already mentioned. It involves a clever use of both Pivot and Unpivot Data Flow tasks. The example given provides the basic concept for me to tackle my slightly complex scenario.

Transact-SQL Method

Again, no point for me to reinvent the wheel. This blog has given the best options available. On top of that, MSDN also provide good examples on how to use the PIVOT and UNPIVOT command.

My Implementation

After the research, time to get the job done. So here’s I need to do:

I have an Excel table that looks like this:

Date: 15/1/2010

Category

Product A

Product B

Product C

Product D

In

Out

In

Out

In

Out

In

Out

Category 1

Category 2

Category 3

I need to transform it into this first:

Date

Product Name

Category

Balance

15/1/2010

Product A

Category 1

{In-Out}

15/1/2010

Product B

Category 2

{In-Out}

15/1/2010

Product C

Category 3

{In-Out}

Then into these tables for SSRS consumption:

Product A

Date

Category 1

Category 2

Category 3

15/1/2010

{Balance Value 1}

{Balance Value 2}

{Balance Value 3}

Product B

Date

Category 1

Category 2

Category 3

15/1/2010

{Balance Value 1}

{Balance Value 2}

{Balance Value 3}

Product C

Date

Category 1

Category 2

Category 3

15/1/2010

{Balance Value 1}

{Balance Value 2}

{Balance Value 3}

I decided to use both SSIS and Transact-SQL PIVOT for my solution. Reason is to avoid having to keep multiple Product tables. In the example shown above, I would need 3 tables for each product. Imagine having more than that…

Part A: SSIS Unpivot

First off, here’s my SSIS Data Flow:

1. Because I can’t Unpivot both [In] and [Out] columns at the same time (SSIS limitation), I have to Unpivot them separately, then join them back later through the Merge Join task.

2. Merge Join task requires all the data flow coming into it sorted with the same key(s). It will sort of being smart to detect if the data flow reaching it is sorted or not. If it is not, then it will not run and you'll get an error. This is typically solved by using the Sort task first, but since I know my data from the Excel is sorted, I avoid using the performance taxing Sort task. Instead, I set the Unpivot task’s output properties of IsSorted to True, then, in the Unpivot Output columns, set the SortKeyPosition of the sorted column to 1. (If more columns were used to sort, set the other columns with SortKeyPosition=2 or 3 or so forth). Screenshot below shows the Avanced Editor of the Unpivot task, where I set the IsSorted property.

3. The resultant join will have some invalid rows, due to merge columns in the Excel. Therefore, I’ve used the Conditional Split task (Get valid data) to exclude the unwanted columns.

4. The rest are simple stuff of adding the derived Date column, calculations of {In}-{Out} and data conversion, before loading into Production table.

Part B: SQL PIVOT

Next is the PIVOT SQL command (following the sample in MSDN)

SELECT Date, [Category 1], [Category 2], [Category 3] FROM

(

SELECT Date, Category, Balance

FROM Table01

WHERE ProductName = ‘Product A’

AND Date = ‘20100115’

) AS SourceTable

PIVOT

(

AVG(Balance)

FOR Category

IN ([Category 1], [Category 2], [Category 3])

) AS PivotTable;

So it’s done. Probably not the best apparoch, but hey, it works anyway J.

Setting up SharePoint 2010 development environment on my Windows 7 laptop

SharePoint 2010 is now well into the Beta 2 phase. As a consultant and developer for SharePoint solutions, it’s time for me to dive in! To do that well, I need to have a development environment to play around on top of reading blogs and PDC materials.

Given the choice, I would love to setup the a virtual box on my work laptop, but the hardware specs of 4GB and 2.0GHz seems to be at the border line of usable performance. Besides, I don’t want to reinstall my newly imaged Windows 7 workstation laptop to Windows 2008 R2 for Hyper-V. I’m not gonna buy VMWare either. I also tried Sun’s VirtualBox which support x64 guest OS, but it’s really really slow…

That leaves me no choice but to install it on the host machine itself, since SharePoint 2010 now support installation on Windows 7 and Vista, or, more or less. I says so because it’s not so straight forward as installing it on Windows Server 2008. However, it’s much more easier than doing the same for SharePoint 2007.

Being a late starter, plenty of resources already available on the Internet. Many people hit into some problems getting it to work on Windows 7. Some straight forward, some not. Here’s the steps I’ve taken to complete the installation until Site Collection creation is working:

  1. Followed the steps in Setting Up the Development Environment for SharePoint Server. (Why can’t it be easier?)
  2. As pointed out in the SharePoint Team Blog, applied the Hotfix KB976462.
  3. Had a UserProfile Exception, due to TimoutException. This blog helped me out, which is to delete some cached ASP.NET files in C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files\d31735f8a1b34f3a8263707181c7e298\5902e8c4\679035c5\.
  4. Some other errors that I also encountered during the configuration wizard process are:

a. Exception: Microsoft.SharePoint.SPException: User cannot be found.

b. Unable to create a Service Connection Point in the current Active Directory domain. Verify that the SharePoint container exists in the current domain and that you have rights to write to it.

These errors were probably resolved by applying the patch in #2 or the steps in #3.

So now I got a working test bed to try out those exciting features that I’ve read for months!

Thursday, June 18, 2009

SharePoint 2007 SP2 Resources

I might be involve in a SP2 deployment. Guess I better get myself ready for it. Following are stuff that I need to crunch through first:

 

Announcement of MOSS 2007 SP2 and WSSv3 SP2

http://blogs.msdn.com/sharepoint/archive/2009/04/28/announcing-service-pack-2-for-office-sharepoint-server-2007-and-windows-sharepoint-services-3-0.aspx

 

Immediate Issue after deploying SP2

http://blogs.msdn.com/sharepoint/archive/2009/05/21/attention-important-information-on-service-pack-2.aspx

Step to re-enter license key - http://support.microsoft.com/kb/971620

 

April Cumulative Update (CU) (with timeline diagram of update schedule)

http://blogs.msdn.com/sharepoint/archive/2009/05/13/april-cumulative-update-packages-ready-for-download.aspx

 

Updates Resource Center @ SharePoint Server TechCenter

http://technet.microsoft.com/en-us/office/sharepointserver/bb735839.aspx

 

Deploy software updates for Office SharePoint Server 2007

http://technet.microsoft.com/en-us/library/cc263467.aspx

 

How the upgrade process works

These article explains In-place and Gradual upgrade approches

WSSv3 - http://technet.microsoft.com/en-us/library/cc288775.aspx
MOSS 2007 - http://technet.microsoft.com/en-us/library/cc262483.aspx

Running VPCs with different date/time

I have plenty of VPC images that were built on trial version for testing and demo purpose. So when these images lives longer than it’s trial period, you’ll need to trick it by changing the host machine’s time. That’s no good since it’ll affect your applications on the host machine.

 

There’s a quick method to either disable the time synchronization between the VPC and the host machine, or  hardcode a specific date/time for the VPC. Both can be done via modification on the vmc file.


1. Disable the time synchronization:

 

Under the following mouse configuration:
<mouse>
<allow type="boolean">true</allow>
</mouse>

Add this:
<components>
<host_time_sync>
<enabled type="boolean">false</enabled>
</host_time_sync>
</components>


2. Set the desired date/time:

You have to find the time_bytes value inside the .vmc file, which looks like this one:

<time_bytes type="bytes">27003200110001201008</time_bytes>

After finding it, set the desired date/time value according to the following specification:
Digits 1 - 2 contain the seconds value.
Digits 5 - 6 contain the minutes value.
Digits 9 - 10 contain the hours value.
Digits 15 - 16 contain the day value.
Digits 17 - 18 contain the month value.
Digits 19 - 20 contain the year value.

In the above example, the date/time value is 11:32:27, 20/10/2008

After making the above 2 changes, save the .vmc file, and the guest operating system will start in the same date/time that you set in the time_bytes value.

 

Reference: http://www.nirsoft.net/blog/2008_12_01_archive.html