Atualização incremental e dados em tempo real avançados com o ponto de extremidade XMLA
Modelos semânticos em uma capacidade Premium com o ponto de extremidade XMLA habilitado para operações de leitura/gravação permitem implantações de atualização, gerenciamento de partição e metadados mais avançados apenas por meio de ferramenta, script e suporte à API. Além disso, as operações de atualização por meio do ponto de extremidade XMLA não são limitadas a 48 atualizações por dia, e o tempo limite de atualização agendada não é imposto.
Partições
As partições de tabela de modelo semântico não são visíveis e não podem ser gerenciadas usando o Power BI Desktop ou o serviço do Power BI. Para modelos em um workspace atribuído a uma capacidade Premium, as partições podem ser gerenciadas por meio do ponto de extremidade XMLA usando ferramentas como o SSMS (SQL Server Management Studio), o Editor de Tabela de software livre, com script com TMSL (Tabular Model Scripting Language) e programaticamente com o TOM (Modelo de Objeto Tabular).
Quando você publica um modelo pela primeira vez no serviço do Power BI, cada tabela no novo modelo tem uma partição. Para tabelas sem nenhuma política de atualização incremental, essa partição contém todas as linhas de dados dessa tabela, a menos que filtros tenham sido aplicados. Para tabelas com uma política de atualização incremental, essa partição inicial só existe porque o Power BI ainda não aplicou a política. Você configura a partição inicial no Power BI Desktop ao definir o filtro de intervalo de data/hora da sua tabela com base nos parâmetros RangeStart e RangeEnd e quaisquer outros filtros aplicados no Editor do Power Query. Essa partição inicial contém apenas as linhas de dados que atendam aos critérios de filtragem.
Quando você executa a primeira operação de atualização, tabelas sem nenhuma política de atualização incremental atualizam todas as linhas contidas na partição única padrão dessa tabela. Para tabelas com uma política de atualização incremental, as partições de atualização e histórica são criadas automaticamente e as linhas são carregadas nelas de acordo com a data/hora de cada linha. Se a política de atualização incremental incluir a obtenção de dados em tempo real, o Power BI também adicionará uma partição do DirectQuery à tabela.
Essa primeira operação de atualização pode levar algum tempo, dependendo da quantidade de dados que precisam ser carregados da fonte de dados. A complexidade do modelo também pode ser um fator significativo, pois as operações de atualização devem executar mais processamentos e recálculos. Essa operação pode ser inicializada. Para obter mais informações, confira Impedir tempos limite na atualização completa inicial.
As partições são criadas e nomeadas por período de granularidade: anos, trimestres, meses e dias. As partições mais recentes, as partições de atualização, contêm linhas no período de atualização especificado na política. As partições históricas contêm linhas por período completo até o período de atualização. Se o tempo real estiver habilitado, uma partição do DirectQuery selecionará as alterações nos dados após a data de término do período de atualização. A granularidade de partições de atualização e histórica depende dos períodos de atualização e histórico (armazenamento) que você escolhe ao definir a política.
Por exemplo, se a data de hoje for 2 de fevereiro de 2021 e nossa tabela FactInternetSales na fonte de dados contiver linhas até hoje, se nossa política especificar a inclusão de alterações em tempo real, atualizar linhas no último período de atualização de um dia e armazenar linhas no período histórico dos últimos três anos. Em seguida, com a primeira operação de atualização, uma partição DirectQuery é criada para alterações no futuro, uma nova partição de importação é criada para as linhas de hoje, uma partição histórica é criada para ontem, um período de dia inteiro, 1º de fevereiro de 2021. Uma partição histórica é criada para o período de mês anterior inteiro (janeiro de 2021), uma partição histórica é criada para o período de ano anterior inteiro (2020) e partições históricas para períodos de ano inteiro de 2019 e 2018 são criadas. Nenhuma partição de trimestre inteiro é criada porque ainda não concluímos o primeiro trimestre de 2021.
Em cada operação de atualização, somente as partições do período de atualização são atualizadas, e o filtro de data da partição do DirectQuery é atualizado para incluir apenas as alterações que ocorrem após o período de atualização atual. Uma nova partição de atualização é criada para novas linhas com uma nova data/hora no período de atualização, e as linhas existentes com uma data/hora já dentro das partições existentes no período de atualização são renovadas com atualizações. As linhas com uma data/hora mais antiga do que o período de atualização não são mais atualizadas.
À medida que os períodos inteiros são fechados, as partições são mescladas. Por exemplo, se um período de atualização de um dia e o período de armazenamento histórico de três anos forem especificados na política, no primeiro dia do mês, todas as partições de dia do mês anterior serão mescladas em uma partição de mês. No primeiro dia de um novo trimestre, todas as três partições de mês anteriores são mescladas em uma partição de trimestre. No primeiro dia de um novo ano, todas as quatro partições do trimestre anterior são mescladas em uma partição de ano.
Um modelo sempre retém partições para todo o período de repositório histórico mais partições de período inteiro até o período de atualização atual. No exemplo, três anos de dados históricos no total são mantidos em partições para 2018, 2019, 2020 e também em partições para o período de mês 2021Q101, o período de dia 2021Q10201 e a partição do período de atualização do dia atual. Como o exemplo mantém os dados históricos por três anos, a partição 2018 é mantida até a primeira atualização em 1º de janeiro de 2022.
Com a atualização incremental do Power BI e dados em tempo real, o serviço lida com o gerenciamento de partição para você com base na política. Embora o serviço possa lidar com todo o gerenciamento de partições para você, ao usar ferramentas por meio do ponto de extremidade XMLA, você pode atualizar de modo seletivo as partições individualmente, sequencialmente ou em paralelo.
Gerenciamento de atualizações com o SQL Server Management Studio
O SSMS (SQL Server Management Studio) pode ser usado para exibir e gerenciar partições criadas pelo aplicativo de políticas de atualização incremental. Usando o SSMS você pode, por exemplo, atualizar uma partição histórica específica que não esteja no período de atualização incremental, de modo a executar uma atualização retroativa sem precisar atualizar todos os dados. O SSMS também pode ser usado ao inicializar para carregar dados históricos para modelos grandes adicionando/atualizando incrementalmente partições históricas em lotes.
Substituir comportamento de atualização incremental
Com o SSMS, você também tem mais controle sobre como invocar atualizações usando a Linguagem de Scripts do Modelo Tabular e o Modelo de Objeto Tabular. Por exemplo, no SSMS, no Pesquisador de Objetos, clique com o botão direito do mouse em uma tabela e selecione a opção de menu Processar Tabela e selecione o botão Script para gerar um comando de atualização TMSL.
Esses parâmetros podem ser usados com o comando de atualização de TMSL para substituir o comportamento de atualização incremental padrão:
applyRefreshPolicy. Se a tabela tiver uma política de atualização incremental definida,
applyRefreshPolicydeterminará se a política será aplicada ou não. Se a política não for aplicada, uma operação de processo completo deixará as definições de partição inalteradas e todas as partições na tabela serão completamente atualizadas. O valor padrão é true.effectiveDate. Se uma política de atualização incremental estiver sendo aplicada, ela precisará saber a data atual a fim de determinar os intervalos de janela sem interrupção para a atualização incremental e períodos históricos. O parâmetro
effectiveDatepermite que você substitua a data atual. Esse parâmetro é útil para testes, demonstrações e cenários de negócios em que os dados são atualizados incrementalmente em datas passados ou futuras, por exemplo, orçamentos futuros. O valor padrão é a data atual.
{
"refresh": {
"type": "full",
"applyRefreshPolicy": true,
"effectiveDate": "12/31/2013",
"objects": [
{
"database": "IR_AdventureWorks",
"table": "FactInternetSales"
}
]
}
}
Para saber mais sobre como substituir o comportamento de atualização incremental padrão por TMSL, confira Comando de atualização.
Garantir o desempenho ideal
Com cada operação de atualização, o serviço do Power BI pode enviar consultas de inicialização para a fonte de dados de cada partição de atualização incremental. É possível melhorar o desempenho da atualização incremental reduzindo o número de consultas de inicialização ao garantir a seguinte configuração:
- A tabela para a qual você configura a atualização incremental deve obter os dados de apenas uma fonte. Se a tabela obtém dados de mais de uma fonte, o número de consultas enviadas pelo serviço para cada operação de atualização é multiplicado pelo número de fontes, o que reduz potencialmente o desempenho da atualização. Certifique-se de que a consulta para a tabela de atualização incremental usa apenas uma fonte de dados.
- Para soluções com atualização incremental de partições de importação e dados em tempo real com a Consulta Direta, todas as partições devem consultar os dados de uma única fonte de dados.
- Se os requisitos de segurança permitirem, defina a Configuração de nível de privacidade da fonte de dados como Organizacional ou Pública. Por padrão, o nível de privacidade é Privado, mas esse nível pode impedir que os dados sejam trocados com outras fontes de nuvem. Para definir o nível de privacidade, selecione o menu Mais opções e escolha Configurações>Credenciais da fonte de dados>Editar credenciais>Configuração de nível de privacidade para essa fonte de dados. Se o Nível de privacidade for definido no modelo do Power BI Desktop antes da publicação no serviço, ele não vai ser transferido para o serviço ao publicar. Você ainda deve defini-lo nas configurações de modelo semântico no serviço. Para saber mais, confira Níveis de privacidade.
- Se estiver usando um Gateway de Dados Local, certifique-se de usar a versão 3000.77.3 ou superior.
Evitar que os tempos limite sejam atingidos na atualização completa inicial
Depois de publicar no serviço do Power BI, a operação inicial de atualização completa do modelo cria partições para a tabela de atualização incremental, carrega e processa dados históricos durante todo o período definido na política de atualização incremental. Para alguns modelos que carregam e processam grandes quantidades de dados, o tempo que a operação de atualização inicial leva pode exceder o limite de tempo de atualização imposto pelo serviço ou um limite de tempo de consulta imposto pela fonte de dados.
A inicialização da operação de atualização inicial permite ao serviço criar objetos de partição para a tabela de atualização incremental, mas não carregar nem processar dados históricos em nenhuma das partições. Em seguida, usamos o SSMS para processar partições seletivamente. Dependendo da quantidade de dados que serão carregados para cada partição, é possível processar cada uma sequencialmente ou em pequenos lotes para reduzir o potencial de uma ou mais dessas partições atingir um tempo limite. Os métodos a seguir funcionam para qualquer fonte de dados.
Aplicar Política de Atualização
A ferramenta de código aberto Tabular Editor 2 fornece uma forma fácil de inicializar uma operação de atualização inicial. Depois de publicar um modelo com uma política de atualização incremental definida para ele do Power BI Desktop para o serviço, conecte-se ao modelo usando o ponto de extremidade XMLA no modo de leitura/gravação. Execute Aplicar Política de Atualização na tabela de atualização incremental. As partições são criadas com apenas a política aplicada, mas nenhum dado é carregado nelas. Em seguida, conecte-se com o SSMS para atualizar as partições sequencialmente ou em lotes a fim de carregar e processar os dados. Para obter mais informações, confira Atualização incremental na documentação do Editor tabular.
Filtro de Power Query para partições vazias
Antes de publicar o modelo no serviço, no Editor do Power Query, adicione outro filtro à coluna ProductKey que filtra qualquer valor diferente de 0, efetivamente ou filtrando todos os dados da tabela FactInternetSales.
Após você selecionar Fechar e Aplicar no Editor do Power Query, definindo a política de atualização incremental e salvando o modelo, o modelo é publicado no serviço. No serviço, a operação de atualização inicial é executada no modelo. As partições para a tabela FactInternetSales são criadas de acordo com a política, mas nenhum dado é carregado e processado porque todos os dados são filtrados.
Depois que a operação de atualização inicial for concluída, de volta no Editor do Power Query, o outro filtro na coluna ProductKey será removido. Após você selecionar Fechar e Aplicar no Editor do Power Query e salvar o modelo, o modelo não será publicado novamente. Se o modelo for publicado novamente, ele substituirá as configurações de política de atualização incremental e forçará uma atualização completa no modelo quando uma operação de atualização subsequente for executada a partir do serviço. Em vez disso, execute uma implantação somente de metadados usando o Kit de Ferramentas do ALM (Gerenciamento de Ciclo de Vida do Aplicativo) que remove o filtro na coluna ProductKey do modelo. Em seguida, o SSMS pode ser usado para processar partições seletivamente. Quando todas as partições tiverem sido totalmente processadas, o que deve incluir um recálculo de processo em todas as partições, do SSMS, as operações de atualização subsequentes no modelo do serviço atualizam apenas as partições de atualização incremental.
Dica
Confira os vídeos, blogs e outros conteúdos fornecidos pela comunidade de especialistas de BI do Power BI.
Para saber mais sobre o processamento de tabelas e partições do SSMS, confira Processar banco de dados, tabela ou partições (Analysis Services). Para saber mais sobre modelos de processamento, tabelas e partições usando TMSL, consulte o comando Atualizar (TMSL).
Personalizar consultas para detectar alterações de dados
TMSL e TOM podem ser usados para substituir o comportamento de alterações de dados detectados. Esse método não apenas pode ser usado para evitar manter a coluna da última atualização no cache da memória, como também permite cenários em que uma tabela de configuração ou instrução é preparada por processos ETL (extrair, transformar, carregar) para sinalizar apenas as partições que precisam ser atualizadas. Esse método permite criar um processo de atualização incremental mais eficiente, em que apenas os períodos necessários são atualizados, independentemente do tempo durante o qual as atualizações de dados ocorreram.
O pollingExpression deve ser uma expressão M leve ou nome de outra consulta M. Ele deve retornar um valor escalar e ser executado em cada partição. Se o valor retornado for diferente do apresentado na última atualização incremental, a partição será sinalizada para processamento completo.
O exemplo a seguir abrange todos os 120 meses no período histórico para alterações retroativas. Especificar 120 meses em vez de 10 anos pode significar uma compactação de dados não eficiente, mas que evita a necessidade de atualizar o histórico de um ano inteiro, o que seria mais caro, quando fazer a alteração retroativa de um mês for o suficiente.
"refreshPolicy": {
"policyType": "basic",
"rollingWindowGranularity": "month",
"rollingWindowPeriods": 120,
"incrementalGranularity": "month",
"incrementalPeriods": 120,
"pollingExpression": "<M expression or name of custom polling query>",
"sourceExpression": [
"let ..."
]
}
Dica
Confira os vídeos, blogs e outros conteúdos fornecidos pela comunidade de especialistas de BI do Power BI.
Implantação somente de metadados
Ao publicar uma nova versão de um arquivo .pbix do Power BI Desktop em um workspace, se já existir um modelo com o mesmo nome, você será solicitado a substituir o modelo existente.
Em alguns casos, talvez você não queira substituir o modelo, especialmente pela atualização incremental. O modelo no Power BI Desktop pode ser muito menor do que o do serviço do Power BI. Se o modelo no serviço do Power BI tiver uma política de atualização incremental aplicada, ele poderá ter vários anos de dados históricos que serão perdidos se o modelo for substituído. Atualizar todos os dados históricos pode levar horas e resultar em tempo de inatividade do sistema para os usuários.
Em vez disso, é melhor executar uma implantação somente de metadados, que permite a implantação de novos objetos sem perda dos dados históricos. Por exemplo, se você adicionou algumas medidas, poderá implantá-las independentemente, sem precisar atualizar os dados, economizando tempo.
Para workspaces atribuídos a uma capacidade Premium configurada para leitura/gravação de ponto de extremidade XMLA, as ferramentas compatíveis habilitam a implantação somente de metadados. Por exemplo, o ALM Toolkit é uma ferramenta de diferenciação de esquema para modelos do Power BI e pode ser usado para executar apenas a implantação de metadados.
Baixe e instale a versão mais recente do kit de ferramentas ALM por meio do Repositório Git do Analysis Services. A orientação passo a passo sobre o uso do Kit de ferramentas do ALM não está incluída na documentação da Microsoft. Links de documentação e informações do Kit de ferramentas do ALM sobre suporte estão disponíveis na faixa de opções Ajuda. Para executar uma implantação somente de metadados, execute uma comparação e selecione a instância do Power BI Desktop em execução como a origem e o modelo existente no serviço do Power BI como destino. Observe as diferenças exibidas e ignore a atualização da tabela com partições de atualização incremental, ou use a caixa de diálogo Opções para reter partições para atualizações da tabela. Valide a seleção para garantir a integridade do modelo de destino e realize a atualização.
Como adicionar uma política de atualização incremental e dados em tempo real de forma programática
Você também pode usar o TMSL e o TOM para adicionar uma política de atualização incremental a um modelo existente por meio do ponto de extremidade XMLA.
Observação
Para evitar problemas de compatibilidade, use a versão mais recente das bibliotecas de clientes do Analysis Services. Por exemplo, para trabalhar com políticas híbridas, a versão deve ser 19.27.1.8 ou superior.
O processo inclui as seguintes etapas:
Verifique se o modelo de destino tem o nível mínimo de compatibilidade necessário. No SSMS, clique com o botão direito do mouse no [nome do modelo]>Propriedades>Nível de Compatibilidade. Para aumentar o nível de compatibilidade, use um script TMSL createOrReplace ou verifique o código de exemplo TOM a seguir para obter um exemplo.
a. Import policy - 1550 b. Hybrid policy - 1565Adicione o parâmetro
RangeStarteRangeEndas expressões de modelo. Se necessário, adicione também uma função para converter valores de Data/Hora em chaves de data.Defina um objeto
RefreshPolicycom o arquivamento desejado (janela rolante) e períodos de atualização incrementais, bem como uma expressão de origem que filtre a tabela de destino com base nos parâmetrosRangeStarteRangeEnd. Defina o modo da política de atualização como Importar ou Híbrido, dependendo dos requisitos de dados em tempo real. O Híbrido faz com que o Power BI adicione uma partição do DirectQuery à tabela para buscar as alterações mais recentes da fonte de dados que ocorreram após a última atualização.Adicione a política de atualização à tabela e execute uma atualização completa para que o Power BI particione a tabela de acordo com seus requisitos.
O exemplo de código a seguir demonstra como executar as etapas anteriores usando o TOM. Se você quiser usar esse exemplo como está, deverá ter uma cópia para o banco de dados AdventureWorksDW e importar a tabela FactInternetSales para um modelo. O exemplo de código pressupõe que os parâmetros RangeStart e RangeEnd, e a função DateKey não existem no modelo. Basta importar a tabela FactInternetSales e publicar o modelo em um workspace no Power BI Premium. Em seguida, atualize o workspaceUrl para que o exemplo de código possa se conectar ao seu modelo. Atualize mais linhas de código, conforme necessário.
using System;
using TOM = Microsoft.AnalysisServices.Tabular;
namespace Hybrid_Tables
{
class Program
{
static string workspaceUrl = "<Enter your Workspace URL here>";
static string databaseName = "AdventureWorks";
static string tableName = "FactInternetSales";
static void Main(string[] args)
{
using (var server = new TOM.Server())
{
// Connect to the dataset.
server.Connect(workspaceUrl);
TOM.Database database = server.Databases.FindByName(databaseName);
if (database == null)
{
throw new ApplicationException("Database cannot be found!");
}
if(database.CompatibilityLevel < 1565)
{
database.CompatibilityLevel = 1565;
database.Update();
}
TOM.Model model = database.Model;
// Add RangeStart, RangeEnd, and DateKey function.
model.Expressions.Add(new TOM.NamedExpression {
Name = "RangeStart",
Kind = TOM.ExpressionKind.M,
Expression = "#datetime(2021, 12, 30, 0, 0, 0) meta [IsParameterQuery=true, Type=\"DateTime\", IsParameterQueryRequired=true]"
});
model.Expressions.Add(new TOM.NamedExpression
{
Name = "RangeEnd",
Kind = TOM.ExpressionKind.M,
Expression = "#datetime(2021, 12, 31, 0, 0, 0) meta [IsParameterQuery=true, Type=\"DateTime\", IsParameterQueryRequired=true]"
});
model.Expressions.Add(new TOM.NamedExpression
{
Name = "DateKey",
Kind = TOM.ExpressionKind.M,
Expression =
"let\n" +
" Source = (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)\n" +
"in\n" +
" Source"
});
// Apply a RefreshPolicy with Real-Time to the target table.
TOM.Table salesTable = model.Tables[tableName];
TOM.RefreshPolicy hybridPolicy = new TOM.BasicRefreshPolicy
{
Mode = TOM.RefreshPolicyMode.Hybrid,
IncrementalPeriodsOffset = -1,
RollingWindowPeriods = 1,
RollingWindowGranularity = TOM.RefreshGranularityType.Year,
IncrementalPeriods = 1,
IncrementalGranularity = TOM.RefreshGranularityType.Day,
SourceExpression =
"let\n" +
" Source = Sql.Database(\"demopm.database.windows.net\", \"AdventureWorksDW\"),\n" +
" dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],\n" +
" #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= DateKey(RangeStart) and [OrderDateKey] < DateKey(RangeEnd))\n" +
"in\n" +
" #\"Filtered Rows\""
};
salesTable.RefreshPolicy = hybridPolicy;
model.RequestRefresh(TOM.RefreshType.Full);
model.SaveChanges();
}
Console.WriteLine("{0}{1}", Environment.NewLine, "Press [Enter] to exit...");
Console.ReadLine();
}
}
}