Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculated Member Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, March 25, 2013 9:34 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, October 31, 2014 8:26 PM Points: 7, Visits: 25
 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 intDim1 :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_DIM21 0 12 1 23 2 34 0 4What 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
 Mr or Mrs. 500 Group: General Forum Members Last Login: Sunday, November 20, 2016 8:55 PM Points: 559, Visits: 994
 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 intDim1 :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_DIM21 0 12 1 23 2 34 0 4What 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 advanceI 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 ROWSFROM(Select ( - {[Dim1].[IDDIm1].[IDDIm1]&[0]} ) ON COLUMNS FROM [NAME OF CUBE])`
Post #1435712
 Posted Tuesday, March 26, 2013 6:42 PM
 Forum Newbie Group: General Forum Members Last Login: Friday, October 31, 2014 8:26 PM Points: 7, Visits: 25
 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
 Mr or Mrs. 500 Group: General Forum Members Last Login: Sunday, November 20, 2016 8:55 PM Points: 559, Visits: 994
 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

 Permissions