1

I'm looking for some advised on how to model the following scenario in our data warehouse.

We have the following entities

  • Staff
  • Mobile Devices
  • Computers
  • Mobile Bills

If feels like its simpler to model without fact tables for Mobile Devices and Computers, because essentially there are no measures, Option 1 shows this. Option 2 shows the use of Fact tables.

enter image description here

enter image description here

What's would your advise be on the best practice in this scenario? Any input would be appreciated, thanks.

Improve this question
7
  • I think this is more of a question of, what are you trying to achieve here? You can create a great many fact tables, but not sure what your looking for there. Is MobileBills supposed to be a fact table? If so, is it also tied to Staff as well as MobileDevice? You can create a fact table for anything that you need to track changes on or want to keep historical data for. But is this something you need?
    – Jason B.
    Commented Jul 20, 2016 at 14:25
  • Hi Jason, Yes MobileBills is depicted above as a fact table, and yes it's tied to Staff but not directly in the examples show, indirectly via the MobileDevices dimension, rightly or wrongly. In term's of what I'm trying to achieve, here are some examples: Count of Mobile Devices and Computers Grouped by Staff member and also counts grouped by Region, Department etc (These are other Attributes in the Staff Dimension not shown above). Cost of Mobile Devices grouped by Staff member and also totalled by Region, Department etc. Does that give you a better indication?
    – DanF
    Commented Jul 20, 2016 at 14:38
  • I have amended the image for option 2, as this was incorrectly shown.
    – DanF
    Commented Jul 20, 2016 at 14:54
  • Then I would agree that Option 2 is closer to what you probably need. Keep in mind you don't necessarily have to create a fact table just to show a relationship between two dimensions, not sure this is what you are doing (but not sure this is not what you are doing). We used to create separate dimension tables that showed relationships between dimensions. Then our fact data tied in to some of these tables instead to get the dimensions. It really depends on how your fact data is laid out.
    – Jason B.
    Commented Jul 20, 2016 at 15:00
  • Ok that's interesting, Option 2 would create a Fact table just to show a relationship between dimensions. Option 1 is directing relating the dimensions, I was thinking that I could implement SCD type 2 to track history as devices don't often move between staff in this scenario. I didn't really think about creating another dimension table that defines the relationship instead. Its tough not having a great deal of dimension modelling experience to work out the best way to model this, it feels like there are various ways to achieve the end result, I guess it then comes down to best practice.
    – DanF
    Commented Jul 20, 2016 at 15:23

1 Answer 1

Reset to default
1

The second version looks more realistic. Mobile devices and computers can change their users and this version allows to keep the history.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.