How do I create a named query to join multiple data sources in SSAS 2005?

Question

In the SQL Server 2005 books online section "Defining Named Queries in a Data Source View (Analysis Services)", it states:

A named query can also be used to join multiple database tables from one or more data sources into a single data source view table.

Does anyone know where I can find examples or tutorials on how this can be done?

EDIT: To provide some additional background...

I am working with an analysis services project in the SQL Server Business Intelligence Development Studio for SQL Server 2005. I have defined a data source for each of my databases which are on different servers. I am trying to create a named query which will be a union of a table from each data source. The problem is that the named query requires me to choose a single data source for the query. The query is executed against this data source which does not know anything about the data sources in my project. However, according to the SQL Server 2005 books online, what I am trying to accomplish should be possible based on my quote from above.

Answer 1

MSDN has this link describing Named Queries and this link walking you through the process of creating one.

Edit: I think that to use multiple datasources, you would need to fully qualify your table to hit other datasources when creating your query, like this:

SELECT user_id, first_name, 'DB1' as DB FROM users
UNION
SELECT user_id, first_name, 'DB2' as DB FROM Database2Name.dbo.users

to get results like

user_id    first_name    DB
1          Bob           DB1
2          Joe           DB1
11         Greg          DB2
12         Mark          DB2

Answer 2

If by "multiple data sources" you mean multiple databases, then you can do this if you fully qualify the database name.

For example if I have two databases I can do this:

SELECT * FROM DatabaseA.dbo.SomeTable 
JOIN DatabaseB.dbo.OtherTable
ON DatabaseA.dbo.SomeTable.Id = DatabaseB.dbo.OtherTable.Id

Make sure that you don't forget the dbo bit (the owner), otherwise it won't work.

The only other sort of "multiple data sources" that I'm aware of is distributed queries which allows you to perform queries over multiple remote instances of sql server:

sp_addlinkedserver 'server\instance'

SELECT * FROM [server\instance].DatabaseA.dbo.SomeTable 
JOIN DatabaseB.dbo.OtherTable
ON [server\instance].DatabaseA.dbo.SomeTable.Id = DatabaseB.dbo.OtherTable.Id