Bus Matrix - looking for options for my current model

Hello everyone,
I'm new to modeling, and hence could use your advice. I'm creating a simple dimensional model and at the Bus Matrix stage identifying business processes.
The business is simple - a ship comes to dock, loads and unloads various liquid products, and leaves. Total process may take 2 to 4 days. We require date time stamp to calculate total hours spent at dock.

Bus Matrix Option I:
Three business processes - Ship Arrival, Ship Product discharge/load, Ship departure: This will have three transaction fact tables. Two of those will be factless - the Ship Arrival and Ship Departure, since there're no measure associated with them. They're only to capture number of ships arriving and departing by date, and time spent on dock.

Bus Matrix Option II:
One business process: Ship docking. It will have one accumulating snapshot fact table, since the process may take 2 to 4 days.

Which is the better option? And why? Are there any other options here?

Thanks in advance!

How about split the difference and have two business processes?

• Ship arrival/departure modeled in an accumulating snapshot fact table (faciliates time-at-dock queries)
• Product discharge/load in a transactional fact table

You really don't need to model arrivals. To get time at dock, you need to capture the departure in a fact table with the arrival time (assuming the source system can give you both times when you pull the departure information). You should also calculate the time at dock as a measure of the fact. Loading/unloading is its own fact.

Having arrivals as a separate fact doesn't provide any useful information since activity analysis is retrospective. Besides, if they are loading or unloading something, it is a safe bet there is a ship in the dock.

He is so wise!

ngalemmo wrote:You really don't need to model arrivals. To get time at dock, you need to capture the departure in a fact table with the arrival time (assuming the source system can give you both times when you pull the departure information). You should also calculate the time at dock as a measure of the fact. Loading/unloading is its own fact.

Having arrivals as a separate fact doesn't provide any useful information since activity analysis is retrospective. Besides, if they are loading or unloading something, it is a safe bet there is a ship in the dock.

Hi ngalemmo, you are wise I agree. In that case, if the source system can give both times when we pull departure information, we can have one fact table with both arrival/departure. so then my question is, is there a need for a different loading/unloading fact table? or can we include everything in one fact?

On a different note, what if users wish to view the ships at dock at a point in time, that are loading/unloading, but haven't yet departed?

Thanks in advance!

On a different note, what if users wish to view the ships at dock at a point in time, that are loading/unloading, but haven't yet departed?

That is a good reason to keep loading/unloading and departure separate.

They really are different processes and you don't know about departures until loading and unloading have completed, so using only one fact table introduces complications into the update process.

Also, the data warehouse by nature will not be current. If the users need to know what is going on right now, they could always look at the operational systems... that is what they are there for.

BI Consultant wrote:On a different note, what if users wish to view the ships at dock at a point in time, that are loading/unloading, but haven't yet departed?

In that case the accumulating snapshot table I mentioned earlier might be worthwhile.

But as ngalemmo said, it will only be as current as the last time the DW was refreshed. How often are you planning to pull data in from the source system?

Thanks to both ngalemmo and VHF. I appreciate you taking the time!

I'd like to add that there is no operational system here, and all the data is in Excel (for this project). This is a new company we acquired, and their data is only in Excel. And I agree with both of you that the data warehouse will not be current.

To answer VHF's question - I'm not sure of the frequency of data pull yet. Could be during ship dock or after departure, or even at arrival.

I have some follow-on questions:
1. Should the loading/unloading fact table have a time dimension associated with it to capture arrival time? If not, will we capture arrival time in the departure star schema?
2. We need to calculate "time on dock" for each ship. Which means we need a date time stamp (not just date). If I include time in my date dimension, that'll make the date table really large! What can I do here?
3. Any expert thoughts on how to handle a situation with Excel as the only operational source like I have here?

Thanks!

Two thoughts on handling time:

Put two datetime fields (arrival, departure) in your fact table and don't handle time in your dimension. This will allow you to use database functions to calculate time at dock.* You would probably also want FK to the date dimension for arrival date and departure date.

If you think you'll need to do analysis by when ships arrive and depart, create a separate time-of-day dimension. You could have 1140 records if you wanted to represent each minute of the day. In this case you would have FKs for arrival date, arrival time, departure date, and departure time. That would allow answering questions like how many ships arrive during the night shift (assuming that was an attribute in your time dimension.) Even with this approach, you might still want to also store arrival and departure datetimes in your fact table.

*If you are on Microsoft SQL Server, be careful when using DATEDIFF that it gives you the results you want. There are situations when using it (to calculate age for example) where it doesn't always give the desired results. Would probably be OK to calculate hours or minutes for your purposes however.

[/quote]

They really are different processes and you don't know about departures until loading and unloading have completed, so using only one fact table introduces complications into the update process.
[/quote]

I've learnt that the loading and unloading may be different processes too. One ship can load and then unload during one visit, and another ship may just do one (load or unload). Does that mean I should have two separate fact tables for loading and unloading?

I would look at the dimensions and measures for both loading and unloading. If they are very similar and the only difference is the direction of goods movement, they are a good candidate for being in a single fact table. A single fact table would also facilitate queries like "what did this ship do while it was in dock?"