计算MDX中单元格中的事实数 [英] Counting the number of facts in a cell in MDX

问题描述

这是一个具有两个维度的简单模式，一个带有量度的事实.

Here is a simple schema with two dimensions and a fact with a measure.

CREATE TABLE DimThingType (
    ThingTypeID          int        NOT NULL PRIMARY KEY,
    ThingTypeDescription varchar(8) NOT NULL
)

CREATE TABLE DimThing (
    ThingID   int        NOT NULL PRIMARY KEY,
    ThingName varchar(8) NOT NULL
)

CREATE FactFacts (
    FactID      int NOT NULL PRIMARY KEY,
    ThingID     int NOT NULL,
    ThingTypeID int NOT NULL,
    Turnips     int NOT NULL
)

现在在MDX中，我们可以为每种事物添加萝卜的数量.

Now in MDX we can sum the number of turnips for each thing type.

SELECT 
    NON EMPTY { [Measures].[Trunips] } ON COLUMNS, 
    NON EMPTY { ([ThingType].MEMBERS) } ON ROWS 
FROM [Things]

现在，如果在此度量值组中创建一个名为[Count of Facts]的新度量值，并且属性Source.[Binding Type]设置为[Row binding]，而AggregateFunction设置为Count，那么我还可以计算事物每种种类.

Now if in this measure group I create a new measure called [Count of Facts] with property Source.[Binding Type] set to [Row binding] and AggregateFunction set to Count then I can also count the number of things of each thing type.

SELECT 
    NON EMPTY { [Measures].[Trunips], [Measures].[Count of Facts] } ON COLUMNS, 
    NON EMPTY { ([ThingType].MEMBERS) } ON ROWS 
FROM [Incidents] 

我真的必须添加这项新措施吗? 数字是用于计算结果单元格中的值的事实数，因此我无法通过查询获得该数字吗? (如果我们要对SQL进行分组，则将其简单地称为COUNT(*).)

Do I really have to add this new measure? The number is the number of facts that were used in computing the value in the result cell, so can't I obtain that via the query? (If we were grouping in SQL then it would simply be COUNT(*).)

我绝对无法工作！

推荐答案

是的，您需要添加一个新的度量值(尽管它们也是一种替代方法).多维数据集在处理时将事实值存储在N维单元中.因此，在查询时，您只能检索存储的内容.与sql不同，多维数据集不会在运行时对其求和.它将只获取该单元格中的值.

Yes you would need to add a new measure(although their is an alternative too). A cube stores fact values in an N dimensional cell while being processed. So while querying you can only retrieve what was stored. Unlike sql the cube will not sum it at runtime. It will just fetch the value in that cell.

有关如何解决查询的详细信息，请继续阅读.

For details how your query is resolved read on.

SELECT 
    NON EMPTY { [Measures].[Trunips] } ON COLUMNS, 
    NON EMPTY { ([ThingType].MEMBERS) } ON ROWS  FROM [Things]

MDX查询在N维平原的地址中解析.在上述情况下，您有两个维度，因此它是一个二维平原的地址.

MDX query is resolved in an address of an N dimensional plain. In the above case you have two dimensions, so it is an address of a two dimensional plain.

现在，您已忽略一个维度，而对于第二个维度，则使用了所有可能的值.

Now you have ignored one dimension and for the second dimension you have used all its possible values.

因此地址为({d1:dn},{All},{Turnips}).其读为 对于该单元格，第一个维度的所有值均未分组列出"，第二个维度的所有值均分组"，返回测量萝卜".现在，在此单元格上没有计数度量.

So the address is ({d1:dn},{All},{Turnips}). Its read as "All values of first dimension listed ungrouped", "All values of second dimension grouped", for that cell return "Measure turnips". Now on this cell there is no count measure.

这篇关于计算MDX中单元格中的事实数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！