Hi,
Suppose I have following Model Dimension which is formed based on Junk Dimension definition.
JunkID
ModelID
ModelCode
ModelTypeID
ModelTypeCode
ModelSpecificationID
ModelSpecificationCode
50
50
BAO
2
FFCVT
2
F1C1
53
53
BGO
1
FFAT
1
FW4A4
55
55
NBO
3
FRAT
9
V4A5
60
60
NCO
3
FRAT
9
V5A5
97
55
NBO
3
FRAT
11
V4A5
99
53
BGO
1
FFAT
13
F4A4
And In Fact Table Suppose there is data as below...
JunkID
Amount
50
5210983
53
9576
55
7994
60
7872
97
4306
99
6519
Now my requirement is to make Distinct Count on ModelID. If I makes Distinct Count on DimModel.ModelID then it gives me 5 on every combination in Cube. If I make Distinct Count on FactTable.JunkID then it gives me 6 in places of 5 as above seen data.
Please help, how can I make Distinct Count on ModelID in this situation?
Thanks in Advance
Bhudev
Are you excluding the ALL member? Take a look at the code sample below? It calculates a count 3 ways. In the first, I count the members of the dimension's attribute hierarchy. It includes the members from all levels including the ALL member because I didn't specify a level. In the next one, I specify the leaf level of the hierarchy which does not include the ALL member. The third example uses DISTINCTCOUNT.
Code Snippet
with
member measures.a as
COUNT([Geography].[Country].Members)
member measures.b as
COUNT([Geography].[Country].[Country].Members)
member measures.c as
DISTINCTCOUNT([Geography].[Country].Country.Members)
select
{[a],[b],[c]} on 0
from [Adventure Works]
;
Good luck,
Bryan
Hi,
It works fine when we are making Calculated Members, but when Analysis Services 2005 has introduced Junk Dimension Concept. So I feel it should give facility to add Distinct Count Cube Measure based on its [Junk Dimension] associated Attribute Dimensions. As per my understanding if there is Junk Dimension and we have very important Attribute Dimension, in Junk Dimension, On which I have to add Distinct Count Measure which can be used across all combination, In that situation we can not, because I can add Distinct Count only on JunkID which will be wrong or not of my use, As I have explained above.
But we can add Several Calculated members as per our need.
Thanks Bryan C. Smith for your suggestion overall it was helpfull.
Bhudev
|||Just to be clear, SSAS 2005 didn't add support for Junk dimensions. Junk dimensions are a concept from the Kimball group that are documented in their "The Data Warehouse Toolkit" and other books. They can be implemented in SSAS 2005 (and just about any other technology that supports regular old dimensions). :-)
B
