SQL BI

Update data on published Excel spreadsheets on SharePoint 2007

I just exited from the SharePoint 2007 odissey to correctly configure settings to get updates from Analysis Services 2005 cubes.

The big problem is the Microsoft Single Sign-On service, that is the single component who really make the data connection to SSAS server. I had to reinstall the WHOLE SharePoint 2007 service to get things done.

The problem is that I found a lot of troubles when I tried to configure Single Sign-On service after a Sharepoint 2007 installation made with a local admin account. When I installed Sharepoint 2007 with a domain administrator account, then everything worked.

Anyway, I would like to emphasize that the security configuration related to this scenario (published Excel spreadsheet getting data from a remote SSAS server) is almost a nightmare: there is the need for a specific documentation for this scenario (that includes every aspect of the configuration) and it should be fine to get more detailed log data when things doesn't work: I found some info on event log, but when I had the single sign-on partially configured (enterprise application definitions were missing) there was no log (neither event log or network traffic to SSAS) to help me to understand where were the problem.

Reading it from a different perspective, there is a lot of space for consultancy on these topic :-) - but frankly I would prefer an easier way to manage it.

MDX quality improvement in Excel 2007

I just had to profile MDX queries sent to SSAS from some client. Office Web Components (that are used in the cube browser of Management Studio) in many circumstances send a lot of MDX queries, increasing roundtrip between client and server, resulting in bad performance in tables with hundreds of rows (crossjoin between 3 attributes on rows and one measure on column).

I was very worried, until I saw the clean and beautiful MDX query that Excel 2007 send to Analysis Services 2005 to get the same results: great performance, a hand-written MDX query has the same performance (WARNING: this is not a benchmark but a small test on a very limited set of queries).

This week the Beta 2 of Excel 2007 could help me to make some customer happy...

MapPoint 2006: first impressions

I tried MapPoint 2006 hoping to see an improved interface to UDM data. Unfortunately it seems there is not a direct support to OLAP cubes and a dynamic navigation with territory dimension.

I wrote a proof-of-concept of a .NET WinForm application that embed MapPoint 2004 as an ActiveX component and use levels of a Territory Dimension to choose what level of detail the user want to display on the map. I had to write data to a temporary file just to import them in MapPoint, but in generale it was very few lines of code. I hoped a better integration would have showed up in MapPoint 2006, but apparently MapPoint is still not considered a first-class citizen as a BI client application.

Excel 2003 OLAP customization with VSTO

Thanks to Romeo Pruno, I just discovered a sample of Excel 2003 customization to access OLAP data through task panes. While this article deserves to be read, I think that client customization is a sort of "last resort", to be applied only for particular customization. I prefer to use clients that are functional-complete by itself and that allows user to customize reports and queries without writing code. For this reason I think this is a good introduction for people who want to build a customized solution, but is not a way to solve the lack of functionality of Excel 2003 (like Search members), many of them will be present into Excel 12.

Christmas gift: Excel AddIn 1.5 for Analysis Services 2005

Microsoft released a new release of Excel AddIn for SQL Server Analysis Services: it's the version 1.5 which is compatible with Analysis Services 2005, compatible in the sense that it fully navigates attributes and hierarchies in a meaningful way. Unfortunately, no KPI support (at least I can't find it).

KpiUtil

Report Builder Model with UDM

Today I lost a lot of time digging on this problem so a I hope post about this will save time of other developers!

I wanted to import the UDM into a model for Report Builder. You have to do these steps:

• go into SQL Server Management Studio
• connect to Reporting Services
• define a data source to your Analysis Services database (remember, use "Data Source=SERVERNAME\INSTANCE;Catalog=DATABASENAME" connection string using "Microsoft SQL Server Analysis Services" as a Data Source Type)
• right-click the data source and choose "Generate Model..." menu item

At this point if you live in an english-language country, probably you're done. But if you live in other countries and your regional settings is not an English one, you many be in trouble.

My italian setting generates a rsModelGeneration error. You can workaround this bug (I suppose it is...):

• close SQL Server Management Studio process
• go in Control Panel / Regional Settings and choose English (United States) in Regional Options tab (other settings as Location aren't significative for this workaround)
• open SQL Server Management Studio
• connect to Reporting Services
• right-click the data source and choose "Generate Model..." menu item

Now you can revert to your original regional settings. I tested the report model using italian regional settings and it works well. Or, almost well. In fact I'm not able to see KPIs and calculated measures of my cubes. I'll work mor on this, but any hint will be appreciated.

UPDATE: Thanks to Brian Welcker, to display KPI and calculated measures into a Report Model it's necessary to associate each KPI and calculated member to a specific measure group; I didn't (and it's the default, guys!) so I could see them into the browser of SQL Management Studio but not into the Report Model. Once I defined this association, Report Builder allow me to use calculated measures and KPIs, including graphics.

Excel 12 BI revealed

I've just attended the Excel 12 BI session held by Amir Netz and Allan Folting. This is the first time that Excel 12 BI functionality are shown to the public. My expectations seems to be satisfied, even if I really want to put my hands on a Beta, but this would still take some months.

These are my raw notes on the session: it's too early to make a deeper analysis.

• Existing Connections
  • Supported data connections library on SharePoint
  • Only one dialog box (not 4/5 like today)
• Stylesheet linked to PivotTable in a simple way
  • It uses the standard style gallery of Excel 12

• Dimension selection with checkbox click
• Drag&Drop between row and label in a panel outside Pivot Table (like Excel OLAP AddIn)
• There are different kinds of pivottable:
  • Report only
  • Report and Chart (not shown today)
  • Chart only (not shown today)
• Dimensons are placed in columns or rows automatically by default (ie Time dimension goes in columns while product goes in rows)
• Tooltip on each cell with information of what the column is (like coordinates and - I suppose - properties)
  • On dimension it gives all member attributes
• Drilldown on the same column  (it's indented but doesn't move all other columns of Pivot Table just to add a dimension level or any other attribute)
• Filter dimension member with a rich menu (sort, top, complex conditions and, of course, checkbox select like the old one)
• Field list has a filter with a combo box (just to go directly to a given measure group or dimension)
• Locale ID can be specified over a connection string (it defaults to OS Locale ID)
  • Reports maintains data correctly, even if there were specific items selected
• Support for conditional server-based formatting (colors, format string, this is a long long waited feature)
• The All Member seems to be replaced with "Grand Total" as description
• Support for named sets (there is a specific folder named Sets)
• Support for actions
  • Beautiful demo: a report built with reporting services can be called passing parameters  derived by the selected item in Excel (it leverages standard action of SQL2005 but it's wonderful to see it working in Excel)
• Support for Manual Update of pivot table (very easy: there is a checkbox on the member selection panel)
• Support for KPI visualization
  • They are visualized in the Field List as a specific category (similar to a Dimension)
    • KPI Hierarchies is maintained
  • Support for KPI value and graphic (Status & Trend)
• OLAP Formulas
  • A Pivot Table report can be converted to formulas, so it became a free-form report given todady by Excel OLAP Add-In
  • When you manually edit the OLAP formula (CUBEVALUE) there is a full IntelliSense support!
    • Connections, dimensions, members, measures, everything is suggested in the right place
    • The only IntelliSense feature to write custom OLAP formulas is great
• Integration with Office Services
  • The OLAP report (or the whole spreadsheet) can be published on a server and can be accessible by a browser without Excel
  • Report is generated and updated on the server

Excel 12 and SharePoint v3

Reporting Services 2000 SP2: SharePoint Web Parts available now!

After a few months of beta, SQL Server 2000 Reporting Services Service Pack 2 is finally available!

As I said in a previous post, there are two SharePoint Web Parts to list and render reports inside a SharePoint site. Really great news.

Install Reporting Services SP2 Web Parts on SharePoint

I just installed the Reporting Services SP2 beta on my dev-test machine. I had to search into beta newsgroups how to install SharePoint Web Parts onto my SharePoint Services installation: to do that, you need to start a command like:

stsadm.exe -o addwppack -filename "C:\Program Files\Microsoft SQL Server\80\Tools\Reporting Services\SharePoint\RSWebParts.cab" -url http://sharepointservername

where STSADM.EXE is a SharePoint utility, by default located in C:\Program Files\Common Files\Microsoft Shared\web server extensions\60\BIN.

I think it's important to blog this because I haven't found this information in the readme file and (you guess!) I'm not an expert of SharePoint.

Besides the setup trouble, web parts seems to work fine until now.

BI Portal for SharePoint

A new version of BI Portal Sample is available from Microsoft. It looks good, but I still haven't tested it.

Integrated with SharePoint and Reporting Services. Support of different localizations. It's not the definitive solution, but may be useful.