Databases
StackOverflow2013
Postsdbo
POwhy do I still need dimension if I have compression
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POwhy do I still need dimension if I have compression
    primarykey
    data
    text
    <p>Recently I have some doubt, looking for answer.</p> <p>Normally, when design a data warehouse, either we use star model or snowflake model or hybrid, and normally we normalize the master data into dimension tables(certainly sometimes for performance consideration, de-normalize). My question is, normalization into dimension table, or create all kinds of different dimension tables, what is the benefit? </p> <p><strong>If for saving space, then the SQL Server different level of compression already saves the space.</strong> For instance, in a fact table, there is a varchar(max) column, which only has 1% of unique value, then normalize it to dimension table, and put key in fact table, it will help saving space; however, because of SQL row level compression, it works theoretically the same way, instead normalized by your own design, SQL Server will find the string pattern and save somewhere, within the row is only the pointer, so the space usage is theoretically like the key. </p> <p><strong>If for improving query performance,</strong> then with dimension table, no matter what index you have on dimension, you need to at least first non-cluster index scan/index seek your dimension table to get the key, then using key to get cluster index/or RID, then get the full data. That is 2 times I/O. If without dimension, you still have the index on fact table, the corresponding column, because of compression, you index table will be similar size as you create index on dimension table. So, when you query, probably it is also one time non-cluster index scan/cluster index seek/then full data, so the I/O probably can be even smaller, and plus without join, the query performance probably can be even faster. </p> <p><strong>So, why do I need dimension if I already have compression?</strong></p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USljh
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POwhy do I still need dimension if I have compression
      1. This table or related slice is empty.
      1. VTClose
    2. VO
      singulars
      1. POwhy do I still need dimension if I have compression
      1. This table or related slice is empty.
      1. VTClose
    3. VO
      singulars
      1. POwhy do I still need dimension if I have compression
      1. This table or related slice is empty.
      1. VTUpMod
    1. COA well designed star schema is compressed by the virtue of using the smallest datatypes possible. I don't see a real question here. Dimensions are there for the end user's ease of slicing/dicing.
      singulars
      1. POwhy do I still need dimension if I have compression
      1. USMitch Wheat
    2. COCould you kindly share more light regarding to "A well designed star schema is compressed by the virtue of using the smallest datatypes possible". And also Dimensions are there for the end user's ease of slicing/dicing, will end user really use dimension table or most case they are just using view?
      singulars
      1. POwhy do I still need dimension if I have compression
      1. USljh
    3. COHow is it harder to analyze a fully denormalized table vs a star schema?
      singulars
      1. POwhy do I still need dimension if I have compression
      1. USNeil McGuigan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload