How to design this cube?

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!

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

>>

Aggregation function Additivity Returned value

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.

>>

|||

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.

|||

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 Microsoft? SQL Server? 2000 support.

>>

|||

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?

Deepak Puri wrote:

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 Microsoft? SQL Server? 2000 support.

>>

|||Since you're using AS 2000, try: DistinctCount([OrderNo].Members)|||Thank you Deepak. You are the man!