4 ms·

Not sure I understand. For analytics/warehouse stuff you tend to pre-join stuff to get a very non-normalised fact table (which is static so unnormalised is fine

Not sure I understand. For analytics/warehouse stuff you tend to pre-join stuff to get a very non-normalised fact table (which is static so unnormalised is fine). This means very fast, maybe optimal, for what you're doing. It's a solved problem really. Sure, it doesn't support general queries but that's ok. Am I missing something?

juancn 4y ago
In pure analytics, most queries need to read most rows, but not all columns. Typical relational queries just touch a few rows of the dataset (think CRUD). That's why if you force analytics into a relational DB you end up with a star schema, but the fact tables are not physically aligned, so you need to join them, this causes a query scan to jump around the data a lot. Analytic engines, keep the data aligned across columns, but still on a per-column basis, so the full scans likely only need to look at nearby data, helping prefetchers (both CPU and from disk). This avoids joins, but makes inserts a bit more annoying, multi valued data, is usually kept also close and compact so the scan doesn't jump around, but the explanation is a bit longer for a comment.
- zasdffaa 4y ago
  Col-based storage is for usual tables, DW don't need that because all the stuff is pre-joined except for the dimension tables. The dimension tables are tiny IIRC, and there should (?) be only one fact table. You don't join between fact tables anyway (do you?) I feel you may be mixing up a few things but it's not my strong point here.
  - juancn 4y ago
    Yeah, I may have messed the terminology here, depending on the dataset, the dimension tables can be close to the fact table in cardinality, for true flexibility, it degenerates into a fact table with just a record id and dimension tables with a FK to the fact table and a single column. If the DB is a true columnar store, there's no need to do that, but most are record based.

juancn 4y ago

In pure analytics, most queries need to read most rows, but not all columns. Typical relational queries just touch a few rows of the dataset (think CRUD). That's why if you force analytics into a relational DB you end up with a star schema, but the fact tables are not physically aligned, so you need to join them, this causes a query scan to jump around the data a lot. Analytic engines, keep the data aligned across columns, but still on a per-column basis, so the full scans likely only need to look at nearby data, helping prefetchers (both CPU and from disk). This avoids joins, but makes inserts a bit more annoying, multi valued data, is usually kept also close and compact so the scan doesn't jump around, but the explanation is a bit longer for a comment.

zasdffaa 4y ago
Col-based storage is for usual tables, DW don't need that because all the stuff is pre-joined except for the dimension tables. The dimension tables are tiny IIRC, and there should (?) be only one fact table. You don't join between fact tables anyway (do you?) I feel you may be mixing up a few things but it's not my strong point here.
- juancn 4y ago
  Yeah, I may have messed the terminology here, depending on the dataset, the dimension tables can be close to the fact table in cardinality, for true flexibility, it degenerates into a fact table with just a record id and dimension tables with a FK to the fact table and a single column. If the DB is a true columnar store, there's no need to do that, but most are record based.