Hi,
I have a question on designing the fact table. There are 2 tables in my database: OrderHeader,OrderDetail. And the OrderDetail table has different lines for different product.
If the OrderDetail table is the fact table, how can I get the measure on how many orders we have I cannot simply use count(orderno) as the measure because the field,orderno, is duplicate in the OrderDetail table, and analysis services don't support "count distinct" for the measure. Or any ideas for redesigning this cube
Thank you so much!
How to design this cube?
Kevin MacDonald
Well, I've seldom used AS 2000 Standard Edition, so I can't say from first-hand knowledge; but BOL doesn't mention Distinct Count as an Enterprise Edition only feature. In any case, your best bet is to upgrade to AS 2005, since there are some limitations with the DistinctCount aggregation in AS 2000.
http://msdn.microsoft.com/library/default.asp url=/library/en-us/architec/8_ar_ts_1cdv.asp
>>
Features Supported by the Editions of SQL Server 2000
This topic summarizes the features that the different editions of MicrosoftR SQL Server 2000 support.
>>
musichenryviolin
Really But in my AS 2000(SQL Server 2000 standard version), the Aggregate Function in Measure's properties only has 4 options: sum, count, min, max.
How can I add DistinctCount in it
Thanks.
my name is KN
Yuval Rakavy
Thank you, Deepak, I appreciate your kindly reply.
In "Calculated Members", I designed a measure using DistinctCount({[OrderNo]}). But the result is not I want. Any ideas
Nucx
muga
Why do you say that analysis services doesn't support "count distinct" for the measure - DistinctCount measure aggregation function exists in both AS 2000 and 2005:
http://msdn2.microsoft.com/en-us/library/ms175623(SQL.90).aspx#AggFunction
>>
Sum
Additive
Calculates the sum of values for all child members. This is the default aggregation function.
Count
Semiadditive
Retrieves the count of all child members.
Min
Semiadditive
Retrieves the lowest value for all child members.
Max
Semiadditive
Retrieves the highest value for all child members.
DistinctCount
Nonadditive
Retrieves the count of all unique child members.
>>