Counting the number of facts in a cell in MDX

Question

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
)

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]

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]

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(*).)

I absolutely can't get anything to work!

score 2 · Answer 1 · edited Mar 06 '20 at 12:08

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 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.

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.

@Richard Barraclough does it help? – MoazRub Mar 01 '20 at 08:54 — MoazRub, Mar 01 '20 at 08:54

Counting the number of facts in a cell in MDX

1 Answers1

Linked