TIPS

VALOR CALCULADO NA FATO.
- Quando temos Valor de Custo e Valor de Venda na tabela fato. Devemos colocar uma coluna para LUCRO = (VL VENDA - VL CUSTO)ou deixar para ser calculado no SQL ou software? R. Devemos implementar uma coluna na tabela fato, SIM.

Date Key is Number or Date?
-Date column must be number type.

Date dimension versus date in Fact table
-Date type column in fact table it is ok, but the numerous classifications for dates cannot be obtained by SQL Date functions. For example: Major event column, that can hold "Jogos Olimpicos". Qual funcao sql para datas iria dizer isso?
-most databases do not index SQL date calculations, so queries constraining on an SQL-calculated field would not take advantage of an index. How oracle handles it?

Date and Time.
It is better if we create a date dimension(3650 linhas) and a date time dimension (5.256.000 linhas).

Devemos evitar nulos na tabela fato. Ex: Coloque um valor "Promocao nao se aplica" na dimensao Promocao com uma chave. Essa chave vai se relacionar com a fato.

Nonadditive Facts: Unit price, gross margin

Promotion dimension in 4 parts: It is possible to divide the promotion dimension, depends on requirements.

Devemos evitar snowflaking, com uma excecao: outtrigers

Cada processo de negocio pode ser representado com menos de 15 dimensoes, se houver mais de 24, o modelo precisa ser revisado.

Surrogate keys for date dimension must be in sequential order. Day 1 jan 1990 = 1, day 2 = 2...
We do not want to embed extensive calendar intelligence in these keys (for example YYYMMDD) because doing so may encorage people to bypass the date lookup dimension table.

SCDs (Slowly changing dimensions):
SCD1 = overwrite the value. NO historical.
SCD2 = ADD a dimension row. BAD for big dimensions. MOST USED.
SCD3 = ADD a dimension column.




Technique 1: chapter2 page 46 outtriger.
Technique 2: chapter2 page49 Promotion Coverage Factless Fact Table
Technique 3: chapter2 page 62 Market Bask Analysis