fala incomparável seja muito bem-vindo a mais um vídeo no canal eu sou o Leo Karpinski MVP Microsoft e especialista em análise de dados e no vídeo de hoje eu quero dar continuidade ao assunto modelos compostos Quando utilizar o Direct query então eu quero agora fazer um baita resumo sobre o que que eu já mostrei em vídeos anteriores e também o que que eu vou mostrar nesse vídeo e num vídeo futuro então vamos lá primeira coisa quero falar aqui ó sobre quando a gente pode utilizar e quando utizar modelos compostos que que is significa modelos compostos é o uso é a combinação de Import Import mais Direct query só como D em qual motivo quando Por que eu gostaria de usar Direct query em três situações só que deemo des agora nunca vai querer usar apenas Direct query e matar o Import Nunca pense isso sempre pense utilizar modelo composto realmente ou seja combinação dos dois Eu já mostrei PR você num vídeo anterior que uma situação possível é quando a gente quer dados em tempo real então botar aqui ó Real Time quando eu quero dados em tempo real eu posso pegar o histórico e armazenar no Import e o tempo real armazenar no no Direct query mostrei isso em vídeos passados vou deixar aqui embaixo na descrição o link para você assistir esse Case aqui como é que eu soluciono né o Real Time beleza não é o assunto desse vídeo de agora tá o assunto desse vídeo de agora é a segunda opção é quando a minha base é tão grande mas tão grande mas tão grande mesmo que não tenho como eu fazer o Import completo dela E aí eu tenho que fazer também esse mix de Import com dir query com agregações fazendo agregações no modo Import e a tabela realmente daí completara no modo directquery ou seja em questões ó de tamanho de base tamanho de base onde Aí sim a solução vai ser fazer o qu vai ser criar agregações no modo Import como Import e deixar a tabela detalhada que é a grandona a gigante como Direct query Por que que isso aqui ajuda porque agora o meu importe é menor tá vendo isso aqui ó como eu fiz uma agregação para importar os dados agregados eu diminui o número de linhas isso faz com que caiba dentro do meu Power bi tá e quando eu quiser bater lá no no detalhe mais fino possível né no no grão mais fino possível aí eu vou bater no Direct query quer dizer que vai ser vai ser mais performático não não quer dizer isso tá quer dizer que na maioria das vezes que é quando eu analiso dados agregados eu vou ter uma boa performance e na minoria das vezes que é quando eu quero ir no detalhe eu vou continuar tendo problema tá então tamanho de base Você contorna você não resolve você dá uma contornada né Isso aqui é uma forma de você contornar esse problema né Léo e a terceira opção a terceira opção que eu vou deixar para outro dia deixa eu botar aqui um pouquinho menor é o seguinte modelo composto quando eu quero combinar dados que estão num conjunto de dados ou seja no nosso modelo semântico com dados ex exemp arquivo csv um arquivo Excel ou seja para dar mais flexibilidade flexibilidade ao usuário tá flexibilidade ao usuário para que ele possa conectar outras fontes outras bases naquele conjunto de dados certificado por exemplo pela equipe de bi da sua empresa ou seja o usuário final tem a possibilidade de conectar nesse a gente chama de golden dataset ou o nosso conjunto de dados certificado que a nossa equipe de bi nossa equipe de ti criou e além de poder já ter esses dados com cálculos feitos modelagem correta eu posso trazer por exemplo uma tabela de metas que não tá no sistema e fazer um cruzamento tá isso é muito legal também muito eh importante nessa funcionalidade mas eu vou deixar para outro vídeo tá então o nosso foco vai ser aqui ó hoje nesse vídeo de agora nesse caso dois tá vamos lá então então antes mais nada não se esqueça de se inscrever no canal ativar o Sininho e compartilhar com a galera bora então pro vídeo eu estou aqui com uma base de 12 milhões de linhas e eu quero comparar PR você a performance do modo Import com modo Direct query 100% Import e 100% Direct query perceba que quando eu uso 100% Direct query meu arquivo fica pequeno por quê Porque os dados não estão ali no arquivo não está na memória do Power Bi está onde lá no servidor então qualquer filtro que eu aplique qualquer coisa que eu faça eu vou bater no servidor servidor vai ter que processar os dados e me devolver isso fica lento primeira coisa e segundo is pode derrubar o banco facilmente tá e quando eu tenho apenas modo Import o arquivo fica grande ocupa mais memória mas fica muito rápido só que se eu tiver bilhões de linhas provavelmente Isso aqui vai extrapolar vai passar de 1 GB e aí vai ter problemas para poder importar todos os dados E aí vou ter que fazer algum artifício que é o que eu vou mostrar na aula de hoje o artifício de agregações tá em vídeos anteriores eu mostrei para você ó como é que a gente faz essa aquela primeira opção né de modelo composto para contornar o Real Time tá então agora vamos ver a questão de performance porque aquilo que eu mostrei na outra aula não ajudou na performance ajudou apenas no tempo real bom Léo como é que fica essa performance então do Direct query me prova que realmente é ruim então aqui ó tô com o Direct query Tá eu vou lá em exibição performance analizer vou iniciar a gravação e vou fazer um filtro qualquer aqui ó filtrei 2 segundos 4.8 segundos para mudar a tabela aqui tá vou fazer um filtro parecido no que tá apenas como Import vamos lá ó exibição performance analizer iniciar gravação fiz um filtro aqui mais ou menos no mesmo lugar menos de 1 segundo 237 milésimos é super rápido muito rápido só que como eu falei né pô isso aqui é muito grande Léo não cabe no power bi aí eu te digo o seguinte antes de você pensar em fazer malabarismos com agregações com Direct query com modelo composto veja se primeiro você não tem coluna sobrando será será que seu modelo tá no modelo estrela Será que não tem linhas que você poderia deixar de trazer porque não não faz não faz mais sentido ou seja primeiro Olhe o modelo como um todo geral e otimize ele primeiro então por exemplo nesse caso aqui ó eu posso abrir uma ferramenta externa que é o dax Studio e dá uma olhada aqui nessa minha tabela fato dá uma olhada no modelo para ver se não tem nada sobrando então se eu olhar aqui ó pô já de cara ó Opa um monte de local date table hum um monte de tabela lá tá sobrando primeira coisa que eu vou fazer então já a vou lá no meu arquivo opções opções e eu vou começar a otimizar ele primeira coisa é otimizar para depois pensar em em malabarismo eu vou lá em arquivo atual ó carregamento de dados tiro fora data hora automática dou um ok vou lá de volta no dax studio Opa já sumiu ali as tabelas tenho certeza que já diminuiu um pouquinho ali o tamanho do meu arquivo melhorou mas se eu vier aqui ó em avançado View Matrix ele vai mostrar ó por ordem tá Quais são as colunas que mais ocupam espaço e aí eu tenho uma chave primária com 12 milhões de registros únicos na fato para qu não tem necessidade alguma de ter chave primária na fato porque eu não vou relacionar chave primária da fato com ninguém se eu quiser fazer uma contagem de linhas eu faço uma contagem de linhas da tabela não preciso dessa coluna aqui ó só de eu deletar essa coluna aqui agora já vai me trazer uma economia gigantesca quer ver vamos ver eu vou lá no Import vou lá em transformar dados aí tem que ver como é que você tá trazendo isso se eu tiver trazendo a tabela como eu fiz aqui eu posso tranquilamente tá deletar a coluna aqui ó no power query ele vai adicionar um passo novo e esse passo se eu clicar com o botão da direita exibir consulta Nativa ele vai mostrar ainda com a minha consulta Nativa Então fechou tá tá tá funcionando belezinha vou fechar e aplicar tá vou fechar e aplicar ele vai recarregar aqui minhas 12 milhões de linhas a na verdade não precisou porque foi um delete né um delete ele não precisou recarregar então beleza vou salvar ah antes disso 190 m vamos ver para quanto é que vai salvei salvei 41 M Pô olha só a economia que já deu olha só a economia gigantesca tá gigantesca aí eu posso continuar otimizando posso olhar aqui será que eu será que eu preciso desse Sales Order Number Será que não tem nenhum cálculo que eu poderia fazer com dax ao invés de fazer trazer pronto você vai vai mexendo tá e Vai pensando aí só isso aqui já tô satisfeito tá pô Léo fiz isso e continua gigantesco meu arquivo então tá bom então vamos lá então o que que você pode fazer então vamos fazer o seguinte ó você pode lá no power query tá e trocar isso aqui ó de Import para Direct query ou fazer uma agregação nessa tabela Então vamos lá V fazer isso tá vou pegara minha tabela fato online Sales vamos supor que eu não precise olhar por loja tá ou vamos pegar aqui qual que é a dimensão que eu menos Necessito olhar no detalhe A daí você tem que tem que saber né Ah pô eu quase nunca tem que olhar por exemplo pelo sku do produto é mais por família por grupo Opa beleza aí você poderia então tirar fora Talvez né a o produ Key que tá em algum lugar aqui cadê e trazer na verdade ao invés do prod aqui poderia trazer o a subcategoria do produto vamos fazer isso eu vou fazer isso ó então a invés de ter a chave do produto eu quero ter a chave da subcategoria tá do produto como é que eu faço isso bom aqui eu posso fazer um mesclar consultas tá na verdade ele já como ele veio do banco de dados eu acho que ele já traz aqui inclusive ó as mesclas no final isso já trouxe aqui mas eu poderia fazer o mesclar Sem problema nenhum e no G product eu posso expandir apenas a subcategoria tá aqui ó subcategoria pronto subcategoria tá E aí eu posso fazer o quê eu posso tirar fora meu produt aqui daqui posso deletar mas para que eu não tenha aqui para não precisar deletar ele aqui tá eu posso agora então fazer uma agregação então o que que eu vou fazer ó eu vou lá em transformar agrupar por por quais Campos bom produto que não eu quero por data eu quero por loja eu quero por promotion eu quero também por currency por customer que mais por Sales Order Number tudo aqui tá eu vou agrupar por tudo menos por produto essa que essa aqui é a ideia tá vamos lá aqui Sales Order Line Number Tem mais alguma coluna de categoria aqui deixa eu ver não resto é tudo cálculo cê subcategory Key tá aqui ó pronto tá então eu agrupe por todas as colunas menos a que eu quero tirar fora que é o produt Key e as medidas Léo ah aí as medidas ó eu tenho que todas elas fazer aqui por exemplo contagem de linhas eh não não precisaria fazer mas por exemplo eu quero botar aqui o o total Sales amount aí eu tenho que fazer um soma da coluna sals amount aqui tá então você pode fazer isso agora para as demais colunas também de desconto e tudo mais claro que se você fizer isso via query no skel fica mais digamos é é o padrão né é quando você faz aquele group buy é o group buy isso aqui é exatamente o group by do SQL tá vamos lá vou dar um OK vou dar um Ok quando eu dou ok você vai perceber que essa tabela tá e quando fechar aplicar né obviamente ela vai ficar menor tá em termos de linhas isso me traz economia também então vamos lá ó deixei lá Total se amount beleza ele tá uma tabela agora agrupada vou chamar de fact online Sales egs tá agregada e eu vou dar um fechar aplicar simplesmente para ver e quantas linhas que Vai resultar nessa tabela tá vamos lá vou fechar e aplicar aqui finalizou de carregar e eu cometi uma besteira por quê pô tá lá continua com 12 milhões é que eu trouxe uma coluna que é o Sales Order Line Number que representa algo muito parecido com o product key porque isso aqui ó significa o seguinte para uma mesma venda ele tem lá ó por exemplo ó Nessa venda aqui ó ele tem lá o produto um o produto dois o TR o 45 ou seja eu não poderia trazer essa coluna aqui também na agregação tá porque que eu continuo trazendo daí como se tivesse trazendo a mesma granularidade dos produtos então tiro vou voltar lá fiz fiz esse erro não vou regravar porque faz parte desses erros né eu vou aqui ó em linhas agrupadas eu não posso trazer no agrupamento esse Sales Order Line Number tira fora agora sim vou dar um ok vou vejar aplicar e vou ver de novo na sequência Agora sim ó 2.8 milhões de linhas e o valor Total tem que continuar igual né não pode mudar vamos supor que para mim me satisfez tá vamos supor que para mim essa queda aqui ó no número de linhas fez com que os dados caibam no meu Power Bi e o tamanho fique Ok e a performance Fique boa aí o que acontece aí você tem uma decisão a tomar que é a decisão que é a seguinte decisão vou precisar algum dia olhar no nível de produto também no nível mais fino possível que é o produto vou bom então vai que que vai acontecer né seu modelo ficou Cap penga porque agora essa tabela não tem mais o produto para eu relacionar aqui direto como é que eu faço para analisar por produto aí que entra o modelo composto você vai fazer o seguinte ó você vai Primeira coisa eu vou ter que ter uma tabela de de subcategoria que eu não tenho então eu vou trazer a subcategoria para cá então vou vir aqui em Obter dados banco de dados do SQL server tá e eu vou agora trazer como Direct query você vai entender por vou trazer aqui para você ver o subcategory Key produ category esse carinha aqui ó vou carregar tá vou carregar Cadê ele Tá aqui então agora eu consigo ligar ó isso que tá EM Direct querico isso aqui que tá ort pelo subcategory Key subcategory ke tá beleza fechou olhou olhou beleza legal mas eu ainda não tenho o produto tá solta essa dimensão aqui não tá Tá solta se chegar lá no nível do produto eu não tenho ainda ela então vou fazer de novo uma nova obtenção de dados do sk server aqui para puxar agora a minha fato original no Direct query também então eu fui lá EM Direct query mantive Direct query tá contoso a minha fato original no Direct query carregar vai ser rápido porque Direct query tá Direct qu não traz os dados ó simplesmente traz a conexão e olha o que que eu vou fazer eu vou deixar ele aqui ó Tá e agora eu vou relacionar essa nossa dimensão produto com esse online sal daqui só que tem um detalhe eu já trouxe Ó essas tabelas no modo Import essas três e quando eu começo a misturar Import com Direct query eu quero fazer com que essas três tabelas aqui ó relacione elas se relacionem com as duas eu tenho que fazer uma mudança eu tenho que trazer elas no modo Direct query também para deixar ela no modo na verdade Dual que é uma combinação dupla entre Import e Direct query e infelizmente uma vez que eu já traga no modo Import eu não tenho como mudar mais ó tá então infelizmente Tá eu vou ter que deletar essas três tabelas trazer de novo então vamos fazer isso customer vou deletar ain date vou deletar e ain product também vou deletar eu vou trazer agora as três no modo Direct query uhum contoso de customer G date e de product tá lá carregar quando ficar com modelinho Prontinho você vai entender 100% hein Não fique não fique preocupado Se ainda tá confuso já vamos resolver isso já product Opa deixa eu me organizar Aqui beleza esse carinha vem para cá aqui assim ó uhum uhum aqui e tá lá bom olha só o que tá com a faixinha Azul é porque é Direct query tá o que tá sem nada é Import então simplesmente com uma tabela importe o resto Direct query porém eu quero fazer também uma um cruzamento ó de cliente com a Fato online sals agregada eu quero olhar cliente no no agregado tá então eu vou lá e faço um relacionamento customer k aqui com customer K lá ok D datate também quero poder filtrar data e ele filtrar para mim o valor agregado então vou fazer um date k aqui com date Kid lá e aí tem um detalhe essas tabelas aqui ó que estão conectadas no Import e no dir e no directquery elas precisam trabalhar de forma dupla uma vez que eu queira cruzar Import com Import né Eu quero que ela seja ela ten esse comportamento do Import Ah não quero agora buscar dados do Direct query tenha o comportamento Direct query por isso eu posso vir aqui agora em avançado e mudar para Dual Ok então ela fica Dupla ela tem as duas opções ela fica inteligente fica inteligente Ah o produto pode ser apenas Ah vamos botar o produto também como Dual tá porque ele não o produto não precisa porque eu nunca vou chegar lá pelo produto é produto pode ser assim pode ser dir query agora esse aqui não esse aqui pode ser Dual porque se você olhar bem né o produto tá sempre né chegando aqui ó somente né então ele não não chega na no Import então pode ser somente Direct query legal e agora Léo agora é o seguinte agora a tem mais um passo importante mais um passo que é fazer com que o usuário nem saiba que existe duas tabelas fato porque fica feio você botar assim para ele né vai dar confusão o cara vai ter duas Med duas tabelas lá qual que eu faço qual que eu uso Então essa Fato online Sales agregada é um artifício apenas um artifício eu quero dizer pro powerback isso é um artifício da seguinte maneira botando na direita gerenciar agregações E aí você você vai dizer lá que o curren Key é um group buy da original curren Key o customer Key é um group by da tabela fato original customer Key o date Key é um GR byy da tabela original date Key o subcategory é um group by da tabela subcategory Key cadê ah aqui no caso eu posso fazer o seguinte ó V fazer um grupo buy da tabela de product subcategory key aqui ó pronto tá ah ah não eu tenho que ter lá no detalhe ó Mais um ponto importante ó então lá na tabela fact online Sales Eu também preciso ter o subcategory Key então vou ter que fazer um esqueminha aqui ó vamos cancelar que nessa tabela eu tenho que ter a mesma coluna que eu tenho aqui tá Então a gente vai lá no power bi Power query na tabela que é essa aqui que é Direct query vou até botar um DQ aqui ó Direct query Eu também preciso expandir subcategory Key feito feito fechar aplicar Vamos ver se agora vai gerenciar agregações tem bastante coisa PR gente fazer vamos lá cur Grou bu cur customer group customer keyy Grou prod Cadê opa não aqui date Key subcategory aqui promotion p p Sales Order Number P Sales Order Number Store ke group by Store Key Cadê aqui e o total total é uma soma soma do quê do Sales amount que eu tenho aqui ah não deu que o pariu por questão de unidade também a vamos cancelar fazer de novo daí na próxima aceler o vídeo a unidade tá diferente quer ver eu devo estar aqui ó com um Sales amount que é moeda e lá na agregação um Sales amount que é Number gente botar aqui ó currency type currency Type que ficou errado aqui não é assim Ah tira for o Type pronto currency Type E aí sim vai dar certo tá aí eu vou fechar aplicar eu vou configurar de novo lá as agregações e mostro no final como é que ficou pronto voltamos aqui já carregamos a tabela ó T lá no último passo Total se amount agora eu consigo fazer que é a soma de sal amount e eu vou dar um aplicar Olha o que que ele faz ele automaticamente oculta para você essa tabela tá porque tudo você pode utilizar agora a partir dessa original vou até tirar fora de volta o DQ que é como se o usuário nem precisa saber que existe essa agregação tá E olha só que massa agora a nossa medida tem que sempre referenciar não mais a agregada e sempre agora aqui está como original tá beleza e qualquer filtro que eu aplique por exemplo Deixa eu tirar fora aqui esse prod description e eu vou botar somente o total total aqui Total qualquer filtro que eu aplique no nível de cliente data ou subcategoria vai bater na agregada então por exemplo aqui ó vou até botar aqui o exibição painel performance analer e v dar um play aqui eu vou botar o subcategoria description aqui dentro desse nosso visual E olha como foi rápido tá olha como foi rápido ali ó ele botou nessa tabela a subcategoria e o total tá Se eu olhar aqui a nossa query copiando ela aqui ó copiando ela aqui por exemplo copiar consulta aqui ó copiar consulta e eu mostrar para você no Notepad você vai ver que só tem dax ó 100% dax dax dax dax agora se eu jogar aqui para dentro dessa tabela é um produto ou aplicar um filtro de produto você ver o que que vai acontecer vou jogar aqui agora o produto ó que tá na tabela aqui ó produto Vamos jogar aqui o Color name que seja ó perceba que já ficou lento já ficou lento tá e se eu agora então pegar essa consulta aqui copiar ela que foi a última aqui vou dar um control V perceba que começou a ter uns SQL da vida aqui agora left al Joint select porque ele teve que lá no Direct query tá então ele teve que lá no Direct query então se você copiar a consulta e tiver SQL é porque ele foi no Direct query tá então qualquer filtro que eu aplique que não vá no nível mais fino do produto Beleza tô tô seguro que vai vai dar rápido vai na vai vai bater na tabela agregada se eu for no nível mais mais detalhado aí não então por isso tem que saber certinho Qual que é o nível que eu quase nunca chego ah quase nunca chego no nível de dia então você poderia fazer eh agregação no mês por exemplo tá Ah eu quase nunca chego no nível de cliente Pô você poderia fazer por localização por cidade por região por estado no nosso caso aqui foi produto né deixa eu dar uma um limpar aqui e vamos fazer mais um teste fazer mais um teste vamos botar aqui ó o cliente agora tá botar o cliente first Name do cliente aqui numa outra tabela com o total ali ó pá rapidao Ó ficou até tudo em branco porque nosso cliente tudo em branco deixa eu trocar o first Name por customer Type ó rapidao rapidao agora se eu aplicar um filtro de botar agora aqui um filtro ó na tela um filtro de cor ou de tamanho size botei um filtro de size aqui tá que é que tá na tabela produto e vou aplicar o filtro ó vamos ver ó Demorou mais Já demorou mais tá vou dar um refresh só nesse carinha aqui demorou um pouquinho mais se eu D um copiar e eu for olhar ali tem Unos skl da vida aqui dentro ó deu ruim deu ruim não né Eu fui no nível que eu queria mas tem que cuidar agora para você evitar ficar colocando filtros e análises no nível mais detalhado porque você tem que esconder o máximo isso talvez num num drill through num no último nível do totip ou do do drill Down coisas do tipo tá então evite ao máximo usar agora essa tabela produto aqui para que ele não fique batendo no Direct query e por consequência deixe lento sacou E aí me di se Ficou claro como é que funcionam as agregações de modelo composto no power bi entendeu ficou realmente claro para você que quando a gente tem agregações o modelo fica mais rápido porque tem menos linhas na tabela Import só que ao mesmo tempo pode ser que a análise fique mais lenta quando você bate no nível mais detalhado porque aquele nível mais detalhado está no Direct query então é um modelo que é bacana para remediar mas não é uma solução não existe muita solução né para quando você utiliza tabelas muito muito grandes de milhões talvez bilhões de linhas Esse é um artifício bem bacana aí para você contornar isso e tentar o máximo usar sempre né a os cálculos agregados sumarizados evitar ali chegar no nível mais detalhado porque senão vai dar realmente ruim né uma tabela de bilhões de linhas se você quer chegar no nível de nota fisc meu amigo tem ter um preço a ser pago e o preço é performance então espero que tenha gostado te vejo no próximo vídeo