a strange design

View previous topic View next topic Go down

a strange design

Post  aug828 on Mon Jun 18, 2012 9:52 am

suppose i have 3 dimension tables:
instance: instance_id(pk), host_id(fk), instance_name
host: host_id(pk), host_name
time: time_id, ....

and two facts:
instance_perf: instance_id(pk), time(pk), cpu, memory
host_perf: host_id(pk), time_id(pk), cpu, memory

actually, there are two dimensions: time, host-instance.
instance_perf relates to instance level of host_instance hierarchy,
host_perf relates to host level of host_instance hierarchy.

will this cause any problems?
somehow, i feel it's not a right solution.

aug828

Posts : 5
Join date : 2012-06-13

View user profile

Back to top Go down

Re: a strange design

Post  ngalemmo on Mon Jun 18, 2012 4:48 pm

Is the sum of all instances on a host the same as the measures for the host?

If so, get rid of the snowflake. If not, get rid of the snowflake.

If the sum is the same, you only need one fact table with host and instance as dimensions.

If the sum is not the same, you still need two fact tables but the instance level table should contain a FK to the host dimension.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: a strange design

Post  aug828 on Mon Jun 18, 2012 5:50 pm

the sum of all instances is not equal to the measure for the host.
they are from different sources.



aug828

Posts : 5
Join date : 2012-06-13

View user profile

Back to top Go down

Re: a strange design

Post  ngalemmo on Mon Jun 18, 2012 9:22 pm

aug828 wrote:the sum of all instances is not equal to the measure for the host.
they are from different sources.



Ok. Two fact tables, no snowflake.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: a strange design

Post  aug828 on Tue Jun 19, 2012 9:46 am

if no snowflake, host_id would have some redudancy, because 1 hos_id could have a lot of instance_id. (each server hosts more than 1 instance).
and host_perf only records host performance info, and has nothing to do with instances.

in this case, if i want a chart of host performance,
i think i should use DISTINCT in my sql.
suppose no snowflake. instances table looks like this: host_id(pk), instance_id(pk), host_desc, instance_desc.
something like this:
select time_id, distinct host_id, cpu, memory from instances left join host_perf

is this correct?

aug828

Posts : 5
Join date : 2012-06-13

View user profile

Back to top Go down

Re: a strange design

Post  aug828 on Tue Jun 19, 2012 10:02 am

and for instance table like this: host_id(pk), instance_id(pk), host_desc, instance_desc.
in the host_perf table, we might have to include instance_id too, because it's part of the key of instance table.

aug828

Posts : 5
Join date : 2012-06-13

View user profile

Back to top Go down

Re: a strange design

Post  ngalemmo on Wed Jun 20, 2012 7:04 pm

Table keys and dimensional design are two different things. While an instance runs on a host, and the natural key may need host to differentiate instance numbers, it does not mean you snowflake. If you need to include the host in context with facts relating to an instance, you include host as a dimension of the fact, not as a dependent of instance.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: a strange design

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