MDX中的交集 [英] Intersection in MDX
问题描述
我最近在我们的SQL Server 2008 Analysis Services多维数据集中遇到了一个问题.假设您有一个包含订单和产品的简单销售数据仓库.每个订单可以与多个产品相关联，并且每个产品可以包含在多个订单中.因此，数据仓库至少由3个表组成:一个用于产品，一个用于订单，一个用于参考表，对两者之间的n:n关系进行建模. 我希望我们的多维数据集回答的问题是:有多少个同时包含产品x和产品y的订单? 在SQL中，这很容易:
I recently ran into a problem in our SQL Server 2008 Analysis Services Cube. Imagine you have a simple sales data warehouse with orders and products. Each order can be associated with several products, and each product can be contained in several orders. So the data warehouse consists out of at least 3 tables: One for the Products, one for the Orders and one for the reference table, modelling the n:n relationship between both. The question I want our cube to answer is: How many orders are there which contain both product x and product y? In SQL, this is easy:
select orderid from dbo.OrderRefProduct
where ProductID = 1
intersect
select orderid from dbo.OrderRefProduct
where ProductID = 3
由于我相当精通SQL，但是我是MDX的新手，所以我无法在MDX中实现它.我尝试使用不同的计数度量，MDX函数intersect和nonempty以及子多维数据集.我还尝试了在逻辑上(通过两次将维度添加到多维数据集)以及在物理上(通过复制数据源表和维度)来复制维度.
Since I am fairly proficient in SQL, but a newbie in MDX, I have been unable to implement that in MDX. I have tried using distinct count measures, the MDX-functions intersect and nonempty and subcubes. I also tried duplicating the dimensions logically (by adding the dimension to the cube twice) as well as physically (by duplicating the data source table and the dimension).
在 http://www.zeitz.net/thts/intersection.zip 上，您可以下载一个大小为25kB的zip文件，其中包含一个包含一些测试数据的SQL脚本和使用表的Analysis Services解决方案.
On http://www.zeitz.net/thts/intersection.zip, you can download a zip file of 25kB size which contains an SQL script with some test data and the Analysis Services Solution using the tables.
我们正在使用SQL Server 2008 R2及其对应的Analysis Services.性能方面的考虑并不那么重要，因为与该多维数据集中包含的其他度量值组(数十亿行)相比，数据量较低(数百万行).
We are using SQL Server 2008 R2 and its Analysis Services counterpart. Performance considerations are not that important, as the data volume is rather low (millions of rows) compared to the other measure groups included in that cube (billions of rows).
最终的目标是能够在标准OLAP中使用所需的功能(可以通过自定义计算得出的度量标准)，因为Excel是我们的主要前端，并且我们的客户希望从尺寸列表中选择其产品并获取多维数据集度量中的正确结果.但是，即使是运行正常的独立MDX-Query也会大有帮助.
The ultimate goal would be to be able to use the desired functionality in standard OLAP (custom calculated measures are ok), since Excel is our primary frontend, and our customers would like to choose their Products from the dimension list and get the correct result in the cube measures. But even a working standalone MDX-Query would greatly help.
谢谢！
编辑3月12日 我错过了什么吗，还是不能以某种方式解决?
Edit March 12th Did I miss something or can't this be solved somehow?
如果它有助于构建mdx，这是使用子查询在sql中获得结果的另一种方法.可以进一步嵌套.
If it helps to build the mdx, here is another way to get the results in sql, using subquerys. It can be further nested.
select distinct b.orderid from
(
select distinct orderid from dbo.OrderRefProduct
where ProductID = 1
) a
join dbo.OrderRefProduct b on (a.orderid = b.orderid)
where ProductID = 3
我在mdx中用子多维数据集尝试了类似的方法，但是没有成功.
I tried something like this with subcubes in mdx, but didn't manage to succeed.
推荐答案
我已经尝试了-您可以从此处下载我的解决方案:
I've had a go - you can download my solution from here:
我添加了Fact表的副本作为交叉引用"，将Product1维度别名为交叉引用"，独立于您现有的关系将维度"引用设置为产品"，并指定了多对多"许多关系.
I've added a copy of your Fact table as a "Cross Reference", Aliased the Product1 dimension as a "Cross Reference", set the Dimension references to Product independently from your existing relationships, and specified the Many-to-Many relationships.
它在Excel中返回正确答案(附加示例).
It is returning the right answer in Excel (sample attached).
您可以根据需要扩展该模式多次.
You could extend that pattern as many times as you need.
祝你好运！ 迈克
这篇关于MDX中的交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
