MDX 相当于带有聚合的 SQL 子查询
我是 MDX 新手，正在尝试解决以下问题。 研究了计算成员、子选择、范围语句等，但无法完全让它执行我想要的操作。
假设我正在尝试提出与以下 SQL 查询等效的 MDX：
SELECT SUM(netMarketValue) net,
SUM(CASE WHEN netMarketValue > 0 THEN netMarketValue ELSE 0 END) assets,
SUM(CASE WHEN netMarketValue < 0 THEN netMarketValue ELSE 0 END) liabilities,
SUM(ABS(netMarketValue)) gross
someEntity1
FROM (
SELECT SUM(marketValue) netMarketValue, someEntity1, someEntity2
FROM <some set of tables>
GROUP BY someEntity1, someEntity2) t
GROUP BY someEntity1
换句话说，我有一个帐户分类帐，其中隐藏内部抵消交易（在 someEntity2 内），然后计算资产和资产。 由 someEntity2 聚合后的负债。 然后我想查看这些资产和资产的总计。 由较大实体 someEntity1 汇总的负债。
在我的 MDX 架构中，我可能有一个具有 someEntity1 和 someEntity1 维度的多维数据集。 someEntity2 和 marketValue 将是我的事实表/度量。 我想我可以创建另一个 DSV 来执行我的子查询的操作（计算网络），并简单地创建一个立方体并将其作为我的度量维度，但我想知道是否有更好的方法。 我宁愿没有 2 个多维数据集（一个用于这些净计算，另一个用于其他用例的较低粒度级别），因为这将是我的数据库中的大量重复信息。 这些将是非常大的立方体。
I'm new to MDX and trying to solve the following problem. Investigated calculated members, subselects, scope statements, etc but can't quite get it to do what I want.
Let's say I'm trying to come up with the MDX equivalent to the following SQL query:
SELECT SUM(netMarketValue) net,
SUM(CASE WHEN netMarketValue > 0 THEN netMarketValue ELSE 0 END) assets,
SUM(CASE WHEN netMarketValue < 0 THEN netMarketValue ELSE 0 END) liabilities,
SUM(ABS(netMarketValue)) gross
someEntity1
FROM (
SELECT SUM(marketValue) netMarketValue, someEntity1, someEntity2
FROM <some set of tables>
GROUP BY someEntity1, someEntity2) t
GROUP BY someEntity1
In other words, I have an account ledger where I hide internal offsetting transactions (within someEntity2), then calculate assets & liabilities after aggregating them by someEntity2. Then I want to see the grand total of those assets & liabilities aggregated by the bigger entity, someEntity1.
In my MDX schema I'd presumably have a cube with dimensions for someEntity1 & someEntity2, and marketValue would be my fact table/measure. I suppose i could create another DSV that did what my subquery does (calculating net), and simply create a cube with that as my measure dimension, but I wonder if there is a better way. I'd rather not have 2 cubes (one for these net calculations and another to go to a lower level of granularity for other use cases), since it will be a lot of duplicate info in my database. These will be very large cubes.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为您应该将聚合逻辑留给多维数据集——这才是它最擅长的。
在您的情况下，我将创建一个帐户维度，然后添加帐户智能。 但是，这仅适用于 SQL Server 企业版（2005 及更高版本）。
如果您碰巧拥有标准版，则执行此操作的规范方法是 使用一元运算符。
这就是我们在 Sql Server 2000 中使用的方法，并且 这里你有一个很好的例子。
I think you should leave the aggregation logic to the cube--that's what it does best.
In your case, I would create an account dimension, and then I would add Account Intelligence. However, this only works for the Enterprise edition of SQL Server (2005 and above).
If you happen to have the standard edition, the canonical way to do this is to use unary operators.
That's the way we used to do it with Sql Server 2000, and here you have a great example.
我认为你想要的不是两个立方体，而是一个带有两个事实表（有时称为星座模式）的立方体。 这个问题是几个月前写的，所以我不会在这里详细说明，除非有人要求更多信息。
I think what you want is not two cubes, but one cube with two fact tables (sometimes called a constellation schema). The question was written months ago so I won't elaborate more here unless someone asks for more info.