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!

How to deploy project changes without wiping out cube data?

I have several cubes deployed with several years worth of historical data. Every time that I add a measure item or make any minor change to the cube or project from BI Dev Studio and then deploy it to the server, it wipes out all of my cube data causing me to reprocess all of the cube partitions again. Is there a way to avoid having to do this or a better way to deploy metadata changes to the cubes?

Thanks!

Hello!

I do not understand the difference or any change in behaviour since OLAP Services(7). Since then, every time you make a major structural change to a cube, like adding a new measure, you have been forced to full process the cube and all related dimensions.

Nothing have changed in SSAS2005.

Regards

Thomas Ivarsson

|||Set the properties for your SSAS solution so that the Deployment Mode is "Deploy Changes Only" (This should be the default) and the Processing Option is "Default". If the changes are "minor", a full rebuild will not be required. Adding a new measure will require a complete rebuild of the measure group though.

How to define KPI dynamically?

Hi, all here,

Thanks for your kind attention.

I wonder is there any way for us to define a KPI dynamically? What I mean is whenever we browse the cube across different levels with a KPI, we can always see the KPI bound to different levels? E.g. when I browse sales for a specific region in a particular year, I can browse its KPI, and then when I change the level to a particular country instead of region level, still I am able to see its KPI.

Hope my question is clear.

I am looking forward to hearing from you shortly for your kind advices.

With best regards,

Yours sincerely,

Hi , dear experts here,

Any advices? Thank you.

With best regards,

Yours sincerely,

|||

This is how KPI's work any way - If you define the KPI components based off values or calculations in your cubes.

If you defined a KPI as measures.[Gross Sales] you would see a different value for a city as opposed to a region or country.

|||

Hi, Darren,

Thank you for your advices.

Could you kindly post an example piece of code here for the above KPI? E.g. [Gross Sale] across different levels of region or country. Thank you.

With best regards,

Yours sincerely,