MS SQL Server OLAP - AS2000 granularity problem

Author

AS2000 granularity problem

Kristi

2006-03-26, 8:27 pm

I’m hoping someone can lend a hand here. In a nutshell, we’re trying to
display data at the leaf level of a dimension when the real granularity of
the data is one level above the dimensional leaf level. I’m not sure this
question can be answered in a cube, but I’m hoping someone might have a
bright idea.

I work for an acute-care healthcare company and we’re developing a cube for
the surgical services departments. Due to the medical terminology in surgical
services, I will use a restaurant analogy to describe the problem we are
facing. Understanding the medical terminology is not relevant to this
question.

Lets assume a restaurant owner is interested in creating a cube that will
track the number of minutes it takes to prepare his orders and each dish
within an order. Each order can have one or more dishes.

EXAMPLE: An order might contain “Eggs Scrambled” and “steak T-Bone”.

Unfortunately, the available data does not make it possible to track the
length of time it takes to prepare each dish. Because of this constraint,
the business owner makes a decision that all dishes within a particular
order will have the same number of minutes.

EXAMPLE: An order for “Eggs Scrambled” and “steak T-Bone “ might take 20
minutes in total to prepare. As a business rule, it is decided that when the
cube is displaying the # of minutes to prepare just the “eggs Scrambled”, it
should show 20 minutes and the # of minutes to prepare just the “steak
T-Bone” should show 20 minutes. Each dish is to report to have taken 20
minutes to prepare individually; however, when the entire order is rolled up,
it needs to show 20 minutes, not 40 minutes. I know, this is weird…

It’s also possible that a single order might contain multiples of the same
dish.

EXAMPLE: An order might contain two “Eggs benedict” dishes. The order might
have taken 15 minutes to fulfill. If the cube is being viewed from the dish
level for this order, each “Eggs benedict” dish would need to display 15
minutes.

In addition, the business owner would also like to group the dishes by
category(see dishes by category below).

EXAMPLE: If the business owner wanted to look at the dish Eggs Category for
the previously described orders, the total sum of minutes should be 35. The
Two “Eggs benedict” dishes would only counted as 15 minutes because they
belonged to the same order.

dishes by category :

Sandwich Category:
Sandwich Ham
Sandwich Cheese
Sandwich beef
Eggs Category
Eggs benedict
Eggs scrambled
Eggs omelet
Steak Category
Steak fillet mignon
Steak T-bone
Steak rib-eye
Steak Salisbury

Thank you for taking the time to look at this.

Deepak

2006-03-26, 8:27 pm

The new many-many dimension support in AS 2005 might be useful here. But if
you're limited to AS 2000, one approach is to create 2 cubes based on the 2
fact tables at different levels of detail, and combine them in a virtual cube:

http://groups.google.com/group/micr...cd8edc48d44819c
General OLAP question

From: Deepak Puri
Date: Thurs, Jul 28 2005 9:03 pm
Groups: microsoft.public.sqlserver.olap

Depending on what questions need to be answered, one approach to
"order-line-item" type of data is to create 2 cubes and combine them in
a virtual cube. The first cube fact table could be at the computer
level, the second at the component level. The second cube would share
all parent dimensions of the first cube, but have additional dimensions
like "component type".
....[color=darkred]

"Kristi" wrote:
[color=darkred]
> I’m hoping someone can lend a hand here. In a nutshell, we’re trying to
> display data at the leaf level of a dimension when the real granularity of
> the data is one level above the dimensional leaf level. I’m not sure this
> question can be answered in a cube, but I’m hoping someone might have a
> bright idea.
>
> I work for an acute-care healthcare company and we’re developing a cube for
> the surgical services departments. Due to the medical terminology in surgical
> services, I will use a restaurant analogy to describe the problem we are
> facing. Understanding the medical terminology is not relevant to this
> question.
>
>
> Lets assume a restaurant owner is interested in creating a cube that will
> track the number of minutes it takes to prepare his orders and each dish
> within an order. Each order can have one or more dishes.
>
> EXAMPLE: An order might contain “Eggs Scrambled” and “steak T-Bone”.
>
> Unfortunately, the available data does not make it possible to track the
> length of time it takes to prepare each dish. Because of this constraint,
> the business owner makes a decision that all dishes within a particular
> order will have the same number of minutes.
>
> EXAMPLE: An order for “Eggs Scrambled” and “steak T-Bone “ might take 20
> minutes in total to prepare. As a business rule, it is decided that when the
> cube is displaying the # of minutes to prepare just the “eggs Scrambled”, it
> should show 20 minutes and the # of minutes to prepare just the “steak
> T-Bone” should show 20 minutes. Each dish is to report to have taken 20
> minutes to prepare individually; however, when the entire order is rolled up,
> it needs to show 20 minutes, not 40 minutes. I know, this is weird…
>
>
> It’s also possible that a single order might contain multiples of the same
> dish.
>
>
> EXAMPLE: An order might contain two “Eggs benedict” dishes. The order might
> have taken 15 minutes to fulfill. If the cube is being viewed from the dish
> level for this order, each “Eggs benedict” dish would need to display 15
> minutes.
>
> In addition, the business owner would also like to group the dishes by
> category(see dishes by category below).
>
>
> EXAMPLE: If the business owner wanted to look at the dish Eggs Category for
> the previously described orders, the total sum of minutes should be 35. The
> Two “Eggs benedict” dishes would only counted as 15 minutes because they
> belonged to the same order.
>
>
>
> dishes by category :
>
> Sandwich Category:
> Sandwich Ham
> Sandwich Cheese
> Sandwich beef
> Eggs Category
> Eggs benedict
> Eggs scrambled
> Eggs omelet
> Steak Category
> Steak fillet mignon
> Steak T-bone
> Steak rib-eye
> Steak Salisbury
>
>
> Thank you for taking the time to look at this.
>
>
>
>
>
>
>
>
>
>
>

Sponsored Links