Configuring heterogenous data sources with SQL Server 2008 R2

Meditation:
The most common cause of problems is ignorance.


Story:


I've been swept up into the SQL 2008 R2 media blitz.   Soon, my end users will be wearing sharp outfits and assembling their own reporting via 'self-serve' business intelligence.   What a happy day that will be.   IT will finally be out of the BI loop, customers will be free to explore the fertile fields of data without a guide, and all will be well.


Central to this is moving towards modeled data.   At present, we use far too many ad-hoc queries against undocumented sources.    Most certainly a centralized, shareable model is the way to go.


This cryptic blog post from 2006 started me thinking about simplifying heterogenous data access- one of our most common, and difficult problems for our relational data.  


So, I fired up BIDS, cracked open the report modeling projects, and proceeded to spend ten hours in a frustrating battle of wits:  Adding, removing objects to the data source view layer.   Changing objects from tables to Named Queries.   Nothing would build to a successful, deployable project.


The ultimate resolution came only after two days of frustration and despair:

As you can see in the properties window- heterogenous data sources must be fully qualified.   By implication, linked servers must be used for off-server datasources.

If you can find this spelled out anywhere in BOL, or any of the MSDN blog posts about R2, I salute your keen observation and attention to detail.    I failed to find this information during the preparation phase of this experiment.

The only cure for my ignorance was proceeding forth stupidly until project building was blocked by BIDS.  

So, that it may be useful:   To build a data source view under 2008 R2- with the intent of deploying a report model to an R2 SSRS instance- you'll need to use named queries (not secondary datasources!) for the non-primary datasets.   Those will need to be fully qualified, and the linked servers required will need to be in place.

I'm grateful this was merely and R&D project and not- for example- a production outage where ignorance can be catastrophic.

I'd done, what I thought was adequate preparation.   Reality proved me wrong.   The correct procedure was discovered only after hours of  flopping about.   

I'm somewhat impressed I managed to power through my own ignorance, and that it only took two days.   I might have spent either 30 seconds or a month on this earlier in my career.   It took just the right amount of time.