Calculated Member

Question

Calculated Member

filipetatarli

SSC Rookie

Points: 47
More actions
March 25, 2013 at 9:34 am

#275550

I'm studying MDX and can't solve one problem.
To simplify there is a fact table (fact_Proc) and two dimension (Dim1 and Dim2). The structure is:
fact_Proc :
id int identity(1,1)
id_Dim1 int
id_Dim2 int
Dim1 :
id_Dim 1 int identity(1,1)
Dim2:
id_Dim 2 int identity(1,1)
And then I filled with some data the fact table:
ID ID_DIM1 ID_DIM2
1 0 1
2 1 2
3 2 3
4 0 4
What i want is count the distinct register of the ID_DIM1 column without consider the 0 value in fact table.
How i do it using the calculated member ? Do i need create a measure first ?
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

davoscollective SSCertifiable Points: 6355 More actions · Answer 1

filipetatarli (3/25/2013)
I'm studying MDX and can't solve one problem.
To simplify there is a fact table (fact_Proc) and two dimension (Dim1 and Dim2). The structure is:
fact_Proc :
id int identity(1,1)
id_Dim1 int
id_Dim2 int
Dim1 :
id_Dim 1 int identity(1,1)
Dim2:
id_Dim 2 int identity(1,1)
And then I filled with some data the fact table:
ID ID_DIM1 ID_DIM2
1 0 1
2 1 2
3 2 3
4 0 4
What i want is count the distinct register of the ID_DIM1 column without consider the 0 value in fact table.
How i do it using the calculated member ? Do i need create a measure first ?
Thanks in advance

I don't really understand this structure, I realize you've simplified for the question but perhaps too much. Your fact_proc has an ID identity key that is sort of a surrogate key but it doesn't link to any other table. The only other two fields are keys that link to the two dimensions. What is the measure (fact) in the fact table? If the fact table only consists of keys there is not much you can do with it.

If you had an "actual measure" in that table then you could do a count on it, and exclude dimension references where the ID_DIM1 is 0.

Assuming you'd defined [fact_proc].[actual measure count] in the cube,

Perhaps like this

Select [fact_proc].[actual measure count] ON COLUMNS,

{} ON ROWS

FROM

(Select

( - {[Dim1].[IDDIm1].[IDDIm1]&[0]} ) ON COLUMNS

FROM [NAME OF CUBE]

)

filipetatarli SSC Rookie Points: 47 More actions · Answer 2

Thanks for your answer. There are more fields in dimensions, but i don't put in the example.

I don't understand one thing that you said:

"If the fact table only consists of keys there is not much you can do with it."

My fact table contain only the ID columns.

I'm thinking in using the Count of the ID like a measure. For these reason I'm not thinking how I create a calcuted member to count the ID excluding the 0 value.

Tomorrow I will try implement your ideia, thanks again.

davoscollective SSCertifiable Points: 6355 More actions · Answer 3

What I mean is that ID's are usually foreign keys that link to other tables. In a datawarehouse you should be using surrogate keys for all your dimensions and link them to the fact table. The combination of all the dimension surrogate keys within the fact table make up the primary key of the fact table, it doesn't really need it's own different primary key.

A measure should be numeric but not an ID. An ID will be slow, and not amenable to fast aggregation.

For example it should be a currency amount, or a count indicator.

So for example if you are counting, you should have a column specially for the count, and just contain a 1 in it if it exists. Don't put a 0 in this column if it doesn't exist, it's unnecessary and will make querying slower. If the source datamart/datawarehouse does not have a row for this particular dimension/measure intersection then the cube should not have a cell, i.e. it should be EMPTY (doesn't exist - like a 'hole' in the matrix).

Cubes are supposed to be sparse, meaning they should not contain blanks or zeros, only EMPTY cells. These will either return as NULL in a query or not at all (depending on your query) If you must report zeros (for example a monthly total where you don't want to skip empty months) you can just use isempty() in an MDX query.

I don't think you should re-purpose an auto-incrementing ID field as a measure. If you have a new column containing only 1's then the cube when processed will automatically sum these values across all the dimensions for you ( and the sum of existing 1's is exactly the same as a count), and the MDX queries will be fast.