On a fairly regular basis, customers I work with wonder out loud (read: gripe) why SSAS 2005 and 2008 don’t allow them to easily drill-down to the relational tables that back a cube using an action. SSAS 2000 did this nicely, but SQL Analysis Services 2005 and 2008 want to return rows from the cube vs. directly from the tables.

There is a sort-of-but-not-too-painful workaround available which encompasses using ROLAP partitions which point to the drill targets in question, but it’s still kind of inconvenience to deal with because you may end up setting up quite a few ROLAP partitions to support everything you need to do.

Brian Knight has come up with a really nice, clean solution which leverages a helper assembly to do the same thing. I like this approach much more because once you’ve installed the assembly, creating drill-through actions is quite easy. Thanks, Brian!

http://blogs.pragmaticworks.com/brian_knight/2009/09/creating-a-ssas-rowset-action.html

http://blogs.pragmaticworks.com/brian_knight/2009/09/calling-an-external-query-from-mdx.html

Carl Rabeler of the Microsoft SQLCat team has just released a really nice set of tools targeted at helping you analyze SSAS performance.

You can read about it here:

http://sqlcat.com/toolbox/archive/2009/02/05/a-solution-for-collecting-analysis-services-performance-data-from-many-sources-for-performance-analysis.aspx

Then download it from codeplex, here:

 http://www.codeplex.com/SQLSrvAnalysisSrvcs

If you have taken the time to implement translations in your SSAS cube, it is comforting to know that the Analytic Grid can and will display your localized text.

From what I can gather, we aren’t quite smart enough to simply read regional settings on the client (as defined in Control Panel | Regional Settings) and run with them like we can when leveraging Excel 2007. Instead, you must set the language in question in Internet Explorer ala:

Tools | Internet Options | Languages (button)

You’ll need to add the language you want the grid localized in to the “language list”, and then move it to the top of the list. Clear your browser cache, and you should be ready to rock and roll.

A couple of days ago I needed to create a new DSV against a Teradata source. I found that the “lag” in the normally light-on-its feet DSV wizard was pretty bad - it took in the neighborhood of 1.5 minutes for the Add Related Tables function to return when I tried it against FactResellerSales (I have AdventureWorks DW loaded up in Teradata).

I was lucky to be in a LiveMeeting with some guys from Teradata who watched me struggle through this process, and they gave me some hints to make my life better – well, my data-related life, anyway:

• Use the .NET provider for Teradata version 12  (I was using the latest 8.1 Provider)
• In the data source for the project, make sure to set the database name you are connecting to in the Database property found in the All properties dialog
• In the same All properties list for the provider, set Use X Views to false

These three changes dropped my “wait time” from 1.5 minutes to 11 seconds. Can’t wait to see if queries against my ROLAP partitions come back more quickly.

Thanks Teradata guys!