Bus Matrix - looking for options for my current model

View previous topic View next topic Go down

Bus Matrix - looking for options for my current model

Post  BI Consultant on Wed Aug 10, 2011 1:56 pm

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!


Last edited by BI Consultant on Wed Aug 10, 2011 2:05 pm; edited 1 time in total (Reason for editing : More details)

BI Consultant

Posts : 18
Join date : 2011-08-09

View user profile

Back to top Go down

Re: Bus Matrix - looking for options for my current model

Post  VHF on Wed Aug 10, 2011 3:05 pm

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

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Bus Matrix - looking for options for my current model

Post  ngalemmo on Wed Aug 10, 2011 3:26 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Bus Matrix - looking for options for my current model

Post  VHF on Wed Aug 10, 2011 4:33 pm

He is so wise!

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Bus Matrix - looking for options for my current model

Post  BI Consultant on Wed Aug 10, 2011 5:01 pm

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!

BI Consultant

Posts : 18
Join date : 2011-08-09

View user profile

Back to top Go down

Re: Bus Matrix - looking for options for my current model

Post  ngalemmo on Wed Aug 10, 2011 6:48 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Bus Matrix - looking for options for my current model

Post  VHF on Thu Aug 11, 2011 9:09 am

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?

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Bus Matrix - looking for options for my current model

Post  BI Consultant on Thu Aug 11, 2011 10:22 am

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!

BI Consultant

Posts : 18
Join date : 2011-08-09

View user profile

Back to top Go down

Re: Bus Matrix - looking for options for my current model

Post  VHF on Thu Aug 11, 2011 12:03 pm

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.


Last edited by VHF on Thu Aug 11, 2011 12:14 pm; edited 1 time in total (Reason for editing : typo)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Bus Matrix - looking for options for my current model

Post  BI Consultant on Fri Aug 12, 2011 4:03 pm

[/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?

BI Consultant

Posts : 18
Join date : 2011-08-09

View user profile

Back to top Go down

Re: Bus Matrix - looking for options for my current model

Post  VHF on Fri Aug 12, 2011 5:39 pm

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?"

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Bus Matrix - looking for options for my current model

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum