Início / dba / Perguntas / 8128
Accepted
Matt Palmerlee
Asked: 2011-11-19 13:20:40 +0800 CST

Projeto de datawarehouse: dimensão combinada de data e hora versus dimensões e fusos horários separados de dia e hora

  • 772

Estamos apenas começando a projetar um novo data warehouse e estamos tentando projetar como nossas dimensões de data e hora funcionarão. Precisamos ser capazes de suportar vários fusos horários (provavelmente pelo menos GMT, IST, PST e EST). Inicialmente, estávamos pensando que teríamos uma dimensão de data e hora combinada ampla com granularidade de talvez 15 minutos, dessa forma, temos uma chave em nossas tabelas de fatos e todos os dados de data e hora diferentes para todos os fusos horários suportados estão em uma tabela de dimensão. (isto é, chave de data, data GMT, hora GMT, data IST, hora IST, etc...)

Kimball sugere ter uma dimensão de dia separada da dimensão de hora do dia para evitar que a tabela fique muito grande (The data warehouse toolkit p. 240), o que parece bom, mas isso significaria que temos duas chaves em nossas tabelas de fatos para cada fuso horário precisamos apoiar (uma para a data e outra para a hora do dia).

Como sou muito inexperiente nesta área, espero que alguém conheça as compensações entre as duas abordagens, ou seja, desempenho versus gerenciamento de todas as chaves de fuso horário diferentes. Talvez existam outras abordagens também, já vi algumas pessoas falando sobre ter uma linha separada na tabela de fatos por fuso horário, mas isso parece um problema se as tabelas de fatos tiverem milhões de linhas, então você precisa quadruplicar para adicionar fusos horários .

Se fizermos o grão de 15 minutos, teremos 131.400 (24 * 15 * 365) linhas por ano em nossa tabela de dimensão de data e hora, o que não parece muito ruim para o desempenho, mas não saberemos com certeza até testarmos alguns consultas de protótipo. A outra preocupação em ter chaves de fuso horário separadas na tabela de fatos é que a consulta precisa juntar a tabela de dimensão a uma coluna diferente com base no fuso horário desejado, talvez isso seja algo que o SSAS cuida para você, não tenho certeza .

obrigado por qualquer pensamento, -Matt

sql-server ssis

3 respostas

  1. Best Answer

    Ter a data e a hora separadas permitirá que você faça agregações por tempo com muita facilidade. por exemplo: se você deseja executar uma consulta para saber qual período do dia é mais movimentado. Isso é muito facilmente realizado usando uma dimensão de tempo separada.

    Além disso, você deve ter apenas uma chave de tempo. Decida o horário GMT/EST - então use isso na tabela de fatos. Se você precisar executar relatórios com base em outro fuso horário, basta convertê-lo em seu aplicativo ou consulta.

    • 5

  2. Apenas um acompanhamento de como decidimos implementar nosso DataWarehouse para suportar múltiplos fusos horários e ser o mais eficiente possível: Escolhemos criar uma tabela de fusos horários (id, nome, etc...) bridge" que se parece com isso:

    time_zone_bridge
---------------
date_key_utc
time_key_utc
timezone_id
date_key_local
time_key_local

    Desta forma, podemos manter nossas tabelas normais de dimensão de data e hora pequenas, todos os nossos fatos vinculados às chaves de data/hora UTC; e vincule as chaves locais de data/hora de volta às tabelas de dimensão de data e hora. Preenchemos nossa tabela de pontes de fuso horário usando o código C# invocado do SSIS, pois isso era muito menos complicado do que fazer coisas TZ diretamente do SqlServer.

    • 5

  3. Já vi a ideia de um armazém usando uma DateTimedimensão combinada rejeitada, mas não vi um motivo muito claro para isso. Simplificando um pouco, aqui está a tabela de fatos que estou construindo agora:

    Transactions
(
...
CreatedDateTimeSK         INT NOT NULL,  -- Four bytes per date...
AuthorizedDateTimeSK      INT NOT NULL,
BatchSubmittedDateTimeSK  INT NOT NULL,
BatchApprovedDateTimeSK   INT NOT NULL,
SettlementDateTimeSK      INT NOT NULL,
LocalTimeZoneSK           TINYINT NOT NULL  -- ...plus one byte for the time zone
)

    Os DateTimecampos se juntam a uma tabela DateTime:

    DateTimes
(
DateTimeSK   INT NOT NULL PRIMARY KEY,
SQLDate      DATE NOT NULL,
SQLDateTime  DATETIME2(0) NOT NULL,
Year         SMALLINT NOT NULL,
Month        TINYINT NOT NULL,
Day          TINYINT NOT NULL,
Hour         TINYINT NOT NULL,
Minute       TINYINT NOT NULL CHECK (Minute IN (0, 30)),
...
)

    Isso está em uma resolução de meia hora, então são 48 registros por dia, 350.400 em 20 anos - bastante gerenciáveis.

    As datas/horas dos eventos são traduzidas para UTC quando armazenadas, mas com o LocalTimeZoneSKcampo e uma tabela de ponte podemos facilmente juntar para obter a hora local:

    TimeZoneBridge
(
DateTimeSK       INT NOT NULL,
TimeZoneSK       TINYINT NOT NULL,
PRIMARY KEY (DateTimeSK, TimeZoneSK),
LocalDateTimeSK  INT NOT NULL
)

    Para obter as transações criadas hoje, horário UTC:

    SELECT COUNT(*)
FROM Transactions AS T
  INNER JOIN DateTimes AS CD ON T.CreatedDateTimeSK = CD.DateTimeSK
WHERE CD.SQLDate = '2014-08-22'

    Para obter as transações criadas hoje, no horário local da transação:

    SELECT COUNT(*)
FROM Transactions AS T
  INNER JOIN TimeZoneBridge AS TZB ON T.CreatedDateTimeSK = TZB.DateTimeSK AND T.TimeZoneSK = TZB.TimeZoneSK
  INNER JOIN DateTimes AS CD ON TZB.LocalDateTimeSK = CD.DateTimeSK
WHERE CD.SQLDate = '2014-08-22'

    Você pode ficar tentado a simplificar as coisas substituindo o TimeZoneSKpor um REALdeslocamento (por exemplo, -5,0 para o horário de verão central dos EUA), mas isso será interrompido se algumas datas/horas de um registro de fato estiverem no horário de verão e outras não.

    Se os eventos para um registro de fato puderem ocorrer em fusos horários diferentes, como uma remessa ou um voo, você precisará de um campo de fuso horário para cada data e terá até cinco bytes por data.

    • 2

relate perguntas