MDX 相当于 SQL 的 GROUP BY，用于排除重复行

发布于 2024-10-29 02:42:12 字数 288 浏览 8 评论 0原文

我的多维数据集有一个事实表，其中包含对任何实体所做的每次修改的行。我需要的是一个将根据所选维度返回实体计数的度量。因此，如果用户从日期维度选择月份，则度量应返回该月修改的实体数量（而不是修改数量）。

在 SQL 中，这将类似于：

SELECT EntityID, COUNT(*)
FROM FactTable
WHERE Date BETWEEN X AND Y
GROUP BY EntityID

如何在 MDX 中执行此操作？当然，这对于立方体来说是一个非常常见的场景。

原文

My cube has a fact table that contains a row for every modification made to any entity. What I need is a measure that will return a count of the entities based on the selected dimensions.
So if the user selects a month from the date dimension then the measure should return the number of entities that were modified that month (rather than the number of modifications).

In SQL this would be something like:

SELECT EntityID, COUNT(*)
FROM FactTable
WHERE Date BETWEEN X AND Y
GROUP BY EntityID

How can you do this in MDX? Surely this would be an extremely common scenario with cubes.

分享到QQ

分享到微博

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

发布评论

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

别靠近我心 2024-11-05 02:42:12

您的 t-sql 查询相当于 mdx 查询：

select 
    [Measures].[<Fact rows count measure>] on columns, 
    <Entity dimension members> on rows 
from [<Cube Name>] 
where (<month member>)

在上面的查询中 [Fact rows countmeasure] 将是具有聚合公式 Count - 行数的度量
但是，如果您在按另一个维度切片时需要返回实体成员的非重复计数，则基本上有多种选择：

在entityID 键上创建非重复计数度量
使用表达式创建计算度量： count(exists(existing [Entity] .[实体].[实体].MEMBERS,,'度量组名称'))

HTH,
赫尔沃耶·皮亚塞沃利

Your t-sql query is equivalent of mdx query:

select 
    [Measures].[<Fact rows count measure>] on columns, 
    <Entity dimension members> on rows 
from [<Cube Name>] 
where (<month member>)

In the above query [Fact rows count measure] would be a measure with aggregation formula Count - count of rows
However, if you need to return the distinct count of entity members when you slice by another dimension, you basically have several options:

create a distinct count measure on the entityID key
create a calculated measure with expression: count(exists(existing [Entity].[Entity].[Entity].MEMBERS,,'Measure Group Name'))

HTH,
Hrvoje Piasevoli

回复收藏 0 原文

~没有更多了~