Atualização incremental avançada e dados em tempo real com o ponto de extremidade XMLA
Os modelos semânticos em uma capacidade Premium com o ponto de extremidade XMLA habilitado para operações de leitura/gravação permitem atualizações mais avançadas, gerenciamento de partições e implantações somente de metadados por meio de suporte a ferramentas, scripts e API. Além disso, as operações de atualização por meio do ponto de extremidade XMLA não estão limitadas a 48 atualizações por dia, e o limite de tempo de atualização agendado 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 espaço de trabalho atribuído a uma capacidade Premium, as partições podem ser gerenciadas por meio do ponto de extremidade XMLA usando ferramentas como o SQL Server Management Studio (SSMS), o Editor de Tabelas de código aberto, com script com TMSL (Tabular Model Scripting Language) e programaticamente com o TOM (Tabular Object Model).
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 política de atualização incremental, essa partição contém todas as linhas de dados dessa tabela, a menos que tenham sido aplicados filtros. 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. Configure a partição inicial no Power BI Desktop quando define o filtro de intervalo de data/hora para a sua tabela com base nos parâmetros e e RangeEnd em quaisquer outros filtros aplicados no Editor do RangeStart Power Query. Essa partição inicial contém apenas as linhas de dados que atendem aos critérios de filtro.
Quando você executa a primeira operação de atualização, as tabelas sem 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óricas 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 DirectQuery à tabela.
Essa primeira operação de atualização pode levar algum tempo, dependendo da quantidade de dados que precisa ser carregada da fonte de dados. A complexidade do modelo também pode ser um fator significativo porque as operações de atualização devem fazer mais processamento e recálculo. Esta operação pode ser inicializada. Para obter mais informações, consulte Evitar tempos limite na atualização completa inicial.
As partições são criadas e nomeadas por granularidade de período: 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 DirectQuery selecionará todas as alterações de dados que ocorreram após a data final do período de atualização. A granularidade para partições de atualização e histórico depende dos períodos de atualização e histórico (armazenamento) escolhidos 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 incluir 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 do mês inteiro anterior (janeiro de 2021), uma partição histórica é criada para o período do ano inteiro anterior (2020) e partições históricas para os períodos do ano inteiro de 2019 e 2018 são criadas. Não foram criadas partições trimestrais inteiras porque ainda não concluímos o primeiro trimestre completo de 2021.
Com cada operação de atualização, somente as partições de período de atualização são atualizadas e o filtro de data da partição 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 dentro do período de atualização atualizado, e as linhas existentes com uma data/hora já dentro de partições existentes no período de atualização são atualizadas com atualizações. As linhas com uma data/hora anterior ao período de atualização não são mais atualizadas.
À medida que períodos inteiros se fecham, as partições são mescladas. Por exemplo, se um período de atualização de um dia e um 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 do mês anterior são mescladas em uma partição trimestral. No primeiro dia de um novo ano, todas as quatro partições do trimestre anterior são fundidas em uma partição de ano.
Um modelo sempre retém partições para todo o período de armazenamento histórico, além de partições de período inteiro até o período de atualização atual. No exemplo, três anos completos de dados históricos são retidos em partições para 2018, 2019, 2020 e também 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 retém dados históricos por três anos, a partição de 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ções para você com base na política. Embora o serviço possa lidar com todo o gerenciamento de partições para você, usando ferramentas por meio do ponto de extremidade XMLA, você pode atualizar seletivamente partições individualmente, sequencialmente ou em paralelo.
Gerenciamento de atualizações com o SQL Server Management Studio
O SQL Server Management Studio (SSMS) 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 para executar uma atualização retroativa sem precisar atualizar todos os dados históricos. O SSMS também pode ser usado durante a inicialização para carregar dados históricos para modelos grandes, adicionando/atualizando incrementalmente partições históricas em lotes.
Substituir o comportamento de atualização incremental
Com o SSMS, você também tem mais controle sobre como invocar atualizações usando a linguagem de script de 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 TMSL refresh para substituir o comportamento padrão de atualização incremental:
applyRefreshPolicy. Se uma tabela tiver uma política de atualização incremental definida,
applyRefreshPolicydeterminará se a política é aplicada ou não. Se a política não for aplicada, uma operação completa do processo deixará as definições de partição inalteradas e todas as partições na tabela serão totalmente atualizadas. O valor predefinido é verdadeiro.effectiveDate. Se uma política de atualização incremental estiver sendo aplicada, ela precisará saber a data atual para determinar os intervalos de janela contínua para os períodos de atualização incremental e históricos. O
effectiveDateparâmetro permite 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 até uma data no passado ou no futuro, por exemplo, orçamentos no futuro. 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 com TMSL, consulte Comando Atualizar.
Garantir um 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 para cada partição de atualização incremental. Talvez seja possível melhorar o desempenho de atualização incremental reduzindo o número de consultas de inicialização garantindo a seguinte configuração:
- A tabela para a qual você configura a atualização incremental deve obter dados de uma única fonte de dados. Se a tabela obtiver dados de mais de uma fonte de dados, o número de consultas enviadas pelo serviço para cada operação de atualização será multiplicado pelo número de fontes de dados, reduzindo potencialmente o desempenho da atualização. Verifique se a consulta para a tabela de atualização incremental é para uma única fonte de dados.
- Para soluções com atualização incremental de partições de importação e dados em tempo real com o Direct Query, todas as partições devem consultar dados de uma única fonte de dados.
- Se os requisitos de segurança permitirem, defina a configuração Nível de privacidade da fonte de dados como Organizacional ou Público. Por padrão, o nível de privacidade é Privado, no entanto, 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 esta fonte de dados. Se o nível de privacidade estiver definido no modelo do Power BI Desktop antes da publicação no serviço, ele não será transferido para o serviço quando você publicar. Você ainda deve defini-lo em configurações de modelo semântico no serviço. Para saber mais, consulte Níveis de privacidade.
- Se estiver a utilizar um Gateway de Dados no local, certifique-se de que está a utilizar a versão 3000.77.3 ou superior.
Evitar tempos limite na atualização completa inicial
Depois de publicar no serviço do Power BI, a operação inicial de atualização completa para o modelo cria partições para a tabela de atualização incremental, carrega e processa dados históricos para 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.
Inicializar a operação de atualização inicial permite que o serviço crie objetos de partição para a tabela de atualização incremental, mas não carregue e processe dados históricos em qualquer uma das partições. O SSMS é então usado para processar partições seletivamente. Dependendo da quantidade de dados a serem carregados para cada partição, você pode processar cada partição sequencialmente ou em pequenos lotes para reduzir o potencial de uma ou mais dessas partições causarem 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 maneira 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 Apply Refresh Policy na tabela de atualização incremental. Com apenas a política aplicada, as partições são criadas, mas nenhum dado é carregado nelas. Em seguida, conecte-se ao SSMS para atualizar as partições sequencialmente ou em lotes para carregar e processar os dados. Para obter mais informações, consulte Atualização incremental na documentação do editor de tabela.
Filtro do Power Query para partições vazias
Antes de publicar o modelo no serviço, no Power Query Editor, adicione outro filtro à ProductKey coluna que filtre qualquer valor diferente de 0, de forma eficaz ou filtre todos os dados da tabela FactInternetSales .
Depois de selecionar Fechar & Aplicar no Power Query Editor, definir a política de atualização incremental e guardar o modelo, o modelo é publicado no serviço. A partir do 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.
Após a conclusão da operação de atualização inicial, de volta ao Power Query Editor, o outro filtro na ProductKey coluna é removido. Depois de selecionar Fechar & Aplicar no Power Query Editor e guardar o modelo, o modelo não é publicado novamente. Se o modelo for publicado novamente, ele substituirá as configurações de diretiva 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 Application Lifecycle Management (ALM) Toolkit que remove o filtro na ProductKey coluna do modelo. O SSMS pode então 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, a partir do SSMS, as operações de atualização subsequentes no modelo do serviço atualizarão apenas as partições de atualização incremental.
Gorjeta
Não deixe de conferir vídeos, blogs e muito mais fornecidos pela comunidade de especialistas em BI do Power BI.
Para saber mais sobre como processar tabelas e partições do SSMS, consulte Processar banco de dados, tabela ou partições (Analysis Services). Para saber mais sobre como processar modelos, tabelas e partições usando TMSL, consulte Comando Atualizar (TMSL).
Consultas personalizadas para detetar alterações de dados
TMSL e TOM podem ser usados para substituir o comportamento de alterações de dados detetadas. Esse método não só pode ser usado para evitar a persistência da coluna de última atualização no cache na memória, mas também pode habilitar cenários em que uma tabela de configuração ou instrução é preparada por processos de extração, transformação e carregamento (ETL) para sinalizar apenas as partições que precisam ser atualizadas. Esse método pode criar um processo de atualização incremental mais eficiente onde apenas os períodos necessários são atualizados, não importa há quanto tempo as atualizações de dados ocorreram.
O pollingExpression destina-se a ser uma expressão M leve ou nome de outra consulta M. Ele deve retornar um valor escalar e será executado para cada partição. Se o valor retornado for diferente do que foi a última vez que ocorreu uma atualização incremental, a partição será sinalizada para processamento completo.
O exemplo a seguir abrange todos os 120 meses do período histórico para alterações retroativas. Especificar 120 meses em vez de 10 anos significa que a compactação de dados pode não ser tão eficiente, mas evita ter que atualizar um ano histórico inteiro, o que seria mais caro quando um mês seria suficiente para uma alteração retroativa.
"refreshPolicy": {
"policyType": "basic",
"rollingWindowGranularity": "month",
"rollingWindowPeriods": 120,
"incrementalGranularity": "month",
"incrementalPeriods": 120,
"pollingExpression": "<M expression or name of custom polling query>",
"sourceExpression": [
"let ..."
]
}
Gorjeta
Não deixe de conferir vídeos, blogs e muito mais fornecidos pela comunidade de especialistas em 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 espaço de trabalho, 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 com a 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 pode ter vários anos de dados históricos que serão perdidos se o modelo for substituído. A atualização de 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 perder os dados históricos. Por exemplo, se você adicionou algumas medidas, pode implantar apenas as novas medidas sem precisar atualizar os dados, economizando tempo.
Para espaços de trabalho atribuídos a uma capacidade Premium configurada para leitura/gravação de ponto de extremidade XMLA, as ferramentas compatíveis permitem a implantação somente de metadados. Por exemplo, o ALM Toolkit é uma ferramenta de comparação de esquema para modelos do Power BI e pode ser usada apenas para executar a implantação de metadados.
Baixe e instale a versão mais recente do ALM Toolkit a partir do repositório Git do Analysis Services. Orientações passo a passo sobre como usar o ALM Toolkit não estão incluídas na documentação da Microsoft. Os links de documentação do ALM Toolkit e informações sobre a capacidade de 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. Considere 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 de tabela. Valide a seleção para garantir a integridade do modelo de destino e, em seguida, atualize.
Adicionando uma política de atualização incremental e dados em tempo real programaticamente
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.
Nota
Para evitar problemas de compatibilidade, certifique-se de usar a versão mais recente das bibliotecas de cliente 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 exigido. No SSMS, clique com o botão direito do mouse no [nome do modelo]>Nível de Compatibilidade de Propriedades.> Para aumentar o nível de compatibilidade, use um script createOrReplace TMSL ou verifique o seguinte código de exemplo TOM para obter um exemplo.
a. Import policy - 1550 b. Hybrid policy - 1565Adicione os
RangeStartparâmetros eRangeEndàs expressões do modelo. Se necessário, adicione também uma função para converter valores de Data/Hora em chaves de data.Defina um
RefreshPolicyobjeto com o arquivamento desejado (janela contínua) e períodos de atualização incremental, bem como uma expressão de origem que filtre a tabela de destino com base nosRangeStartparâmetros eRangeEnd. Defina o modo de política de atualização como Importar ou Híbrido, dependendo dos seus requisitos de dados em tempo real. Híbrido faz com que o Power BI adicione uma partição DirectQuery à tabela para buscar as alterações mais recentes da fonte de dados que ocorreram após a última hora de 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 TOM. Se quiser usar este exemplo como está, você deve 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 RangeStart parâmetros e e RangeEnd a DateKey função não existem no modelo. Basta importar a tabela FactInternetSales e publicar o modelo em um espaço de trabalho no Power BI Premium. Em seguida, atualize o para que o workspaceUrl 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();
}
}
}