Companion for MS Analysis Server

/0 Comments/in /by

SQL Minds has released Companion for MS Analysis Server. Companion for MS Analysis Server is a product that helps with the health monitoring and performance tuning of Microsoft SSAS 2005. The product offers historical baseline, custom aggregations and their usage, meta data browsing, cube browsing, various reports on SSAS inner workings, trace information on slowest SQL, DMX, MDX and XMLA requests, processing information broken down by steps, capacity planning and forecasting, and more…

Not All Hyperlinks are Born Equal

/0 Comments/in /by

The ASP.NET ReportViewer control has a HyperlinkTarget property which may give false expectations. You may believe that this property applies to all hyperlinks, including links to drillthrough reports. For example, you may attempt to set the HyperlinkTarget to _blank to open a drillthrough report in a new window. However, you will be disappointed to find that drillthrough reports ignore the HyperlinkTarget property completely. That’s because they require a page postback.


As it stands, the HyperlinkTarget property applies only to “regular” (Jump to URL) hyperlinks. Propagating HyperlinkTarget to drillthrough links is on the wish list for a next release. Meanwhile, if you need to open a drillthrough report in a new window, consider using a Jump To URL link that points to an ASP.NET page hosting the ReportViewer control (or reuse the same one that displays the master report). The ASP.NET page would parse the link and load the drillthrough report in the ReportViewer control. If you set the HyperlinkTarget property to _blank, the drillthrough report will open in a new window because it now launched from a regular link.

SQL Server 2005 SP2 Brings Self-upgrading Catalogs

/0 Comments/in /by

A little known but very useful SSRS feature which debuted in SQL Server 2005 SP2 is that the SSRS Windows service automatically upgrades the report catalog (ReportServer database) if required. As I discussed in this blog, you may need to swap the report catalog for testing or troubleshooting purposes. In the past, you had to manually upgrade the catalog from a previous version using the Report Services Configuration Utility or using the rskeymgmt utility.

Starting with SQL Server 2005 SP2, when the SSRS Windows Service connects to the report catalog it runs the stored procedure to get the database version of the catalog. If the versions don’t match, SSRS Windows Service calls down to WMI to generate an upgrade script and applies it. The service executes the catalog version check every time it connects to the catalog.

My TechEd Session Scheduled

/0 Comments/in /by

My breakout TechEd session BIN305 – Applied Microsoft SQL Server 2005 Reporting Services is scheduled for June 4 at 10:30 AM – 11:45 AM in room S320 A. That’s right – let’s talk reporting right after the keynote!

I hope to see some of you there.

SQL Server Samples to CodePlex

/0 Comments/in /by

The March release of SQL Server 2005 samples is available for download. Note that Microsoft has moved all SQL Server 2005 samples (standard and open source) to the CodePlex website. The documentation is still on MSDN. So, GotDotNet is dead, long live CodePlex!

Introducing LINQ

/0 Comments/in /by

If you follow the Microsoft .NET roadmap, you have probably heard about the forthcoming Language Integrated Query (LINQ) in .NET 3.0. LINQ will add query capabilities directly into the CLR and will be supported by both VB.Net and C#. This means that you will be able to use standard query operators directly from within your code!

To help you get started with LINQ, Marco Russo and Paolo Pialorsi wrote a book
Introducing Microsoft LINQ published by Microsoft Press. The book is expected to be published in mid-May. Meanwhile, the authors have set up a public forum (http://introducinglinq.com/) and they are eagerly awaiting your LINQ-related questions.

Oh, yes, I’ve made a tiny contribution to the book by reviewing a few chapters. I found the book to be a great introduction to LINQ. I particularly liked the code examples.

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.