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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 |
------------------------------------------------------------------------ -- CRIAĂ‡ĂƒO DAS DIMENSĂ•ES ------------------------------------------------------------------------ IF (OBJECT_ID('dbo.Dim_Cliente') IS NOT NULL) DROP TABLE dbo.Dim_Cliente CREATE TABLE dbo.Dim_Cliente ( Codigo INT IDENTITY(1, 1), Ds_Nome VARCHAR(100), Dt_Nascimento DATETIME, Sg_Sexo VARCHAR(20), Sg_UF VARCHAR(2), ) INSERT INTO dbo.Dim_Cliente VALUES ('Dirceu Resende', '1987-05-28', 'Masculino', 'RJ'), ('Cliente 1', '1971-01-15', 'Feminino', 'ES'), ('Cliente 2', '1955-02-05', 'Masculino', 'ES'), ('Cliente 3', '1997-08-07', 'Masculino', 'RJ'), ('Cliente 4', '2001-11-14', 'Masculino', 'MG'), ('Cliente 5', '1985-12-13', 'Masculino', 'SP'), ('Cliente 6', '1982-09-22', 'Masculino', 'ES') IF (OBJECT_ID('dbo.Dim_Forma_Pagamento') IS NOT NULL) DROP TABLE dbo.Dim_Forma_Pagamento CREATE TABLE dbo.Dim_Forma_Pagamento ( Codigo INT IDENTITY(1, 1), Ds_Nome VARCHAR(100) ) INSERT INTO dbo.Dim_Forma_Pagamento VALUES ('Dinheiro'), ('Cheque'), ('Boleto'), ('CartĂ£o de CrĂ©dito') IF (OBJECT_ID('dbo.Dim_Produto') IS NOT NULL) DROP TABLE dbo.Dim_Produto CREATE TABLE dbo.Dim_Produto ( Codigo INT IDENTITY(1, 1), Ds_Nome VARCHAR(100), Peso INT, Categoria VARCHAR(50), Preco FLOAT ) INSERT INTO dbo.Dim_Produto VALUES ('Toalha', 25, 'Cama, Mesa e Banho', 19.99), ('TV 55', 3200, 'Eletro', 3500), ('TV 42', 2500, 'Eletro', 2359.70), ('Celular Top Android Novo', 120, 'Celulares', 1890), ('Celular Top iOS Usado', 114, 'Celulares', 4999.99), ('Cama Box', 7510, 'Cama, Mesa e Banho', 1249.99), ('Toalha de Rosto', 15, 'Cama, Mesa e Banho', 12.99), ('Prato', 250, 'Cozinha', 34.80), ('Talher', 25, 'Cozinha', 22.50), ('Panela', 250, 'Cozinha', 69.80), ('Microondas', 1450, 'Eletro', 369.99), ('Encosto de Mesa', 35, 'Cama, Mesa e Banho', 15.50) ------------------------------------------------------------------------ -- CRIAĂ‡ĂƒO DA FATO ------------------------------------------------------------------------ IF (OBJECT_ID('dbo.Fato_Venda') IS NOT NULL) DROP TABLE dbo.Fato_Venda CREATE TABLE dbo.Fato_Venda ( Cod_Cliente INT, Cod_Produto INT, Cod_Forma_Pagamento INT, Dt_Venda DATETIME, Vl_Venda FLOAT ) DECLARE @Contador INT = 1, @Total INT = 1000 WHILE(@Contador <= @Total) BEGIN INSERT INTO dbo.Fato_Venda SELECT TOP 1 (SELECT TOP 1 Codigo FROM dbo.Dim_Cliente ORDER BY NEWID()) AS Cod_Cliente, Codigo AS Cod_Produto, (SELECT TOP 1 Codigo FROM dbo.Dim_Forma_Pagamento ORDER BY NEWID()) AS Cod_Forma_Pagamento, DATEADD(DAY, dbo.fncRand(1885), '2012-01-01') AS Dt_Venda, Preco AS Vl_Venda FROM dbo.Dim_Produto ORDER BY NEWID() SET @Contador += 1 END |
Visualizando a massa aleatĂ³ria de testes:
1 2 3 4 |
SELECT * FROM dbo.Dim_Cliente SELECT * FROM dbo.Dim_Forma_Pagamento SELECT * FROM dbo.Dim_Produto SELECT * FROM dbo.Fato_Venda |
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ĂºdoCriando um novo Data Source View
Visualizar conteĂºdoCriando um novo Cubo
Visualizar conteĂºdoGerenciando permissões
Visualizar conteĂºdoCriando a dimensĂ£o de Tempo
Visualizar conteĂºdoProcessando o Cubo e Publicando no Analysis Services
Visualizar conteĂºdoÉ isso aĂ, pessoal!
Abraço!
Bom dia. ParabĂ©ns pelo tutorial. Mas fiquei com uma dĂºvida: Quando vou processar a DimensĂ£o estĂ¡ dando erro de login e senha incorretos. JĂ¡ conferir os dados de acesso estĂ£o corretos, o usuario Ă© administrador do SO e os servicos do SQL Server estĂ£o todos ativos. Tem alguma ideia do que possa ser?
Dirceu, Ă³timo material sobre cubo, ParabĂ©ns!
Tenho uma duvida, como que faria depois que cubos estivesse pronto e quisesse inserir um novo campo, seria preciso dar UPDATE na fato com o novo campo?
Abraços!
Ei Rafa, tudo bem?
Tem que rodar o alter table na fato pra inserir o novo campo, utilizando o update posteriormente para popular os dados do novo campo.
Depois que tem atualizar o dsv no data tools, fazer os ajustes necessĂ¡rios no cubo e reprocessar o cubo.
Parabéns.. muito bom.
Fico feliz em saber que vocĂª gostou đŸ™‚
Dirceu Resende…
PĂ´, vocĂª Ă© o cara! Que Deus continue abençoando voce.
Desejo tudo de bom para vocĂª de verdade. Que artigo completo e incrĂvel. Era o que eu tava precisando.
Abraços de um angolano,
EdilĂ¡sio Paulo
Bom dia.
JĂ¡ faz um bom tempo que procuro um material desse, eu depois de muita pesquisa, sem dĂºvida nenhuma esse foi o melhor e mais completo que encontrei, sĂ³ tenho a agradecer esse compartilhamento de informações.
Que Deus continue abençoando muito vocĂªs , um grade abraço.
Marcelo,
Boa tarde.
Muito obrigado pelo seu feedback!! É isso que me motiva a continuar sempre postando.