MDX MEMBER 导致 NON EMPTY 无法过滤
我正在使用 MDX 查询来提取信息以支持一组报告。报告需要非常详细的信息，因此需要一些时间才能生成。为了加快访问速度，我们提取所需的数据并将其存储在平面 Oracle 表中，然后连接到 Excel 中的表。这使得报告在几秒钟内而不是几分钟内刷新。
以前，MDX 是按部门为 100 个部门生成和运行的，然后为许多其他过滤器生成和运行。所有这些都是在 VB.Net 中完成的。对过滤器的要求已经增长到这种方法不可持续的程度（并且可能不是最好的方法）。
我已将整个数据集构建到一个完美运行的 MDX 查询中。我交叉加入的一个集合包括来自三个不同层次结构级别的成员，它看起来像这样：
(
Descendants([Merch].[Merch CHQ].[All], 2),
Descendants([Merch].[Merch CHQ].[All], 3),
[Merch].[Merch CHQ].[Department].&[1].Children
)
对我来说，问题在于我们的层次结构（我无法更改）、每个组（第一项）和每个部门（第二个） item）的命名结构相同，即 15-DeptName，使用起来很混乱。
为了解决这个问题，我添加了一个成员：
MEMBER
[Measures].[Merch Level] AS
(
[Merch].[Merch CHQ].CurrentMember.Level.Name
)
它返回该成员的类型并且它工作得很好。
问题是它会针对每个成员进行更新，因此没有任何行被非空白过滤，而不是 65k 行，而是 130k 行，这会损害我的访问性能。
我的查询是否可以更改为仍然过滤掉非空白，而不是使用 IIF 检查每个测量值是否为空？
I'm using an MDX query to pull information to support a set of reports. A high degree of detail is required for the reports so they take some time to generate. To speed up the access time we pull the data we need and store it in a flat Oracle table and then connect to the table in Excel. This makes the reports refresh in seconds instead of minutes.
Previously the MDX was generated and run by department for 100 departments and then for a number of other filters. All this was done in VB.Net. The requirements for filters have grown to the point where this method is not sustainable (and probably isn't the best approach regardless).
I've built the entire dataset into one MDX query that works perfectly. One of my sets that I cross join includes members from three different levels of hierarchy, it looks like this:
(
Descendants([Merch].[Merch CHQ].[All], 2),
Descendants([Merch].[Merch CHQ].[All], 3),
[Merch].[Merch CHQ].[Department].&[1].Children
)
The problem for me is in our hierarchy (which I can't change), each group (first item) and each department (second item) have the same structure to their naming, ie 15-DeptName and it's confusing to work with.
To address it I added a member:
MEMBER
[Measures].[Merch Level] AS
(
[Merch].[Merch CHQ].CurrentMember.Level.Name
)
Which returns what type the member is and it works perfectly.
The problem is that it updates for every member so none of the rows get filtered by NON BLANK, instead of 65k rows I have 130k rows which will hurt my access performance.
Can my query be altered to still filter out the non blanks short of using IIF to check each measurement for null?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以根据您的主要度量为您的成员指定 Null，例如：
这样它只会在有数据时生成。如果您需要更精确，您可以进一步向空支票添加额外的尺寸。
You can specify Null for your member based on your main measure like:
That way it will only generate when there is data. You can go further and add additional dimensions to the empty check if you need to get more precise.