Primary Key of the Purchase Order (PO) Fact

This is regarding "The Data warehouse Toolkit-Practical techniques to build Dimensional Data warehouse" book.

What is primary key of "PO Line Cumulative FACT" as per chapter 12- "Building a Dimensional Data Warehouse". We can see following columns in the fact table.
List of Columns -
Original_Order_Date, product_key, vendor_key, ship_from_key, ship_mode_key, ship_to_key, mfgr_deal_key, PO_NUMBER (degenerate), PO_Line_num,First_Recevied_Date, Last_Received_Date, First_inspect_date, first_auth_to_sell_Date, first_shipment_date,Last_shipment_date, Last_return_date, QTY_RECEIVED and rest of the measure fields.

As per my understanding following columns makes the primary key (grain) for this fact and also as names suggests PO Line but would like to confirm as it is not explicitly mentioned in the book. Also please let me know if I am missing anything.

Primary Key fields -Original_Order_Date, product_key, vendor_key, ship_from_key, ship_mode_key, ship_to_key, mfgr_deal_key, PO_NUMBER (degenerate), PO_Line_num
Regards
Kenny

Primary key and grain are not the same thing.

'Primary Key' is a relational modeling term, while 'grain' is a dimensional modeling term.

So, primary key means a unique key for a row. In this case, PO number and line. Grain is the dimensionality of the fact.

Thanks Nick for your reply, really appreciate the same.

Have follow-up question on below response. Will try to divide in two parts.

1. I can see your point that that grain and primary key are not same thing because grain is more of identification of business process through various dimensions and there can more than one occurrences of the business process and hence primary key can be different for example in case of Cumulative or snapshot kind of fact.

But in case of Transaction fact, wouldn't the dimensional fields which are part of grain make a unique row.
For example in Purchase Order Line fact considering dimensions keys -Original_Order_Date ,vendor_key ,product_key, it seems source system is created Purchase Order and respective line on below mentioned logic as per the example given in DW toolkit book.

"for a particular date, for a particular vendor, a purchase Order is created and for every product procured an respective Line item is created in source system".

There can be one possibility of multiple orders in the same day, so in that case it is instead of date, time stamp might be driving the grain.
Please confirm if this understanding is correct.

2. Delivery Fact (SAP Source system)
On the same line of defining the grain of the fact, in some source systems like SAP (for consumer goods company which includes warehouse or store delivery sales not retail sales), the grain dimensions are not straightforward, sometime we might have more dimensions involved which are triggering a line item and its not only on basis of Product (like Retail Sales), for example in Delivery Line Item, the dimensions called "Item Category" (SAP concept where how they process each kind of material, for e.g. whether its free of cost or it needs to be charged) and Batch (SAP Concept again for e.g. - how they process each quantity to be delivered depending on some parameter let's say on quality grades of material )

We tried to ask SAP functional guys but they are not exactly sure what all dimensional fields which create the line item for a particular delivery record. There are multiple business scenarios and hence multiple dimensions involved.
So will it be fine in going ahead with Source system Delivery_Id and Delivery_line_number as that's the primary key there and for grain perspective we add the dimensions which we know for know and keep adding the same as we explore further.

So in this case primary key = Source Delivery_id, Source Delivery Line Item will make sure that we got lowest granularity data from source system and then dimensions can be added in near future if anything missing.

Please add if any other better approach you suggest in case of building the Enterprise Delivery Fact considering SAP as one of the major source system.

"wouldn't the dimensional fields which are part of grain make a unique row"

Maybe,  maybe not.  Depends on how you design and load the fact.  It doesn't really matter if it does or doesn't.  In fact, when combining data between fact tables, the going in assumption is always the two have a M:M relationship.

Primary key is all about being able to update a row and define foreign keys.  You would not, could not, include a dimensional key in the primary key if you intend to update that dimensional key.   And a foreign key referencing a fact would not be all the dimension columns in the fact.

I worked with SAP a while ago at a CPG manufacturer.  There is little in the terminology SAP uses that has any basis in common professional use.  For the longest time, SAP touted how BW provides thousands of  KPI's out of the box.  Of course this sounds great to any executive making a purchase decision.  However, what SAP calls a KPI, anybody else would call a column.  Their huge list of "KPI"s includes such high demand management performance indicators as the operator ID and batch number.

So, it is anyone's guess what they call a dimension.  I can't help you from a BW point of view... never actually used it.  When I implemented a warehouse from SAP we bypassed BW completely.

If you are doing order-to-cash type data, you need to build separate facts for orders, fulfillment and invoicing.  Orders itself would need a line level fact and a pricing level fact if you are using their pricing module.  The two tables would be used independently as needed.  It is also useful to build an aggregate of order/fulfillment/invoice data.

Let me give you an example...

Let's say I design an aggregate fact table containing month end account balances. Balances can be adjusted, but I don't want to bother updating the table, rather I would only prefer to insert rows. The job is run weekly with balance changes added each run.

The dimensionality of the new rows may be identical to the dimensionality of existing rows. It doesn't matter as the measures are fully additive and I can get the current balance with a simple query. The table may or may not have a primary key, but since rows are never updated, that doesn't matter either.