Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

a strange design

2 posters

Go down

a strange design Empty a strange design

Post  aug828 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

Back to top Go down

a strange design Empty Re: a strange design

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

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

http://aginity.com

Back to top Go down

a strange design Empty Re: a strange design

Post  aug828 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

Back to top Go down

a strange design Empty Re: a strange design

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

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

http://aginity.com

Back to top Go down

a strange design Empty Re: a strange design

Post  aug828 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

Back to top Go down

a strange design Empty Re: a strange design

Post  aug828 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

Back to top Go down

a strange design Empty Re: a strange design

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

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

http://aginity.com

Back to top Go down

a strange design Empty Re: a strange design

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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