Kasper de Jonge BI Blog

I was playing around with my new SharePoint machine and when i openend the PowerPivot gallery i asked myself how can we manage the sort order of the powerpivot sheet ourselves. Luckily the PowerPivot gallery is a just a regular SharePoint list.

This is the gallery now: 

when you look at the list properties you see:

It will be sorted on the modified date. What we are going to do is add a new column to the list where we are going to sort on, click create column:

And add a new numeric column:

We have some default views available:

The All documents view shows us the list as a traditional list, click the modify view and select the sortorder to show up in the view. You now can edit the properties of an item and set the sortorder:

When you go back to the Gallery view, click modify view again and select the sortorder as column to sort on, this gives you your own way of sorting the gallery:

Found this post at  Dan English’s BI Blog about SharePoint 2010, he found these intresting artikels at at  the MSDN Downloads:

Discusses the business intelligence tools available in SharePoint Server 2010

• Getting started with business intelligence in SharePoint Server 2010 (download site link)

This one is a poster, so feel free to blow it up and put it up on your wall;)

Interested in what the upgrade path to SharePoint 2010 will look like?  Take a look at this Visio diagram – Microsoft SharePoint Server 2010 Upgrade Planning.

With SharePoint 2010 on the horizon Microsoft released more information about SharePoint 2010 and the integration with BI at the SharePoint conference last week.
They are so serious about BI and SharePoint they even released a name for the subject calling it “SharePoint Insights”:

SharePoint Insights enables users to find the information they need across unstructured assets (blogs, wikis, presentations, documents) and structured assets (reports, spreadsheets, analytical systems). Empower users to discover the right people and expertise to make better informed and more agile business decisions.

They released a blog specific to the subject: http://blogs.msdn.com/sharepointbi where you can find more information about SharePoint Insights. You can also check out the official SharePoint Insights page and download the new datasheet and whitepaper to help you start thinking about what SharePoint 2010 can help your organizations use Business Intelligence.

The new SharePoint Insights will also incorporate PerformancePoint dashboards now called PerformancePoint Services:

and of course the new self service BI project formerly called Gemini, now named PowerPivot:

Something called Visio services (also from PPS) in which you can dynamicly show Visio documents:

In SP 2010 they have a successor for the SharePoint 2007 Business Data Catalog (BDC) called the Business Connectivity Services (BCS), It provides out-of-box features, services and tools that streamline development to deeply integrate external data and services. What the direct added value for us BI’er is i’m not sure, but surely these will be new sources to extract data from.

And there is even good news for your server admin: they made the installation a walk in the park (according to a Donald Farmer interview)

Also, we provide a really nice setup. You run a SQL Server 2008 R2 setup and if you don’t have a SharePoint farm we’ll say “Insert your SharePoint disc and we’ll go ahead and install the farm for you and configure it with all the defaults that you need for Gemini.” So it’s actually very cool. Even if you’re not a SharePoint administrator, the setup will walk you through the process of setting up the SharePoint farm and configuring it, which we know could be a challenge if you’re not familiar with it.

I really hope they can back this up, installing Reporting services with SharePoint 2007 is NOT easy.

Microsoft is really putting more and more into SharePoint and want a tighter integration with BI, whether you like it or not. I think as MS Partner it opens a lot of new doors so i suggest to keep an eye on SharePoint the following months.

SQLCat has a great blog post about installing SSRS with Sharepoint integration:

This technical note describes how we designed and implemented a business intelligence solution that utilized a server farm containing Microsoft® Office SharePoint® Server 2007, Microsoft Office PerformancePoint® Server 2007 Monitoring Server, and Microsoft SQL Server® 2008 Reporting Services in SharePoint mode, all running on Windows Server® 2008 R2 and with all servers and applications configured for Kerberos authentication. In this technical note, we discuss the design requirements for this business intelligence solution, its logical architecture, the challenges we faced in architecting and implementing this solution, and our resolutions to these challenges.

wish we had this sooner, implementing this is NOT easy. See also my colleague Marc Valk excellent blog post about Sharepoint and SSRS integration over multiple servers (not the default all on one box) with the famous double hop issues and how to fix them. Read it here:

http://www.marcvalk.net/2009/04/sharepoint-and-ssrs-integration/

Most people who have installed a Reporting services with SharePoint know it’s NOT easy and takes a lot of tuning and in depth knowledge of SharePoint, Kerberos and Server configurations.

Microsoft recently released a Troubleshooting guide which you can find here:

http://msdn.microsoft.com/en-us/library/ee384252.aspx

The MS gemini team posted a new blog with a overview of Gemini, what was new to me was the following part:

Sharing Gemini Applications

While many workbooks are built for personal use, some are worthy of being shared across a workgroup. Here again, Gemini works the way Office users do. Since Gemini data is stored within an Excel document file, any way to move that document – through file shares, emails, publishing to SharePoint, etc. – transport the Gemini contents along as well. Users without the Gemini addin can browse the data, those with the addin get the full experience. Just as Excel and Gemini light up together, Gemini also extends SharePoint capabilities in several ways.

Report Gallery

For the more visually-inclined amongst us, a flat SharePoint list leaves something to be desired. File names, data last updated and by who are useful but only tell part of the story. Gemini provides Silverlight based skins that present different views on document libraries. These views show snapshots of the contents of documents. In the example below, we see two workbooks with two spreadsheets within them:

These snapshots are also live links in that clicking on a thumbnail of the a worksheet will take users directly into ECS with the worksheet loaded.

Scheduled Data Refresh

The Gemini model embedded within the spreadsheet keeps information about where data came from. Once published to SharePoint, users can specify schedules for the data refresh operation so the workbooks use the resources of the server to stay fresh.

And what was even more impressive:

Using Gemini Applications as Data Sources

Once published to SharePoint, Gemini models embedded within workbooks appear as an Analysis Services databases! This means any AS client tool – Excel, Report Builder, etc. – can connect to this database as if it were on just another AS server. The only difference for these clients is use of a URL to the document stored in SharePoint instead of a server name. Gemini services running on SharePoint handle loading the right database, managing its lifetime, and transparently redirecting client queries to the right database on the right server.

This gives some incredible new options, but i wonder what it will take of server perfomance (memory ?) when you have 20 of these models on your server.

Read the entire preview here:

http://blogs.msdn.com/gemini/archive/2009/08/24/overview-of-gemini-features.aspx

UPDATE:

after posting the question about the performance on the original msdn blog post i got an answer from one of the Gemini team members:

You’re correct, the Gemini embedded data engine loads data into memory. However this is only while the models are in use. Gemini’s SharePoint services manage the lifetime of these models and move these in and out of the SP content database transparently from the end users, the only realization users might have is the first time they connect to a model it takes slightly longer because we’re extracting the workbook then extracting the AS database and loading it up in memory.

Setting up security for SSRS in SharePoint integrated mode can be a bit tricky, particularly if you want to set up some of your users to only be able to run reports, but not to be able to modify or change them. I found a great post explaining how to create a Reporting group in SharePoint:

http://agilebi.com/cs/blogs/jwelch/archive/2009/07/10/view-permissions-for-reporting-services-in-sharepoint-integrated-mode.aspx

On MSDN  you can find an very nice article which provides tips for and answers to frequently asked questions for using Microsoft Office SharePoint Server 2007 with Microsoft Office Excel 2007 and Excel Services. It gleans community-contributed information from the SharePoint – Excel Services forum on the Microsoft Developer Network (MSDN).

Now available is a white paper outlining the technologies and steps required to publish PerformancePoint Monitoring Server dashboards in an Extranet.  You can download this white paper from:

http://go.microsoft.com/fwlink/?LinkId=151630

My collegue Marc Valk has wrote an excellent blog post about Sharepoint and SSRS integration over multiple servers (not the default all on one box) with the famous double hop issues and how to fix them. Read it here:

http://www.marcvalk.net/2009/04/sharepoint-and-ssrs-integration/