MDX 查询未正确过滤
我有以下星型架构：
Objects <-> Facts <-> Simulation
-ObjectID -ObjectID -SimulationID
-SimulationID -SimulationID
-ObjHierarchy -Volume
-ObjectType
现在我尝试使用两个维度过滤多维数据集：
select [Measures].[Volume] on columns,
[Objects].[ObjHierarchy].[Level 02] on rows
from [DM OC]
where ([Objects].[ObjectType].&[2], [Simulation].[SimulationID].&[52])
但是，这会返回 SimulationID=52 的行（带有值），但也会返回 SimulationID=53 的重复行 （带有空值）：
ObjHierarchy | Volume
MyObj1 | 12345
MyObj2 | 54321
MyObj1 | (NULL)
MyObj2 | (NULL)
解决方法是使用 NonEmpty，但似乎立方体的建模方式不正确。
I have the following star schema:
Objects <-> Facts <-> Simulation
-ObjectID -ObjectID -SimulationID
-SimulationID -SimulationID
-ObjHierarchy -Volume
-ObjectType
Now I'm trying to filter the cube using both dimensions:
select [Measures].[Volume] on columns,
[Objects].[ObjHierarchy].[Level 02] on rows
from [DM OC]
where ([Objects].[ObjectType].&[2], [Simulation].[SimulationID].&[52])
However, this returns rows for SimulationID=52 (with values) but also duplicates for SimulationID=53 (with nulls):
ObjHierarchy | Volume
MyObj1 | 12345
MyObj2 | 54321
MyObj1 | (NULL)
MyObj2 | (NULL)
A workaround is to use NonEmpty, however it just seems the cube isn't modeled the right way.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是常见情况，并不意味着立方体的模型错误。
在 MDX 中，如果您不希望行出现在结果中，还应该使用任何筛选函数来筛选行。在您的情况下，您应该使用 NonEmtpy 来消除空值。
It is a usual case and doesn't mean that the cube's model wrong.
In MDX, you should also filter the rows by using any filter function if you don't want them to appear in your result. In your case, you should use NonEmtpy to eleminate the empty values.
您可以执行以下操作：
将成员 XX 作为 [Objects].[ObjHierarchy].currentMember.Properties( "KEY" )
select { [Measures].[Volume], XX } on columns，
行上的 [对象].[ObjHierarchy].[级别 02]
来自 [DM OC]
其中 ([Objects].[ObjectType].&[2], [Simulation].[SimulationID].&[52])
MyObj1 和 MyObj2 不应具有相同的密钥；否则我现在看不到。
Can you execute the following :
with member XX as [Objects].[ObjHierarchy].currentMember.Properties( "KEY" )
select { [Measures].[Volume], XX } on columns,
[Objects].[ObjHierarchy].[Level 02] on rows
from [DM OC]
where ([Objects].[ObjectType].&[2], [Simulation].[SimulationID].&[52])
MyObj1 and MyObj2 should not have the same key; otherwise I do not see right now.