Order Dimension and Order Fact

Hello All,

I am creating a model. In the data mart/warehouse, I have the fact tables, Orders and OrderItem (transaction). In my model, I have used the Orders table as both a fact and dimension...to satisfy how the users see the data relationally. In the Order Fact, resides the OrderID, Order Total, Items Per Order. In the OrderItem Fact, resides the OrderID, ItemID, Line Total, Quantity_Ordered. The Order dimension provides the information about the orders. Using the two facts separates granularities of order level and item level. This works PERFECTLY. Is this common? Good, Bad design?

Thanks,
Krystal

Who are we to say something that works perfectly is the wrong solution. The only comment I have is whether the order grained fact table is even needed. I.E. is the peformance boost from the aggregate table that significant?

Well, the users were becoming confused with the dimensions. Some dimensions are specific to OrderItem fact and others Order fact. Or measures were being jumbled together. For example, number of orders is at the Order level and number of orders per item is at the OrderItem level. Should I view say Order Method with number of orders, it's great, then if I add Item to that, the numbers change to the OrderItem level...in which you should use number of orders per item. If I separate the two, the measure, number of orders, would show a repeating value if used with the Item dimension, thus denoting it does not apply to that dimension...makes sense.

What do you mean by 'performance boost'? If viewed in ROLAP, yes, because calculations will not be affected by the differing grains.

By performance boost I am asking whether the queries at the order level are faster than the same query at the item level. If the increase in response time is neglible, then it may be that the Order level fact is optional. For example, an order count could be calculated as a select distinct on a degenerate dimension order id in your item fact. Select distincts can be woefully slow, so clearly there would be a "performance boost" in the order count metric. If the users like it and it works well, I would continue to do what you're doing.

Ahhh, yes. Not only that...select distincts only work for reporting and not calculations. For example if you are looking at the item level and the count of orders (select distinct on orderid)...then the distinct value will show however if that field were to be used in a calculation, the actual number used is not the distinct count but the transactional count at the item level.

Example:

OrderID 1
ItemID 2, 3, 5

The count here would be 3 for this order instead of just the 1. The count distinct does not work in calculations. I thought of using a running total for ranking but that's still attaching the order count to a item and I want them to be independent of the other...

Thanks for your input. I was afraid I had done something crazy in the design.

I still can't see why DISTINCT COUNT wouldn't work. I guess you should refer to Kimball's dimensional modeling book, second edition, chapter 5, Order Management.

In essence, the preferred model is to 'allocate' order header-level facts down to line-item level, as B&L suggested. A separate higher-level fact has some inherent usability issues when the facts need to be explored by product as the product isn't identified in the header-grain fact table. And as B&L rightly said, the existence of higher level fact tables is purely for performance if needed, nothing else.

That's interesting...

Because if I am looking at the Number of ORDERS by order type...I get Number of distinct orders. However, if I add, say the item to that...it is no longer distinct orders but orders per item (the measure drops to the lower grain) BUT the summary line still shows distinct orders. This is confusing for the users since the measure is called 'number of orders' but shows the 'number of orders per item'. I have referred to the book hon but for these users they prefer to see measures at the order level completely separate from the item grain. Using...Distinct Count is ONLY good if I am looking at Order grain attributes. Should I look at the item level attribute with that measure...it shows item level values (which is not what the measure is).

Let me give some background, these users are using Cognos Query Studio to consume the data. The way distinct count performs there is not acceptable. Because obviously, if the number of orders measure is associated with the item dimension...then it should not allocate because number of orders is an order level measure... Now, in report studio using MDX, etc. this is a different story but for them to pull their own ad-hoc reports, they'll need something more....friendly. Here is an example of what is happening:

Order Method | Number of Orders
AutoReorder | 1
Mail | 2
---------------------------
Summary 3

After adding items:

Order Method | Item | Number of Orders
AutoReorder | 497 | 1
AutoReorder | 502 | 1
Mail | 444 | 2
Mail | 323 | 1
---------------------------
Summary 3

The second is showing Number of Orders per Item but the column name says Number of Orders and the summary shows number orders but the data is showing Number of Orders per Item. Are you saying that is not confusing???? Now, I know that this is TECHNICALLY correct but...it's nowhere near user friendly.

What about this. Create an order view on top of the order item table simply be COUNT(*) ... GROUP BY OrderNo to achieve what you would get from the order table. Just remember, once you have your base count, you should use SUM for further aggregation.

That's....what I am stating I did... Only, their is an Order table in the data warehouse already so no view is necessary. And count of orders isn't the only measure at an order level...there's 5 others.

Maybe I misunderstood your comments, but it's Not exactly the same. The physical order fact table with measures are loaded from operational source. The other one is through a view based on low level order item fact table. The point is you don't need a physical higher level fact table if not for PERFORMANCE BOOST. I guess the important concept from Kimball in this case is 'Allocation'. Let me try to explain it in more details if Kimball's book is not within you reach.

To have reconcilable results across different levels of facts, you should always, if possible, build the higher level facts on top of the base level fact instead of having separate ETL process and loading them independently. In case of order management, order header facts including 5 others (eg. order shipping charges), should be allocated to order item fact tables so that you are able to analyse these facts by the product dimension. There might be some political reason stoping you from doing so. However from technical perspective, allocation is the preferred model for order management. If you think the current model works fine, stay with it. What we can do in this forum is to propose best practice solutions if people are not confident about what they doing.

I understand what you are saying. I suggested on my part as the modeler to build the view instead of the physical table, but I have no control over my ETL developer. As it is, there exists a physical table for both orders and orderitem. The order table resembles exactly the results as if I would have written a view on top of the orderitem table. But that's not the original question. There is a data warehouse and then there is modelling FROM the warehouse, correct?

So what I was stating was that my MODEL, this would be a DSV in Microsoft or the model in Cognos Framework Manager, contains two facts; Order and OrderItem, and a conformed Order dimension between them. Just as you can model two separate date dimensions (OrderDate, ShipDate) from the same Date dimension, I am doing that on the Order table. Only one is a fact and the other is a dimension. This is what I was asking about. The model not the warehouse.

The following design tip gives some more convincing points:
http://www.kimballgroup.com/html/07dt/KU95PatternsAvoidHeader-LineItem.pdf

Thanks hang,

Tool tip #25 was the helper. But this is all information that I know. I understand the position here (rolling the business by product) but that's why I conformed all the dimensions at both order and product levels so that eliminated that issue. Next, there are certain degenerate attributes that apply to ORDERS only...so bringing the orders to the item level is unacceptable because the product shouldn't be associated with that attribute.

So I guess I would suffice it to say that this design was driven by the business and not best practice. The best practice was ENTIRELY too confusing...having to KNOW which measure to use with which dimension.

Thanks again!

kclark wrote:Next, there are certain degenerate attributes that apply to ORDERS only...so bringing the orders to the item level is unacceptable because the product shouldn't be associated with that attribute.

Huh??? How is the product associated with the attribute? We are talking about an order line fact table are we not? The attributes are associated with the fact, not other related dimensions. Order attributes are part of the order, the line is part of the order... ergo... why wouldn't the line assume attributes of the order?

You are thinking relationally... not dimensionally.

Yes my thinking had to be relational because of the business demands. Here's an example:

An item can be formulary or not. That is an attribute of an ITEM only. So, if I wanted I could see the number of times a nonformulary item was ordered...no problem. Now, should the users want to see the number of orders that contained a non-formulary item then I would need to have the attribute on the ORDER as nonformulary or formulary.

Now, I know what you are going to say...filter for non-formulary items and do count distinct on the order number. That may work in a report with the use of MDX but not for ad-hoc. Simply because, the summary will be the distinct count of orders BUT should I drop in item name, the records that make up the summary are count of non-formulary ITEMS. The example above depicts this.

Month/Year | Product | Count of Orders
----------- -------- -----------------
08-2010 | Gloves | 3
08-2010 | Ice | 2
08-2010 | Bread | 1
-------------------------------------------
Summary 3


Ideally, Count of Orders should not be allocated at the Product level. However, I am an analyst, I would NEVER use the Count of Orders metric with the Product dimension...but the users aren't thinking that way. Also, should I do a calculation on the Count of Orders measure...it's dropping the distinct and just using count so when the users do a manual calculation for verfication, it's not the number expected because they SEE count of orders but the calculation is using count of items (again this is fixed in a report with code; but this is built for the user's to do their own analysis with the absence of code).

How would the business demand a physical database implementation? And, if you are doing what the business demands why do you dismiss the notion of counting orders by product?

Any reasonable person understands that if they count the number of orders by product (or attributes of product) that they may count the same order more than once. It is also a very common thing end users want to see.

The thing is, it is unusual to have an order dimension. Also, if you implement both an order level fact and an order line fact then the order line fact should assume all dimensions of the order level fact. Having both facts is also not common, but necessary in certain situations. However, from what you have described, it doesn't seem like an order level fact table is necessary.

Ok...physical database implementation??? No, I am speaking of the model. So IF my back end has only the OrderItem fact, I could still write a view for an Order fact, correct? Even if I didn't do that, I could design an Order fact in my model (Cognos Framework Manager or SSAS) right?

LOL! I do not dismiss the notion of counting orders by product. I am SEPARATING the count of orders and the count of orders per item. There is a difference, wouldn't you agree? Like I have said over and over again, the measures are DIFFERENT. Count of Orders is DISTINCT and Count of Orders by product is not. So if you review the previous posts that the use of the metric yields a different summary line...which is confusing to the users. This crazy design is because the "reasonable" people you are referring to think that the way I designed it (what you are speaking of now) is confusing so that's why I am separating the two.

This is difficult to explain because I am not speaking of the physical db...but of what you manipulate it into AFTER it's in the dw for ROLAPS and MOLAPS.