Speeding up SQL Analysis Services DSV table selection against Teradata

May 20th, 2008 Russell

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!

Posted in SQL Analysis Services | No Comments »