topic Re: Star Schema Without Center Fact in QlikView

Star Schema Without Center Fact

Anonymous — Tue, 28 Apr 2015 05:05:32 GMT

Hi All,

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,

Suryanto

Re: Star Schema Without Center Fact

danieloberbilli — Tue, 28 Apr 2015 05:22:19 GMT

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.

Re: Star Schema Without Center Fact

petter — Tue, 28 Apr 2015 05:25:19 GMT

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.

Re: Star Schema Without Center Fact

Anonymous — Tue, 28 Apr 2015 05:36:51 GMT

Hi Daniel,

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.

Regards,

Suryanto

Re: Star Schema Without Center Fact

Anonymous — Tue, 28 Apr 2015 05:43:44 GMT

Hi Petter,

if it regarding the subset-ratio here my subset-ratio of each table:

Fact
- SubsetRatio = 87%
- Information Density = 99.72%
Product
- SubsetRatio = 93%
- Information Density = 100%
MasterPolicyAgent
- SubsetRatio = 50%
- Information Density = 99.94%
MasterCustomer
- SubsetRatio = 99.22%
- Information Density = 100%

indeed this data model look rather peculiar that why I a bit worry regarding this kind of modeling.

Regards,

Suryanto

Re: Star Schema Without Center Fact

Tue, 28 Apr 2015 05:49:21 GMT

Here my Approach is crate a LINKTABEL that will give better and better performance

Thanks

Manju