The trouble with data…

There’s a great resource for those people whose company can’t (or won’t) pay for training in these cash-strapped times

How Do I BI?

Check them out!

Microsoft announced  yesterday that the “Gemini” capabilities of Office 2010 / SQL Server 2008 R2 will be released under the brand, PowerPivot for Excel 2010… There’s a new site up and running dedicated to the product with little data as yet, however the Public Beta will be available in November.

PowerPivot

More of a placeholder for me because I keep forgetting. You know (probably) the scenario where you have enabled more than the default 1,000 drill-through rows in your SSAS Cube but Excel is still stubbornly limiting the drill-through to 1,000 rows.

You need to edit the data connection in Excel as per the picture below to increase the default.

Something to keep an eye on as we move towards what is pretty likely to be Office 2010 by all accounts…

The Microsoft Office 2010 IT Blog

More of a link for myself than anything else, however for those of you having difficulty getting Excel workbooks containing external data connections to refresh in a MOSS 2007 Excel Services environment, this one is for you…

 Microsoft have released a new white paper that answers many questions about how you can use the new features of Office 2007 to get the best out of your SQL 2005 installation…

The paper is divided into two main sections. The first shows an overview of the three main BI integration points between Microsoft SQL Server 2005 and the 2007 Microsoft Office system:

• Microsoft SQL Server 2005 Reporting Services and Microsoft Office SharePoint Server 2007
• Microsoft SQL Server 2005 Analysis Services and Microsoft Office Excel 2007
• Microsoft SQL Server 2005 Data Mining Add-Ins for Office 2007

You can download the paper here

One of the core selling points Microsoft are using to push MOSS 2007 is the ability to present, in one centralised location, key business data

 Present business-critical information in one central location. Create live, interactive business intelligence (BI) portals that assemble and display business information from disparate sources by using integrated BI capabilities such as dashboards, Web Parts, key performance indicators (KPIs), and business data connectivity technologies. Centralized Report Center sites give users a single place to find the latest spreadsheets, reports, or KPIs.

I just wanted to point out that in reality, this is not (yet) particularly easy to achieve with the softwares involved “out of the box”. If my understanding is correct, the following issues will need addressing before you can hope to achieve the seamless integration between the various bits of software that MSFT would like to portray in their product marketing. This is my understanding of the current issues, and if any of the information here is incorrect, please feel free to correct me via comments.

Specifically I am talking about a scenario that involves the following

• SQL Server 2005
• Excel 2007
• Sharepoint 2007 (MOSS)

Firstly – a note about security. Rather than reinvent the wheel, I will point you to an excellent Microsoft Technet Article that covers the planning and setting up of security so that you can successfully deploy solutions that use Excel Services in MOSS 2007. In short, plan the security aspect very carefully prior to setting up your Sharepoint/SQL/Excel environment or you will have some real difficulties authenticating users when using external data connections. Personally i’m not a great fan of Single Sign-On (SSO) so Windows authentication via Kerberos would seem the way to go. Also, Excel Services will not work in a server farm (however small) environment unless Kerberos is correctly configured. Don’t get me wrong – you will be able to display Excel ranges in the Sharepoint web parts, but your users will not be able to alter parameters or refresh the data, despite adding both file and connection to trusted locations.

The other two issues surround SQL Server Reporting Services and its integration with Sharepoint. In short, & despite much searching, it appears that it won’t be a particularly easy or pleasant development experience until SP2 of SQL Server 2005 Reporting Services goes RTM. Currently, there’s a CTP available via the microsoft download site but as yet I don’t know of a published RTM date for this (although it should be imminent by all accounts) . This Service Pack, along with a special reporting services addin will enable the following:

…integrate a report server instance with Windows SharePoint Services 3.0 or Microsoft Office 2007 SharePoint Server to store, secure, access, and manage report server items from a SharePoint site. Integration features are provided jointly through SP2 and a special Reporting Services Add-in that you download and install on an instance of the SharePoint technology you are using.

The new Report Viewer Web Part is included in the Reporting Services Add-in that you install on a SharePoint technology instance. For more information about the Web Part and other integration features, see Reporting Services and SharePoint Technology Integration and Features Supported by Reporting Services in SharePoint Integration Mode.

Once you have done all these steps, integration between SQL 2005, Sharepoint 2007 & Excel 2007 will be a much improved experience, as I have found – I just wish that all of this information was much better publicised and didn’t require a number of failures in development followed by 2 days of Google to discover the reality of the “out of the box” scenario. It also seems that there will be a simple upgrade path from the CTP to the RTM of SP2. It was mentioned that this would be due “a couple of months after Office 12 and Vista went RTM” – we shall see I guess

Lastly, I’ll point to a couple of other useful links I found along the way

• Mike Glaser’s BI Blog
• William Li’s Enterprise Reporting Blog (little recent content but useful)
• MSDN Article – Gathering MOSS