Adding to an existing dimensional model

Hi all,

I am fairly new to dimensional modelling so I have spent some time trawling the forums for some input and I have learnt some things but I require some further input.

I am modelling the transit of motor vehicles through an access controlled drop-and-go. The scenario includes multiple enty points and multiple exit points both connected to multiple streets. The time spent in the drop-and-go zone is negligable and only the hour in which the vechicles entered/left the drop-and-go is required. The current solution has a fact table with the grain describing the transit of the vehicles; the fact contains the following information:
Time Id - Dimension
Vehicle Type - Dimension
Gate In - Dimension
Street In - Dimension
Gate Out - Dimension
Street Out - Dimension
Number of occupants - Fact

(Additional Ino: As this is a high-traffic flow area the end users are interested in making it as effecient as possible, each gate also has a left turn, righ turn and straight street that the vehicles may use. The gates are laid out such that each street naturally leads to its own gate. The result is that the legacy system also records the street in and street out using dimensiosn DIM_STREET_IN and DIM_STREET_OUT)

The implementation that I have inherited has identifical dimensions DIM_GATE_IN and DIM_GATE_OUT (also DIM_STREET_IN & DIM_STREET_OUT), as the gate used for entry can also be a gate used as an exit the information inside is also identical. What I have picked up from the forums is that this is a role-playing dimension and that it would be better design to have a single business dimension DIM_GATE (and single DIM_STREET dimension) and then have role-playing views to model the directional quality. Is that correct?

I have been tasked to extend the design/implementation to allow reporting showing total vehicles moving in or out at a specific gate on an hourly basis.
It seems to me that this grain is different (the end users are not interested in the street in the new requirement) to the one I currently have and I would like to build a summary fact table having only a single DIM_GATE dimension. So I am planning on extending the ETL to extract the information from the current fact table and putting it into a summarised fact table. Is this a good approach?

The implementation I have inherited has also used the operational keys as the primary keys to the dimensions and the foreign keys to the fact table. I have picked up from the forums that it is better practise to use surrogate keys.

Would be feasable to introduce the base business dimension DIM_GATE to the summarized fact table and to use surrogate keys with this dimension? This would mean that the original fact table and summarized fact table would differ in the key set (the legacy using operational keys and the new using surrogate keys). My plan going forward would be to create role-playing views on top of my DIM_GATE dimension and move away from the current DIM_GATE_IN and DIM_GATE_OUT dimensions that I currently have. My plan is also to replace the operational keys in the legacy fact table with the surrogate keys of the new base business dimension. This should almost halve the number of dimensions I have. What are some of the challenges I would face changing from operational keys to surrogate keys?

[Eventually I would like to introduce a base dimension DIM_STREET and create role-playing vierws for DIM_STREET_IN and DIM_STREET_OUT and replace the operational keys with surrogate keys in the legacy design].

Any comment/help is most welcome

Your instincts are good in terms of where you want to go with it. Is your question: what is the best way to transition to a proper model?

It's hard to say, but I would say don't do it unless the business buys into it. You need to be very careful in how you approach this, otherwise you could put yourself in a situation with no up-side. Political realities vary between organizations, but there is one common thread... if someone in IT changes something without the business being aware of it, if it works, no one is going to notics or care... if it disrupts what the business does, your head goes on the block.

Hello and thank you for the reply,

Yes, I need some guidance on how to move forward with the extension. What should I change in the existing model now? Should I move forward using best practises and then try to retrofit the best practises to the legacy design?

Thank you also for the advice in terms of being cautious and making sure that business buys into the solution.

robertfowler wrote:Hi all,

...
The implementation that I have inherited has identifical dimensions DIM_GATE_IN and DIM_GATE_OUT (also DIM_STREET_IN & DIM_STREET_OUT), as the gate used for entry can also be a gate used as an exit the information inside is also identical. What I have picked up from the forums is that this is a role-playing dimension and that it would be better design to have a single business dimension DIM_GATE (and single DIM_STREET dimension) and then have role-playing views to model the directional quality. Is that correct?

I've yet to create role playing views for a dimension. I just role name the FK.

robertfowler wrote:I have been tasked to extend the design/implementation to allow reporting showing total vehicles moving in or out at a specific gate on an hourly basis.
It seems to me that this grain is different (the end users are not interested in the street in the new requirement) to the one I currently have and I would like to build a summary fact table having only a single DIM_GATE dimension. So I am planning on extending the ETL to extract the information from the current fact table and putting it into a summarised fact table. Is this a good approach?

If the grain is simply an aggregation of a lower fact table (i.e. less dimensions) then yes.

robertfowler wrote:Would be feasable to introduce the base business dimension DIM_GATE to the summarized fact table and to use surrogate keys with this dimension? This would mean that the original fact table and summarized fact table would differ in the key set (the legacy using operational keys and the new using surrogate keys). My plan going forward would be to create role-playing views on top of my DIM_GATE dimension and move away from the current DIM_GATE_IN and DIM_GATE_OUT dimensions that I currently have. My plan is also to replace the operational keys in the legacy fact table with the surrogate keys of the new base business dimension. This should almost halve the number of dimensions I have. What are some of the challenges I would face changing from operational keys to surrogate keys?

[Eventually I would like to introduce a base dimension DIM_STREET and create role-playing vierws for DIM_STREET_IN and DIM_STREET_OUT and replace the operational keys with surrogate keys in the legacy design].

Any comment/help is most welcome

I agree with ngalemmo here, tread lightly. Changing the key structure of an aggregate fact to be different than the detail fact is precarious at best. SK's simply insulate you from source identifier problems. If I were to undertake this task, I would go for the big bang approach. Change them all at the same time. However, all the existing reporting will be impacted.