what will happen if I have this kind of star schema? is it will cause me worst performance compare to snowflake?
%Key is consist of ApplicationNumber & ' - ' &SourceDatabase. the data can't joined or concatenate since concatenation will cause the data lost information that needed while join will cause memory to overload.
Thanks & Regards,
There might be some more points to consider when talking about the performance of a certain schema, e.g. the cardinality of the fields (# of distinct values), common dimensions that might be better solved via a link table between different fact tables. But this should be tested - if you worry about the performance, you should create some dummy data to see how the performance suffers with increasing amount of data.
Also ensure that the %Keys are created with AutoNumber() as sequential consecutive numbers are much better then a compound string.
It all depends on the number of rows of the involved tables. And the cardinality and subset-ratio of the %Key.
In some cases this could work if you have a small data model or just one table with many rows.
But at first glance this data model looks rather peculiar.
I suspect that an analysis of your underlying data and tables could lead to a good star-schema.
in this current modeling the value already created to have one to one relation and believe there no worry for distinct value since the relation currently one to one. but i just think this table a bit odd to me and I just worry with this kind of modeling. the %Key itself need to be created such way since the data source come from 5 different database and the ApplicationNumbers are auto generate by the system, so it possible to have same ApplicationNumber from each source.
if it regarding the subset-ratio here my subset-ratio of each table:
indeed this data model look rather peculiar that why I a bit worry regarding this kind of modeling.