THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Some thoughts on Visual Studio database references and how they should be used for SQL Server BI

Over the past few weeks I have been making heavy use of the Database tools in Visual Studio 2010 (formerly known as datadude, DBPro, VSTS for Database Professionals or one of a plethora of other names that it has gone by over the past few years) and one of the features that has most impressed me has been database references.

 

Database references allow you to have stored procedures in your database project that refer to objects (tables, views, stored procedures etc…) that exist in other database projects and hence when you build your database project it is able to resolve those references. Gert Drapers has a useful introduction to them at his blog post Database References; in his words database references allow you to: 

 

represent and resolve 3 and/or 4-part name usage inside a database project. Database references are conceptually the same as assembly references inside a C# or VB.NET projects; they allow you to reference objects from your database project that live inside another namespace (database).

 

It occurred to me that similar functionality would be incredibly useful for SQL Server Integration Services(SSIS), Analysis Services (SSAS) & Reporting Services (SSRS) projects. After all reports, packages and data source views are rife with references to database objects – why shouldn’t we be able to have design-time dependency checking in our BI projects the same way that database and .Net developers do?

 

Here are some examples of ways in which this would be useful:

  1. If a SSRS report pulls data from a view, I firstly want to know (at design-time) that that view exists. Secondly, if the view definition changes I want to know that my report will still work.
  2. Similarly for a SSAS cube, if a view referenced in the DSV changes or disappears I want my SSAS project build to fail or succeed accordingly
  3. Similarly for a SSIS Execute SQL Task, if it executes a stored procedure I want to know that the named parameters that I am using actually exist in the stored procedure.

 

To this end I have submitted three identical suggestions to Connect, one each for SSIS, SSAS & SSRS:

 

[SSAS] Declare database object dependencies

[SSRS] Declare database object dependencies 

[SSIS] Declare database object dependencies

 

I have said before in my blog post The SQL developer gap that we database and BI developers deserve as much love from Microsoft as our .Net counterparts do and I’m of the opinion that providing design-time dependency checking across the full gamut of BI projects would be a huge step in the right direction. The holy grail is an object dependency graph from database tables all the way up through database views, database functions, stored procedures, DAL, ORM layer & business logic layer all the way through to presentation – I don’t know how long it will take but we will get there one day I am sure, hopefully sooner rather than later!

 

@JamieT

UPDATE: This feature (i.e. the ability to define dependencies on database objects at build time) is coming to the Entity Framework as confirmed in this Connect submission: http://bit.ly/igmEiS. Let's hope the same comes to SSIS/SSAS/SSRS soon.

Published Tuesday, September 14, 2010 2:53 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peter Schott said:

My only issue w/ the DB references is that creating a partial project with only the objects you actually need is a painful process.  If you don't do that, the load times are really, really long.  We do a lot of cross-DB development in our shop and the really long validation process and worse, the build/deploy process makes the DB Project painful.  We deploy 10 DBs to an empty server and the process takes 45 minutes to build/deploy.  Maybe I'm doing something wrong, but waiting 45 minutes to push a new copy of the databases to your local box is a pretty long time.

September 14, 2010 3:13 PM
 

jamiet said:

Paul,

I have similar issues although certainly not that extreme (our deploy of 10 DBs takes between 10 & 15 mins). I find that the pain is worth it tho.

-Jamie

September 14, 2010 3:18 PM
 

Kristian Wedberg said:

Parsing/building/deploying with huge .dbschema file references (e.g. master.dbschema and any others you've grabbed wholesale) takes ages!

Edit (i.e. the XML, it's pretty straight forward) the large .dbschema files to only include the referenced objects you are actually using from your project DB - Project startup & deploy will FLY :-)

September 15, 2010 8:46 AM
 

SSIS Junkie said:

Three months ago I wrote a blog post entitled Some thoughts on Visual Studio database references and

December 7, 2010 5:37 AM
 

SSIS Junkie said:

Over the past eighteen months I have worked on four separate projects for customers that wanted to make

January 2, 2012 11:07 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement