Partições em modelos tabulares

Artigo
05/08/2023

Aplica-se a: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

As partições dividem porções de dados que você deseja processar (atualizar) com frequência dos dados que podem ser processados com menos frequência. Por exemplo, uma tabela de fatos pode incluir determinados conjuntos de linhas que contêm dados que raramente são alterados, mas outros conjuntos de linhas têm dados que são alterados com frequência. Não é necessário processar todos os dados quando apenas uma parte dele precisa ser processada.

As partições funcionam dividindo uma tabela em objetos de partição lógica. As partições individuais, cada uma contendo um segmento exclusivo de dados, podem ser processadas de forma incremental, sequencialmente ou em paralelo, independentemente de outras partições, ou excluídas das operações de processamento.

Granularidade

Por padrão, cada tabela em um modelo tem uma única partição. Em muitos casos, como com tabelas de fatos, dividir a partição única de uma tabela em várias partições pode utilizar melhor os recursos disponíveis para processamento.

Uma estratégia eficaz de design e processamento de modelo utiliza partições para eliminar a carga desnecessária do processador e o consumo de memória, ao mesmo tempo em que garante que os dados sejam atualizados com frequência suficiente para refletir os dados mais recentes das fontes de dados. Por exemplo, um modelo tabular pode ter uma tabela Sales que inclui dados de vendas para o ano fiscal atual e cada um dos anos fiscais anteriores. A tabela Sales do modelo tem as seguintes partições:

Partition	Dados de
Vendas2020	Ano fiscal atual
Vendas2019-2010	Anos fiscais 2010, 2011, 2012, 2013, 2014, 2015. 2016, 2017, 2018, 2019
SalesOld	Todos os anos fiscais antes dos últimos dez anos.

À medida que novos dados de vendas são adicionados para o ano fiscal atual de 2020, esses dados devem ser processados diariamente para serem refletidos com precisão na análise de dados de vendas do ano fiscal atual, portanto, a partição Sales2020 é processada todas as noites.

Não é necessário processar dados na partição Sales2019-2010 todos os noites. No entanto, como os dados de vendas dos dez anos fiscais anteriores ainda podem ser alterados devido a retornos de produtos e outros ajustes, eles ainda devem ser processados regularmente, portanto, os dados na partição Sales2019-2010 são processados mensalmente. Os dados na partição SalesOld raramente são alterados, portanto, processados apenas anualmente.

Ao inserir o ano fiscal de 2021, uma nova partição Sales2021 é adicionada à tabela Vendas do modelo. A partição Sales2020 pode ser mesclada com a partição Sales2019-2010 e renomeada para Sales2020-2011. Os dados do ano fiscal de 2010 são eliminados da partição Sales2020-2011 e movidos para a partição SalesOld. Todas as partições são então processadas para refletir as alterações. Isso é comumente conhecido como um padrão de janela sem interrupção – os dados em cada partição estão dentro de um intervalo de datas predefinido e incrementados conforme necessário, mantendo o uso de recursos de memória e processamento dentro de um intervalo previsível ao longo do tempo.

A granularidade é influenciada por vários fatores, incluindo a quantidade de dados necessários para serem processados incrementalmente dentro de um período aceitável de tempo. Por exemplo, se apenas o último dia inteiro precisar ser processado diariamente, talvez seja benéfico usar a granularidade diária. A granularidade mista pode ser configurada para cenários como atualização quase em tempo real em baixa granularidade, juntamente com partições históricas e estáticas em maior granularidade. Isso resulta em menos partições, mas também aumenta a sobrecarga de gerenciamento para garantir que os intervalos de partição sejam definidos corretamente.

O particionamento também é eficaz para tabelas que contêm dados de mais de uma fonte de dados. Diferentes fontes de dados podem atualizar dados em momentos diferentes, o que pode determinar diferentes requisitos de granularidade e processamento para os dados de tabela do modelo. Por exemplo, uma tabela Orders em um modelo contém transações de ordem de duas tabelas de fatos diferentes, factInternetOrders e factRetailOrders. Na fonte de dados, factInternetOrders é atualizado por hora. factRetailOrders, por outro lado, é atualizado apenas uma vez por dia depois que todas as lojas de varejo são fechadas. Ao criar partições separadas em granularidades diferentes na tabela Pedidos de modelo para dados importados de factInternetOrders e factRetailOrders, as operações de processamento na tabela Orders podem ser separadas e executadas mais embutidas com os dados de ordem nas fontes de dados.

Cada cenário é exclusivo. Certifique-se de definir uma granularidade para seu modelo de dados que divide com mais eficiência os dados em partições que devem ser processadas com frequência em comparação com aquelas que não o fazem.

Limites de partição

Independentemente da plataforma, não há nenhum limite rígido no número de objetos de partição em um modelo. No entanto, cada partição tem pelo menos um segmento de dados com um volume de memória. Muitas partições pequenas podem resultar em muitos segmentos pequenos. O desempenho da consulta pode ser afetado negativamente quando o mecanismo de armazenamento precisa verificar um número excessivo de segmentos. A velocidade das operações de metadados em muitas partições também pode afetar negativamente os recursos de processamento.

Crie o número mínimo de partições enquanto ainda cumpre efetivamente suas metas de particionamento. É mais importante concentrar uma estratégia de particionamento eficaz com base na granularidade e processar apenas essas partições com os dados de alteração mais relevantes nos recursos de processamento e memória disponíveis em momentos em que as consultas de usuário estão baixas.

Também não há limite para a quantidade de dados em uma partição. Embora seja improvável, um modelo pode ter uma única tabela com uma única partição padrão e essa tabela pode conter todos os dados no modelo. A quantidade de dados na partição seria limitada apenas pelos recursos de memória disponíveis para o plano de serviço ou hardware.

Criando e gerenciando partições

Ao criar modelos com o designer de modelo tabular no Visual Studio, você cria novas partições, edita, mescla ou exclui partições no banco de dados de workspace modelo usando o Gerenciador de Partições. Dependendo do nível de compatibilidade do modelo que você está criando, o Gerenciador de Partições fornece dois modos para selecionar dados a serem incluídos em uma partição: para modelos tabulares 1400 e superiores com fontes de dados estruturadas, as partições são definidas usando uma expressão Power Query M. Por exemplo, a consulta a seguir define uma partição para o ano civil de 2019:

let
    Source = #"SQL/sqlserver database windows net;Contoso",
    dbo_Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_Sales, each [OrderDateKey] >= 20190101 and [OrderDateKey] <= 20191231)
in
    #"Filtered Rows"

Para fontes de dados do provedor, as partições são definidas usando uma consulta SQL. Por exemplo,

SELECT [dbo].[Sales].* FROM [dbo].[Sales]
WHERE (([OrderDateKey] >= '20190101') AND ([OrderDateKey] <= '20191231'))

Observe que o argumento Linhas Filtradas na expressão Power Query M e a cláusula WHERE na instrução SQL definem exatamente um ano civil usando operadores maiores que (>), menores que (<) e iguais (=). Ao definir partições, é importante que a consulta de cada partição defina um intervalo exclusivo de dados que não possa causar duplicação de dados com outras partições.

SQL Server Management Studio (SSMS)

Depois de implantar o modelo, as partições aparecem como objetos no SSMS (SQL Server Management Studio). Crie, edite, mescle e exclua partições para um modelo implantado usando a caixa de diálogo Partições no SSMS executando um script TMSL (Tabular Model Scripting Language) ou programaticamente usando o TOM (Modelo de Objeto Tabular).

TMSL (Linguagem de Scripts do Modelo de Tabela)

As partições para um modelo são definidas no objeto Partitions. No exemplo a seguir, a partição Sales2019 é definida como:

"partition": {
      "name": "Sales2019",
      "mode": "import",
      "source": {
        "type": "m",
        "expression": [
          "let",
          "    Source = #\"SQL/sqlserver database windows net;Contoso\",",
          "    dbo_Sales = Source{[Schema=\"dbo\",Item=\"Sales\"]}[Data],",
          "    #\"Filtered Rows\" = Table.SelectRows(dbo_Sales, each [OrderDateKey] >= 20190101 and [OrderDateKey] <= 20191231)",
          "in",
          "    #\"Filtered Rows\""
        ]
      },

As ações no objeto Partitions podem ser especificadas nos seguintes comandos TMSL:

Os scripts TMSL podem ser executados em SQL Server Management Studio, com o PowerShell executando o comando Invoke-ASCmd ou por uma tarefa script SSIS (SQLServer Integration Services).

Para modelos em níveis de compatibilidade 1100 e 1103, o ASSL (Analysis Services Scripting Language) será usado se o TMSL.

TOM (Modelo de Objeto Tabular)

No Modelo de Objeto Tabular, as partições são definidas por uma classe de partição no namespace Microsoft.AnalysisServices.Tabular. Para saber mais sobre soluções programáticas usando o TOM como uma API, consulte Criar tabelas, partições e colunas (TOM) e Estratégias avançadas de particionamento posteriormente neste artigo.

Para modelos em níveis de compatibilidade 1100 e 1103, use AMO (Objetos de Gerenciamento de Análise).

Processando partições

Quando os dados da tabela são particionados, essas partições podem ser processadas por vez e a cadência apropriada para sua solução. Quando uma operação de processo (atualização) é executada, uma conexão com a fonte de dados é feita usando a conexão de fonte de dados. O Analysis Services usa as consultas especificadas para cada partição para consultar a fonte de dados. Dados novos e atualizados são carregados nas tabelas de modelo, relações e hierarquias são recriadas e colunas calculadas são recompiladas.

Ao criar modelos no Visual Studio, você pode executar manualmente operações de processo em partições de banco de dados do workspace no menu ou na barra de ferramentas. Para modelos implantados, as operações de processamento são invocadas manualmente usando a caixa de diálogo Tabelas de Processo no SSMS, executando um script que inclui o comando Refresh (TMSL) ou programaticamente usando o TOM (Modelo de Objeto Tabular).

Processamento paralelo

O Analysis Services utiliza o processamento paralelo para duas ou mais partições, aumentando o desempenho do processamento. Não há nenhuma configuração para o processamento paralelo. O processamento paralelo ocorre por padrão quando você processa Tabela ou seleciona várias partições para a mesma tabela e Process. No entanto, há configurações que limitam operações de processamento paralelo.

MaxConnections

Por padrão, cada operação de processamento se conectará e consultará uma fonte de dados para cada partição. O número máximo padrão de conexões, especificado como a propriedade MaxConnections para uma única fonte de dados, é 10. O Analysis Services determina o número de operações de processamento simultâneas a serem executadas com base no número de núcleos e threads disponíveis. Esses threads são compartilhados entre a instância do servidor. Um único comando como processo pode não receber todos os threads disponíveis. Os threads que iniciam para processamento, um para cada operação de processamento paralelo, podem ser atrasados para permanecer dentro do limite MaxConnections.

MaxParallelism

Por padrão, as operações de processamento são executadas em paralelo o máximo possível. No entanto, você pode optar por processar partições sequencialmente ou em paralelo especificando a opção de propriedade maxParallism com o comando Sequence (TMSL). Definir o valor como 1 significa não paralelo – um thread é usado para processamento. Definir o valor como 2 ou mais especifica que um número fixo de threads pode ser usado para operações de processamento paralelo.

Monitor

Para determinar o uso efetivo de threads disponíveis durante operações de processo, para Azure Analysis Services, use o Explorer de Métricas do Azure para monitorar CommandPoolIdleThreads e CommandPoolBusyThreads. Para obter mais informações, consulte Monitorar métricas do servidor. Para SQL Server Analysis Services, use Monitor de Desempenho para monitorar threads de E/S não ociosos do pool de processamento e threads que não são de E/S ocupados no pool de processamento. Para saber mais, confira Contadores de desempenho (SSAS).

Observação

Se a recodificação for detectada, o processamento paralelo poderá causar maior uso de recursos. Isso ocorre porque várias operações de partição precisam ser interrompidas e reiniciadas com a nova codificação em paralelo.

Estratégias avançadas de particionamento

O artigo Gerenciamento automatizado de partições para modelos tabulares do Analysis Services .pdf artigo, juntamente com o exemplo de código AsPartitionProcessing que acompanha no GitHub, fornece informações detalhadas e um exemplo de solução para a empresa fictícia, a Advenure Works, usando o TOM (Modelo de Objeto Tabular) para criar e gerenciar partições. Os conceitos descritos neste artigo e no projeto se aplicam a todas as plataformas do Analysis Services.

Confira também

Criar e gerenciar partições de modelos tabulares
Objeto Partitions (TMSL)
Criar tabelas, partições e colunas com o TOM (Modelo de Objeto Tabular)
Criar partições (lição de tutorial)