Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

Analysis Services – Como criar seu primeiro cubo multidimensional no modelo estrela (Star schema)

Visualizações: 11.172 views
Tempo de Leitura: 12 minutos

OlĂ¡ pessoal!
Como vocĂªs estĂ£o ?

Neste post vou demonstrar como criar seu primeiro cubo multidimensional no modelo estrela (Star schema). Esse post acabou sendo feito sem a idealizaĂ§Ă£o que costumo fazer, pois estava escrevendo outro post para o blog e precisei criar um cubo simples no Analysis Services para a demonstraĂ§Ă£o e entendi como uma oportunidade de falar um pouco sobre BI tambĂ©m, assunto que trabalho no meu dia a dia e realmente, quase nĂ£o escrevo sobre. Resolvi entĂ£o, criar esse post demonstrando o passo-a-passo para a criaĂ§Ă£o desse cubo.

Alguns conceitos de BI

Modelagem multidimensional

A modelagem multidimensional, ou dimensional como Ă s vezes Ă© chamada, Ă© a tĂ©cnica de modelagem de banco de dados para o auxĂ­lio Ă s consultas do Data Warehouse nas mais diferentes perspectivas. A visĂ£o multidimensional permite o uso mais intuitivo para o processamento analĂ­tico pelas ferramentas OLAP (On-line Analytical Processing).

Toda modelagem dimensional possuem dois elementos imprescindĂ­veis: as tabelas Fatos e as tabelas Dimensões. Ambas sĂ£o obrigatĂ³rias e possuem caracterĂ­stica complementares dentro de um Data Warehouse.

OLAP (On-line Analytical Processing)

O OLAP possui um conjunto de tĂ©cnicas para o tratamento dos dados contidos na visĂ£o multidimensional do Data Warehouse. As ferramentas OLAP podem ser de diferentes tipos: MOLAP, ROLAP ou HOLAP.

O OLAP multidimensional (MOLAP) Ă© o tipo de ferramenta que utiliza estrutura de banco de dados multidimensional. O OLAP relacional (ROLAP) utiliza a arquitetura relacional dos dados, onde o banco de dados possui a estrutura tradicional. JĂ¡ o OLAP hĂ­brido (HOLAP) Ă© a junĂ§Ă£o das duas anteriores, utilizando os melhores aspectos e recursos de cada um dos dois tipos.

Dimensões

As Dimensões sĂ£o os descritores dos dados oriundos da Fato. Possui o carĂ¡ter qualitativo da informaĂ§Ă£o e relacionamento de “um para muitos” com a tabela Fato. É a DimensĂ£o que permite a visualizaĂ§Ă£o das informações por diversos aspectos e perspectivas. Ex: Cadastro de clientes, cadastro de formas de pagamento, etc.

Medidas

As medidas sĂ£o valores numĂ©ricos que representam a Fato e a performance de um indicador de negĂ³cio associado Ă s dimensões. Ex: Valor da venda, Quantidade de vendas, Ticket mĂ©dio, etc.

Fatos

As Fatos contĂ©m as mĂ©tricas. Possui o carĂ¡ter quantitativo das informações descritivas armazenadas nas Dimensões. É onde estĂ£o armazenadas as ocorrĂªncias do negĂ³cio e possui relacionamento de “muitos para um” com as tabelas perifĂ©ricas (DimensĂ£o). É uma tabela que possui apenas as medidas e os ID’s que se ligam Ă s dimensões para exibir as informações completas referente aos dados que serĂ£o visualizados.

Modelos da modelagem multidimensional

A modelagem dimensional possui dois modelos: o modelo estrela (star schema) e o modelo floco de neve (snow flake). Cada um com aplicabilidade diferente a depender da especificidade do problema.

As Dimensões do modelo estrela sĂ£o desnormalizados, ao contrĂ¡rio do snow flake, que parcialmente possui normalizaĂ§Ă£o. A estrutura relacional diferencia-se da estrutura multidimensional principalmente devido a normalizaĂ§Ă£o, pouca redundĂ¢ncia e a frequĂªncia de atualizações suportadas. A estrutura multidimensional possui, normalmente, desnormalizaĂ§Ă£o de tabelas, alta redundĂ¢ncia e suporta periodicidade de atualizações de dados muito menor do que uma estrutura relacional convencional.

Modelo estrela:

Modelo floco de neve:

Gerando as dimensões e Fatos (ETL)

Nesta parte da criaĂ§Ă£o do cubo, vou gerar as dimensões e Fatos com dados aleatĂ³rios. Claro que esses dados sĂ£o utilizados apenas para demonstraĂ§Ă£o. Num cenĂ¡rio real, nĂ£o se deve utilizar dados aleatĂ³rios em anĂ¡lises de BI para criaĂ§Ă£o de cubos multidimensionais, pois a anĂ¡lise nĂ£o teria um valor real.

Para a geraĂ§Ă£o aleatĂ³ria das datas utilizei a funĂ§Ă£o fncRand(), disponĂ­vel no post SQL Server – Msg 443 Invalid use of a side-effecting operator ‘rand’ within a function.

Visualizando a massa aleatĂ³ria de testes:

Criando o cubo no Visual Studio (Data Tools)

Antigamente conhecimento como Business Intelligence Development Studio (BIDS) ou Visual Studio Shell, o Microsoft Data Tools foi incorporado ao Visual Studio a partir da versĂ£o 2015 e agora fazem parte de um sĂ³ produto, sendo agora apenas um plugin do Visual Studio, que pode ser baixado acessando este link.

Para começar, abra o SQL Server Data Tools 2015 e no menu File > New > Project e selecione o tipo de projeto “Analysis Services Multidimensional and Datamining Project”.

Criando uma nova fonte de dados (Data Source)

Visualizar conteĂºdo
Clique com o botĂ£o direito sobre a pastinha “Data Sources” e seleciona a opĂ§Ă£o “New Data Source…”

Na tela que irĂ¡ abrir, clique no botĂ£o “New…” para criar uma nova fonte de dados.

Digite o nome do servidor\instĂ¢ncia, seu usuĂ¡rio de acesso e o database que os dados estĂ£o localizados

Veja que apĂ³s clicar no botĂ£o de “OK”, vocĂª irĂ¡ voltar para a tela anterior, que agora possui os dados preenchidos.

Nesta tela vocĂª poderĂ¡ definir a forma de conexĂ£o do cubo do Analysis Services ao banco de dados. Geralmente utilizo um usuĂ¡rio do AD com permissões no database e no Analysis Services ou uma conta de serviço.

Por fim, defina o nome da fonte de dados recém criada.

Criando um novo Data Source View

Visualizar conteĂºdo
Agora vamos criar um novo datasource view, para adicionar as tabelas fĂ­sicas na nossa estrutura do Cubo.

Clique com o botĂ£o direito sobre a pasta “Data Source Views” e selecione a opĂ§Ă£o “New Data Source View…”.

Selecione a origem dos dados na lista dos Datasources previamente criados.

Nesta janela, defina como serĂ£o criados os relacionamentos lĂ³gicos

Selecione as dimensões e fatos que irĂ£o compor esse cubo e clique no botĂ£o marcado para adicionar as tabelas fĂ­sicas no Cubo.

Essa tela deverĂ¡ ficar assim apĂ³s selecionar os objetos

Digite o nome do Data Source View que vocĂª deseja para finalizar a criaĂ§Ă£o

ApĂ³s a criaĂ§Ă£o do Data Source View, vocĂª pode criar os relacionamentos lĂ³gicos clicando na coluna da Foreign Key na Fato e arrastando para a coluna da Primary Key na dimensĂ£o. ApĂ³s concluir a aĂ§Ă£o, caso nĂ£o exista uma Foreign Key criada na base de dados, vocĂª verĂ¡ essa mensagem de alerta:

Pode criar no botĂ£o “Yes” para concluir a criaĂ§Ă£o do relacionamento lĂ³gico.

ApĂ³s criar esse relacionamento de todas as dimensões, o seu datasource view ficarĂ¡ com essa estrutura:

Exemplo de relacionamento entre Fato e DimensĂ£o

Criando um novo Cubo

Visualizar conteĂºdo

Clique com o botĂ£o direito sobre a pasta “Cubes” e selecione a opĂ§Ă£o “New Cube..”

Nesta tela, vocĂª pode definir como serĂ¡ criado o(s) seu(s) cubo(s). Como jĂ¡ tenho criada a tabela Fato, vou escolher a primeira opĂ§Ă£o.

Agora vocĂª deverĂ¡ selecionar a tabela Fato para formar a Measure Group do seu cubo.

Nesta tela, vocĂª pode utilizar a tecla F2 para renomear as medidas do seu Cubo a partir da Fato.

Aqui vocĂª pode escolher quais dimensões vocĂª deseja importar para o Cubo e renomear essas dimensões

Para finalizar, vocĂª pode definir o nome do Cubo

ApĂ³s a criaĂ§Ă£o do Cubo, essa serĂ¡ a nova estrutura do seu Cubo:

Gerenciando permissões

Visualizar conteĂºdo

Clique com o botĂ£o direito sobre a pasta “Roles” e seleciona a opĂ§Ă£o “New Role…”

Na tela que foi aberta, vocĂª poderĂ¡ definir as permissões da Role criada (O nome da role sĂ³ pode ser alterado na tela de Propriedades)

Na aba “Membership” vocĂª pode definir quem sĂ£o os usuĂ¡rios que estĂ£o nessa role de acesso

Na aba “Data Sources”, podemos definir a forma de acesso ao datasource pelos usuĂ¡rios da Role

Nesta aba “Cubes” vocĂª pode definir se os membros da Role terĂ£o acesso a utilizar o Cubo no Analysis Services

Na aba “Cell Data” podemos definir as permissões a nĂ­vel de linha (cĂ©lula), utilizando expressões MDX

Na aba “Dimensions”, pode-se definir as permissões a nĂ­vel de dimensĂ£o pelos usuĂ¡rios da Role.

Na aba “Dimension Data” podemos definir quais linhas da dimensĂ£o os membros dessa role podem visualizar, utilizando expressões MDX

ApĂ³s realizar todas as configurações de permissĂ£o, vocĂª pode alterar o nome da sua role utilizando a tecla F2 ou a opĂ§Ă£o de renomear o objeto.

VocĂª pode navegar pelas dimensões e adicionar mais campos da sua tabela fĂ­sica para sua dimensões, de modo que seja possĂ­vel visualizĂ¡-las no Cubo

Lembre-se que essa permissĂ£o Ă© apenas no database. Para gerenciar as permissões de Administrador do Analysis Services, vocĂª deverĂ¡ abrir o Analysis Services pelo SQL Server Management Studio, selecionar a opĂ§Ă£o “Analysis Services”

Agora clique com o botĂ£o direito do mouse sobre o Ă­cone do Analysis Services e seleciona a opĂ§Ă£o “Properties”

Na tela de propriedades do Analysis Services, clique na aba “Security” e pronto. Nesta tela vocĂª pode configurar quem sĂ£o os administradores do Analysis Services, podendo criar/excluir/alterar todos os cubos da instĂ¢ncia.

Criando a dimensĂ£o de Tempo

Visualizar conteĂºdo
Para criar a dimensĂ£o de tempo, basta clicar com o botĂ£o direito sobre a pasta “Dimensions” e selecionar a opĂ§Ă£o “New Dimensions..”

Selecione o tipo de tabela de tempo. Selecione a opĂ§Ă£o “Generate a time table in the datasource”, para que a tabela seja criada fisicamente no datasource.

Nesta tela, pode-se definir o perĂ­odo do calendĂ¡rio de datas e o nĂ­vel de granularidade das datas.

Aqui vocĂª pode definir se o seu calendĂ¡rio serĂ¡ o modelo padrĂ£o ou serĂ¡ personalizado, com um perĂ­odo de inĂ­cio diferente do padrĂ£o.

Para finalizar, selecione o nome da dimensĂ£o de Tempo e marque a opĂ§Ă£o “Generate schema now” para criar a tabela na base de dados

Selecione o datasource view que vocĂª deseja utilizar para criar a tabela da dimensĂ£o de Tempo no banco de dados

Deixe as opções padrĂ£o selecionadas, conforme o print abaixo, e clique no botĂ£o “Next”

Nesta tela vocĂª pode visualiazar e definir as convenções de nomenclatura de objetos da tabela que serĂ¡ criada

SumĂ¡rio com as alterações que serĂ£o realizadas

Tabela criada no banco de dados

Lembre-se de editar o Datasource view e adicionar o relacionamento entre a data na tabela Fato e a chave primĂ¡ria na tabela Tempo

O relacionamento deve ficar assim

ObservaĂ§Ă£o: Se a data da sua tabela Fato for do tipo DATETIME com hora, vocĂª precisarĂ¡ remover a hora da coluna data da sua tabela Fato ou criar uma nova coluna sem a hora, pois a dimensĂ£o Tempo nĂ£o possui hora, conforme exemplo abaixo, e com isso, o JOIN nĂ£o serĂ¡ realizado corretamente (Vai retornar apenas os registros da coluna DATETIME com a hora zerada)

Processando o Cubo e Publicando no Analysis Services

Visualizar conteĂºdo
Antes de iniciar o processamento do Cubo, precisamos definir a URL do Deploy do nosso Cubo. Para isso, clique com o botĂ£o direito na soluĂ§Ă£o e seleciona a opĂ§Ă£o “Properties…”

Navegue na aba “Deployment” e preencha corretamente o nome da sua instĂ¢ncia do Analysis Services no campo Target > Server.

Agora que terminamos a criaĂ§Ă£o dos objetos do Cubo, podemos processĂ¡-lo, que Ă© publicar o Cubo no servidor do Analysis Services e atualizar os dados. Para isso, clique com o botĂ£o direito sobre o Cubo e seleciona a opĂ§Ă£o “Process…”

ApĂ³s a solicitaĂ§Ă£o de processamento, vocĂª verĂ¡ a mensagem de alerta abaixo. Pode clicar no botĂ£o “Yes”.

Tela indicando que o Deploy ocorreu com sucesso

Nesta tela vocĂª poderĂ¡ escolher o modo de processamento e processar os dados do banco de dados para o Analysis Services

Tipos de processamento:

  • Process Default: Realiza o menor esforço possĂ­vel (com a menor quantidade de tarefas) para processar a estrutura e os dados. O servidor converte esta opĂ§Ă£o na mais adequada Ă  necessidade do seu ambiente, neste momento. AplicĂ¡vel: Todos Objetos
  • Process Full: Processa toda a estrutura e os dados, excluindo e recriando os objetos. Isso significa que o processamento discarta tudo o que existe e cria novamente a estrutura analĂ­tica e depois processa os dados para esta estrutura. Se algum novo atributo Ă© adicionado Ă  dimnensĂ£o, deve rolar um process full. AplicĂ¡vel: Todos Objetos
  • Process Update: Quando acontece alguma alteraĂ§Ă£o de atributos na dimensĂ£o, seja adicionando apagando ou atualizando, este processo deve ser executado. Tem inteligĂªncia suficiente para processar somente o diferencial da estrutura e dos dados que sĂ£o novos, porĂ©m Ă© mais lento (para aplicar a “inteligĂªncia”). AplicĂ¡vel: DimensĂ£o
  • Process Data: Descarta todos os dados armazenados e processa todos os dados novamente, ignorando se houve alteraĂ§Ă£o na estrutura e tambĂ©m nos Ă­ndices. O foco sĂ£o sĂ³ os dados. AplicĂ¡vel: DimensĂ£o, Cubo, Measure e PartiĂ§Ă£o
  • Process Add (Incremental): Processa somente os novos dados, ignorando os dados que jĂ¡ existem e tambĂ©m qualquer alteraĂ§Ă£o nova na estrutura ou Ă­ndices. AplicĂ¡vel: DimensĂ£o e PartiĂ§Ă£o

ApĂ³s clicar no botĂ£o “Run”, o nosso cubo estĂ¡ Processado e disponĂ­vel para Consultas.

Lembre-se: Qualquer alteraĂ§Ă£o que seja feita no Cubo, precisarĂ¡ que ele seja reprocessado. Se vocĂª fizer um Process Full em uma dimensĂ£o, vocĂª precisarĂ¡ obrigatoriamente executar um Process Full no Cubo tambĂ©m.

É isso aí, pessoal!
Abraço!