a strange design

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.

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.

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

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.

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?

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.

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.