Project REAL: Ciclo de vida dos dados - Particionamento

Publicado em: 1 de setembro de 2005
Por Erin Welker (Scalability Experts)

Artigo técnico sobre o SQL Server
Revisores técnicos: Grant Dickinson, Dave Wickert, Len Wyatt e Stuart Ozer
Aplica-se a: SQL Server 2005

Nesta página

Introdução
Visão geral do ciclo de vida dos dados
Particionamento relacional
Particionamento de cubos
Alterações de ETL
Referências
Conclusão

Introdução

Aplicativos de BI (inteligência comercial) bem-sucedidos precisam ser executados em ferramentas sólidas e são beneficiados quando têm uma base de conhecimento associada sobre como executar uma implementação com êxito. Em resumo, informações sobre práticas recomendadas. Com o Project REAL, a Microsoft® e vários de seus parceiros estão descobrindo práticas recomendadas para aplicativos de BI baseados no Microsoft SQL Server 2005 através da criação de implementações de referência baseadas em cenários reais de clientes. Isso significa que dados de clientes são trazidos internamente e usados para superar os mesmos problemas que os clientes enfrentam durante a implantação. Esses problemas incluem:

• Design de esquemas – relacional e para o Analysis Services.

• Implementação de processos de ETL (extração, transformação e carregamento) de dados.

• Design e implantação de sistemas de front-end de clientes, para geração de relatórios e análise interativa.

• Dimensionamento de sistemas para produção.

• Gerenciamento e manutenção dos sistemas em uma base contínua, incluindo atualizações incrementais dos dados.

Ao trabalhar com cenários reais de implantação, obtemos um entendimento completo de como trabalhar com as ferramentas. Nosso objetivo é abordar toda a gama de problemas que uma grande empresa enfrenta durante uma implantação de verdade.

Este documento oferece uma abordagem detalhada sobre como o particionamento foi implementado no data warehouse relacional e nos cubos do Analysis Services. Além de fornecer a visão geral de "como fizemos", incluímos segmentos específicos de código e lições aprendidas para que o leitor aprenda com nossos sucessos e nossas falhas. Nossa expectativa é que a implementação de referência seja útil para qualquer pessoa que esteja planejando ou implementando um sistema de BI com base no SQL Server 2005.

Para obter uma visão geral do Project REAL, consulte <link para o documento “Project REAL – Technical Overview”>. O Project REAL produzirá vários documentos, ferramentas e exemplos durante sua vida útil. Para localizar as informações mais recentes, visite o site a seguir:

https://www.microsoft.com/sql/bi/ProjectREAL (em inglês)

O Project REAL é um esforço cooperativo entre a Microsoft e vários de seus parceiros na área de BI. Esses parceiros incluem: Apollo Data Technologies, EMC, Intellinet, Panorama, Proclarity, Scalability Experts e Unisys. O cenário comercial do Project REAL e o conjunto de dados de origem foram generosamente fornecidos pela Barnes & Noble.

Observação:  este documento é provisório. Ele contém as práticas recomendadas baseadas em nossas experiências de trabalho com as compilações CTP (Community Technology Preview) anteriores do SQL Server 2005. Este documento apresenta informações precisas na data de sua publicação. A funcionalidade do produto descrita neste documento poderá ser alterada ou informações sobre práticas recomendadas melhores poderão ser desenvolvidas posteriormente. O SQL Server 2005 ainda está na versão beta, e alterações são esperadas.

Visão geral do ciclo de vida dos dados

É uma tarefa crítica, na implementação de qualquer data warehouse, desenvolver uma estratégia de ciclo de vida dos dados que seja apropriada ao projeto e que se ajuste às necessidades da empresa. O ciclo de vida dos dados poderia englobar vários aspectos de um projeto, mas tivemos que defini-lo de forma a incluir os processos de gerenciamento de bancos de dados que lidam com a introdução de novos dados e facilitam a remoção de dados antigos. Por isso, dividimos o ciclo de vida dos dados em três áreas distintas: particionamento, movimentação de dados antigos para um disco de baixo custo e remoção de dimensões. Abaixo é apresentada uma explicação resumida do assunto abordado em cada uma dessas áreas.

Particionamento

É um método de divisão de um grande conjunto de dados em partes menores e mais gerenciáveis. Neste documento, vamos abordar o particionamento das tabelas do SQL Server e dos grupos de medidas do Analysis Services. Vamos nos concentrar no particionamento horizontal, que segmenta linhas de dados com base em valores em uma coluna de particionamento. Essa é uma estratégia comum em implementações de grande porte de data warehouse, principalmente como um meio de gerenciamento de dados, carregamento de cubos e manutenção. Há também benefícios ao consultar os dados, pois o SQL Server e o Analysis Services oferecem um meio de diferenciar as partições relevantes a uma consulta, pressupondo-se que a consulta usará a coluna de particionamento para filtrar os dados.

Movimentação de dados antigos para um disco de baixo custo

Um data warehouse, por natureza, contém uma quantidade incrível de dados. Qualquer técnica possível pode e deve ser usada para gerenciar o custo dos recursos para a manutenção desses grandes conjuntos de dados. Quando uma estratégia de particionamento por data é usada, é possível procurar por uma divisão cronológica na qual os dados antigos não estão mais em um nível alto de demanda. Nesse ponto, os dados relevantes podem ser movidos para um disco menos caro no qual ainda estarão disponíveis para consultas, porém com desempenho reduzido ou com um nível menor de disponibilidade, ou ambos. Essa compensação é estabelecida pela empresa para determinar o equilíbrio entre os custos de recursos, o desempenho das consultas e a disponibilidade dos dados.

Remoção de dimensões

Um aspecto às vezes negligenciado do gerenciamento de dados é a remoção de dimensões. Normalmente, esse é um problema mais relacionado à capacidade de utilização do que ao gerenciamento de discos ou de bancos de dados. Na Barnes e Noble, as duas maiores dimensões são Item (produtos) e Customer (cliente). À medida que os produtos são descontinuados e os clientes descartados, os usuários comerciais não querem mais vê-los, especialmente ao navegar por dimensões com milhões de membros.

Uma exigência para a remoção de dimensões é que nenhum dado de fatos faça referência a elas. Na verdade, o Analysis Services 2005 possibilita o armazenamento de uma chave de dimensão "desconhecida" sobre registros de fatos, onde uma ou mais dimensões não estão presentes nos dados de origem, mas isso ainda não é desejável na maioria dos casos. Se o registro de fatos existir, apenas faz sentido ter registros correspondentes de dimensões.

A empresa precisa tomar uma decisão em relação a quando as dimensões podem ser removidas com segurança do data warehouse; por exemplo, dois anos depois de não haver registros de venda e de inventário com referências a esse produto, mas a diretiva para exclusão de dados antigos das tabelas de fatos também deve ser condizente com essa decisão.

Nossa implementação da remoção de dimensão é apresentada em detalhes em outro documento.

Ambiente atual da Barnes and Noble

Em 2004, a Barnes and Noble implementou um data warehouse que usa o Microsoft Data Warehouse Framework. As áreas incluem vendas ao cliente, inventário da loja e inventário do DC (centro de distribuição). No momento, há 3 anos de vendas, 1 ano de inventário da loja e 6 meses de inventário do DC. Os dados são carregados em tabelas semanalmente. Um padrão de nomeação de tabelas foi implementado para identificar os dados contidos em uma tabela. Atualmente, o SSIS (SQL Server 2005 Integrated Services) é usado para carregar um data warehouse relacional do SQL Server 2000 e o Analysis Services 2000 é usado para hospedar uma grande quantidade de cubos. O banco de dados total do data warehouse relacional tem cerca de 2 terabytes. Embora os dados sejam particionados de forma conceitual, há a possibilidade de se obter muitos benefícios com a implementação do particionamento de tabela do SQL Server 2005, como veremos mais adiante neste documento.

Há várias exigências que precisam ser atendidas antes de determinar uma estratégia de particionamento. Algumas delas estão relacionadas a como os dados são carregados e ao tamanho de uma partição com base em um determinado intervalo de particionamento. A empresa define por quanto tempo os dados estarão disponíveis. O objetivo da Barnes and Noble é ter, eventualmente, 5 anos corridos de histórico de vendas e 3 de inventário. Eles ainda não alcançaram essa quantidade de histórico, portanto estabelecemos nossas próprias exigências no Project REAL para poder demonstrar a funcionalidade de distribuição da implementação de "janela deslizante" (sliding window), que será abordada posteriormente neste documento. Nossa próxima etapa é avaliar os prós e os contras do particionamento na Barnes and Noble e, pressupondo-se que o particionamento seja vantajoso, determinar o melhor método para implementá-lo.

Particionamento relacional

Refere-se ao particionamento do armazenamento de dados relacionais de base. Em um data warehouse tradicional, esses dados são armazenados em um formato dimensional, normalmente conhecido como esquema em estrela ou flocos de neve. É o caso de nossos dados. Como resultado, as tabelas são de dimensão ou de fatos. A maioria das tabelas de dimensão é relativamente pequena, com várias colunas e apenas algumas linhas. A Barnes and Noble tem duas tabelas de dimensão bem grandes: a Item (7 milhões de linhas, 5 GB) e a Customer (cerca de 6 milhões de linhas, 1 GB). A terceira maior dimensão é a Store, que tem pouco mais de 4.000 linhas e utiliza menos de 2 MB de espaço em disco. As dimensões Customer e Item são pequenas se comparadas com as tabelas de fatos.

Tabela de fatos	Contagem de linhas	Espaço usado (dados e índices)	Número de partições carregadas
Tbl_Fact_Store_Sales	1.366.052.628	306 GB	157
Tbl_Fact_Store_Inventory	8.450.555.562	1037 GB	53
Tbl_Fact_DC_Inventory	51.387.065	4 GB	18
Total		1347 GB

Para tornar essas tabelas gerenciáveis em termos de carregamento, backup/restauração e manutenção de índice, a melhor solução é segmentá-las horizontalmente em tabelas menores, que é o método de "particionamento" ao qual nos referimos.

Benefícios do particionamento

Os benefícios do particionamento já foram mencionados anteriormente. Para ser mais explícito, todas as operações de gerenciamento em tabelas muito grandes podem ser realizadas em um nível mais granular quando a tabela está particionada. Os backups de tabela podem ser segmentados em uma tabela particionada. Esses recursos tornam-se ainda mais fáceis com algumas das novas funcionalidades do SQL Server 2005. Ou seja, quando um grupo de arquivos for somente leitura, o SQL Server não exigirá mais um backup do log de transações em um cenário de restauração de grupo de arquivos. Nesse caso, as partições mais atuais podem residir em grupos de arquivos separados das antigas partições não-voláteis. As partições não-voláteis podem ser colocadas em seus próprios grupos de arquivos somente leitura, que precisam passar por manutenção e backup apenas uma vez. Nesse ponto, apenas as partições voláteis exigem manutenção. Outras operações, como a manutenção de índice, também são mais fáceis em um cenário de particionamento. Essa pode ser a diferença entre o gerenciamento ativo de um banco de dados de vários terabytes e o gerenciamento de partições de dados com menos de 100 GB.

Outro benefício, especialmente em um ambiente de data warehouse, é o desempenho da consulta. Isso é importante quando as consultas são emitidas pelo Analysis Services durante o processamento de cubos ou quando os usuários finais consultam diretamente o data warehouse relacional. No caso do processamento de cubos, obtém-se melhoria no desempenho quando o cubo de destino é particionado usando a mesma coluna de particionamento usada pela origem relacional. Quando um cubo particionado é processado, o Analysis Services emite uma consulta que limita os dados àqueles contidos na partição. Por exemplo, se a partição de destino inclui dados da semana que termina em 01/01/2005, ele emitirá uma consulta para o SQL Server solicitando dados somente dessa semana. Isso limita bastante o número de partições examinadas.

Consultas que são emitidas diretamente pelo usuário contra o data warehouse relacional normalmente têm um componente de data. Consultas comuns são comparações de vendas entre o período atual versus o mesmo período do ano passado. Em casos como esses, o SQL Server pode limitar normalmente a quantidade de dados que serão usados como referência, melhorando, assim, o desempenho da consulta. O mesmo acontece nas consultas com o Analysis Services.

Opções no SQL Server 2000

O SQL Server 2000 tem uma única opção de particionamento: os "modos de exibição particionados". Para criar um modo de exibição particionado, você simplesmente define um modo de exibição que liste todas as tabelas que participam do modo de exibição e usa a instrução "UNION ALL" para concatená-las. Para que o otimizador de consulta isole as partições relevantes a uma determinada consulta, é preciso estabelecer uma restrição confiável para informar as partições que contêm os dados. Uma restrição "confiável" refere-se àquela criada com a opção "WITH CHECK". Se essa opção não for usada quando a restrição for criada ou se os dados forem carregados em massa ou copiados com um programa de cópia em massa (BCP) na tabela sem a presença da dica CHECK_CONSTRAINTS durante cada carregamento, poderá haver dados na tabela que violam a restrição. O SQL Server não confia nessas restrições, e muitos dos benefícios funcionais e de otimização dessas restrições não serão considerados como resultado.

Opções no SQL Server 2005

O SQL Server 2005 oferece uma nova opção de particionamento: as tabelas e os índices particionados. As tabelas particionadas proporcionam um meio de carregar e gerenciar segmentos de dados horizontais com um impacto mínimo sobre os outros usuários da tabela. Discussões detalhadas sobre tabelas particionadas são abordadas em outro fóruns; portanto, não serão novamente mencionadas aqui exceto para recapitular alguns conceitos de nível superior. Referências a outras informações podem ser encontradas no final deste documento.

O particionamento de tabela permite que cada segmento logicamente distinto de uma tabela ou de um índice seja tratado como uma entidade separada. Esses segmentos ou partições podem ser carregados fora da tabela para que o carregamento não afete os usuários da tabela particionada durante o carregamento. Quando o carregamento da partição é concluído, ele é "transferido" para a tabela particionada. Há várias condições a serem satisfeitas para que se tenha uma mudança de partição bem-sucedida; as mais comuns são:

• O esquema da estrutura da tabela particionada deve ser igual ao da tabela para a qual o carregamento será transferido. Isso inclui nomes de coluna, tipos de dados, atributos da condição de nulidade, agrupamento, precisão e restrições de chave primária.

• Qualquer índice da tabela particionada deve estar presente na tabela a receber a transferência e todos os atributos do índice, com exceção do nome, devem corresponder (por exemplo, colunas indexadas, atributo agrupado e atributo exclusivo).

• A partição de destino deve estar vazia.

• A tabela de origem e a partição de destino devem estar no mesmo grupo de arquivos.

• A tabela de origem deve ter uma restrição de verificação confiável definida que seja compatível com a partição de destino.

Para reiterar, esta é uma lista parcial dos requisitos. Outras limitações mais específicas podem ser encontradas no SQL Server Books Online (em inglês).

Há alguns termos que são específicos de como o SQL Server implementa tabelas particionadas. Eles estão resumidamente definidos a seguir, mas são abordados com detalhes em outras fontes, como o Server Books Online e o trabalho de Kimberly Tripp sobre tabelas particionadas (consulte as referências no final deste documento):

• Função de partição

  É um objeto físico do banco de dados que define o limite superior ou inferior.

• Esquema de partição

  É um objeto físico do banco de dados baseado em uma função de partição. Ele define o local no disco de cada partição definida pela função de partição.

• Alinhados

  Tabelas e índices particionados são considerados alinhados quando têm como base a mesma função de partição, de forma implícita ou explícita.

• Co-localizados

  Tabelas e índices particionados são considerados co-localizados quando compartilham uma função de partição equivalente (estão alinhados) e um esquema de partição equivalente. Assim, os limites da partição são iguais e os dados que correspondem a esses limites estão localizados no mesmo grupo de arquivos.

• RANGE LEFT/RANGE RIGHT

  Este é provavelmente um dos aspectos mais confusos do particionamento de tabela. As funções da partição são definidas com RANGE LEFT ou RANGE RIGHT. Uma maneira de memorizar a diferença é que uma função com RANGE LEFT significa que os dados da partição relativos ao limite estão à esquerda do limite; RANGE RIGHT significa que os dados da partição relativos ao limite estão à direita do limite. Portanto, se o limite for "01/01/2005" e a função de partição estipular RANGE LEFT, "01/01/2005" será um limite superior e os dados inferiores ao limite estarão à sua esquerda.

• MERGE

  A mesclagem de partições transforma duas partições em uma. MERGE é usado para remover uma partição.

• SPLIT

  A divisão de uma partição transforma uma partição em duas. SPLIT é usado para adicionar uma partição.

Comparando tabelas particionadas com modos de exibição particionados

Os modos de exibição particionados continuam disponíveis no SQL Server 2005 e ainda podem ser uma boa opção de particionamento em um data warehouse. Em sua maioria, as tabelas particionadas serão mais fáceis de gerenciar. A tabela a seguir oferece uma lista de verificação rápida dos prós e dos contras de ambos os métodos:

Tabela 1 Tabelas particionadas do SQL Server 2005 versus modos de exibição particionados

Funcionalidade	Tabela particionada	Modo de exibição particionado
Manutenção da tabela	A tabela é gerenciada como uma entidade única.	Cada tabela participante é sua própria entidade na qual as alterações de metadados devem ser feitas.
Indexação	Cada partição deve ter os mesmos índices.	Cada tabela pode ter sua própria estratégia de indexação.
Implementação	São praticamente iguais em complexidade de implementação. As tabelas particionadas são mais explícitas na detecção de erros quando há um problema.	Há problemas ocultos que impedem um modo de exibição particionado de funcionar como pretendido. Um problema comum é a criação de restrições de verificação sem verificar os valores atuais. Sem um motivo evidente, o modo de exibição particionado será implementado com êxito mas não funcionará corretamente.
Tempo de compilação	Como todas as partições são indexadas da mesma maneira, o otimizador pode usar o mesmo plano de execução para cada uma e o tempo de compilação será bem menor.	Como as partições podem ter índices diferentes, o otimizador deve avaliar o melhor plano de execução para cada tabela (partição). O tempo de compilação é prejudicado quando há muitas tabelas no modo de exibição particionado.
Carregamento	Uma partição pode ser carregada externamente, o que reduz o impacto sobre os usuários atuais da tabela.	Uma tabela pode ser carregada externamente, o que reduz o impacto sobre os usuários atuais do modo de exibição.
Transferência dos novos dados	Essa é uma operação de metadados que é rápida e é enfileirada naturalmente.	É uma operação de metadados, mas uma instrução ALTER VIEW pode aguardar indefinidamente um bloqueio de SCHEMA.
Capacidade de atualização	Não há regras especiais além daquelas necessárias para a criação de uma tabela particionada que devam ser estabelecidas para que a tabela possa ser atualizada. As tabelas particionadas podem ter colunas de identidade, não exigem chaves primárias, etc.	Várias restrições dificultam a atualização de um modo de exibição particionado, como a falta de identidades e a existência de uma chave primária nas tabelas participantes. Isso significa que as tabelas de base devem ser atualizadas diretamente, o que complica a codificação de INSERTs e UPDATEs.
Backups/restaurações	As partições de uma tabela podem passar por backup ou restauração juntas ou separadamente, dependendo da implementação do grupo de arquivos. Observação: se o grupo de arquivos não estiver marcado como somente leitura, sua restauração exigirá a reaplicação dos logs de transações a partir do ponto do backup.	É possível fazer o backup ou a restauração de tabelas individuais que formam o modo de exibição particionado, individual ou separadamente, dependendo da implementação do grupo de arquivos. Tabelas do modo de exibição particionado também residem em bancos de dados separados, o que permite o backup de bancos de dados separados em partições separadas. Observação: se o grupo de arquivos não estiver marcado como somente leitura, sua restauração exigirá a reaplicação dos logs de transações a partir do ponto do backup.
Implicações de banco de dados	Todas as partições de uma tabela particionada devem residir no mesmo banco de dados.	Tabelas unidas em um modo de exibição particionado podem residir em bancos de dados diferentes. Isso pode tornar as operações de backup e de restauração dos segmentos históricos bastante simples de implementar.
Paralelismo da consulta	Partições individuais são a unidade de paralelismo em um plano de consulta paralela. As consultas que acessam uma única partição não acessarão a tabela usando paralelismo.	Cada tabela de um modo de exibição particionado é considerada separadamente para o acesso de consulta paralela. As consultas que acessam uma única tabela podem acessá-la usando paralelismo.
Carregamento em massa	Uma tabela particionada pode ser o destino de uma operação de inserção em massa/cópia em massa (BCP) diretamente.	Os modos de exibição de partição não podem ser o destino de uma operação de inserção em massa/cópia em massa. Tabelas individuais do modo de exibição devem ser o destino.

Decisão de implementação

A Barnes and Noble não implementou modos de exibição particionados no SQL Server 2000 por causa do tempo de compilação muito grande, normalmente de 30 segundos. No momento, há três anos de dados de vendas, que estão correlacionados a 156 partições de tabela. Uma consulta que se estende por todo o modo de exibição particionado precisaria examinar cada uma das tabelas de base para avaliar um plano de execução. Isso torna impossível o uso de modos de exibição particionados. Além do tempo de compilação, o motivo mais forte para preferir tabelas particionadas a modos de exibição particionados é a facilidade de manutenção da tabela e a facilidade de executar operações INSERT, UPDATE e DELETE que se estendem por várias partições.

Depois de escolher o método de particionamento, precisamos decidir sobre a coluna de particionamento e o intervalo ideal de datas. Muitos dos benefícios do particionamento em um data warehouse são percebidos quando a coluna de particionamento é uma coluna de data. No nosso caso, a coluna não é literalmente do tipo de dados data/hora, mas os dados contidos nela referem-se a uma data armazenada como um número inteiro (SSAAMMDD).

Decisões quanto ao design do particionamento

Nossa primeira decisão em relação ao design é a chave de particionamento. A tabela de fatos de vendas tem uma coluna Transact_Date com o tipo de dados data/hora do SQL Server. Inicialmente, essa parecia ser a coluna de particionamento ideal. No entanto, essa coluna não está definida nas tabelas de fatos de inventário. Em vez disso, a coluna "data" está implementada como um tipo de dados inteiro do SQL Server (no formato SSAAMMDD) para reduzir a alocação de espaço. Essa coluna, chamada SK_Date_ID, é consistente em todas as três tabelas de fatos; portanto, foi a coluna escolhida como chave de particionamento. Um problema ao escolher essa coluna é que ela tentará consultar a tabela de fatos de vendas pela coluna Transact_Date, pois é um tipo de dados data/hora real. Lembre-se de que o otimizador de consulta poderá limitar-se apenas às partições examinadas para resolver uma consulta caso a consulta filtre pela coluna de particionamento.

Nossa próxima decisão de design é o intervalo de particionamento. Os dados atuais da Barnes and Noble estão em tabelas semanais, pois os carregamentos de inventário são executados logicamente uma vez por semana (um instantâneo é criado no início de cada semana e atualizado diariamente). Isso também mantém o tamanho da partição em cerca de 25 GB, que é um tamanho gerenciável. Um esquema de particionamento mensal teria cerca de 100 GB, o que seria mais difícil de gerenciar. Escolhemos a mesma estratégia de particionamento para vendas a fim de aproveitar os benefícios do alinhamento da tabela particionada, que serão discutidos posteriormente.

Provavelmente, nossa maior consideração estava relacionada a como as partições seriam dispostas no disco. Vamos mapear as partições para um ou mais grupos de arquivos, com um ou mais arquivos? Uma discussão detalhada dos prós e dos contras das duas estratégias de alto nível para o mapeamento de partição é abordada no documento “Strategies for Partitioning Relational Data Warehouses in Microsoft SQL Server” (em inglês), cuja referência pode ser encontrada no final deste documento. Decidimos que era melhor mapear cada partição para seu próprio grupo de arquivos, cada uma com um único arquivo de base. Dessa forma, temos controle sobre como ela será disposta no disco. Há diferentes pontos de vista em relação à necessidade ou não disso. Poderia-se dizer que a distribuição que impomos por meio do uso de grupos de arquivos separados poderia ser implementada através do subsistema do disco. Há três principais motivos para a implementação de grupos de arquivos separados:

1. O atributo somente leitura pode ser definido no nível do grupo de arquivos. Quando as partições alcançam oito semanas, que é quando não ocorrem mais inserções na tabela de fatos, podemos ativar o atributo somente leitura para habilitar cenários de backup/restauração em partes (esse é um novo recurso do SQL Server 2005) e reduzir o bloqueio.

2. O cenário de envelhecimento poderia ser realizado no nível do disco. Para habilitar essa possibilidade, as partições devem ser isoladas em seus próprios arquivos do disco. Se as partições estiverem distribuídas por vários discos de um grupo de arquivos, essa opção não estará disponível.

3. Para implementar a passagem das partições antigas para discos de baixo custo, precisamos ter pelo menos dois grupos de arquivos: um para a matriz de disco ativa e outro para a inativa. Isso nos impediria de mapear tudo para um único grupo de arquivos.

Houve duas etapas principais na implementação de tabelas particionadas no ambiente da Barnes and Noble. Primeiro, tivemos que mover todas as tabelas existentes para as tabelas particionadas que criamos e, em seguida, tivemos que modificar os processos existentes de ETL para criar novas partições a cada semana e carregar os novos dados nessas partições. Abordaremos esses processos separadamente a seguir.

Índices particionados?

Há algumas decisões a tomar a respeito do particionamento de índices. A primeira é se realmente devemos particionar índices nas tabelas particionadas. Se devemos, precisamos decidir se os índices devem ser alinhados com a tabela base. Finalmente, precisamos determinar se devemos colocá-los no mesmo local que as tabelas base. A decisão de particionar os índices foi fácil. Para começar, toda a idéia por trás do particionamento está amplamente relacionada à capacidade de gerenciamento e à facilidade de administração. Como apenas uma fração de nossos dados é volátil, não faz sentido gerenciar um grande índice quando grande parte dele não será alterada. A maior parte da manutenção do índice é feita com o reconhecimento da partição e, portanto, podemos executar a manutenção necessária em um subconjunto do índice, em vez de executar por completo.

Os índices podem realmente ser particionados de forma diferente daquela da tabela base, exceto no caso de um índice agrupado. Isso não faz muito sentido em um cenário no qual estaremos trocando dados regularmente. Se os índices seguirem a mesma função de partição, então os dados poderão entrar e sair de forma mais eficiente, pois os dados estarão segmentados da mesma maneira.

O problema final refere-se ao local no qual os índices residirão. Por padrão, os índices agrupados seguem o esquema de partição da tabela base, que define o local no qual as partições residem no disco. Índices não agrupados poderiam ser criados em um esquema de partição separado, mas as operações poderão ser mais facilmente paralelizadas pelo SQL Server se for possível garantir que os dados relacionados estejam no mesmo grupo de arquivos. Para obter mais informações, consulte o trabalho de Kimberly Tripp sobre particionamento no SQL Server 2005.

Conversão das tabelas existentes em tabela particionada

A primeira etapa da conversão das tabelas existentes foi analisar o ambiente atual da Barnes and Noble. As tabelas de fatos e de dimensão estão atualmente armazenadas em dois bancos de dados, um para as tabelas de fatos de inventário (inventário da loja e do centro de distribuição) e outro para tabelas de fatos e de dimensão de vendas. No Project REAL, eles foram consolidados em um único banco de dados, REAL_Warehouse, durante o processo de mascaramento dos dados. Em nosso exercício de particionamento, estamos lidando apenas com as tabelas de fatos; portanto, nos concentraremos nelas.

As tabelas de fatos de vendas herdadas são representadas como uma tabela física do SQL Server por semana. Cada tabela segue a convenção de nomenclatura "Tbl_Fact_Store_Sales_WE_ssaa_mm_dd", onde as datas referem-se à data na qual a semana termina (sábado). Um exemplo de tabela é "Tbl_Fact_Store_Sales_WE_2003_12_27" que compreende os dados de 21/12/2003 a 27/12/2003.

Informações relevantes sobre as tabelas de fatos de inventário são semelhantes às mencionadas anteriormente. Na verdade, há duas variações das tabelas de fatos de inventário: uma para o inventário de DC (centro de distribuição) e outra para o inventário da loja. As convenções de nomenclatura dessas tabelas são "Tbl_Fact_DC_Inventory_WE_ssaa_mm_dd" e "Tbl_Fact_Store_Inventory_WE_ssaa_mm_dd", respectivamente. Os metadados dessas tabelas são significativamente diferentes; portanto, não podem ser combinados. Como nas tabelas de vendas, nossa coluna de particionamento é a SK_Date_ID, com o mesmo tipo de dados.

Conseguimos selecionar uma coluna de particionamento comum, SK_Date_ID, entre nossas três tabelas de fatos, pois os tipos de dados eram consistentes. Isso nos permite alinhar as tabelas para facilitar a união. A chave é uma representação da data em número inteiro. Por exemplo, "25 de dezembro de 2004" torna-se 20041225. Como as tabelas físicas de origem são baseadas em incrementos semanais, nossa conversão em partições semelhantes é bem mais fácil. Uma partição em nossa tabela particionada é igual a uma tabela física de origem. A seguir é fornecida uma representação de alto nível das etapas de carregamento de cada tabela de fatos lógica:

• Crie todos os arquivos e grupos de arquivos.

• Crie a função de particionamento. Ela define todos os limites de uma determinada tabela de partição. Veja a seguir um subconjunto da instrução CREATE PARTITION FUNCTION:

  CREATE PARTITION FUNCTION pf_Range_Fact(int)
  AS 
  RANGE LEFT FOR VALUES (
     20020105,
     20020112,
     20020119,
     .
  .
  .
     20041231)
  

• Crie o esquema de particionamento. Ele define a disposição de cada partição no disco. Decidimos alinhar as tabelas de fatos de inventário e de vendas, mas elas não são alinhadas por loja. Isso facilita as uniões entre partições distintas, mas propaga a E/S pelo armazenamento físico. Quando dizemos "alinhar", queremos dizer que as tabelas particionadas compartilham a mesma função de particionamento. Isso não significa que a função de particionamento deve ser literalmente a mesma, mas que as definições de limite, o tipo de dados da chave de particionamento e o número de partições devem corresponder. O alinhamento do armazenamento indicaria que uma determinada partição de ambas as tabelas reside no mesmo grupo de arquivos.

• Crie a tabela particionada. É como qualquer outra instrução CREATE TABLE, mas indica que a tabela deve ser "colocada" no esquema de particionamento indicado anteriormente.

• Para cada tabela de origem, como dbo.Tbl_Store_Inventory_WE_2004_12_25:

  • Execute SELECT INTO em uma tabela temporária que será transferida para a tabela particionada.

  • Adicione uma restrição de verificação (WITH CHECK) que corresponda à partição de destino.

  • Determine a partição de destino usando $partition function:SELECT @PartitionNum = $partition.FactRangePFN(20041225).

  • Execute SWITCH da tabela temporária para a partição de destino.

• Crie índices.

Observação: Se os dados de vendas e/ou o inventário herdados estivessem sendo transferidos para uma tabela particionada dentro do mesmo banco de dados e já residissem nos grupos de arquivos desejados, poderíamos ter ignorado a etapa SELECT INTO e simplesmente transferido as tabelas para as partições apropriadas depois de criar a restrição de verificação e de alterar a coluna de particionamento para NOT NULL. Esse não era o caso; portanto, precisávamos copiar a tabela de origem em uma tabela separada que residisse no grupo de arquivos desejado antes de transferi-la para a tabela particionada.

Os comandos CREATE PARTITION FUNCTION e CREATE PARTITION SCHEME foram gerados automaticamente. Especialmente no nível semanal, esses comandos seriam cansativos e propensos a erros se tivéssemos que digitá-los manualmente. Isso também nos proporcionou um pouco de flexibilidade ao definir os limites da partição e facilitou a confirmação de que o esquema de partição era correspondente aos limites da função de partição. Esse código foi incluído para reutilização nos artefatos do Project REAL.

A criação inicial da tabela particionada pode ser um processo extremamente demorado. Em nosso caso, estávamos literalmente copiando 1,5 TB de dados. Nosso desafio era tornar o processo o mais vantajoso possível. INSERT INTO foi rapidamente descartado devido à sobrecarga de logs e de bloqueio. O BCP exigiria que os dados fossem primeiro copiados fora do disco e, em seguida, seriam novamente inseridos na tabela temporária via BULK INSERT. Isso nos proporciona maior controle sobre como o arquivo é copiado do que a opção SELECT INTO, mas seria realmente mais rápido executar SELECT INTO do que apenas fazer a cópia em massa dos dados para fora via BCP. A opção SELECT INTO provou ser mais de nove vezes mais rápida do que a combinação BCP/BULK INSERT, mesmo após a confirmação de que o BCP não gera logs e causa um mínimo de bloqueios.

O principal problema em relação ao SELECT INTO é que não há como indicar o grupo de arquivos da tabela criado no processo. Isso é crítico, pois o grupo de arquivos dessa tabela temporária deve corresponder ao grupo de arquivos de sua partição de destino. Para ter certeza de que o grupo de arquivos da tabela temporária tenha sido definido corretamente, o grupo de arquivos padrão do banco de dados foi alterado antes de SELECT INTO para garantir a criação da tabela no grupo de arquivos desejado. Isso limitou bastante a nossa capacidade de processar tabelas em paralelo, pois todas as instruções SELECT INTO simultâneas teriam que usar o mesmo grupo de arquivos (apenas um grupo de arquivos padrão do banco de dados por vez!). Isso também introduziria um ponto de acesso ao disco, o que poderia ter um impacto negativo sobre o paralelismo. A natureza serial da opção SELECT INTO ainda era mais desejável do que a opção BCP/BULK INSERT; sendo assim, optamos pela primeira.

As tabelas de origem não continham restrições em relação aos dados de data que continham. É preciso haver uma restrição de verificação confiável na coluna de particionamento de origem que seja consistente com o limite da partição de destino para que a transferência seja bem-sucedida. Essa restrição foi adicionada durante o processo de carregamento, após a conclusão de SELECT INTO.

Além disso, a coluna de particionamento, SK_Date_ID, foi definida como anulável nas três tabelas. Poderíamos ter incluído um limite em nossa função de partição que definisse uma partição para a qual todas as linhas com SK_Date_ID = NULL devessem ir, mas isso não faria sentido em uma estratégia de particionamento baseada em datas. Além disso, um SK_Date_ID com NULL violaria nossa CHECK CONSTRAINT. Confirmamos com a Barnes and Noble que SK_Date_ID nunca deveria ser nulo, e implementamos essa regra comercial no esquema.

A seguir é mostrada uma análise detalhada de uma de nossas maiores tabelas de fatos de inventário:

Tabela 2 Análise da partição da tabela de cópia

Etapa	Tempo decorrido (mm:ss)	% do total
SELECT INTO	7:33	74%
ALTER SK_Date_ID not null	5:51
ALTER ADD check constraint	2:42	26%
SWITCH to partitioned table	0:00	0%
Total	10:15

Observe que não usamos uma instrução ALTER TABLE para implementar a restrição NOT NULL na coluna SK_Date_ID da tabela de destino. Em vez disso, implementamos essa restrição durante a instrução SELECT INTO usando a função ISNULL. Para ilustrar esse ponto, veja a instrução a seguir.

SELECT <Column 1>, 
<Column 2>, 
isnull([SK_Date_ID], -1) as [SK_Date_ID],
.
.
.
<Column x>
INTO dbo.Tmp_NewPartition FROM Tbl_Fact_Store_Sales_WE_2005_01_01

Antes da implementação desse truque, a instrução ALTER TABLE levava cerca de seis minutos por tabela para adicionar a restrição NOT NULL. É uma enorme economia de tempo ao multiplicarmos isso por partição entre as três tabelas particionadas. A economia de tempo estimada usando esse "truque" foi de mais de cinco horas somente na tabela de fatos de inventário da loja.

Examinando a tabela particionada

A primeira reação, depois de carregar as novas tabelas particionadas, é dar uma olhada nos resultados. Algo que é rapidamente aparente é que nada é imediatamente aparente. Não há nada, na inspeção inicial, que diferencie as tabelas particionadas das não particionadas. Um aspecto positivo é que há bem menos objetos no banco de dados. O banco de dados de origem armazenava 229 tabelas de fatos; o banco de dados com tabelas particionadas contém 3 tabelas de fatos. Qualquer uma das 3 tabelas de fatos pode ser analisada mais profundamente por meio da exibição das propriedades no SQL Server Management Studio. A tela a seguir revela as propriedades da tabela Tbl_Fact_Store_Inventory:

Figura 1 Propriedades da tabela particionada

Fica óbvio pela contagem de linhas que nosso carregamento foi bem-sucedido. Pode-se fazer uma verificação adicional para verificar se a soma da contagem de linhas das tabelas de origem é igual à contagem de linhas indicada nessa caixa de diálogo. A seção "Storage" mostra que a tabela está particionada e indica o esquema de partição para o qual as partições são mapeadas.

DMVs (Dynamic Management Views) podem ser consultados para dar uma indicação melhor da distribuição das linhas pelas partições. Essa pode ser uma proposta cara, pois uma contagem real é realizada em cada partição da tabela, mas a consulta a seguir oferece uma boa visão geral da tabela particionada e da distribuição das linhas.

SELECT $partition.pf_Range_Fact(o.SK_Date_ID) AS [Partition Number]
      , min(o.SK_Date_ID) AS [Min Date]
      , max(o.SK_Date_ID) AS [Max Date]
      , count(*) AS [Rows In Partition]
FROM dbo.Tbl_Fact_Store_Sales AS o
GROUP BY $partition.pf_Range_Fact(o.SK_Date_ID)
ORDER BY [Partition Number]

Ela produz as seguintes informações para nossa tabela de fatos de vendas (apenas um subconjunto do conjunto de resultados é incluído, para mantermos a concisão).

Nº da partição	Chave de data mín.	Chave de data máx.	Linhas na partição
…	…	…	…
140	20040829	20040904	8061536
141	20040905	20040911	8308355
142	20040912	20040918	8044390
143	20040919	20040925	7824844
144	20040926	20041002	7864007
145	20041003	20041009	7853734
146	20041010	20041016	8056497
147	20041017	20041023	8017784
148	20041024	20041030	7684242
149	20041031	20041106	7924918
150	20041107	20041113	8845731
151	20041114	20041120	8963072
152	20041121	20041127	9361857
153	20041128	20041204	11201851
154	20041205	20041211	13974601
155	20041212	20041218	17549392
156	20041219	20041225	18736647
157	20041226	20041231	12016107

Processamento incremental

Depois de criar as tabelas particionadas, precisamos fazer uma manutenção contínua. Isso envolve criar uma nova partição para cada uma de nossas tabelas particionadas no início de cada semana. Além disso, optamos por implementar uma "janela deslizante" e migrar dados antigos do disco mais caro para o disco menos caro.

Janela deslizante

Uma implementação de "janela deslizante" (sliding window) modifica o intervalo de dados disponíveis em uma tabela particionada com o passar do tempo, adicionando novas partições quando novos dados são introduzidos e removendo as partições antigas à medida que os dados históricos se tornam desnecessários. A quantidade de dados disponibilizados em uma tabela particionada depende exclusivamente das exigências da empresa, e isso varia de uma implementação para outra. Essas exigências podem estabelecer que três anos corridos de vendas deverão estar sempre disponíveis. Nesse caso, as vendas históricas podem ser removidas enquanto as novas são adicionadas. Para esclarecer a implementação de janela deslizante e para dar um exemplo de como adicionar e remover partições com as tabelas particionadas do SQL Server 2005, criamos o cenário a seguir:

1. Depois de carregar algumas informações em uma nova tabela de fatos externa para a semana que termina em 01/01/2005, desejamos disponibilizar as novas informações na tabela particionada.

   

   Figura 2

2. Em primeiro lugar, dividimos a última partição na função de partição, que é tecnicamente tudo o que se encontra após 25/12/2004, para incluir um novo limite que englobe tudo o que estiver entre 25/12/2004 e 01/01/2005.

   

   Figura 3

3. Agora trocamos a partição recém-criada, que está vazia, pela tabela externa que foi carregada antes da etapa 1. Isso resulta em uma tabela externa vazia que excluiremos.

   

   Figure 4

4. Agora temos uma tabela particionada que contém os novos dados.

   

   Figura 5

5. Em seguida, desejamos remover os dados da partição mais antiga. Começamos criando uma tabela externa vazia com um esquema igual ao da tabela particionada (incluindo índices).

   

   Figura 6

6. Trocamos a primeira partição da tabela particionada pela tabela externa que acabamos de criar.

   

   Figura 7

7. Agora, mesclamos a primeira partição, que incluiu todas as datas com exceção das inferiores ou iguais a 05/01/2002, com a segunda partição. Nossa primeira partição agora inclui todas as datas inferiores ou iguais a 12/01/2002. Como os dados da semana que termina em 05/01/2002 estarão fora da tabela particionada, podemos arquivá-los em algum lugar e excluir a tabela do SQL Server.

Figura 8

Uma implementação de janela deslizante normalmente significa adicionar e remover partições ao mesmo tempo por meio de ações de divisão/troca e mesclagem/troca. No entanto, as ações de adicionar e remover partições não são diretamente dependentes e podem certamente ocorrer em separado. Uma variação da janela deslizante pode indicar que os dados mais antigos não podem ser removidos com a mesma freqüência em que os novos dados são adicionados. Um exemplo disso é a exigência da empresa de disponibilidade dos três últimos anos fiscais completos. Nesse caso, um novo ano fiscal é gradualmente formado, semana a semana ou mês a mês, até alcançar um ano fiscal completo. Em seguida, o ano fiscal mais antigo é removido de uma só vez. Todos esses cenários são possíveis por meio de uma variação da implementação de janela deslizante usada no Project REAL. A diferença estará em como e quando as operações de mesclagem/troca serão realizadas.

A Barnes and Noble ainda não acumulou a quantidade de dados de vendas ou de inventário que garantiria o arquivamento dos dados. Portanto, reduzimos o período de exclusão para poder testar a implementação de janela deslizante. O processo é conduzido por parâmetros e pode ser facilmente modificado para ajustar-se ao tempo.

Envelhecimento dos dados

Um conceito que é muito comentado, mas raramente implementado, é o de manter os dados mais ativos no subsistema de disco mais rápido e mais disponível e hospedar os dados menos ativos no disco menos caro que é mais lento e/ou menos disponível. Isso é particularmente útil em um data warehouse no qual há uma exigência de armazenamento em disco extremamente alta e, normalmente, dados de vários anos precisam ser mantidos online. Sabemos que haverá um ponto de acesso de atividade nos dados dos últimos um ou dois anos. Entretanto, isso não significa que os dados antigos não precisem estar disponíveis para consultas ocasionais ou exigências legais. Para gerenciar as despesas de manutenção dessa infra-estrutura, implementamos um mecanismo no Project REAL para mover partições de um subsistema mais caro para um menos caro durante nosso processamento incremental. Isso foi feito durante o mesmo processo incremental no qual as partições são adicionadas e excluídas.

A movimentação para um disco de baixo custo é um tanto complexa na teoria, mas relativamente simples na prática. O principal a lembrar é que estamos realmente movendo dados de um subsistema de disco para outro. Essa pode ser uma operação bastante cara, especialmente ao mover a quantidade de dados encontrada em um data warehouse. Optamos por mover uma partição por semana para reduzir a quantidade de movimentação de dados em um determinado período.

E agora a complexidade teórica. Um esquema de partição define como as partições definidas na função de partição são dispostas no disco. Depois que um esquema de partição é criado, ele não pode ser renomeado nem modificado de forma alguma, exceto para indicar a localização das novas partições. A divisão e a mesclagem das partições na função de partição podem adicionar ou remover partições, mas não afetam a localização das partições existentes. Portanto, para "mover" uma partição, devemos criar um novo esquema de partição para indicar o novo layout das partições no disco.

Figura 9 Movendo dados antigos para um disco de baixo custo

A seguir é apresentada uma visão geral de alto nível das etapas usadas para executar a movimentação dos dados antigos. Inicialmente, esse processo parece complexo, particularmente em uma situação, como em nosso Project REAL, na qual a tabela particionada de vendas tem mais de 150 partições. Como todas as etapas anteriores são etapas de metadados, com exceção da etapa de movimentação dos dados, elas são executadas muito rapidamente.

1. Crie um novo esquema de partição, com base na função de partição existente, que duplique com exatidão o esquema de partição existente, com exceção da partição ou das partições sendo movidas. O limite da partição em movimento na definição do esquema de partição indicará um grupo de arquivos no disco menos caro.

2. Crie uma nova tabela particionada sobre o novo esquema de partição.

3. Faça a iteração por cada partição e mude da partição antiga para o mesmo número de partição na nova partição (ambas as tabelas particionadas usam a mesma função de partição) até alcançar a partição sendo movida. As caixas sombreadas referem-se às partições preenchidas e as caixas brancas indicam uma partição vazia.

   

   Figura 10

4. A partição sendo movida precisa ser explicitamente copiada, pois os dados estão sendo movidos. Isso pode ser feito copiando os dados diretamente da partição antiga para a nova, usando um INSERT INTO..SELECT, ou podemos executar SELECT INTO para uma tabela externa que resida no mesmo grupo de arquivos que a partição de destino. Como no carregamento inicial, SELECT INTO funciona muito melhor que INSERT INTO; portanto, optamos pelo primeiro método.

   

   Figura 11

5. Ao usar o método SELECT INTO, precisamos transferir a tabela externa para seu destino final na nova tabela particionada.

   

   Figura 12

6. Agora fazemos a iteração nas demais partições do esquema de partição atual e transferimos as partições para a nova tabela particionada, como fizemos na etapa 3.

   

   Figura 13

7. Para limpar, excluímos a tabela particionada e o esquema de particionamento antigos e renomeamos a nova tabela particionada com o nome da tabela particionada original.

   

   Figura 14

Exemplos de código

A seguir apresentamos uma análise mais aprofundada do processo usado para implementar o gerenciamento de dados antigos que acabou de ser descrito.

1. Crie um novo esquema de particionamento.

   O novo esquema de partição é uma cópia exata da partição existente, a não ser pelo limite de data que define o momento em que uma partição fica antiga. A data da semana atual é incluída como um sufixo no nome, para que ele seja exclusivo. Os nomes de esquema de partição não podem ser alterados; portanto, esse nome será mantido até o próximo processo de envelhecimento, quando o novo esquema de partição substituirá esse.

   Em um processo semanal comum de criação de uma nova partição, apenas uma partição será movida de um grupo de arquivos "ativo" para um "antigo". Um cursor é criado nos metadados para percorrer os limites e criar o script do novo esquema de partição. Embora os cursores normalmente não sejam uma boa idéia em qualquer processo de ETC devido ao baixo desempenho, neste caso eles são usados apenas para fazer a iteração por um pequeno número de objetos de metadados. Observe que todos os grupos de arquivos devem permanecer iguais entre ambos os esquemas de partição, com exceção das partições que estejam sendo movidas do estado "ativo" para o estado "antigo". O código para isso tem a seguinte aparência:

   DECLARE CurrentSchemePartitions CURSOR FOR 
   SELECT FileGroupName, Boundary 
   FROM dbo.fn_Get_FileGroupsByPartitionBoundary(@psOld_Scheme_Name)
   ORDER BY Boundary ASC 
   
   OPEN CurrentSchemePartitions
   
   SET @psSQL_Text = 'CREATE PARTITION SCHEME ' + @psNew_Scheme_Name + '  
   AS PARTITION pf_Range_Fact 
   TO ('
   
      FETCH NEXT FROM CurrentSchemePartitions INTO @psFG_Name, @pnBoundary_Date
   
      WHILE @@FETCH_STATUS = 0
      BEGIN
         -- If the partition boundary is less than the beginning date, use the file   
         -- group for the new partition to move into the Agedd area
         IF @pnBoundary_Date < CONVERT(int, CONVERT(char(10), @pdLogical_Date, 112))
         BEGIN
     SET @psSQL_Text = @psSQL_Text + @psFG_Name + ', '   
            SET @psAged_FG_Name = @psFG_Name
         END
         -- If the partition boundary is less than or equal to the Aged date and was 
         -- previously in one of the "Current" filegroups, script the partition to 
         -- the Aged filegroup that will be relinquished when the old partition drops  
         -- off.
         ELSE IF @pnBoundary_Date <= CONVERT(int,CONVERT(char(10),@pdAged_Date, 112)) 
            AND @psFG_Name LIKE @psActive_FG_Prefix + '%'
         BEGIN
            SET @psSQL_Text = @psSQL_Text + @psAged_FG_Name + ', '
     SET @psActive_FG_Name = @psFG_Name
         END
         ELSE   
            SET @psSQL_Text = @psSQL_Text + @psFG_Name + ', '
   
         FETCH NEXT FROM CurrentSchemePartitions INTO @psFG_Name, @pnBoundary_Date
   
      END
   
      -- !!When we are done, we need to add a additional partition to the scheme. 
      -- This is for the right-most partition, which was not represented in our 
      -- cursor query because it is not in the partitioning function. Since we are 
      -- left partitioning, data will ever be in this final partition.
      SET @psSQL_Text = @psSQL_Text + '[Primary])'
      EXEC (@psSQL_Text)
   
      CLOSE CurrentSchemePartitions
      DEALLOCATE CurrentSchemePartitions
   

2. Crie a nova tabela particionada.

   A definição da nova tabela particionada tem exatamente a mesma aparência da antiga. A única diferença é que ela será definida no novo esquema de partição que acabamos de criar. Também é necessário criar índices para que as partições possam transferir diretamente da tabela particionada antiga para a nova.

3. Um novo cursor, idêntico àquele usado na etapa 1, é criado. Ao fazer o looping desse cursor, transferiremos diretamente cada partição para a partição equivalente na nova tabela. Como tanto o esquema novo de partição quanto o antigo são baseados na mesma função de partição, sabe-se que os números da partição de origem e de destino são iguais.

   SELECT @PartitionNum = $partition.pf_Range_Fact(@pnBoundary)
   SET @psSQL_Text = 'ALTER TABLE ' + @psPartitioned_Table_Name + 
   ' SWITCH PARTITION ' + CONVERT(varchar(3), @PartitionNum) + ' TO ' 
   + @psNew_Partitioned_Table_Name + ' PARTITION ' + 
   CONVERT(varchar(3), @PartitionNum)
   EXEC (@psSQL_Text)
   

4. Se a partição estiver sendo movida, precisaremos copiar os dados para uma nova tabela externa com a mesma estrutura. Como mencionado anteriormente, a maneira mais eficiente de fazer isso é executando uma operação SELECT INTO, seguida pela criação da restrição de verificação e dos índices. Em seguida, transferimos a tabela externa (agora uma tabela particionada, depois de criar os índices) para a tabela particionada que criamos na etapa 2. O segmento de código para essas etapas é semelhante a este:

   SET @pnAged_Boundary_Date = @pnBoundary 
   
   -- Get the name of the Aged FG that the moving partition will reside on by 
   -- looking it up on the new partition scheme
   SELECT @psNew_FG_Name = dbo.fn_Get_FileGroupForBoundary(@psNew_Scheme_Name, 
   @pnBoundary)
   
   -- Change the default filegroup to the filegroup the moving partition will 
   -- reside on so the SELECT INTO will create the table on the correct filegroup.
   EXEC etl.up_SetDefaultFG @pnBoundary, @psNew_Scheme_Name
   
   IF (SELECT COUNT(*) FROM sys.tables WHERE name = 'MovingPartition') > 0
   DROP TABLE MovingPartition
   
   -- Copy the data from the moving partition on the old table to a temporary 
   -- partition on the new filegroup.
   SELECT @PartitionNum = $partition.pf_Range_Fact(@pnBoundary)
   SET @psSQL_Text = 'SELECT * INTO MovingPartition FROM ' + 
   @psPartitioned_Table_Name + 'WHERE $partition.pf_Range_Fact(SK_Date_ID) = ' 
   + CONVERT(varchar(4), @PartitionNum)
   EXEC (@psSQL_Text)
   
   -- Since constraints and indexes were lost during the SELECT INTO, create them 
   -- to match those on the destination partitioned table.
   SET @psBoundary = @pnBoundary
   SET @pdWeek_Begin = SUBSTRING(@psBoundary, 5, 2) + '/' +
   SUBSTRING(@psBoundary, 7, 2) + '/' + 
   SUBSTRING(@psBoundary, 1, 4)
   
   SET @psSQL_Text = 'ALTER TABLE MovingPartition WITH CHECK 
   ADD CONSTRAINT MovingPartition_Date CHECK  
   (SK_Date_ID BETWEEN ' + 
   CONVERT(varchar(8), DATEADD(dd, -6, @pdWeek_Begin), 112) + ' AND ' + 
   CONVERT(varchar(8), @pnBoundary, 112) + ')'
   EXEC (@psSQL_Text)
   
   EXEC etl.up_CreateIndexes 'MovingPartition', @psNew_FG_Name
   
   -- Get the partition number for the moving partition and switch it in to the 
   -- new partitioned table
   SET @psSQL_Text = 'ALTER TABLE MovingPartition SWITCH TO ' + 
   @psNew_Partitioned_Table_Name + ' PARTITION ' + 
   CONVERT(varchar(3), @PartitionNum)
   EXEC (@psSQL_Text)
   
   DROP TABLE MovingPartition
   

5. Exclua ou renomeie a tabela particionada antiga.

6. Renomeie a nova tabela particionada.

Observações e recomendações

Várias observações foram feitas com base na experimentação com um conjunto completo de dados. Elas estão relacionadas principalmente às comparações de desempenho. Essas observações estão documentadas a seguir.

Compartilhando a função de partição

Logicamente, há uma única função de partição para as três tabelas de fatos. Decidimos particioná-las da mesma maneira para facilitar as uniões entre elas. Por exemplo, vamos supor que emitimos uma consulta relacional que solicita um item de vendas comparado com o inventário de cada mês no ano corrente. O SQL Server pode identificar que essas duas tabelas particionadas estão alinhadas, o que significa que elas compartilham a mesma função de partição. Ao unir duas tabelas particionadas alinhadas, o otimizador tem a opção de unir dentro das partições primeiro e, em seguida, reunir os resultados do subconjunto. Quando dizemos que as tabelas compartilham a mesma função de partição, isso não significa que elas devam compartilhar, literalmente, a mesma função de partição. As respectivas funções de partição devem ter o mesmo número de partições e a chave de particionamento deve ser do mesmo tipo de dados. A maneira mais fácil de manter essas regras era compartilhar literalmente a mesma função de partição.

Compartilhar a mesma função de partição gera alguns problemas. Ao dividir a última partição para adicionar um novo limite, todos os esquemas de partição que fazem referência à função de partição devem ter identificado o local no qual será colocada a próxima partição. Ao remover partições com a função de mesclagem, todos os esquemas de particionamento dependentes devem ter esvaziado a partição. Se a primeira partição não estiver vazia para todas as tabelas que fazem referência à função de partição, a ação de mesclagem resultará na movimentação física das linhas da partição removida para o local da partição com a qual está sendo mesclada. Esse não é um problema grave, mas o desempenho seria afetado com a movimentação das linhas e, o mais importante, não removeria realmente as linhas que desejamos arquivar.

Para demonstrar a seguir, dispusemos a função de partição à esquerda e os três esquemas de partição nas colunas à direita. Ao dividirmos a última partição na preparação para transferir os novos dados, o Próximo FG apropriado deve ser definido nos três esquemas de partição para que os novos dados sigam para o local correto. Quando mesclamos a primeira partição durante os preparativos para remover a semana que termina em 05/01/2002, os dados da primeira partição de todas as tabelas particionadas que usam os três esquemas deverão ter sido transferidos e a partição deverá estar vazia. Se, por exemplo, a tabela Tbl_Fact_Store_Inventory ainda contiver dados dessa semana, a ação de mesclagem resultará na movimentação desses dados para FG 3 antigo.

Função de partição		Esquemas de partição
Pf_Range_Fact	ps_FactStoreSales	ps_FactStoreInventory	ps_FactDCInventory
05/01/2002	FG 1 antigo	FG 2 antigo	FG 3 antigo
12/01/2002	FG 2 antigo	FG 3 antigo	FG 4 antigo
…
18/12/2004	FG 1 ativo	FG 2 ativo	FG 3 ativo
25/12/2004	FG 2 ativo	FG 3 ativo	FG 4 ativo
01/01/2005	Próximo FG	Próximo FG	Próximo FG

Isso tudo poderia ter sido resolvido com a criação de funções de partição separadas, mas as mesmas ações teriam que ser realizadas de qualquer forma. O único benefício seria que elas não teriam que ser executadas inteiramente. Separar as funções de partição também poderia resultar em uma disparidade no número de partições que as tabelas de fatos possuem em um determinado momento. Isso anularia o benefício de se ter tabelas alinhadas.

Criação de índices

Durante o preenchimento inicial das tabelas particionadas, surgiu uma dúvida em relação a se deveríamos criar os índices na tabela particionada antes ou depois de ela ter sido carregada. Inicialmente, essa decisão parecia ter pouco impacto sobre o desempenho. As opções estão descritas a seguir:

Opção 1 - Criar índices na tabela particionada antes do carregamento

1. Crie a tabela particionada.

2. Crie índices na tabela particionada.

3. Para cada tabela de origem:

   1. Crie a tabela externa.

   2. Execute SELECT INTO na tabela externa.

   3. Crie a restrição CHECK.

   4. Crie índices para corresponder àqueles da tabela particionada.

   5. Transfira a tabela externa para a partição apropriada na tabela particionada.

Opção 2 - Criar índices na tabela particionada após o carregamento

1. Crie a tabela particionada.

2. Para cada tabela de origem:

   • Crie a tabela externa.

     1. Execute SELECT INTO na tabela externa.

     2. Crie a restrição CHECK.

     3. Transfira a tabela externa para a partição apropriada da tabela particionada.

3. Crie índices na tabela particionada.

As etapas sublinhadas indicam as diferenças entre as duas opções. Como, em ambos os casos, os índices são criados após o carregamento dos dados, não deveria existir uma diferença substancial entre as duas opções, mas existia. A opção 2 demonstrou ser 70% mais rápida do que a opção que escolhemos para o carregamento inicial. Não temos o que escolher entre as duas opções para o carregamento incremental, porque estamos transferindo para uma tabela particionada que já tem os índices criados. Assim, devemos criar índices correspondentes na tabela externa antes de fazer a transferência.

Ao usar a opção 1, seja para o carregamento inicial seja para carregamentos incrementais, certifique-se de criar o índice no esquema de particionamento usado pela tabela particionada de destino. Isso, essencialmente, transforma a tabela externa em uma tabela particionada com uma única partição carregada. Assim, você deve indicar o número da partição da tabela de origem externa E da tabela de destino durante a execução da instrução SWITCH.

Funções definidas pelo usuário para consultas de metadados

Há vários modos de exibição de gerenciamento de dados (DMVs, Data Management Views) que representam os metadados das funções de partição e dos esquemas. Utilizamos esses modos de exibição em várias partes de nossa implementação, mas reduzimos a complexidade criando duas funções definidas pelo usuário que estão documentadas aqui. Como esses DMVs não estavam documentados no momento do nosso desenvolvimento, o meio correto de usá-los foi determinado pelo rastreamento das consultas que foram produzidas pelo SQL Server Management Studio ao executar o script CREATES na função de partição e nos objetos do esquema de partição.

A primeira função retorna uma tabela com um conjunto de resultados de todos os grupos de arquivos e seus limites de partição correspondentes (limites superiores, em nosso caso, pois usamos a função de partição LEFT) em um esquema de particionamento.

CREATE FUNCTION dbo.fn_Get_FileGroupsByPartitionBoundary(@SchemeName varchar(50))
RETURNS TABLE
AS RETURN
(
SELECT sf.name AS FileGroupName, 
CONVERT(int, sprv.value) AS Boundary
FROM sys.partition_schemes AS sps
INNER JOIN sys.partition_functions AS spf 
ON sps.function_id = spf.function_id
INNER JOIN sys.destination_data_spaces AS sdd 
ON sdd.partition_scheme_id = sps.data_space_id 
AND sdd.destination_id <= spf.fanout
INNER JOIN sys.partition_range_values sprv 
ON sprv.function_id = spf.function_id
AND sprv.boundary_id = sdd.destination_id
INNER JOIN sys.filegroups AS sf 
ON sf.data_space_id = sdd.data_space_id
WHERE sps.name= @SchemeName
)

A segunda função definida pelo usuário retorna o último grupo de arquivos anterior ou igual ao limite especificado.

CREATE FUNCTION dbo.fn_Get_FileGroupForBoundary (@SchemeName varchar(50), @Boundary int)RETURNS varchar(50)WITH EXECUTE AS CALLERAS-- Find last filegroup prior to or equal to specified boundaryBEGINDECLARE @FileGroupNamevarchar(50)SELECT TOP 1 @FileGroupName = sf.name FROM sys.partition_schemes AS spsINNER JOIN sys.partition_functions AS spf ON sps.function_id = spf.function_idINNER JOIN sys.destination_data_spaces AS sdd ON sdd.partition_scheme_id = sps.data_space_id AND sdd.destination_id <= spf.fanoutINNER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_idAND sprv.boundary_id = sdd.destination_idINNER JOIN sys.filegroups AS sf ON sf.data_space_id = sdd.data_space_idWHERE sps.name = @SchemeName AND sprv.value <= @BoundaryORDER BY sprv.value DESCRETURN(@FileGroupName)END

Particionamento de cubos

Benefícios do particionamento de cubos

Além do particionamento do data warehouse relacional, optamos por particionar os cubos do Analysis Services. Mais precisamente, no Analysis Services 2005, particionamos os grupos de medidas, mas vamos nos referir genericamente ao processo todo como "particionamento de cubos". Há muitos benefícios no particionamento de um cubo grande, semelhante àqueles obtidos com o particionamento do data warehouse relacional. Um dos benefícios mais óbvios é na manutenção do cubo. Os cubos são processados, completa ou incrementalmente, no nível da partição. Um cubo com várias partições pode ser processado de forma seletiva. Isso é particularmente relevante quando apenas uma ou duas partições são afetadas pelas alterações feitas nas tabelas de origem. Isso reduz significativamente a janela em lotes no processamento incremental. As partições também têm agregações e modo de armazenamento próprios. Ao particionar por data, as partições antigas poderiam ser reprocessadas em um nível de agregação inferior ou com um modo de armazenamento diferente (ROLAP/HOLAP/MOLAP) para reduzir a quantidade de espaço em disco e o tempo de processamento necessários. As configurações de cache proativas também são definidas no nível da partição. As partições podem até mesmo ser armazenadas em um servidor remoto, como partições remotas. O gerenciamento é mais fácil porque as partições antigas podem ser simplesmente excluídas, em vez de reprocessar o grupo de medidas todo para reconhecer o arquivamento dos dados na fonte de dados relacional de base. O desempenho da consulta também deve ser considerado quando consultas de cubo são criadas de maneira a limitar os dados com base na chave de particionamento.

Alterações no Analysis Services 2005

A maior parte dos princípios de particionamento aplicáveis no Analysis Services 2000 também é relevante no Analysis Services 2005. Há muitas alterações, e a maioria delas são melhorias em relação à versão anterior.

Em primeiro lugar, uma partição é definida no nível do grupo de medidas e não no nível do cubo. Um cubo contém um ou mais grupos de medidas relacionados a uma tabela de fatos lógica nas tabelas de origem do grupo de medidas. Uma melhora de desempenho incrível é o fato de o processamento de um cubo ou grupo de medidas resultar automaticamente no processamento paralelo das partições de base. No Analysis Services 2000, as partições eram processadas de forma serial, a não ser que um programa DSO personalizado forçasse explicitamente o processamento paralelo, como o utilitário Parallel Process (disponível gratuitamente para download no site da Microsoft).

No Analysis Services 2000, os benefícios no desempenho da consulta obtidos com o particionamento eram percebidos somente se frações de dados fossem definidas nas partições do cubo para permitir que o mecanismo OLAP tivesse conhecimento dos dados contidos em cada partição. Isso é semelhante à definição de restrições de verificação nos modos de exibição particionados para permitir que o otimizador do SQL Server reduza o número de tabelas consultadas com base na coluna de particionamento. No Analysis Services 2005, os cubos MOLAP não exigem mais frações de dados para essa finalidade. Isso porque os cubos MOLAP incluem heurísticos para mapear os dados contidos nas várias partições. Lembre-se de que qualquer cubo revertido para ROLAP, como durante a recriação do cache proativo, não terá esses heurísticos disponíveis e o desempenho poderia ser prejudicado se não fossem definidas frações de dados. Portanto, se o cubo será revertido para ROLAP, o ideal é definir frações de dados.

Os dados de origem nos cubos do Analysis Services 2000 foram definidos diretamente a partir de uma tabela ou de um modo de exibição do banco de dados relacional de origem. Um filtro poderia ser definido para especificar um subconjunto da tabela ou do modo de exibição. Por exemplo, se um modo de exibição particionado fosse usado, o nome dele poderia ser usado como origem de uma partição do Analysis Services 2000 e um filtro separado serviria para limitar os dados na partição do AS aos dados de uma tabela de base do modo de exibição particionado. No Analysis Services 2005, a origem é especificada como uma tabela/um modo de exibição ou uma consulta. Se um subconjunto da tabela de base ou do modo de exibição preencherá a partição, então a definição de partição especifica uma consulta, que é chamada de ligação de consulta (query-binding).

Finalmente, há mais opções de automação para a criação de partição no Analysis Services 2005. Anteriormente, o DSO (Decision Support Objects) era usado para "clonar" uma partição existente, alterar os atributos relevantes e salvar a nova partição. O DSO foi substituído pelo AMO (Analysis Management Objects). Todas as solicitações do AMO são eventualmente convertidas em scripts XMLA (XML for Analysis). Com o SSIS tem a capacidade de executar XMLA nativo, essa é outra opção. Qual é a melhor? É o que nós esperamos determinar.

Discussões sobre a estratégia de particionamento do Analysis Services

A primeira etapa da implementação do particionamento no Analysis Services é determinar a estratégia de particionamento, ou seja, em que limites as partições devem ser divididas. Como no data warehouse relacional, uma estratégia comum é particionar com base na data. Isso simplifica o processamento, pois os dados são normalmente carregados incrementalmente com base na data. Também simplifica o arquivamento, pois, como mencionado anteriormente, partições antigas podem ser simplesmente excluídas após a execução de um backup. É útil particionar cubos do Analysis Services com os mesmos critérios usados no data warehouse relacional de base. Assim, o arquivamento de dados pode ser realizado em um único processo que exclui as partições apropriadas do data warehouse e dos cubos. Essa foi a estratégia escolhida na Barnes and Noble e no Project REAL.

A implementação da Barnes and Noble inclui um modo de exibição sobre cada tabela de fatos para incluir algumas informações adicionais dos membros de dimensão associados. Esse modo de exibição serviu como "tabela" de origem para as partições de cubo do Analysis Services 2000. Com a introdução de tabelas particionadas, o número de modos de exibição diminui de um por semana por tabela de fatos (229 modos de exibição) para um por tabela de fatos (3 modos de exibição). As partições do Analysis Services 2005 especificam uma consulta em relação ao modo de exibição relevante com uma instrução WHERE para limitar os dados a uma única partição, como:

SELECT [SK_Store_ID], [SK_Parent_Store_ID], ...
FROM [dbo].[vTbl_Fact_Store_Inventory] 
WHERE [SK_Date_ID] BETWEEN 20041212 AND 20041218

Há dois métodos para automatizar a criação e o processamento de cubos no Analysis Services 2005. Detalhes sobre ambos, incluindo os prós e os contras, estão documentados nas próximas seções.

Visão geral do XML/A

O primeiro método é o XMLA ou, mais especificamente, ASSL. O XMLA é uma especificação XML para consulta de dados OLAP que foi lançada em abril de 2001 (há mais informações sobre a especificação XMLA no site www.xmla.org). O ASSL é específico do Analysis Services e é uma especificação XML de DDL para OLAP. O XMLA é um protocolo nativo de troca de dados do Analysis Services 2005. Toda a comunicação com o Analysis Services é realizada, conseqüentemente, por meio do XMLA. Isso o torna o meio mais rápido de comunicação com o Analysis Services 2005, pois não há necessidade de conversão. Provavelmente, isso não é um fator significativo na maioria das operações de metadados, pois não haverá um número significativo de consultas enviadas ao Analysis Services e o custo dessa conversão será pequeno.

Os scripts XMLA podem parecer um pouco complexos no início. Como em qualquer outra linguagem de script XML, os elementos no XMLA são representados em uma estrutura hierárquica e são autodescritivos. A linguagem é detalhada e não algo que poderia ser facilmente desenvolvido do zero. Felizmente, o Analysis Services 2005 nos dá um ótimo ponto de partida.

Implementação do XML/A

No Analysis Services 2005, há várias melhorias na capacidade de criar scripts para objetos, como cubos, dimensões e partições. Em comparação, não havia recursos de script no Analysis Services 2000. Essa capacidade de criar scripts torna o uso do XMLA uma alternativa razoável, pois não é necessário um conhecimento profundo da especificação XMLA. O XMLA pode ser executado no SQL Server Management Studio abrindo uma nova janela de consulta XMLA do Analysis Services. Ele também pode ser executado no SSIS (SQL Server Integration Services) por meio da tarefa de execução de DDL do Analysis Services. Isso nos permite automatizar facilmente a criação e o processamento de partições por meio do XMLA.

A primeira etapa é produzir um script XMLA para cada um de nossos grupos de medidas de cubo. Para isso, basta usar o SQL Server Management Studio para navegar até uma partição em um grupo de medidas, clicar com o botão direito do mouse e selecionar "Script Partition as". Em seguida, exibimos o XMLA para compreender o que precisa ser alterado em cada partição. Para isso, basta colar o script em uma janela de consulta XMLA do SQL Server Management Studio. Esse script será então colado em uma tarefa de script do SSIS e todo o texto variável de uma partição para outra será substituído pelas variáveis que são modificadas com base na partição. Essa seqüência de caracteres XML é salva como uma variável SSIS executada na tarefa de execução de DDL do Analysis Services. O script a seguir foi extraído da tarefa de script do SSI que cria o script de uma partição para o grupo de medidas Store Inventory no cubo REAL Warehouse. Foram criadas variáveis para o nome da partição, a identificação da partição e a consulta em relação à origem relacional.

sXMLA = sXMLA & "<Create 
https://schemas.microsoft.com/analysisservices/2003/engine"">"
sXMLA = sXMLA & "    <ParentObject>"
sXMLA = sXMLA & "        <DatabaseID>REAL Warehouse Partitioned</DatabaseID>"
sXMLA = sXMLA & "        <CubeID>REAL Warehouse</CubeID>"
sXMLA = sXMLA & "        <MeasureGroupID>Store Inventory</MeasureGroupID>"
sXMLA = sXMLA & "    </ParentObject>"
sXMLA = sXMLA & "    <ObjectDefinition>"
sXMLA = sXMLA & "        <Partition xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" 
xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">"
sXMLA = sXMLA & "            <ID> & sPartitionName & </ID>"
sXMLA = sXMLA & "            <Name> & sPartitionName & </Name>"
sXMLA = sXMLA & "            <Annotations>"
sXMLA = sXMLA & "                <Annotation>"
sXMLA = sXMLA & "                    <Name>AggregationPercent</Name>"
sXMLA = sXMLA & "                    <Value>13</Value>"
sXMLA = sXMLA & "                </Annotation>"
sXMLA = sXMLA & "            </Annotations>"
sXMLA = sXMLA & "            <Source xsi:type=""QueryBinding"">"
sXMLA = sXMLA & "                <DataSourceID>REAL Warehouse</DataSourceID>"
sXMLA = sXMLA & "                <QueryDefinition> & sQuery & "</QueryDefinition>"
sXMLA = sXMLA & "            </Source>"
sXMLA = sXMLA & "            <StorageMode>Molap</StorageMode>"
sXMLA = sXMLA & "            <ProcessingMode>Regular</ProcessingMode>"
sXMLA = sXMLA & "            <ProactiveCaching>"
sXMLA = sXMLA & "                <SilenceInterval>PT10M</SilenceInterval>"
sXMLA = sXMLA & "                <Latency>-PT1S</Latency>"
sXMLA = sXMLA & "                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>"
sXMLA = sXMLA & "                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>"
sXMLA = sXMLA & "                <Source xsi:type=""ProactiveCachingInheritedBinding"" />"
sXMLA = sXMLA & "            </ProactiveCaching>"
sXMLA = sXMLA & "            <EstimatedRows>2000000</EstimatedRows>"
sXMLA = sXMLA & "            <AggregationDesignID>AggregationDesign 2</AggregationDesignID>"
sXMLA = sXMLA & "        </Partition>"
sXMLA = sXMLA & "    </ObjectDefinition>"
sXMLA = sXMLA & "</Create>"

O processamento do cubo pode ser colocado em um script no XMLA. Basta navegar até a partição do grupo de medidas no SQL Server Management Studio, clicar com o botão direito do mouse, selecionar Process e pressionar o botão Script na parte superior da caixa de diálogo resultante. Um exemplo de script XMLA para processar um cubo é parecido com este:

<Batch xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Object>
        <DatabaseID>REAL Warehouse Partitioned</DatabaseID>
        <CubeID>REAL Warehouse</CubeID>
        <MeasureGroupID>Store Inventory</MeasureGroupID>
        <PartitionID>Store Inventory WE 2004 12 11</PartitionID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

Visão geral do AMO

O AMO (Analysis Management Objects) é um conjunto completo de objetos .NET para o gerenciamento do Analysis Services. O AMO substitui o modelo de objeto DSO usado no Analysis Services 2000 e no OLAP Services do SQL Server 7.0. Assim como todas as ações do Analysis Services eram implementadas por meio do DSO na versão 2000, todos os mecanismos de administração das instâncias e dos bancos de dados do Analysis Services são implementados usando o AMO no Analysis Services 2005. O AMO é uma camada sobre o XMLA e, conseqüentemente, gera XMLA para executar toda a comunicação com uma instância do Analysis Services. Isso deixa o seu desempenho um pouco inferior ao do XMLA. No entanto, se não houver várias chamadas de método do AMO, essa diferença no desempenho provavelmente nem será notada.

Outras melhorias no SQL Server 2005 tornam o trabalho com o AMO muito mais fácil do que usar seu predecessor, o DSO. Como as tarefas de automação eram freqüentemente implementadas no DTS, nós éramos obrigados a trabalhar no ambiente de desenvolvimento do VBScript. Esse ambiente não tinha o avançado conjunto de funções de codificação (Intellisense, codificação por cores) encontrado na tarefa Script do SSIS, que usa uma interface do VSA (Visual Studio for Applications). Essa funcionalidade contava em grande parte com o desenvolvimento do código do AMO no Project REAL, pois a documentação do AMO em Books Online ainda era escassa.

Talvez o aspecto mais confuso do DSO tenha sido a implementação da interface MDStores para navegação no objeto de partição. Isso não existe mais no AMO e a navegação pelo banco de dados é muito mais intuitiva. Isso foi demonstrado em um exemplo de código a seguir.

Algo a observar é que a implementação atual do Analysis Services 2000 na Barnes and Noble não cria partições de cubo por meio do DSO e do DTS. As partições são criadas manualmente, um ano por vez, e o processamento é implementado por meio do utilitário Parallel Process, pois o Analysis Services 2000 não consegue processar partições em paralelo de forma nativa. Os aprimoramentos no Analysis Services 2005 nos permitem automatizar facilmente esses processos e reduzir a carga de trabalho administrativa.

Implementação do AMO

Nosso objetivo era obter com o AMO a mesma funcionalidade que obtivemos, anteriormente, com o XMLA. Na verdade, podíamos melhorar a funcionalidade com base nos mecanismos de loop inerentes ao modelo de objeto do AMO. Fizemos o loop por cada grupo de medidas e verificamos se os grupos de medidas faziam referência à tabela de fatos específica. Se fizessem, usávamos a data de processamento especificada para determinar se a partição associada já existia. Se a partição não existisse, seria necessário criá-la.

Com a implementação do XMLA, a partição era criada com a execução de um script gerado anteriormente e com a alteração dos elementos no XML que designavam o nome da partição, a identificação da partição e a definição da consulta. O AMO oferece a capacidade de copiar uma partição existente no grupo de medidas com o método de Clone. Em vez de criar a partição configurando explicitamente as suas propriedades no código, usamos o método Clone para copiar todo o conteúdo da última partição na coleção Partitions e alterar as mesmas propriedades que foram modificadas em nosso script XMLA.

O ambiente VSA implementado com a tarefa de script também nos permitiu o acesso a recursos mais avançados de tratamento de erros. O código VBScript no DTS exigia que cada ação fosse seguida por uma verificação de erro, pois não havia como tratar globalmente dos erros. Isso resultava em um código mais complicado e menos legível. Usando o Visual Basic .NET em uma tarefa de script do SSIS (SQL Server Integration Services), pudemos usar a instrução Try..Catch para tratar dos erros de forma consistente em um único conjunto de instruções. Também usamos a capacidade de declarar e inicializar variáveis para manter a legibilidade do código.

Outra técnica que foi usada foi a de reunir as informações de conexão de um gerenciador de conexões do SSIS que já existia. Isso significa que precisamos apenas modificar as informações do servidor e do banco de dados em um lugar ao implantar esse pacote nos ambientes de controle de qualidade e de produção. Isso também poderia ser implementado com configurações do SSIS.

A seguir está a principal parte do código usado para executar a funcionalidade de criação de partição.

Try

   Dim oDB As Database = oServer.Databases(sDatabase)
   Dim oCube As Cube = oDB.Cubes("REAL Warehouse")

   Dim dLogicalDate As Date = CDate(Dts.Variables("vdtLogical_Date").Value)
   Dim sTableName As String = CStr(Dts.Variables("vsPartitioned_Table_Name").Value)
   Dim sWeekEnd As String = GetIntegerDateFormat(dLogicalDate)

   ' Find all measure groups that reference the table being processed
   For Each oMeasureGroup In oCube.MeasureGroups
  oPartition = oMeasureGroup.Partitions(0)
      oQueryBinding = oPartition.Source
      If oQueryBinding.QueryDefinition Like "*" & sTableName & "*" Then
         ' Get the relevant boundary partition name and check to see if it 
         ' already exists
         sPartitionNew = GetNewPartitionName(sWeekEnd, oPartition.Name)
         oPartition = oMeasureGroup.Partitions.FindByName(sPartitionNew)
         If oPartition Is Nothing Then
            ' Get the last partition
            oPartition = oMeasureGroup.Partitions(oMeasureGroup.Partitions.Count - 1)
            ' Clone the properties from the last partition to the new partition.
            oPartitionNew = oPartition.Clone
            oPartitionNew.ID = sPartitionNew
            oPartitionNew.Name = sPartitionNew
            oQueryBinding = oPartitionNew.Source
            oQueryBinding.QueryDefinition = GetNewQuery(oPartition.Source, sWeekEnd)
            oMeasureGroup.Partitions.Add(oPartitionNew)
            oPartitionNew.Update()
         End If
      End If
   Next
   Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
   Dts.Events.FireError(0, "Create Partition", ex.Message, "", 0)
   Dts.TaskResult = Dts.Results.Failure
End Try
If oServer.Connected Then
   oServer.Disconnect()
End If

Há um problema no VSA que exige que todos os assemblies estejam localizados no subdiretório da versão apropriada do caminho <caminho do windows>\ Microsoft.NET\Framework. Os assemblies do AMO e do SMO devem ser copiados manualmente do diretório <SQL Server>\90\SDK\Assemblies para o diretório mencionado anteriormente. Com a instalação das compilações subseqüentes do SQL Server, um novo diretório da versão do VSA será provavelmente criado, e é possível que ocorram alterações nos assemblies que exijam que esses arquivos sejam novamente copiados. Na codificação do AMO mencionada neste documento, somente o assembly do AMO foi necessário (Microsoft.AnalysisServices.DLL).

Depois de verificar se os arquivos apropriados foram copiados em um local no qual o VSA os reconhecerá, adicione uma referência a eles na tarefa de script. Para isso, basta clicar com o botão direito do mouse em "References" no Project Explorer e selecionar "Add Reference...". Localize a referência "Analysis Management Objects" e adicione-a. Em seguida, adicione uma linha no início do script que "Imports Microsoft.AnalysisServices", como mostra a figura a seguir.

Figura 15 Adicionando referências da tarefa de script

Observações e recomendações

Tanto o script XMLA quanto as opções de AMO podem ser facilmente implementados, especialmente ao utilizar o SSIS. Algumas observações sobre cada método são mencionadas a seguir. Implementações específicas podem tornar uma opção mais desejável do que outra. De modo geral, achamos que o AMO ofereceu alguns benefícios em relação ao XMLA, como documentado a seguir.

Observações sobre o AMO

Prós:

• Mais elegante e direto - isso é subjetivo, mas provavelmente verdadeiro para a maioria das pessoas.

• Pode ser usado para incluir objetos futuros - ao analisar o código do AMO, você notará que a primeira partição de cada grupo de medidas é verificada a fim de se obter uma referência de consulta para a tabela de fatos transferida. Com cubos bem projetados, é improvável que cubos adicionais sejam incluídos para fazer referência à mesma tabela de fatos, mas há como lidar com essa situação. O AMO também resolve a possibilidade de renomeação de um grupo de medidas. Os nomes dos grupos de medidas são embutidos no código da implementação do XMLA.

• Incorpora dinamicamente as alterações de propriedade da partição - como a última partição é copiada, qualquer alteração no design de agregação, no modo de armazenamento, no cache proativo, etc. é copiada na nova partição. Na maioria dos casos, isso é desejável.

• Todo o código está em uma tarefa de script fácil de ler - a implementação de XMLA exige uma tarefa de script e um tarefa separada de execução de DDL do Analysis Services.

• Pode ser combinado com trabalho adicional, como processamento, em uma única tarefa - independentemente de a implementação do processamento ser feita através da tarefa de execução de DDL ou da tarefa de processamento do Analysis Services, seria necessária uma tarefa separada. Em comparação, o processamento pode ser executado com a inclusão de uma única linha adicional no script do AMO.

Contras:

• É preciso copiar manualmente os assemblies no Microsoft .NET a cada nova compilação do SQL Server

Observações sobre o XMLA

Prós:

1. Tudo é exposto - todas as propriedades relevantes do objeto de base são expostas, o que facilita determinar o que deve ser alterado ou não.

2. Nível inferior - como o XMLA é o protocolo de comunicação nativo do Analysis Services, ele é o mais rápido.

Contras:

• É preciso criar scripts manualmente - na verdade, esse não é um requisito rigoroso. Seria possível desenvolver um script generalizado que usaria variáveis para substituir todas as propriedades que pudessem mudar de um grupo de medidas para outro. Essas propriedades teriam que ser então embutidas no código de cada grupo de medidas ou extraídas de cada grupo de medidas usando o AMO. De qualquer forma, grupos de medidas adicionais teriam que ser adicionados manualmente ao script.

• O AMO ainda precisa verificar a existência da partição - em nosso caso, queríamos que o processo de ETL fosse reinicializável; portanto, verificamos a preexistência da partição desejada antes de criá-la. Podemos fazer isso utilizando o AMO. Esse código é, naturalmente, incorporado ao código de criação de partição no cenário de implementação do AMO.

• Alterar qualquer item não incluído originalmente no script exigirá a criação de um novo script ou a alteração manual do script - esse é, provavelmente, o maior problema ao usar o script XMLA para automatizar a criação de partição. Em alguns casos raros, talvez seja desejável sempre manter as propriedades originais do script. Esse caso poderia ser facilmente implementado com o AMO ou o XMLA.

Observações gerais

Ambos os métodos se beneficiam das várias melhorias do SSIS, ou seja:

• A criação de partição é facilmente implementada no SSIS - embora o DTS fornecesse a tarefa VBSCript para codificação do DSO, o ambiente de desenvolvimento incluía poucos dos benefícios disponíveis no ambiente do VSA usado pela tarefa de script do SSIS. A tarefa de execução de DDL do Analysis Services oferece uma maneira bastante fácil de executar o XMLA.

• Melhores recursos de tratamento de erros e de depuração resultam em um código mais fácil de ler e em desenvolvimento mais rápido do código.

• Ao usar AMO, você deve usar uma solução alternativa para que o VSA possa fazer referência ao assembly do AMO (consulte o último item das observações sobre o AMO).

Alterações de ETL

Agora que todos os componentes foram definidos para implementar a manutenção de partição incremental, precisamos integrar isso novamente ao processo de ETL existente. Lembre-se de que a implementação existente usa uma tabela explícita do SQL Server 2000 para cada partição. Como não há dados históricos suficientes para exigir a implementação do arquivamento da partição, esse processo não existe no ETL atual. Do mesmo modo, não há processo para implementar o recurso de envelhecimento dos dados.

Esta é uma visão geral do processo existente de ETL para as tabelas de fatos de inventário de lojas e de centros de distribuição da Barnes and Noble, pois ela se aplica ao gerenciamento das partições relacionais.

• Faça a pré-execução do processamento.

• Copie o conteúdo da tabela atual para uma nova tabela “nomeada” de forma a refletir a tabela anterior (por exemplo: Tbl_Fact_Store_Inventory_WE_2004_12_11).

• Crie índices na tabela "nomeada" (pois uma instrução SELECT INTO foi usada).

• Reinicialize a tabela atual para refletir a semana vindoura (SK_Date_ID, Days_In_Stock, ETL_Load_ID).

• Faça pós-execução do processamento.

Figura 16 Criação de partição da Barnes and Noble - antes

Não há processo equivalente para partições de fatos de vendas, pois elas são criadas em massa antecipadamente. Isso foi feito para eliminar a necessidade de criar um processo para fazer isso de forma incremental. A manutenção da partição de fatos de vendas está incluída no processamento incremental do Project REAL e, portanto, elimina o processo manual. A manutenção de partição incremental foi modificada de acordo com a seguinte visão geral:

1. Faça a pré-execução do processamento - essa etapa não mudou.

2. Copie os dados da última partição para uma nova tabela externa no próximo grupo de arquivos (podemos reinicializar colunas, como SK_Date_ID, durante esta etapa).

3. Crie índices na nova tabela externa (pois uma instrução SELECT INTO foi usada).

4. Divida a última partição e transfira para a nova tabela externa.

5. Remova a partição antiga e mova a partição de arquivamento para o disco de baixo custo - NOVA FUNCIONALIDADE.

6. Faça a pós-execução do processamento.

A manutenção das partições de cubo do Analysis Services 2000 na Barnes and Noble é executada atualmente fora do processo de ETL. As partições de cubo de vendas e de inventário são criadas levando em conta um ano no futuro e não são processadas até que os dados relativos à semana atual sejam inseridos nelas. Isso não é mais necessário, pois é fácil incorporar a manutenção de partição de cubo ao processo de ETL.

Requisitos adicionais

Incluímos dois requisitos adicionais na parte de gerenciamento de ciclo de vida dos dados de manutenção da partição.

1. Capacidade de reinicialização

   Queríamos ter certeza de que esse processo é reinicializável, do ponto de vista da manutenção da partição. Isso significa que qualquer código que tenha a função de criar uma nova partição, remover uma antiga ou determinar o envelhecimento de uma partição deve executar uma verificação para ver se isso já ocorreu.

2. Separação do dia da semana (para a execução do pacote do SSIS) e invocação do processo de gerenciamento da partição

O pacote do SSIS de manutenção da partição pode ser chamado em qualquer dia da semana. Os procedimentos armazenados verificarão se é hora de executar qualquer função de manutenção da partição consultando os metadados. Portanto, se a data de processamento for uma segunda-feira, uma verificação será realizada para ver se a partição relevante existe. Essas consultas são executadas de forma muito rápida e não acrescentam sobrecarga significativa ao processo diário. Isso remove a necessidade de ter processos separados de ETL diários e semanais.

Unindo componentes de particionamento da tabela

Em relação à automação dessa funcionalidade, tentava-se consolidar as atividades de movimentação de dados com a implementação de "janela deslizante". Isso rapidamente provou ser bastante complicado, especialmente do ponto de vista da manutenção futura. Esse método exige a criação do novo esquema de partição para incluir a nova partição e remover a antiga. A adição ou exclusão das partições é feita no nível de função da partição, que é compartilhado por todos os esquemas de partição. Isso complicou rapidamente o código, à medida que tentávamos determinar se era uma partição que existia no esquema antigo mas não no novo e vice-versa. Conseqüentemente, separar as duas tornou o código muito mais simples e ofereceu a oportunidade de separar fisicamente os processos caso uma empresa precisasse disso. Na verdade, a implementação de janela deslizante também estava separada em dois procedimentos armazenados. Isso facilita a alteração potencial futura em relação às necessidades da empresa. Se, por exemplo, ela decidir posteriormente que novas partições serão adicionadas semanalmente, mas as antigas serão removidas um ano por vez, essa alteração poderá ser implementa de forma mais fácil. Os procedimentos armazenados a seguir foram criados para encapsular a lógica de manutenção da partição incremental:

• up_CreateNewPartition – com base na data lógica (a data à qual os dados se aplicam), verifique se existe uma partição para esses dados e, se não existir, crie-a. Se for uma partição de inventário, inicialize a partição com dados da partição precedente, alterando as colunas relevantes, como SK_Date_ID. Se for uma partição de vendas, não serão necessárias outras etapas.

• up_RemoveOldPartitions – com base na data lógica, verifique se alguma partição deve ser arquivada. No Project REAL, nós simplesmente excluímos as partições antigas, mas talvez haja uma estratégia de arquivamento em fita a ser implementada.

• up_MoveAgedPartitions – com base na data lógica, verifique se alguma partição deve ser movida para um disco de baixo custo.

Esses três procedimentos armazenados são chamados por um procedimento pai armazenado: up_MaintainPartitionedTable. Todo o processamento da manutenção da partição é encapsulado em um pacote de SSIS separado que é chamado durante o processo geral de ETL.

Figura 17 Criação de partição da Barnes and Noble - depois

Referências

• Strategies for Partitioning Relational Data Warehouses in Microsoft SQL Server (em inglês) -
  https://www.microsoft.com/technet/prodtechnol/sql/2005/plan/spdw.mspx

• SQL Server 2005 Partitioned Tables and Indexes (em inglês) de Kimberly L. Tripp -
  http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm.

• TechNet Webcast: SQL Server 2005 Series (Parte 6 de 10): Managing Large Databases using Partitioning (em inglês) -
  http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?eventID=1032270016&Culture=en-US

Conclusão

A Barnes and Noble pode usufruir de vários benefícios ao usar as tabelas particionadas no SQL Server 2005. Os modos de exibição particionados não foram implementados na Barnes and Noble devido ao longo tempo de compilação resultante, às vezes superior a 30 segundos. Os dados eram particionados em tabelas separadas, mas essas tabelas eram gerenciadas separadamente. O processo de ETL exigia a codificação para determinar a tabela apropriada a ser atualizada durante o carregamento dos dados. As tabelas particionadas eliminam essa exigência e resultam em uma carga administrativa muito menor.

Para obter mais informações:

https://www.microsoft.com/sql/

Este documento foi útil para você? Envie-nos seus comentários. Em uma escala de 1 (fraco) a 5 (excelente), como você classificaria este documento?

Download

Project REAL Data Lifecycle Partitioning.doc
260 KB
Arquivo do Microsoft Word
Atualizado em: 31-08-2005