Nick Barclay is telling us that the book The Rational Guide to Microsoft Office Business Scorecard Manager 2005 that he co-wrote with Adrian Downes is out! You can use BSM to design scorecards that can be published to SharePoint dashboards or SSRS reports. As far as I know, this is the first BSM book which makes it even more appealing.
A bit outdated announcement perhaps but the Office team has released a Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables whitepaper. Instead of pestering you with how-to PivotTable questions, now your users can have proper documentation. A great deal of it discusses UDM implementation details but who is to say that you didn’t travel the extra mile… As for me, well I still wait for the Office team to implement a few more goodies like pre-filtering support for large dimension, before I get impressed by Excel 2007 as an SSAS browser.
I’ve been having fun lately with a dashboard page which has an Excel Web Access (EWA) web part connected to a PivotTable report. The PivotTable report is nothing to brag about. It displays a few KPI-related measures coming from an SSAS cube. PivotTable was converted to formulas to achieve a free-form report layout. So far, pretty straightforward stuff. That is, until the customer wanted the ability to drill through the chart to see a more detailed view from the cube.
Since to my understanding cross-EWA drillthrough is not possible because Excel Services is not URL-addressable (shame [:@]), I implemented this scenario by allowing the user to drill through from the Excel spreadsheet to an SSRS report. To implement this, I converted the Excel cell to a hyperlink which has the URL address of the SharePoint report using the Excel HYPERLINK function:
=HYPERLINK(“http://” & CUBEVALUE(“<connection name>”,”[Measures].[ServerName]”) & “:8080/ReportServer?http://” & CUBEVALUE(“<connection name>”,”[Measures].[ServerName]”) & “/Reports/ReportsLibrary/Top 50 Customers.rdl&DateCalendar=[Date].[Calendar].[Calendar Year].%26[” & PreviousPeriod & “]&rs:Command=Render”, “Growth in Customer Base”)
There are a few interesting things going on in this nasty expression that deserve more explanation. First, instead of hardcoding the server name, I added a ServerName calculated member to the cube that is mapped to the following SSAS stored procedure:
public static string ServerName() {
return Context.CurrentServerID;
}
Second, note the URL address of the report. Since SSRS is configured for SharePoint integration, the report path points to the WSS library where the report is deployed. Finally, I had to solve the nasty issue with ampersands in the member names b/c the drillthrough SSRS report takes a member as a parameter. If I just use & the Report Server will choke b/c it will think that it is a parameter placeholder. The hack is to escape & to %26 .
Here is what the link looks when rendered in the browser.
Documenting the UDM schema is a common requirement but to the best of my knowledge neither Microsoft nor third-party vendors provide schema generators. While you are waiting for a commercial tool to fill in the gap, you can roll out your own schema generators. Thanks to the Analysis Management Objects (AMO) library, this task is remarkably simple to implement. For example, here is a .NET code snippet that builds upon the DisplayObjectNames sample (included in the SSAS samples). The code loops through all SSAS databases on the server, traverses the dimension attribute hierarchies and measures and displays their names and source columns.
foreach (Database db in server.Databases) {
Console.WriteLine(“{0}”, db.Name);
// Display Dimensions
Console.WriteLine(“\tDimensions:”);
foreach (Dimension dimension in db.Dimensions)
{
Console.WriteLine(“\t\t{0}”, dimension.Name);
// Display attributes
Console.WriteLine(“\t\t\tAttributes:”);
foreach (DimensionAttribute a in dimension.Attributes)
Console.WriteLine(“\t\t\t\t Name: {0}Source: {1}”, a.Name.PadRight(50), GetKeyCollection(a.KeyColumns));
}
// Display Cubes
Console.WriteLine(“\tCubes:”);
foreach (Cube cube in db.Cubes) {
Console.WriteLine(“\t\t{0}”, cube.Name);
// Display Measure Groups
Console.WriteLine(“\tMeasure groups:”);
foreach (MeasureGroup mg in cube.MeasureGroups)
{
Console.WriteLine(“\t\t{0}”, mg.Name);
// Display attributes
Console.WriteLine(“\t\t\tMeasures:”);
foreach (Measure m in mg.Measures)
Console.WriteLine(“\t\t\t\t Name: {0}Source: {1}”, m.Name.PadRight(50), m.Source.ToString());
}
}
}
One must-read SSRS resource which I missed was the excellent article “Get More Out of SQL Server Reporting Services Charts” by Robert Bruckner from the Reporting Services dev team. Now I have something to point folks to for how-to chart-related questions.
MVP Summit is over and I am back in Atlanta. I had great time meeting other MVPs, MS employees, and learning about the future in MS terms. I posted pictures from the event. Some interesting statistics given to us by Microsoft:
The MVP Summit is the largest event held at the Microsoft campus
Next stop – TechEd USA 2007. See you in Orlando.
Joining some 2,000 other MVPs from all around the world, I am heading tomorrow for Seattle to participate in the annual MVP Global Summit. It’s time to shake hands, take pictures, put names to faces, and soak up as much as possible about what’s coming up in Katmai (the next SQL Server release). Unfortunately, the last part is under strict NDA so I won’t be able to talk about it (not until the first public CTP is out that is).
I will also present a spotlight session for the Reporting Services group during which I will share my feedback about the product harvested from a real-life project. Looking forward to meeting these guys at last! Hey, BillG will be also kind enough to talk to the MVP Community and this will be one of his last public appearances as a Microsoft Chairman. A sort of a farewell party for Bill. I will back on Friday with lots of pictures I hope.
Although not a common practice, UDM supports defining text-based calculated members. For example, here is an useless but valid text-based calculated member:
Create Member CurrentCube.[Measures].[Hello] As “Hello”;
Much to my surprise, a text-based member won’t show up in Report Builder. That’s because the Report Builder defaults the member’s data type to float and the text is simply “lost” when the report is run. How does the Report Builder determine the field data type with UDM as a data source. With regular measures, it’s simple: the Report Builder derives the field type from the DSV data type. It seems that for calculated members, however, the Report Builder always defaults the data type to float.
While there may other workarounds to resolve this issue, one way is to “correct” the Report Builder invalid assumption about the member data type. Since one would typically auto-generate the Report Builder model when UDM is used as a data source, the ultimate hack is to change the model directly. To do so, go to the model properties in the Report Manager and click the Edit button to save the model as a file. Then, find the text-based member (attribute) by name and change its type from float to string. Then, back to the Report Manager, click the Update button to update the model from the file. As a result, the report will show the member text values correctly.
The initial release of SQL Server 2005 Service Pack 2 (SP2) contained an issue that caused maintenance plan cleanup tasks to remove data before the specified cleanup interval. Install this update to correct the interpretation of existing cleanup task intervals, and to avoid this issue in new cleanup tasks. After you install this item, you may have to restart your computer.
If you already installed the SQL 2005 SP2 released on Feb. 19th, 2007:
If you have not upgraded to SQL 2005 SP2 official release:
The updated SP2 doesn’t fix the RS black preview issue for which a separate hotfix will be available at later time.
