如何在 MDX 中进行类似于“分区求和”的聚合sql中的语句
我有一种情况，我有一个产品和一个时间维度，以及一个销售量事实表。随着时间的推移，产品的各种细节都会发生变化，但产品的业务密钥除外。在来自多维数据集的平面报告中，我希望在“业务关键”级别包含一些聚合，而不管显示产品维度的其他部分。
在 sql 中，这将是微不足道的，就像这样：
select sum(volume) over (partition by productKey,year) as Total
无论我选择了什么其他内容，“总计”列都将仅在这两个字段上聚合。
在 MDX 中我已经成功地实现了相同的结果，但似乎必须有一种更简单的方法。
WITH MEMBER Measures.ProductKeyTotal AS
'SUM(([Product].[ProductKey],[Time].[Year]
,[Product].[Product Name].[Product Name].ALLMEMBERS
,[Volume Type].[Volume Type Id].[Volume Type Id].ALLMEMBERS)
,[Measures].[Volume])'
SELECT {[Measures].[Volume],[Measures].[ProductKeyTotal]} ON COLUMNS,
NONEMPTYCROSSJOIN ([Product].[ProductKey].[ProductKey].ALLMEMBERS
,[Time].[Time].[Year].ALLMEMBERS
,[Product].[Product Name].[Product Name].ALLMEMBERS
,[Volume Type].[Volume Type Id].[Volume Type Id].ALLMEMBERS) ON ROWS
FROM [My Cube]
WHERE ([Product].[Include In Report].&[True])
1）如果我没有在计算成员中包含我不想要的行的所有成员，则总数不正确，是否有快捷方式强制它忽略除您指定之外的所有维度？
我问的部分原因是我需要添加一堆其他计算成员，其中一些将使用参数，如果我使用上面示例中的方法，我将需要在多个位置复制相同的内容，并且代码会变得很重要。
I have a situation where I have a product and a time dimension, with a fact table of sales volume. Over time, various details about the product changes, with the except of the business key for the product. In my flat reporting from the cube, I want to include some aggregration at the 'business key' level, regardless of what other parts of the product dimension are shown.
In sql this would be trivial as something like:
select sum(volume) over (partition by productKey,year) as Total
Regardless of whatever else I had selected, the Total column would be aggregated only on those two fields.
In MDX I have managed to achieve the same result, but it seems like there must be a simpler way.
WITH MEMBER Measures.ProductKeyTotal AS
'SUM(([Product].[ProductKey],[Time].[Year]
,[Product].[Product Name].[Product Name].ALLMEMBERS
,[Volume Type].[Volume Type Id].[Volume Type Id].ALLMEMBERS)
,[Measures].[Volume])'
SELECT {[Measures].[Volume],[Measures].[ProductKeyTotal]} ON COLUMNS,
NONEMPTYCROSSJOIN ([Product].[ProductKey].[ProductKey].ALLMEMBERS
,[Time].[Time].[Year].ALLMEMBERS
,[Product].[Product Name].[Product Name].ALLMEMBERS
,[Volume Type].[Volume Type Id].[Volume Type Id].ALLMEMBERS) ON ROWS
FROM [My Cube]
WHERE ([Product].[Include In Report].&[True])
1) If I don't include the allmembers for the rows I don't want in the calculated member the total is not correct, is there a shortcut to force it to ignore all the dimensions other that what you specify?
Part of the reason I ask is that I need to add a bunch of other calculated members, some of which will be using parameters and if I use the method from the example above I am going to need to duplicate the same stuff in multiple places, and the code will get weighty.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
好吧，首先，不要使用
NonEmptyCrossJoin- 它已经已弃用。使用非空，然后使用交叉连接运算符(*)。了解元组和元组集如何工作来回答您的问题非常重要。本质上，任何未明确说明的维度始终都会获取给定维度的
CurrentMember。通常，这是DefaultMember，但如果您在查询中将其设置为其他内容，则会更改此设置。您必须为这些维度指定ALLMEMBERS的原因是因为它将使用CurrentMember，否则。您可以只使用[All]成员来代替尝试对ALLMEMBERS求和（特别是如果它们不平坦！），这会给您带来更好的性能。执行此操作的最高效方法是将另一个度量值组添加到多维数据集，然后从该度量值组中删除不适用于度量值的键。这样，您就可以获得这些的本机计算，而不是运行时计算（运行时计算往往很慢，尤其是当您将多维数据集中的所有内容相加时）。此外，您甚至可以在该度量组上设置一些聚合设计，这将非常高效。
Well, first of all, don't use
NonEmptyCrossJoin--it's been deprecated. Usenon emptyand then thecross joinoperator (*).It's important to understand how tuples and tuple sets work to answer your question. Essentially, any dimension not explicitly stated will always get the
CurrentMemberof a given dimension. Typically, this isDefaultMember, but if you have it set to something else in your query, that will change this up. The reason you have to specifyALLMEMBERSfor those dimensions is because it will useCurrentMember, otherwise. You could just use the[All]member in lieu of trying to sum upALLMEMBERS(especially if they're not flat!), which will give you a bit better performance.The most performant way to do this is to add another Measure Group to your cube, and then remove the keys that don't apply to the measure from that Measure Group. This way, you get a native calculation for these rather than a run-time calculation (which tend to be slow, especially when you're adding up everything in your cube). Moreover, you can even set up some aggregation design on that Measure Group, and it will be very performant.