Cube Browser Woes

/0 Comments/in /by

I had an interesting issue this week. I was working on a small test cube with financial data. The lowest grain in the measure group was the Account dimension which represents a customer bank account. I had to implement rolling 12 calculations on semi-additive measures (e.g. Avg Account Balance) by weighting the balances at the account level:

Rolling 12 Bal = ∑(Number of Account Active Days * Avg Account Balance) / ∑ (Number of Days for 12-month period

 

Since I had to weight the account balances by the number of the number of the days the account was active, I decided to scope at the account level, e.g.; SCOPE (Leaves(Account), Leaves(Date)…). Also, I had to use the ParallelPeriod() MDX function to sum the weighted balanced for the 12-month period.  To my surprise, the small test cube took an enormous time to initialize in the Cube Browser. The Cube Profiler showed as many subcube events as the number of the accounts of the cube. It appeared that the server was initializing the expression for each account. This didn’t make sense at all considering the fact that the server should only write the formula in the scope cells which should happen pretty fast even with large cubes and very granular scope assignments. After some digging, it turned out that the culprit wasn’t the server but the Cube Browser L. For some obscure reason, the Cube Browser issues the following statement each time you reconnect even with an empty report.

 

select filter([Measures].members, [Measures].currentmember.member_caption = [Measures].currentmember.member_caption) on columns from [<cube name>]

 

This statement was causing the server to evaluate the Account All member which was triggering the formula evaluation for each account. Oops! The moral of this story is to test your cube initialization time in SQL Server Management Studio. As for me, I decided to reduce the scope (always a good idea) to Leaves (Date) only by pro-rating the Active Days in the data source so I could use the days in the month. This makes the formula universal on rows irrespective of the cube slice and makes the Cube Browser happy.

SSAS Performance Samples

/0 Comments/in /by

Thanks for Russell Christopher’s blog, I’ve learned that Microsoft donated an Analysis Services Load Simulator (ASLoadSim) sample project which lets you load test Analysis Services using the VS.NET 2005 test capabilities. While I am to try the code sample, I am very impressed by the whitepaper included in the sample which got harvested from Project REAL.

Chris Webb also contributed in the SSAS performance area by donating his MDX Script Performance Analyzer sample which I am yet to try as well.

Cumulative Update Package (build 3161) for SQL Server 2005 Service Pack 2 Available

/0 Comments/in /by

Microsoft has released a cumulative update package (build 3161) for SQL Server 2005 SP2. Among other things, the update package fixes the SSRS black preview.

The Rational Guide to Microsoft Office Business Scorecard Manager 2005

/0 Comments/in /by

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.

Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables Whitepaper

/0 Comments/in /by

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.

Excel Web Access Drill-through to SSRS Report

/1 Comment/in /by

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.

Report Viewer SP1 Redistributables

/0 Comments/in /by

As I mentioned in a previous blog, Visual Studio 2005 SP1 includes updated versions of both the WinForms and ASP.NET versions of the Report Viewer control. Microsoft has just released full and upgrade redistributables for the Report Viewer SP1.

Roll Your Own UDM Schema Generator

/0 Comments/in /by

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());

}

}

}

Get More Out of SQL Server Reporting Services Charts

/0 Comments/in /by

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

/0 Comments/in /by

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

  • 1889 attendees were registered to attend
  • 88 countries will be represented (based on registrations)…including for the first time: Algeria, Andorra, Bahrain, Bangladesh, Bolivia, Cyprus, El Salvador, Gibraltar, Honduras, Latvia, Morocco, Nepal, Oman, Rwanda, Tunisia, UAE, Uruguay and Vietnam
  • 533 Sessions were delivered
  • 956 Microsoft employees pre-registered as speakers/attendees
  • 10 Microsoft Executives and 4 Technical Fellows

 

Next stop – TechEd USA 2007. See you in Orlando.