Sales DW Model

[JagWalia]

We are designing new DW for multilevel marketing.

1. Sales Order
Item Category Master
Item Master
Sales Order (That involves sales order activity like order initiation, payment made, shipment, picked, Returned)

2. Sales Force (Selling Products)
Units
New/Reinstate/Inactive Recruits
Multilevel Parent / Child Hierarchy

3. Forecast

Questions:
1. How to structure Sales/Orders fact table and log all the activity for the order? Do I keep the activity in separate factless fact table? So when there is a new order, I add a row in fact table, but other information will not be available at that time, like payment date, ship date etc. If I keep all the activities in separate table, do I go back to fact and update the dates in the fact table or insert a new row for every activity; if I do it then I do not need to maintain another factless fact table. However if I keep all the activities in sale fact table, I end up with multiple rows for one order.
2. Since sales force (recruits) dimension table is huge (over 1 million rows), I am thinking to slice this into multiple dimensions, one for active/inactive recruits, one for demographics and changes and one for Parent/Child hierarchy. Please advise.
3. Keeping Forecast dimension and Forecast fact table, but how to tie it back to sales fact table? Joining Fact to Fact is a good practice?

[ngalemmo]

It would help if you understood your business processes. Is there a fulfillment system, an invoicing system, a receivables system? When does an order become a sale? When it is shipped or invoiced? Do substitutions occur in fulfillment? Each business process generates information that is reflected in a fact table, an atomic fact table represents a specific process. An aggregate fact can be created from the atomic facts to represent the entire flow (order to cash).

As you have a very large sales force, I would assume this is not a small operation. Spend the time to do it right and don't worry about the number of fact tables.

[JagWalia]

Here are the answers to the questions:

Is there a fulfillment system, an invoicing system, a receivables system?
Fulfillment, when shipped.

When does an order become a sale? When it is shipped or invoiced?
At the time of shipment

Do substitutions occur in fulfillment?
Possible

I get your point to keep an atomic fact table representing each processes and separate aggregate fact to represent order, cash.

Do you recommend keeping separate hierarchy bridge dimension for sales force?

[ngalemmo]

The hierarchy is represented by a separate bridge table, not a dimension table (it is basically an adjunct to the dimension). The bridge will have significantly more rows than the dimension table, assuming you explode the recursive relationships. You don't want end-users to do recursive queries to traverse the hierarchy.

You should implement separate facts for orders and shipments. Shipments should include a back reference to the order line.

[JagWalia]

I should have referred to hierarchy bridge table instead of dimension table.

Of course, I will explode the recursive relationships in a bridge table, and store it accordingly if level changes with date time stamp.

We have multi-level Parent-Child Hierarchy, means one parent can have many children, and child can further have many children. Parent can have multi level downline. How do you advise me to build the Hierarchy bridge table and tie it with fact and Sales Force Dimension?

[JagWalia]

I am putting here the tips I got from ngalemmo in case any body needs it:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Store the FK to the salesperson in the fact. The bridge has every parent/child relationship, both are FK's to the same dimension. To aggregate sales up the hierarchy, join the fact FK to the child in the bridge and the parent bridge key to the dimension.

In general there is seldom a reason to have an order header dimension. Usually things in the order header can be reduced to other dimensions (customer, dates, etc...) and a few junk dimensions for miscellaneous statuses and codes. Order number is carried in the fact as a degenerate dimension (i.e. a dimension with no dimension table).

You would create an order header fact table only if there are measures that cannot be reflected at the line level. Most of the time that isn't necessary. In a lot pf cases you can 'cheat' and make things like shipping charges a 'product' and place them on their own line in the detail fact. Taxes are normally reflected at the line level on the product line that was sold.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[JagWalia]

More from ngalemmo :

Yes it would if you construct the bridge properly.

The bridge contains every possible relationship at every level including each member's relationship to themselves.

If you had the following hierarchy C -> B -> A (top), the bridge would look like:

Parent/Child/Parent level/Child level
A/A/1/1
A/B/1/2
A/C/1/3
B/B/2/2
B/C/2/3
C/C/3/3

When you aggregate where parent = A, it would include A, B & C as children.

[Sponsored content]