如何创建命名查询来连接 SSAS 2005 中的多个数据源?

发布于 2024-08-21 14:37:47 字数 435 浏览 13 评论 0原文

在 SQL Server 2005 在线书籍部分“在数据源视图中定义命名查询 (Analysis Services)”中,它指出:

命名查询还可用于将一个或多个数据源中的多个数据库表连接到单个数据源视图表中。

有谁知道我在哪里可以找到有关如何完成此操作的示例或教程?

编辑:为了提供一些额外的背景...

我正在 SQL Server 2005 的 SQL Server 商业智能开发工作室中处理一个分析服务项目。我已经为位于不同服务器上的每个数据库定义了一个数据源。我正在尝试创建一个命名查询,它将是每个数据源中的表的并集。问题是命名查询要求我为查询选择单个数据源。该查询是针对此数据源执行的,该数据源对我的项目中的数据源一无所知。然而,根据 SQL Server 2005 在线书籍,根据我上面的引用,我想要完成的事情应该是可能的。

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

屌丝范 2024-08-28 14:37:47

MSDN 有链接描述命名查询和< a href="http://msdn.microsoft.com/en-us/library/ms175643(SQL.90).aspx" rel="nofollow noreferrer">此链接将引导您完成创建一个的过程。

编辑:我认为要使用多个数据源,您需要在创建查询时完全限定您的表以命中其他数据源,如下所示:

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

获得如下结果

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

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
岁月苍老的讽刺 2024-08-28 14:37:47

如果“多个数据源”指的是多个数据库,那么如果您完全限定数据库名称,则可以执行此操作。

例如,如果我有两个数据库,我可以这样做:

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

确保您不要忘记 dbo 位(所有者),否则它将无法工作。

我所知道的唯一其他类型的“多个数据源”是 分布式查询,它允许您对 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

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
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文