Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculated Member Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 9, 2013 7:13 AM
Points: 4, Visits: 23
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

Post #1434982
Posted Tuesday, March 26, 2013 5:02 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 467, Visits: 867
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]
)




Post #1435712
Posted Tuesday, March 26, 2013 6:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 9, 2013 7:13 AM
Points: 4, Visits: 23
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.
Post #1435723
Posted Tuesday, March 26, 2013 6:56 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 467, Visits: 867
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.
Post #1435725
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse