Invoice dimension - best way to model it

We have a system that will have ~15 million invoices per year. We would like to track lots of very unique attributes of each invoice (invoice number, etc). Currently we are using a junk dimension ([Dim Invoice Attribute]) that contains 4 or 5 of the attributes, but our changes will require going to the grain of the invoice table. I have read having a physical dimension table that has a 1:1 cardinality to the fact table isn't a good idea (strictly because of the size of the table and the operation of joining fact to dimension). Currently we have 2 or 3 attributes of the invoice in the fact table (degenerate dimensions) in order to fulfill relational queries for tying things back to the MDX results.

My question is what is the best way to model this? I have read both sides of the story - create an invoice dimension table, and store everything there with keys relating back to the fact table, and also store all of the attributes in the fact table and create a named query in the .dsv that selects out only the attributes for the [Dim Invoice] dimension, thus saving space and join energies during processing.

Can anyone shed some light on this?

Thank you in advance,

John Hennesey

Typically in this situation, you have a fact table that represents the invoicing process. One of the dimensions is the invoice which will most likely have a one-to-one relationship with the fact. Does this describe your model?

Kimball would describe this as a degenerate dimension and would recommend you move the dimension into the fact table. SSAS provides support for this through a fact dimension. (See Books Online topic "Dimension Relationships" for a more detailed description.)

So this works if we're talking about a fact dimension with just a few attributes. After that, the model starts to look a little sloppy. I would look at the cardinality of some of these other attributes. If the cardinality is relatively low, you may want to move these into their own dimensions or if the cardinality is very low lump them into a junk dimension.

The other thing I would consider is the user's experience. How often will users be accessing these other dimensions created to hold those attributes? If these are presented separate from the Invoice dimension, would these seem weird or be cumbersome for your user? Do you plan on providing user-hierarchies that role your invoice number up to these attributes? If not, then I'd recommend going ahead with moving them out.

From a performance standpoint, I don't think your design choice has too big an impact on SSAS. If you figure that every attribute hierarchy is given its own storage and the only thing that would be different internally would be the relationship maps. The impact of having or not having these depends on how you intend to use your attributes.

So, I guess my general recommendation would be keep to standard, Kimball principles and put an emphasis on end-user experience. If you run into a performance problems down the road, you should be able to easily tweak the model.

If others folks have thoughts on this, esp regarding how the SSAS engine handles this stuff, please, please chime in.

Thanks,
Bryan

|||

I agree with the approach of keeping the invoice attributes in the fact table.

One issue is the number of invoices(metadata) that will be downloaded to the client when they use the invoice dimension. This will be a really slow process if you show to many invoice members and the solution is to create artificial levels above the invoice number. I build the first level with the first character in the invoice number using the TSQL-left function in the data source view.. A guide line is to have no more than 10-15 groups on each level. On the leaf level you have have larger groups.

I have asked the SSAS2005 development team about any performance issues with building a fact table dimension and they have no negative impact.

HTH

Thomas Ivarsson

|||

Interesting - I have heard arguments on both sides of the argument. To implement a fact table dimension, it would be using a named query that goes against the fact table, right? Is there a special way to set up a fact table dimension (any attributes of the dimension itself?) I have also read in just about every article it is necessary to set up a hierarchy to limit the number of members returned to the client - indeed a very good idea.

Thank you all for your input,

John Hennesey

|||

Hello John! Or use named queries in the fact table for each artifical level in the invoice hierarchy/dimension.

Good luck!

/Thomas

Inventory Stock, Triggers vs Views/SP

Hello..

I am designing a Database Application that covers Inventory System. And I am now in a dilemma of chosing which design to track Inventory stock better, in performance, reliability, and error free?

1st Design

PRODUCT TABLE
ItemID
ItemName
Price
QtyOnHand
..and other unique info of the product..

SALES TABLE
SalesID
Date
...etc...

SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price

PURCHASE TABLE
PurchaseID
Date
...etc...

PURCHASEDETAIL TABLE
PurchaseID
ItemID
QtyPurchase
Price
...etc...

and similar design with SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL

Tracking Inventory stock is done by using (update, insert and delete) triggers in each of the DETAILS to update the QtyOnHand in the PRODUCT TABLE

2nd Design

PRODUCT TABLE
ItemID
ItemName
Price
...etc...

INVENTORY TABLE
ItemID
QtyBegin
...etc...

SALES TABLE
SalesID
Date
...etc...

SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price
...etc...

and similar design with PURCHASE+DETAIL, SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL

The later design does not hold QtyOnHand, but only save QtyBegin instead. To get the QtyOnHand, it uses views/stored procedure with Union Query, so it looks like this:

QtyOnHand = QtyBegin + Sum(QtySold) + Sum(QtyPurchase) + Sum(QtySalesReturn) + ......

And at the end of a accounting period, the calculation of the QtyOnHand will be the QtyBegin of the next accounting period.

According to you guys, which way is better in PERFORMANCE, RELIABILITY, ERROR FREE, and why? What are the pros and cons of these two?

Thanks a lot.Hi

I would suggest to have design 2 as my option. Performance without reliability is of no use. There is no meaning in giving a wrong information to the user.

In the first design, you are storing the stock of each item whenever there is a movement of the product, either inward or outward. As per your design, let us assume, initially you are having a product, Product A, with no stock. Now you make a purchase on 01/23/2007 for quantity 50. Now the QtyOnHand will have value 50. Suppose you make a sales on 01/25/2007 for 30, as per your design you will update the QtyonHand field, which will now become 20. Now, if I need to get the stock of Product A on 01/04/2007, I cannot use the value in the field QtyonHand, which will be wrong. In this case it will be 20. But actually it must be 50. So we need to calculate the stock by adding the inwards and deducting the outwards. Also if you allow backdate billing, stock updation will become a serious issue.

In the second design, I accept that the performance will be lesser than the first one. But by careful designing that can also be solved. For eg, having a seperate table which contains a date field, Product key and QtyonHand fields. For each purchase, sales and other product movement transactions, you can update this table for each day. There can be several other methods as well. If you come across please intimate me as well.

with regards

Abdur Raoof M|||For what it's worth...
No matter which solution you choose, don't forget to leave room to account for stock transactions that are not
related to sales and receipts, such as damage or shrink.
Invariably, Beginning inventory-sales+purchases<>actual ending inventory.|||@.Abdul Raoof

I very much agree that the 2nd one is far more reliable, but I am still wondering how much more do I have to pay for the reliability? I have done the 2nd one before, and the difference to load the 'item' table without calculating qty compared to load a view that calculates the QtyOnHand with many tables related (although there are only 2 tables with more than 30000 rows) is about 1 sec.

And thanks for minding me the problem of the 1st design, never thought of that before.

@.RedNeckGeek
I think I have included Adjustment Table if that's what you mean?
Beginning inventory-sales+purchases-adjustment = actual ending inventory :)

Thank you Guys.

Anyone? Please... feel free to comment...