Flattened Table in vertica
Hi
i am currently working on an assignment which involves a huge table( lets call it staging) ( 7 billion records) and have designed a datawarehouse with 6 dimensions and a fact table.the performance is much faster with this and then came across the concept of flattened tables in vertica and would like to change my design to leverage this. the initial table would be used as a staging table for my usage and i was wondering if we still need to have these dimensions and then add the fields in the fact table on addition to the key that we already have in the fact table or may be skip the whole dimension modelling and derive the fact table directly without keys and just the values from the staging table directly. anybody who has experience with flattened tables ?
Savio
Comments
Hi,
If you are referring to the Flattened Table feature then you will have to store both a key value and the actual value in the Fact table. If you don't have the key in the Fact table, how would Vertica be able to lookup up the value from the dimension table via the SET USING or DEFAULT expressions
The point of the Flattened Table feature is to avoid having to join the Fact table with the Dimensions tables at query run time.