Sharepoint server 2010 Insights

You may or may not have heard by now that the business intelligence capability in SharePoint 2010 now has a platform branding called INSIGHTS.

I will blog more about the features in my “down time” at the SQL PASS conference next week, but I wanted to put the links out to everyone so you can start ramping up on what is coming inside SharePoint 2010 with respect to business intelligence.

First and foremost, the official Insights link: http://sharepoint2010.microsoft.com/product/capabilities/Insights/Pages/default.aspx. Lots of information and resource links posted here - -a great way to take a top down look at what the Insights platform is all about.

If you have completely been ignoring the news and the gossip, then you probably have missed the two biggest architectural features in the Insights platform will be PerformancePoint Services and Excel Services support of PowerPivot (formerly Project Gemini).  PerformancePoint Services will utilize the new application services architecture that is now part of SharePoint - -this allows for far greater flexibility in deployment and most importantly, security. 

PerformancePoint Services links:

• http://technet.microsoft.com/en-us/library/ee661741(office.14).aspx

1.  
   • http://technet.microsoft.com/en-us/library/ee424392(office.14).aspx

Excel Services: http://technet.microsoft.com/en-us/library/ee424401(office.14).aspx

Report Builder 3.0: http://technet.microsoft.com/en-us/library/bb418434(SQL.10).aspx

Technical Diagrams: http://technet.microsoft.com/en-us/library/cc263199(office.14).aspx

So, keep reading and don’t get left behind.  If you landed here and you are more interested in the other features of SharePoint and not business intelligence, visit my fellow Catapultian Matt’s post here. I will, of course, encourage you to stay here and become more interested in BI, but I’m not into forcing my opinion in anyone, I’ll leave that to the media.

October 28, 2009 15:57 by Sid
E-mail | Permalink | Comments (5) | Comment RSS

Installing SSRS for SharePoint, the fun way

I figured the search engines would do a good job of delivering the answers to these questions, but as I have seen this theme reoccur many times over the past few weeks in the MSDN forums, I am going to write out the steps for everyone (if nothing else, at least I'll just be pasting a link to this versus copying the steps all over again in forum responses).

For many people installing the SSRS plug in for SharePoint/WSS (both SSRS 2005, but more likely 2008), you can get these annoying errors (this error can be found in Root:\Users\YourLogin\AppData\Local\Temp and will be prefixed with RS_SP):

For some of you, this error may become a common nuisance when installing the SSRS plug in for MOSS/WSS 3.0 for both SSRS 2005 and 2008:

******* User does not have permission to add feature to site collection: http://<SharePoint URL here>:36076.

This can be particularly frustrating if you are logged in to the box under the farm admin account and have local admin privileges.

To work around this error, follow these steps:

1. Double check that you have a site collection set up on the farm, not just the web app

2. Log into the server you are installing the Reporting Services plug in to with credentials that are the farm admin for the MOSS install

3. Run the CMD tool with Administrator privileges (also called elevated permission)

4. Navigate to where you have the sharepointrs.msi and type sharepointrs.msi SKIPCA=1

5. Navigate to your Temp% directory to find the unpacked rsCustomAction.exe file

6. In the CMD tool, execute rsCustomAction.exe /i

7. After the install, you will probably need to navigate to Central Administration > Site Actions > Site Settings and under the heading Site Collection Administration click Site Collection Features.

8. From there, click activate on Report Server Integration Feature

9. The settings for SSRS will now have their sub section on the Application Management tab in Central Admin

References:

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

July 7, 2009 13:09 by Sid
E-mail | Permalink | Comments (16) | Comment RSS

64 bit Report Builder 2.0 Installation Issues in SharePoint Integrated Mode

If you wish to upgrade to SSRS Report Builder 2.0 and you are on a 64-bit environment, you may run into this issue when running the April 7, 2009 download:

Text (so it is search-able): Reporting Services Add-in for SharePoint products and technologies is not installed. It must be installed before you can install the SQL Server 2008 Report Builder 2.0 for SharePoint.

If you are like me when you got this error, you first thought reaction is: "but I did! What is going on? I know I have everything installed!".

Relax, it is not your fault. This time (provided you are like me and on a 64 bit platform).

To confirm, check out this thread on Microsoft Connect to confirm your sanity and also vote this issue as a priority. In addition to other users adding their comments about, some have posted work-arounds to the issue. I find a couple of them rather complicated and opted for another method that has thus far suited me very well. Below are the steps:

1. Download the stand-alone install for Report Builder 2.0 and install it on the app server/server hosting SSRS.
2. Go to your site's Central Admin and click on the Set server defaults link

   

3. Modify the Custom Report Builder launch URL. In my example, the URL value is: http://localhost/ReportServer/ReportBuilder/ReportBuilder_2_0_0_0.application

   

4. Publish a Report Data Source or Report Model content type to your site, then from the Create actions on the list, select new report builder report. If you get the 2.0 version, you have installed it and set the Central Admin settings correctly.
5. Once in Report Builder, there is one last setting. Click the round start button in the upper left corner and then select Options. The Report Builder Options will pop open and you will need to fill in the top option with a value so that you can access the data sources from the lists on your SharePoint site.

   

While this works, it is not the most ideal in my mind as the user has to enter the SharePoint site URL, making usage of Report Builder not entirely intuitive. I did find a Report Builder config file in the install directory with an option to set this value - -hopefully making it effective for any launch of the app, however I did not find this to be the case. Any value inputted there was ignored by the application URL, however only recognized by a direct launch of the executable from the install directory.

If I find how to modify this setting server side, I will be sure to post, but for the time being, here is a simple work around for enabling Report Builder 2.0 in 64 bit SharePoint integrated environments.

June 16, 2009 11:13 by Sid
E-mail | Permalink | Comments (15) | Comment RSS

Gather stats from SharePoint

I was not actually looking for this, but that does not mean it is not incredibly useful.

I was actually looking for what was available from the PerformancePoint Team blog in the way of updates to the SDK documentation, or heck, an SDK versus just a single page in the MSDN canon, when I came across this code sample posted on code.msdn.microsoft.com (what the real difference between this site and codeplex.com is, I do not know, but it gets confusing having both).

This code sample, titled "Gather information from SharePoint to a database for reporting", does not come with a lot of documentation or instruction, but by odd coincidence, it seems I found this on its publishing day – today, May 11. The code sample consists of two parts, the C# console app project and the database.

To get this tool up and running:

• Download the project file for the app and the database
• Unless you are in France or have French collation set up on your server or personal machine, I would recommend hand editing the script to remove that language collation……………Or just do what I did and create a database called Stats and used the BAK file included to restore to (the DDL does not have any table create scripts?)
• Start up VS 2008 and open the unzipped project. From here you can either compile so you have an executable or debug through it and watch it run

One important note: This app is not yet configured for remote execution, so it has a dependency on being run directly on the SharePoint server in order to gather stats. SQL Server configuration is just a connection string in the app.config, so that can be a different box/remote server.

This handy little app produces quite a few usage statistics on a SharePoint farm, with the most rich being at the site level. Be interesting to see where this goes next, I have no immediate need for it, but I figured it is interesting enough of a find to post and share!

May 11, 2009 20:23 by Sid
E-mail | Permalink | Comments (2) | Comment RSS

SSIS Adapter for SharePoint

Kevin Idzi of Microsoft was kind enough to leave a comment on my Extracting SharePoint Data using SSIS post informing me of Integration Services enhancements posted on CodePlex.

I had been delaying posting as I wanted to have a full sample showing it in use, but between work and buying a new house recently, I have not had the time to put together my virtual machine images for a complete demo.

The community sample has the following components available to download (in separate install packages):

• XML Destination Pipeline component
• Regular Expression Flat File for regular expression based flat file parsing
• Delimited Flat File Parser, for files with rows that are missing column fields
• Package Generation Sample
• Hello World Sample for demonstrating using the UI API
• SharePoint List Source and Destination – this component was written by Kevin
• WMI Source Component

All of these components are available for download here.

The base requirements for installing these components are .NET 3.5 and SQL 2008 Integration Services.

Once you run the MSI, you will be able to select the two new adapters from the "Choose Toolbox Items" dialogue:

One of the nice features that Kevin included in this adapter is the ability to execute CAML queries against the SharePoint List:

If you do not know how to write CAML or are not good at it like I am, then use the U2U CAML Query Builder written by Patrick Tisseghem; I've got a quick tutorial here: CAML Query Builder.

If you have any feedback about using this SharePoint List Adapter with SSIS, please be sure to leave comments or suggestions on the CodePlex site for Kevin and his team.

September 19, 2008 13:07 by Sid
E-mail | Permalink | Comments (8) | Comment RSS

Extracting SharePoint Data using SSIS

!!!!   Follow-up to this posted September 19, 2008: SQL 2008 SSIS Adapter for SharePoint from Microsoft Dev team posted to Codeplex !!!!

SharePoint and WSS are fantastic in that almost everything is driven by Web Services. For the data and reporting gurus, our job is a lot easier when it comes to extracting data from the mini-applications and workflows that the business community creates inside a WSS or SharePoint Portal framework – though not completely straight forward, so that is what we’ll discuss today: using SSIS to extract data from WSS lists using the out of the box WSS web services.

In this example, we have a user community that has data in a WSS list that needs to be extracted so it can be reported on. The data in this list represents a collection of information about a business process.

One of our major limitations in using out of the box SSIS features is the Web Service Task. If you look at Books Online, article: http://msdn2.microsoft.com/en-us/library/ms140114.aspx, the first note indicates that the Web Service task supports primitive types only. If you look at the WSDL for our particular method we’ll be using, GetListItems, you can see from the WSDL below that we will have a problem:

To get around this we’ll use a proxy since the Web Service task is the only item currently that can use a web reference. For our first step, we will use the same tool the VS 2005 uses when you add a web reference to a web or windows project: wsdl.exe. We’ll create our class by executing the following from the command line (wsdl.exe should be located in your C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin directory):

Note that you will need to do this again for any sub-site that you are targeting for your import as part of the WSDL description is the location of the service.

Our SSIS package will have three simple control flow steps:

-         Script Task

-         XML Task

-         Data Flow Task

Script Task

After you run this command, open your newly created vb file (I normally code C#, but the SSIS script task only uses VB). In your script task, we are going to make a new class reference. Right click on the solution, go to Add > Add Class. You can use the same name as newly created file from wsdl.exe or something that will more appropriately fit given your project. Once you create the new class file, erase any text/code automatically generated and then paste the content of your wsdl.exe outputted file into this code file in your project. Add references to the System.XML and System.Web.Services.

After you have completed this step, you are ready to consume your new class and mimic the Web Service SSIS task.

Here is sample code from the default ScriptMain file. My code in this sample uses the proxy class Lists (generated by wsdl.exe) to call the service, pass the necessary parameters and then save the output to an xml file. Note that for the view name for a list, you have to pass the GUID. It seems that the method was not set to return values using the common view name.

One of the easiest ways to grab the GUID is to go to modify the view you want to use to pull the data from and look in the URL, the portion of the query string after View= has the GUID for this view.

Another way to do this is to use the Web Service Task against the Views web service in WSS/SharePoint, call the method GetViewCollection, pass the List name as a parameter and you can get an XML output with all the views and GUIDs associated with a list item.

After implementing the code above, your Script Task is complete and your output will be an XML file of all the data from the list given the view you hit. An important point to note is to set your rowLimit parameter. The others can be blank or null, but if the rowLimit is not set, then by default you will only get the data that displays in the default view paging size, which is usually 100 records. By setting it to a figure larger than the collection, you will get the entire collection of records.

XML Task

The SSIS XML Source in the Data Flow section of your project does not like XML files that have multiple namespaces, which is almost a guarantee when you are retrieving data from a SharePoint List.

The XML Task is pretty basic in that it strips the extraneous namespaces from the XML file. I got this from Ashvini Sharma’s blog when trying to solve the same problem in hitting RSS feeds for a real estate project. Here is the link to his post:

http://sqljunkies.com/WebLog/ashvinis/archive/2005/05/25/15654.aspx

Right click on the XML Task, choose edit, and then we will set the properties on the task as such:

Under the SecondOperand property, paste the following XSLT:

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

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" indent="no" />

<xsl:template match="/|comment()|processing-instruction()">

<xsl:copy>

<xsl:apply-templates />                        

</xsl:copy>           

</xsl:template>           

<xsl:template match="*">                                    

<xsl:element name="{local-name()}">                       

<xsl:apply-templates select="@*|node()" />                       

</xsl:element>           

</xsl:template>           

<xsl:template match="@*">                       

<xsl:attribute name="{local-name()}">                                   

<xsl:value-of select="." />                        

</xsl:attribute>           

</xsl:template>

</xsl:stylesheet>

I have the FileConnection set to the file that my first task outputted, and the operation saves the results so that I end up with a clean version of my outputted WSS/SharePoint list data.

Data Flow Task

Setting up the import can be one of the more painful tasks, depending on the number of columns you have on the view and list your are hitting. The XML that you will have after pulling the data will have GUIDs for all of the user defined columns in your list, which probably is where most of your data is.

To know exactly how the GUIDs in your XML export map up to columns on your list, you can export the schema for SharePoint. In the Lists web service, hit the method GetList and pass it the name of the List you are using. Your output will have the schema of the list. Here is a sample for one column:

 <Field ReadOnly="TRUE" DisplayName="TestColumn" Name="xd__x007b_8CEEC711_x002d_0B7C_x002d_4C6A_x002d_9F82_x002d_5584303A997F_x007d_" XName="{8CEEC711-0B7C-4C6A-9F82-5584303A997F}" Node="/my:myFields/my:TestList/my:TestColumn" Type="Text" ColName="nvarchar18" /> 

The XName element has the GUID for the column that will show up in the data export. You can use the output from the GetList method to build mappings for your export.

Your Data Flow Task at a minimum will have two objects: an XML Source and your OLE DB Destination. I would recommend putting a Data Conversion object between the two – that way you can save on space on the insert into the database and have a way to redirect failed rows for analysis should you get something really strange in your incoming dataset.

That rounds it out; please feel free to post any questions.

October 25, 2007 04:04 by Sid
E-mail | Permalink | Comments (17) | Comment RSS

MOSS Performance Issue on site creation

Working on a particular MOSS implementation project, we ran across an issue with performance when new sites were provisioned. The act of creating a new site within the site collection literally brought the site performance to its knees and it didn't recover for several minutes. User experience little to no response from sites in the collection.

I am working on a detailed document to show how to create the issue in a test environment, but here is the nitty gritty detail on what happened: After monitoring all the servers, we noticed disk I/O on the SQL server looked high, not abnormal, but high. We ran SQL Profiler during a site creation event and pulled the results into ClearTrace, a free .NET SQL log analyzer.

We immediately noticed that the stored procedure: proc_GetTpWebMetaDataAndListMetaData was called a large number of times during the site creation process (in this case due to the site collection size, it was in the thousands per site created). The average for the number of calls to this procedure held no matter the site template chosen.

After reporting this issue to Microsoft, the workaround was the following: change the setting in the master page for the site collection to MaximumDynamicDisplayLevels="1". This client had the setting on 3. It appears that the dynamic display level of the top menu navigation drove how many lists and web pieces that the site creation event was looking at during it's build process. I do hope that this is an issue that will be fixed in a service release, however no messaging to that effect has been given. For the time being, if you are facing a similar performance issue during site creation within a site collection, this is one area you can look to in trying to find a root cause.

Look for me to post the details on how to recreate this issue. Thanks!

September 20, 2007 02:07 by Sid
E-mail | Permalink | Comments (0) | Comment RSS