I'm new to MDX, so I assume this is a newbie question.
我是MDX的新手,所以我认为这是一个新手问题。
Existing dimension: status Existing Measure: count
现有维度:状态现有度量:计数
Existing Query:
SELECT
NON EMPTY [status].CHILDREN ON 0,
NON EMPTY Measures.count ON 1
FROM [objects]
I want count the records with status='C'. But sometimes, there're no records that match that criteria. So if I do:
我想要计算status ='C'的记录。但有时,没有符合该标准的记录。所以,如果我这样做:
SELECT
NON EMPTY [status].[C] ON 0,
NON EMPTY Measures.count ON 1
FROM [objects]
I get this error:
我收到此错误:
*MDX object '[status].[C]' not found in cube 'objects'*
I would like a query that returns 0 in that case. Which is the right way to do that query?
我希望在这种情况下返回0的查询。这是查询的正确方法?
Thanks!
2 个解决方案
#1
The best idea, which is what most client applications do, is to query the metadata to find out what members exist in a dimension and then only issue queries for members that exist.
大多数客户端应用程序所做的最好的想法是查询元数据以找出维度中存在的成员,然后仅对存在的成员发出查询。
The following article lists the various ways of getting at dimension metadata link text
以下文章列出了获取维度元数据链接文本的各种方法
the other "hack" might be to do the following:
另一个“黑客”可能是做以下事情:
SELECT
NON EMPTY StrToSet("[status].[C]") ON 0,
NON EMPTY Measures.count ON 1
FROM [objects]
But that will still not give you a 0, instead it will give you cell set with no columns. And it's not really an approach that I would recommend.
但这仍然不会给你0,相反它会给你没有列的单元格。这并不是我推荐的方法。
#2
I assume from your question that you don't actually have a Status dimension table in your datasource. Instead, you have a dimension defined that is using a column in the Fact table. This works great, but it doesn't allow you to define a fixed set of members for that dimension...you're basically stuck with the facts that you have.
我从您的问题中假设您实际上没有数据源中的状态维度表。相反,您有一个使用Fact表中的列定义的维度。这很好用,但是它不允许你为那个维度定义一组固定的成员......你基本上都坚持你拥有的事实。
The solution, the way I see it, would be to create a separate table called DimStatus, and pre-populate it with all of the valid statuses that might be used by your facts, and have the fact table reference the Status dimension table.
我所看到的解决方案是创建一个名为DimStatus的独立表,并使用您的事实可能使用的所有有效状态预先填充它,并使事实表引用Status维度表。
Then things will work the way you want them to. You can automatically hide unused members, or you can include all of them, or a set of just the ones you want.
然后事情就会按照你想要的方式运作。您可以自动隐藏未使用的成员,也可以包含所有成员,或者只包含您想要的成员。
One caveat is that it will show (null) instead of 0 if there are no matching facts. You can get around this with a simple IIF(ISEMPTY()):
需要注意的是,如果没有匹配的事实,它将显示(null)而不是0。你可以用一个简单的IIF(ISEMPTY())解决这个问题:
WITH MEMBER
[ZeroCount] AS
IIF(ISEMPTY([Measures].[Count]), 0, [Measures].[Count])
SELECT [ZeroCount] ON COLUMNS,
[Status].Members on ROWS
FROM [MyCube]
This will show all statuses and either the count, or a zero. Unfortunately it requires changes to your data source and cube, so hopefully that's an option for you.
这将显示所有状态和计数,或零。不幸的是,它需要更改您的数据源和多维数据集,所以希望这是您的选择。
I played with some queries and I don't think it's possible to do this more easily. You can't create a set with invalid members--that would create invalid tuples, and nothing would work properly. I also tried the "hack," and didn't get it to work as expected.
我玩了一些查询,我认为不可能更容易做到这一点。您无法创建具有无效成员的集合 - 这将创建无效的元组,并且没有任何方法可以正常工作。我也试过“黑客”,并没有按预期工作。
#1
The best idea, which is what most client applications do, is to query the metadata to find out what members exist in a dimension and then only issue queries for members that exist.
大多数客户端应用程序所做的最好的想法是查询元数据以找出维度中存在的成员,然后仅对存在的成员发出查询。
The following article lists the various ways of getting at dimension metadata link text
以下文章列出了获取维度元数据链接文本的各种方法
the other "hack" might be to do the following:
另一个“黑客”可能是做以下事情:
SELECT
NON EMPTY StrToSet("[status].[C]") ON 0,
NON EMPTY Measures.count ON 1
FROM [objects]
But that will still not give you a 0, instead it will give you cell set with no columns. And it's not really an approach that I would recommend.
但这仍然不会给你0,相反它会给你没有列的单元格。这并不是我推荐的方法。
#2
I assume from your question that you don't actually have a Status dimension table in your datasource. Instead, you have a dimension defined that is using a column in the Fact table. This works great, but it doesn't allow you to define a fixed set of members for that dimension...you're basically stuck with the facts that you have.
我从您的问题中假设您实际上没有数据源中的状态维度表。相反,您有一个使用Fact表中的列定义的维度。这很好用,但是它不允许你为那个维度定义一组固定的成员......你基本上都坚持你拥有的事实。
The solution, the way I see it, would be to create a separate table called DimStatus, and pre-populate it with all of the valid statuses that might be used by your facts, and have the fact table reference the Status dimension table.
我所看到的解决方案是创建一个名为DimStatus的独立表,并使用您的事实可能使用的所有有效状态预先填充它,并使事实表引用Status维度表。
Then things will work the way you want them to. You can automatically hide unused members, or you can include all of them, or a set of just the ones you want.
然后事情就会按照你想要的方式运作。您可以自动隐藏未使用的成员,也可以包含所有成员,或者只包含您想要的成员。
One caveat is that it will show (null) instead of 0 if there are no matching facts. You can get around this with a simple IIF(ISEMPTY()):
需要注意的是,如果没有匹配的事实,它将显示(null)而不是0。你可以用一个简单的IIF(ISEMPTY())解决这个问题:
WITH MEMBER
[ZeroCount] AS
IIF(ISEMPTY([Measures].[Count]), 0, [Measures].[Count])
SELECT [ZeroCount] ON COLUMNS,
[Status].Members on ROWS
FROM [MyCube]
This will show all statuses and either the count, or a zero. Unfortunately it requires changes to your data source and cube, so hopefully that's an option for you.
这将显示所有状态和计数,或零。不幸的是,它需要更改您的数据源和多维数据集,所以希望这是您的选择。
I played with some queries and I don't think it's possible to do this more easily. You can't create a set with invalid members--that would create invalid tuples, and nothing would work properly. I also tried the "hack," and didn't get it to work as expected.
我玩了一些查询,我认为不可能更容易做到这一点。您无法创建具有无效成员的集合 - 这将创建无效的元组,并且没有任何方法可以正常工作。我也试过“黑客”,并没有按预期工作。