single denormalized dimension or 2 separate dimensions?

View previous topic View next topic Go down

single denormalized dimension or 2 separate dimensions?

Post  salaman on Fri Jan 03, 2014 1:11 pm

Hi,

I'm trying to model a monitoring environment where objects (such as network cards, disks, services) are monitored and alerts are raised if something goes wrong with these objects. The specific process being modelled is "raised_alerts" so there's a fact table called fact_raised_alerts. Amongst various dimensions there's a "monitored_object" dimension.

Now, each monitored object lives on a computer so my initial thought was to snow-flake off the "monitored_object" dimension to a "monitored_computer" dimension, which would contain all of the attributes that belong to a computer (e.g. operating_system, service_pack, amount_of_ram, is_virtual_machine, etc....).

However, in an attempt to avoid snow-flaking, I figured I would denormalize the "monitored_object" dimension and add all of the computer-related attributes to this dimension. However, this doesn't quite feel right - partly because there are thousands of monitored objects on a given computer so repeating attributes like operating_system thousands of times seems like an overhead; and partly because computer-related attributes don't logically sound like they belong in this dimension.

So, would a valid alternative be to create a completely separate dimension called "monitored_compter" and then just have 2 separate FKs in my fact table? Any input/feedback would be much appreciated. I've included a brief sample below.

Option 1 (single dimension table):

dim_monitored_object
object_keyobject_nameobject_typecomputer_namecomputer_domainoperating_systemamount_of_ramis_clusteredis_virtual_machine
1Drive C:DiskSrv1ABC.COMWindows Server4 GBNoNo
2Drive D:DiskSrv1ABC.COMWindows Server4 GBNoNo
3rpc.exeServiceWkSt1ABC.COMWindows Vista1 GBNoNo

Option 2 (2 separate dimension tables):

dim_monitored_object
object_keyobject_nameobject_type
1Drive C:Disk
2Drive D:Disk
3rpc.exeService
and...

dim_monitored_computer
computer_keycomputer_namecomputer_domainoperating_systemamount_of_ramis_clusteredis_virtual_machine
1Srv1ABC.COMWindows Server4 GBNoNo
2Srv1ABC.COMWindows Server4 GBNoNo
3WkSt1ABC.COMWindows Vista1 GBNoNo

Option 3: Something else?

salaman

Posts : 21
Join date : 2011-03-24

View user profile

Back to top Go down

Re: single denormalized dimension or 2 separate dimensions?

Post  ngalemmo on Fri Jan 03, 2014 4:33 pm

Why snowflake? Your question about the 'valid alternative' is backward. The idea of separate dimensions and two foreign keys on the fact is not a 'valid alternative' it is the primary way to do it. Snowflaking and consolidating the dimension are the alternatives.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: single denormalized dimension or 2 separate dimensions?

Post  salaman on Fri Jan 03, 2014 5:45 pm

Thanks ngalemmo,

The reason my logic sounded "backwards" is because I approached the original problem "backwards" (i.e. snowflake first) - probably as a result of my relational modelling background.

Out of interest, would there ever be a situation in which the alternatives (snowflake/consolidate) would be preferable?

salaman

Posts : 21
Join date : 2011-03-24

View user profile

Back to top Go down

Re: single denormalized dimension or 2 separate dimensions?

Post  ngalemmo on Fri Jan 03, 2014 6:02 pm

Offhand, no. But Ralph wrote about it a (long) while back and identified situations where snowflaking is preferable, if not necessary. You may be able to find it by searching the main site.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: single denormalized dimension or 2 separate dimensions?

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