归一化问题的 MDX 选择性求和
我是 MDX 新手，在从求和中过滤掉值时遇到问题。
我们想要执行归一化计算，特别是数量 (q) 除以基数 (b) 以获得强度 (I)。公式相当简单 I=q/b。
我们的多维数据集结构：
我们有事实表 FactQuantity 和 FactBasis，每个表都与我们的 DimOrg 和 DimTime 维度表相关联。这两个维度都有层次结构：DimTime 中的月份汇总为年份，DimOrg 中的位置汇总为业务部门和组。
事实表彼此不同步：仅仅因为特定 org_id 和时间存在数量，就不能保证同一 org_id 和时间存在基础。
我尝试的解决方案：
计算成员的基本形式很简单： [度量].[数量]/[度量].[基础]
当我们在两个维度的最低级别查看时，此方法工作正常，但当您滚动维度并开始聚合时，就会出现问题。
问题在于，包含了在同一时间和位置没有相应基础值的数量值，从而使分子太大，因此不正确。
简单示例：
A 组中的 org_id 001 的基数为 100，数量为 1000，因此计算出的强度为 1000/100=10。到目前为止还不错。 org_id 002也在A组中，没有依据，但它的数量确实是2000，所以它计算的强度有误差。美好的。
向上滚动到 A 组，对数量 (3000) 和基数 (100) 进行求和，计算出的强度为 30，这对于该组来说是不正确的。 2000 应该被排除，因为它没有相同 org_id 和时间的相应基础值。
非常感谢您提供的任何帮助。
谢谢，
I'm new to MDX and have a problem regarding filtering out values from a summation.
We want to perform normalization calculations, specifically a quantity (q) divided by a basis (b) to get an Intensity (I). The formula is fairly simple I=q/b.
OUR CUBE STRUCTURE:
We have fact tables FactQuantity and FactBasis, each of which are tied to our DimOrg and DimTime dimension table. Both dimensions have hierarchies: Months rollup into years in DimTime, and Locations roll up into business units and groups in DimOrg.
The fact tables are not in sync with each other: just because a quantity exists for a particular org_id and time, a basis is not guaranteed to exist for that same org_id and time.
MY ATTEMPT AT A SOLUTION:
The basic form of the calculated member is easy:
[Measures].[Quantity]/[Measures].[Basis]
This works fine when we view at the lowest level of both dimensions, but the problems arise when you roll up the dimensions and begin aggregating.
The problem is that Quantity values that don't have a corresponding Basis value at the same time and location are included, thus making the numerator too large and therefore incorrect.
QUICK EXAMPLE:
org_id 001 in Group A has a basis of 100 and a quantity of 1000, so its calculated intensity is 1000/100=10. Good so far.
org_id 002 also in Group A has no basis but it does have a quantity of 2000, so its calculated intensity errors out. Fine.
Rolling up to Group A sums the quantity (3000) and the basis (100), leaving a calculated intensity of 30, which is incorrect for the Group. The 2000 should have been excluded because it had no corresponding basis value for the same org_id and time.
Any help you can offer is much appreciated.
Thanks,
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Deepak Puri 回答了此问题 在 MSDN 论坛上：
Deepak Puri answered this on the MSDN Forums: