Em uma tabela dinâmica padrão, os dados de origem são armazenados no disco rígido local. Desta forma, você sempre poderá gerenciá-los e reorganizá-los, mesmo sem acesso à rede. Mas isso não se aplica de forma alguma às tabelas dinâmicas OLAP. Nas tabelas dinâmicas OLAP, o cache nunca é armazenado no disco rígido local. Portanto, imediatamente após desconectar rede local sua tabela dinâmica não funcionará mais. Você não poderá mover um único campo nele.

Se você ainda precisar analisar dados OLAP depois de ficar offline, crie um cubo de dados offline. Um cubo de dados offline é um arquivo separado que é um cache de tabela dinâmica e armazena dados OLAP que são visualizados após a desconexão da rede local. Os dados OLAP copiados para uma tabela dinâmica podem ser impressos; isso é descrito em detalhes no site http://everest.ua.

Para criar um cubo de dados independente, primeiro crie uma tabela dinâmica OLAP. Coloque o cursor na tabela dinâmica e clique no botão Ferramentas OLAP na guia contextual Ferramentas, que faz parte do grupo de guias contextuais Ferramentas de Tabela Dinâmica. Selecione uma equipe Modo off-line OLAP (OLAP offline) (Fig. 9.8).

A caixa de diálogo Configurações do cubo de dados OLAP offline aparece na tela. Clique no botão Criar arquivo de dados offline. Você iniciou o Assistente para Criar Arquivo de Cubo de Dados. Clique no botão Avançar para continuar o procedimento.

Primeiro você precisa especificar as dimensões e os níveis que serão incluídos no cubo de dados. Na caixa de diálogo você deve selecionar os dados que serão importados do banco de dados OLAP. A ideia é especificar apenas as dimensões que serão necessárias após o computador ser desconectado da rede local. Quanto mais dimensões você especificar, maior será o cubo de dados autônomo.

Clique no botão Avançar para passar para a próxima caixa de diálogo do assistente. Dá a você a capacidade de especificar membros ou elementos de dados que não serão incluídos no cubo. Em particular, você não precisará da medida Valor Estendido de Vendas pela Internet, portanto sua caixa de seleção estará desmarcada na lista. Uma caixa de seleção desmarcada indica que o item especificado não será importado e ocupará espaço desnecessário no disco rígido local.

Na última etapa, especifique o local e o nome do cubo de dados. No nosso caso, o arquivo do cubo se chamará MyOfflineCube.cub e estará localizado na pasta Work.

Os arquivos de cubo de dados têm a extensão .filhote

Depois de algum tempo, o Excel salvará o cubo de dados offline na pasta especificada. Para testá-lo, clique duas vezes no arquivo, que irá gerar automaticamente pasta de trabalho Excel que contém uma tabela dinâmica associada ao cubo de dados selecionado. Depois de criado, você poderá distribuir o cubo de dados offline para todos os usuários interessados ​​que estejam trabalhando no modo LAN offline.

Uma vez conectado à sua rede local, você pode abrir o arquivo do cubo de dados offline e atualizá-lo junto com a tabela de dados associada. Princípio principal afirma que o cubo de dados offline é usado apenas para funcionar quando a rede local está desconectada, mas é necessário atualizá-lo após a restauração da conexão. A tentativa de atualizar um cubo de dados offline após uma falha de conexão resultará em falha.

OLAP (processamento analítico on-line)é um método de processamento eletrônico de dados analíticos que representa a organização dos dados em categorias hierárquicas usando totais pré-calculados. Os dados OLAP são organizados hierarquicamente e armazenados em cubos em vez de tabelas. Os cubos OLAP são um conjunto de dados multidimensional com eixos contendo parâmetros e células contendo dados agregados dependentes de parâmetros. Os cubos são projetados para análises complexas e multidimensionais de grandes volumes de dados porque fornecem apenas resultados resumidos para relatórios, em vez de um grande número de registros individuais.

O conceito de OLAP foi descrito em 1993 pelo famoso pesquisador de banco de dados e autor do modelo de dados relacional, E. F. Codd. Atualmente, o suporte OLAP está implementado em muitos SGBDs e outras ferramentas.

Um cubo OLAP contém dois tipos de dados:

· valores totais, valores para os quais você deseja resumir, representando campos de dados calculados;

· informações descritivas que representam medições ou dimensões. As informações descritivas são normalmente organizadas em níveis de detalhe. Por exemplo: “Ano”, “Trimestre”, “Mês” e “Dia” na dimensão “Tempo”. A organização dos campos em níveis de detalhe permite que os usuários de relatórios escolham o nível de detalhe que desejam visualizar, começando com dados resumidos de alto nível e, em seguida, detalhando até uma visualização mais detalhada e vice-versa.

As ferramentas Microsoft Query também permitem criar cubos OLAP a partir de uma consulta que carrega dados de banco de dados relacional, como Acesso Microsoft, neste caso a tabela linear é transformada em uma hierarquia estrutural (cubo).

O Assistente para Criar Cubo OLAP é uma ferramenta interna do Microsoft Query. Para criar um cubo OLAP baseado em um banco de dados relacional, você deve concluir as etapas a seguir antes de executar o assistente.

1. Determine a fonte de dados (veja a Figura 6.1).

2.C usando Microsoft Consulta cria uma consulta, incluindo apenas os campos que serão campos de dados ou campos de dimensão de um cubo OLAP. Se um campo em um cubo for usado mais de uma vez, ele deverá ser incluído na consulta; o número certo uma vez.

3. Na última etapa do assistente de criação de consulta, coloque a chave no item Criando um cubo OLAP a partir de deste pedido (ver Fig. 6.2) ou após a criação da solicitação usando o menu Consulta diretamente Arquivo selecione uma equipe Criar cubo OLAP, após o qual o Assistente para Criar Cubo OLAP será iniciado.

O Assistente para Criar Cubo OLAP consiste em três etapas.

Na primeira etapa do assistente (ver Fig. 6.6) o campos de dados– campos calculados para os quais os valores totais devem ser determinados.

Arroz. 6.6. Definição de campos de dados

O assistente coloca os campos calculados esperados (geralmente campos numéricos) no topo da lista, verifica-os e determina a função resultante desses campos, geralmente - Soma. Ao selecionar campos de dados, pelo menos um campo deve ser selecionado como campo calculado e pelo menos um campo deve ser deixado desmarcado para determinar a dimensão.

Ao criar um cubo OLAP, você pode usar quatro funções de resumo - Soma, Número(número de valores), Mínimo, Máximo para campos numéricos e uma função Número para todos os outros campos. Se você quiser usar diversas funções de resumo diferentes do mesmo campo, esse campo deverá ser incluído na consulta o número necessário de vezes.

O nome de um campo calculado pode ser alterado em uma coluna Nome do campo de dados.

Na segunda etapa do assistente, são determinados os dados descritivos e suas dimensões (ver Fig. 6.7). Para selecionar um campo de medição, você deve na lista Campos de origem arraste o campo de dimensão de nível superior desejado para a lista Medições para a área marcada como Arraste os campos aqui para criar dimensões. Para criar um cubo OLAP, você deve definir pelo menos uma dimensão. Na mesma etapa do assistente, usando menu de contexto Você pode alterar o nome de um campo de dimensão ou nível.

Arroz. 6.7. Definição de campos de dimensão

Os campos que contêm dados isolados ou discretos e não pertencem a uma hierarquia podem ser definidos como dimensões de nível único. No entanto, o cubo será mais eficiente se alguns dos campos estiverem organizados em níveis. Para criar um nível como parte de uma dimensão, arraste um campo da lista Campos de origem em um campo que é uma dimensão ou nível. Os campos contendo informações mais detalhadas devem ser colocados em níveis inferiores. Por exemplo, na Figura 6.7 o campo Cargoé o nível do campo Nome do departamento.

Para mover um campo para um nível inferior ou superior, é necessário arrastá-lo para um campo inferior ou superior dentro da dimensão. Para exibir ou ocultar níveis, use os botões ou, respectivamente.

Se você usar campos de data ou hora como dimensão de nível superior, o Assistente de Cubo OLAP criará automaticamente níveis para essas dimensões. O usuário pode então selecionar quais níveis devem aparecer nos relatórios. Por exemplo, você pode selecionar semanas, trimestres e anos ou meses (veja a Figura 6.7).

Lembre-se de que o assistente cria níveis automaticamente para campos de data e hora somente quando você cria uma dimensão de nível superior; Ao adicionar estes campos como subníveis de uma dimensão, não são criados níveis automáticos.

Na terceira etapa do assistente, é determinado o tipo de cubo criado pelo assistente, com três opções possíveis (ver Fig. 6.8).

Arroz. 6.8. Selecionando o tipo de cubo a ser criado na terceira etapa do assistente

· As duas primeiras opções envolvem a criação de um cubo cada vez que você abre um relatório (se o cubo é visualizado no Excel, estamos falando de uma tabela dinâmica). Neste caso, o arquivo de solicitação e o arquivo definições de cubo *.oqy, que contém instruções para criar um cubo. O arquivo *.oqy pode ser aberto em Programa Excel para criar relatórios com base no cubo e, se precisar fazer alterações no cubo, você poderá abrir Consulta para executar o Assistente para Criação de Cubo novamente.

Por padrão, os arquivos de definição de cubo, bem como os arquivos de consulta, são armazenados na pasta de perfil do usuário em Application Data\Microsoft\Que-ries. Ao salvar um arquivo *.oqy na pasta padrão, o nome do arquivo de definição de cubo é exibido na guia Cubos OLAP ao abrir uma nova consulta no Microsoft Query ou ao selecionar um comando Crie uma solicitação(menu Dados, submenu Importando dados externos) no Microsoft Excel.

· Caso escolha a terceira opção de tipo de cubo Salvando um arquivo de cubo contendo todos os dados do cubo, todos os dados do cubo serão recuperados e um arquivo de cubo com a extensão * será criado em um local especificado pelo usuário .filhote, no qual esses dados são armazenados. Criação este arquivo não acontece imediatamente quando o botão é pressionado Preparar; o arquivo é criado quando você salva a definição de cubo em um arquivo ou quando cria um relatório baseado no cubo.

A escolha do tipo de cubo é determinada por vários fatores: a quantidade de dados que o cubo contém; o tipo e a complexidade dos relatórios que serão criados com base no cubo; recursos do sistema (memória e espaço em disco), etc.

Arquivo separado cube *.cub deve ser criado nos seguintes casos:

1) para relatórios interativos alterados com frequência, se houver espaço em disco suficiente;

2) quando você precisar salvar o cubo em um servidor de rede para fornecer acesso a ele para outros usuários ao criar relatórios. Um arquivo cubo pode fornecer dados específicos do banco de dados de origem enquanto omite dados confidenciais ou confidenciais que você deseja impedir que outros usuários acessem.

Talvez para alguns o uso da tecnologia OLAP (On-line Analytic Processing) na criação de relatórios pareça um tanto exótico, então o uso do OLAP-CUBE para eles não é de forma alguma um dos requisitos mais importantes na automatização do orçamento e da contabilidade gerencial.

Na verdade, é muito conveniente usar um CUBO multidimensional ao trabalhar com relatórios gerenciais. Ao desenvolver formatos de orçamento, você pode encontrar o problema dos formulários multivariados (você pode ler mais sobre isso no Livro 8, “Tecnologia para configurar o orçamento em uma empresa” e no livro “Configurar e automatizar a contabilidade gerencial”).

Isto se deve ao fato de que a gestão eficaz de uma empresa exige relatórios gerenciais cada vez mais detalhados. Ou seja, o sistema utiliza cada vez mais seções analíticas diferentes (em sistemas de informação análises são definidas por um conjunto de livros de referência).

Naturalmente, isso leva ao fato de os gestores desejarem receber relatórios em todas as seções analíticas que lhes interessam. Isso significa que os relatos precisam ser feitos para “respirar” de alguma forma. Ou seja, podemos dizer que neste caso estamos a falar do facto de um mesmo relatório dever fornecer informação em diferentes aspectos analíticos. Portanto, os relatórios estáticos não são mais adequados para muitos gerentes modernos. Eles precisam da dinâmica que um CUBO multidimensional pode proporcionar.

Assim, a tecnologia OLAP já se tornou um elemento obrigatório nos sistemas de informação modernos e futuros. Portanto, ao escolher um produto de software, você precisa prestar atenção se ele utiliza a tecnologia OLAP.

Além disso, você precisa ser capaz de distinguir CUBOS reais de imitações. Uma dessas simulações são as tabelas dinâmicas no MS Excel. Sim, esta ferramenta é semelhante a um CUBE, mas na verdade não é, pois são tabelas estáticas e não dinâmicas. Além disso, eles têm uma implementação muito pior da capacidade de construir relatórios usando elementos de diretórios hierárquicos.

Para confirmar a relevância da utilização do CUBE na construção do relatório gerencial, podemos citar exemplo mais simples com um orçamento de vendas. No exemplo em consideração, as seguintes seções analíticas são relevantes para a empresa: produtos, filiais e canais de vendas. Se essas três análises são importantes para a empresa, então o orçamento (ou relatório) de vendas pode ser exibido em diversas versões.

Deve-se notar que se você criar rubricas orçamentárias com base em três seções analíticas (como no exemplo em consideração), isso permitirá criar linhas bastante complexas modelos de orçamento e crie relatórios detalhados usando CUBE.

Por exemplo, um orçamento de vendas pode ser compilado usando apenas uma análise (diretório). Um exemplo de orçamento de vendas construído com base em uma análise de "Produtos" é apresentado em Figura 1.

Arroz. 1. Um exemplo de orçamento de vendas construído com base em uma análise de “Produtos” em OLAP-CUBE

O mesmo orçamento de vendas pode ser compilado usando duas análises (diretórios). Um exemplo de orçamento de vendas construído com base em duas análises “Produtos” e “Filiais” é apresentado em Figura 2.

Arroz. 2. Um exemplo de orçamento de vendas construído com base em dois analíticos “Produtos” e “Filiais” no OLAP-CUBE do pacote de software INTEGRAL

.

Se houver necessidade de construir relatórios mais detalhados, o mesmo orçamento de vendas poderá ser compilado usando três análises (diretórios). Um exemplo de orçamento de vendas construído com base em três análises “Produtos”, “Filiais” e “Canais de Vendas” é apresentado em Figura 3.

Arroz. 3. Um exemplo de orçamento de vendas construído com base em três análises “Produtos”, “Filiais” e “Canais de Vendas” no OLAP-CUBE do pacote de software INTEGRAL

Deve-se lembrar que o CUBO utilizado para gerar relatórios permite exibir os dados em diferentes sequências. Sobre Figura 3 O orçamento de vendas é primeiro “ampliado” por produto, depois por filial e depois por canal de vendas.

Os mesmos dados podem ser apresentados em uma sequência diferente. Sobre Figura 4 o mesmo orçamento de vendas é “expandido” primeiro por produto, depois por canal de vendas e depois por filial.

Arroz. 4. Um exemplo de orçamento de vendas construído com base em três análises “Produtos”, “Canais de Distribuição” e “Filiais” no OLAP-CUBE do pacote de software INTEGRAL

Sobre Figura 5 o mesmo orçamento de vendas é “desdobrado” primeiro por filiais, depois por produtos e depois por canais de vendas.

Arroz. 5. Um exemplo de orçamento de vendas construído com base em três análises “Filiais”, “Produtos” e “Canais de Vendas” no pacote de software OLAP-CUBE “INTEGRAL”

Na verdade isso não é tudo opções possíveis retirada do orçamento de vendas.

Além disso, é preciso atentar para o fato de que o KUB permite trabalhar com a estrutura hierárquica de diretórios. Nos exemplos apresentados, os diretórios hierárquicos são “Produtos” e “Canais de Distribuição”.

Do ponto de vista do usuário, neste exemplo ele recebe diversos relatórios gerenciais (ver. Arroz. 1-5) e do ponto de vista das configurações do produto de software, este é um relatório. Simplesmente usando o CUBE você pode visualizá-lo de diversas maneiras.

Naturalmente, na prática é muito possível grande número opções de saída de diversos relatórios gerenciais caso seus artigos sejam baseados em um ou mais analistas. E o próprio conjunto de análises depende das necessidades de detalhes dos usuários. É verdade que não devemos esquecer que, por um lado, quanto maior o analista, mais detalhados podem ser os relatórios. Mas, por outro lado, isto significa que o modelo de orçamentação financeira será mais complexo. Em qualquer caso, caso exista um KUB, a empresa terá a oportunidade de visualizar os relatórios necessários em diversas versões, de acordo com as secções analíticas de interesse.

É necessário mencionar mais alguns recursos do OLAP-CUBE.

Em um OLAP-CUBE hierárquico multidimensional existem diversas dimensões: tipo de linha, data, linhas, diretório 1, diretório 2 e diretório 3 (consulte. Arroz. 6). Naturalmente, o relatório exibe tantos botões com diretórios quantos houver na linha do orçamento que contém o número máximo de diretórios. Se não houver um único livro de referência em nenhuma rubrica orçamentária, o relatório não terá um único botão com livros de referência.

Inicialmente, o OLAP-CUBE é construído em todas as dimensões. Por padrão, quando o relatório é criado inicialmente, as dimensões estão localizadas exatamente nas áreas mostradas em Figura 6. Ou seja, uma dimensão como “Data” está localizada na área das dimensões verticais (dimensões na área da coluna), dimensões “Linhas”, “Diretório 1”, “Diretório 2” e “Diretório 3” - no área de dimensões horizontais (dimensões nas linhas da área), e a dimensão “Tipo de linha” está na área de dimensões “não expandidas” (dimensões na área da página). Se uma dimensão estiver na última área, os dados do relatório não serão "expandidos" nessa dimensão.

Cada uma dessas dimensões pode ser colocada em qualquer uma das três áreas. Depois que as medições são transferidas, o relatório é reconstruído instantaneamente para corresponder à nova configuração de medição. Por exemplo, você pode trocar a data e as linhas com livros de referência. Ou você pode mover um dos livros de referência para a área de medição vertical (consulte. Arroz. 7). Em outras palavras, você pode “torcer” o relatório no OLAP-CUBE e selecionar a opção de saída do relatório que for mais conveniente para o usuário.

Arroz. 7. Um exemplo de reconstrução de um relatório após alterar a configuração de medição do pacote de software INTEGRAL

A configuração de medição pode ser alterada no formulário CUBE principal ou no editor de mapa de alterações (consulte. Arroz. 8). Neste editor, você também pode arrastar e soltar medições de uma área para outra com o mouse. Além disso, você pode trocar medidas na mesma área.

Além disso, no mesmo formulário você pode configurar alguns parâmetros de medição. Para cada dimensão, você pode personalizar a localização dos totais, a ordem de classificação dos elementos e os nomes dos elementos (consulte. Arroz. 8). Você também pode especificar qual nome de elemento exibir no relatório: abreviado (Nome) ou completo (FullName).

Arroz. 8. Editor de mapas de medição do pacote de software INTEGRAL

Você pode editar os parâmetros de medição diretamente em cada um deles (ver. Arroz. 9). Para isso, clique no ícone localizado no botão ao lado do nome da medida.

Arroz. 9. Exemplo de edição do diretório 1 Produtos e serviços em

Usando este editor, você pode selecionar os elementos que deseja mostrar no relatório. Por padrão, todos os elementos são exibidos no relatório, mas se necessário, alguns elementos ou pastas podem ser omitidos. Por exemplo, se você precisar exibir apenas um grupo de produtos em um relatório, será necessário desmarcar todos os outros no editor de medição. Depois disso, o relatório conterá apenas um grupo de produtos (ver. Arroz. 10).

Você também pode classificar os elementos neste editor. Além disso, os elementos podem ser reorganizados de várias maneiras. Após esse reagrupamento, o relatório é reconstruído instantaneamente.

Arroz. 10. Exemplo de saída em relatório de apenas um grupo de produtos (pasta) no pacote de software INTEGRAL

No editor de dimensões, você pode criar rapidamente seus próprios grupos, arrastar e soltar elementos de diretórios, etc. Por padrão, somente o grupo Outros é criado automaticamente, mas outros grupos podem ser criados. Assim, através do editor de dimensões, você pode configurar quais elementos dos livros de referência e em que ordem devem ser exibidos no relatório.

Deve-se notar que todos esses rearranjos não são registrados. Ou seja, após o fechamento do relatório ou após seu recálculo, todos os diretórios serão exibidos no relatório de acordo com a metodologia configurada.

Na verdade, todas essas alterações poderiam ter sido feitas inicialmente durante a configuração das linhas.

Por exemplo, usando restrições você também pode especificar quais elementos ou grupos de diretórios devem ser exibidos no relatório e quais não devem.

Observação: o tema deste artigo é discutido com mais detalhes em workshops "Gestão orçamentária de uma empresa" E “Organização e automação da contabilidade gerencial” conduzido pelo autor deste artigo, Alexander Karpov.

Se o usuário precisar exibir quase regularmente apenas determinados elementos ou pastas de diretório no relatório, é melhor fazer essas configurações com antecedência ao criar linhas de relatório. Se várias combinações de elementos de diretório em relatórios forem importantes para o usuário, não será necessário definir nenhuma restrição ao configurar a metodologia. Todas essas restrições podem ser configuradas rapidamente usando o editor de medição.

No artigo anterior desta série (ver nº 2’2005) falamos sobre as principais inovações dos serviços analíticos Servidor SQL 2005. Hoje examinaremos mais de perto as ferramentas para a criação de soluções OLAP incluídas neste produto.

Resumidamente sobre os fundamentos do OLAP

Antes de começarmos a falar sobre ferramentas para criação de soluções OLAP, lembremos que OLAP (On-Line Analytical Processing) é uma tecnologia para análise complexa de dados multidimensionais, cujo conceito foi descrito em 1993 por E.F. Codd, famoso autor do relacional modelo de dados. Atualmente, o suporte OLAP está implementado em muitos SGBDs e outras ferramentas.

Cubos OLAP

O que são dados OLAP? Para responder a esta pergunta, considere um exemplo simples. Suponhamos que na base de dados corporativa de uma determinada empresa exista um conjunto de tabelas contendo informações sobre vendas de bens ou serviços, e com base nelas foi criada uma visualização de Faturas com os campos País (país), Cidade (cidade), NomeDoCliente (nome da empresa cliente), Vendedor (gerente de vendas), OrderDate (data de colocação do pedido), CategoryName (categoria do produto), ProductName (nome do produto), ShipperName (empresa transportadora), ExtendedPrice (pagamento da mercadoria), enquanto o último destes campos é, de fato, objeto de análise.

A seleção de dados dessa visualização pode ser feita usando a seguinte consulta:

SELECIONE País, Cidade, NomeDoCliente, Vendedor,

OrderDate, CategoryName, ProductName, ShipperName, ExtendedPrice

DE Faturas

Suponha que estejamos interessados ​​no valor total dos pedidos feitos por clientes de diferentes países. Para obter uma resposta a esta pergunta, você precisa fazer a seguinte solicitação:

SELECIONE País, SUM (ExtendedPrice) DE Faturas

Agrupar por país

O resultado desta consulta será um conjunto unidimensional de dados agregados (neste caso, somas):

País	SOMA (Preço Estendido)
Argentina	7327.3
Áustria	110788.4
Bélgica	28491.65
Brasil	97407.74
Canadá	46190.1
Dinamarca	28392.32
Finlândia	15296.35
França	69185.48
	209373.6
	...

Se quisermos saber o custo total dos pedidos feitos por clientes de diferentes países e entregues por diferentes serviços de entrega, devemos realizar uma consulta contendo dois parâmetros na cláusula GROUP BY:

SELECIONE País, ShipperName, SUM (ExtendedPrice) EM Faturas

GRUPO POR PAÍS, ShipperName

Com base nos resultados desta consulta, você pode criar uma tabela semelhante a esta:

Este conjunto de dados é chamado tabela dinâmica(tabela dinâmica).

SELECIONE País, ShipperName, SalesPerson SUM (ExtendedPrice) FROM Faturas

GRUPO POR PAÍS, Nome do Remetente, Ano

Com base nos resultados desta consulta, um cubo tridimensional pode ser construído (Fig. 1).

Adicionando opções adicionais para análise, você pode criar um cubo com teoricamente qualquer número de dimensões e, junto com as somas, as células do cubo OLAP podem conter os resultados do cálculo de outras funções agregadas (por exemplo, valores médios, máximos, mínimos, o número de registros da visão original correspondente a um determinado conjunto de parâmetros). Os campos a partir dos quais os resultados são calculados são chamados de medidas de cubo.

Hierarquias em dimensões

Suponhamos que estamos interessados ​​não apenas no valor total dos pedidos feitos pelos clientes em países diferentes, mas também o custo total dos pedidos feitos por clientes em diferentes cidades do mesmo país. Nesse caso, você pode aproveitar o fato de os valores plotados nos eixos possuírem diferentes níveis de detalhe – isso é descrito dentro do conceito de hierarquia de mudanças. Digamos que os países estejam localizados no primeiro nível da hierarquia e as cidades no segundo. Observe que a partir do SQL Server 2000, os serviços de análise suportam as chamadas hierarquias não balanceadas, que contêm, por exemplo, membros cujos “filhos” não estão contidos em níveis adjacentes da hierarquia ou estão faltando para alguns membros da mudança. Exemplo típico tal hierarquia - levando em consideração o fato de que em diferentes países podem ou não existir unidades administrativo-territoriais, como um estado ou região localizada na hierarquia geográfica entre países e cidades (Fig. 2).

Note-se que recentemente tem sido comum distinguir hierarquias típicas, por exemplo aquelas que contêm dados geográficos ou temporais, e também apoiar a existência de várias hierarquias numa só dimensão (em particular, para o ano civil e fiscal).

Criando cubos OLAP no SQL Server 2005

Os cubos do SQL Server 2005 são criados usando o SQL Server Business Intelligence Development Studio. Esta ferramenta é uma versão especial Estúdio Visual 2005, projetado para resolver esta classe de problemas (e se você já possui um ambiente de desenvolvimento instalado, a lista de modelos de projetos é reabastecida com projetos projetados para criar soluções baseadas em SQL Sever e seus serviços analíticos). Em particular, o modelo de projeto do Analysis Services foi projetado para criar soluções baseadas em serviços analíticos (Fig. 3).

Para criar um cubo OLAP, primeiro você precisa decidir quais dados ele irá formar. Na maioria das vezes, os cubos OLAP são construídos com base em data warehouses relacionais com esquemas estrela ou floco de neve (falamos sobre eles na parte anterior do artigo). O pacote SQL inclui um exemplo de tal armazenamento - o banco de dados AdventureWorksDW, para usá-lo como fonte, você deve encontrar a pasta Data Sources no Solution Explorer, selecionar o item de menu de contexto New Data Source e responder sequencialmente às perguntas do assistente correspondente ( Figura 4).

A seguir, é recomendado criar uma Visualização da Fonte de Dados na qual o cubo será criado. Para fazer isso, você precisa selecionar o item de menu de contexto apropriado na pasta Data Source Views e responder consistentemente às perguntas do assistente. O resultado dessas ações será um esquema de dados, com o qual será construída uma representação das fontes de dados, e no esquema resultante, em vez dos originais, você poderá especificar nomes de tabelas “amigáveis” (Fig. 5) .

O cubo assim descrito pode ser transferido para o servidor de serviços analíticos selecionando a opção Deploy no menu de contexto do projeto e visualizando seus dados (Fig. 7).

Muitos recursos são usados ​​atualmente na criação de cubos nova versão SQL Server, como a exibição da fonte de dados. A descrição dos dados de origem para a construção de um cubo, bem como a descrição da estrutura do cubo, agora é feita utilizando a ferramenta Visual Studio familiar a muitos desenvolvedores, o que é uma vantagem significativa da nova versão deste produto - o o estudo de novas ferramentas por desenvolvedores de soluções analíticas, neste caso, é minimizado.

Observe que no cubo criado você pode alterar a composição das medidas, excluir e adicionar atributos de dimensão e adicionar atributos calculados de membros de dimensão com base em atributos existentes (Fig. 8).

Arroz. 8. Adicione um atributo calculado

Além disso, os cubos do SQL Server 2005 podem agrupar ou classificar automaticamente os membros da dimensão por valor de atributo, definir relacionamentos entre atributos, implementar relacionamentos muitos-para-muitos, determinar as principais métricas de negócios e muito mais (saiba mais sobre como todas essas etapas podem ser executadas). encontrado no Tutorial do SQL Server Analysis Services sistema de ajuda deste produto).

Nas partes subsequentes desta publicação, continuaremos a explorar os serviços analíticos do SQL Server 2005 e descobrir o que há de novo na área de suporte à Mineração de Dados.

Em geral, todo especialista sabe o que é OLAP hoje. Pelo menos, os conceitos de “OLAP” e “dados multidimensionais” estão firmemente conectados em nossas mentes. No entanto, o fato de este tema estar sendo levantado novamente, espero, será aprovado pela maioria dos leitores, pois para que a ideia de algo não fique desatualizada com o tempo, é necessário comunicar-se periodicamente com pessoas inteligentes ou leia artigos em uma boa publicação...

Armazéns de dados (lugar do OLAP em estrutura de informação empresas)

O termo "OLAP" está intimamente ligado ao termo "data warehouse" (Data Warehouse).

Aqui está a definição formulada pelo “pai fundador” do data warehousing, Bill Inmon: “Um data warehouse é uma coleção de dados imutável, específica de domínio e com limite de tempo para apoiar a tomada de decisões de gerenciamento”.

Os dados entram no armazenamento de sistemas operacionais(sistemas OLTP), que são projetados para automatizar processos de negócios. Além disso, o repositório pode ser reabastecido a partir de fontes externas, como relatórios estatísticos.

Por que construir data warehouses - afinal, eles contêm informações obviamente redundantes que já “vivem” em bancos de dados ou arquivos do sistema operacional? A resposta pode ser breve: é impossível ou muito difícil analisar diretamente os dados dos sistemas operacionais. Isto se deve a vários motivos, incluindo a fragmentação dos dados, seu armazenamento em diferentes formatos de SGBD e em diferentes “cantos” rede corporativa. Mas mesmo que uma empresa armazene todos os seus dados num servidor de base de dados central (o que é extremamente raro), um analista quase certamente não compreenderá as suas estruturas complexas e por vezes confusas. O autor tem uma experiência bastante triste ao tentar “alimentar” analistas famintos com dados “brutos” de sistemas operacionais - acabou sendo “demais para eles”.

Assim, o objetivo do repositório é fornecer as “matérias-primas” para análise em um só lugar e em uma estrutura simples e compreensível. Ralph Kimball, no prefácio de seu livro The Data Warehouse Toolkit, escreve que se, após a leitura de todo o livro, o leitor compreender apenas uma coisa - a saber, que a estrutura do warehouse deve ser simples - o autor considerará sua tarefa concluída .

Há mais um motivo que justifica o surgimento de um armazenamento separado - consultas analíticas complexas de informações operacionais retardam o trabalho atual da empresa, bloqueando tabelas por muito tempo e apreendendo recursos do servidor.

Na minha opinião, um repositório não significa necessariamente um acúmulo gigantesco de dados - o principal é que seja conveniente para análise. De modo geral, existe um termo separado para pequenas instalações de armazenamento - Data Marts (quiosques de dados), mas em nossa prática russa você não o ouve com frequência.

OLAP - uma ferramenta de análise conveniente

Centralização e estruturação conveniente não são tudo o que um analista precisa. Ele ainda precisa de uma ferramenta para visualizar e visualizar informações. Os relatórios tradicionais, mesmo aqueles criados em um único repositório, carecem de uma coisa: flexibilidade. Eles não podem ser "torcidos", "expandidos" ou "recolhidos" para obter a visualização desejada dos dados. Claro, você pode ligar para um programador (se ele quiser vir), e ele (se não estiver ocupado) fará um novo relatório com rapidez suficiente - digamos, dentro de uma hora (estou escrevendo isso e não acredito eu mesmo - isso não acontece tão rápido na vida; vamos dar a ele três horas). Acontece que um analista não pode testar mais do que duas ideias por dia. E ele (se for um bom analista) pode ter várias ideias desse tipo por hora. E quanto mais “fatias” e “seções” de dados o analista vê, mais ideias ele tem, o que, por sua vez, exige cada vez mais “fatias” para verificação. Se ao menos ele tivesse uma ferramenta que lhe permitisse expandir e recolher dados de forma simples e conveniente! OLAP atua como tal ferramenta.

Embora o OLAP não seja um atributo necessário de um data warehouse, ele está sendo cada vez mais utilizado para analisar as informações acumuladas no warehouse.

Os componentes incluídos em um repositório típico são mostrados na Fig. 1.

Arroz. 1. Estrutura do armazém de dados

Os dados operacionais são coletados de diversas fontes, limpos, integrados e armazenados em um armazenamento relacional. Além disso, já estão disponíveis para análise através de diversas ferramentas de relatório. Em seguida, os dados (no todo ou em parte) são preparados para análise OLAP. Eles podem ser carregados em um banco de dados OLAP especial ou armazenados em um armazenamento relacional. Seu elemento mais importante são os metadados, ou seja, informações sobre a estrutura, posicionamento e transformação dos dados. Graças a eles, é garantida a interação eficaz de vários componentes de armazenamento.

Resumindo, podemos definir OLAP como um conjunto de ferramentas para análise multidimensional de dados acumulados em um warehouse. Teoricamente, as ferramentas OLAP podem ser aplicadas diretamente aos dados operacionais ou às suas cópias exatas (para não interferir nos usuários operacionais). Mas corremos o risco de pisar no rake já descrito acima, ou seja, começar a analisar dados operacionais que não são diretamente adequados para análise.

Definição e conceitos básicos de OLAP

Primeiro, vamos decifrar: OLAP é Processamento Analítico Online, ou seja, análise de dados operacionais. Os 12 princípios definidores do OLAP foram formulados em 1993 por E. F. Codd, o “inventor” dos bancos de dados relacionais. Sua definição foi posteriormente reformulada no chamado teste FASMI, que exige que o aplicativo OLAP forneça a capacidade de analisar rapidamente informações multidimensionais compartilhadas ().

Teste FASMI

Rápido(Rápido) – a análise deve ser realizada com igual rapidez em todos os aspectos da informação. O tempo de resposta aceitável é de 5 segundos ou menos.

Análise(Análise) - deve ser possível realizar tipos básicos de análises numéricas e estatísticas, pré-definidas pelo desenvolvedor da aplicação ou definidas livremente pelo usuário.

Compartilhado(Compartilhado) – muitos usuários devem ter acesso aos dados, embora seja necessário controlar o acesso às informações confidenciais.

Multidimensional(Multidimensional) é a característica principal e mais essencial do OLAP.

Informação(Informações) - a aplicação deve ser capaz de acessar qualquer informação necessária, independente do seu volume e local de armazenamento.

OLAP = Visão Multidimensional = Cubo

OLAP fornece ferramentas convenientes e rápidas de acesso, visualização e análise informações comerciais. O usuário recebe um modelo de dados natural e intuitivo, organizando-os na forma de cubos multidimensionais (Cubos). Os eixos do sistema de coordenadas multidimensional são os principais atributos do processo de negócio analisado. Por exemplo, para vendas pode ser produto, região, tipo de comprador. O tempo é usado como uma das dimensões. Nas intersecções dos eixos - dimensões (Dimensões) - encontram-se dados que caracterizam quantitativamente o processo - medidas (Medidas). Podem ser volumes de vendas em peças ou em termos monetários, saldos de estoque, custos, etc. Um usuário que analisa as informações pode “cortar” o cubo em diferentes direções, obter resumo (por exemplo, por ano) ou, inversamente, detalhado (por semana) informações e realizar outras manipulações que lhe vierem à mente durante o processo de análise.

Como medidas no cubo tridimensional mostrado na Fig. 2, são usados ​​os valores de vendas e como dimensões tempo, produto e loja. As medições são apresentadas em níveis específicos de agrupamento: os produtos são agrupados por categoria, as lojas por país e os dados de tempo de transação por mês. Um pouco mais tarde consideraremos os níveis de agrupamento (hierarquia) com mais detalhes.

Arroz. 2. Exemplo de cubo

"Cortando" um cubo

Mesmo um cubo tridimensional é difícil de exibir na tela de um computador para que os valores das medidas de interesse fiquem visíveis. O que podemos dizer sobre cubos com mais de três dimensões? Para visualizar os dados armazenados em um cubo, como regra, são usadas visualizações bidimensionais familiares, ou seja, tabulares, com linhas hierárquicas complexas e cabeçalhos de coluna.

Uma representação bidimensional de um cubo pode ser obtida “cortando-o” em um ou mais eixos (dimensões): fixamos os valores de todas as dimensões, exceto duas, e obtemos uma tabela bidimensional regular. O eixo horizontal da tabela (cabeçalhos das colunas) representa uma dimensão, o eixo vertical (cabeçalhos das linhas) representa outra e as células da tabela representam os valores das medidas. Neste caso, um conjunto de medidas é na verdade considerado como uma das dimensões - ou selecionamos uma medida para exibir (e então podemos colocar duas dimensões nos cabeçalhos de linha e coluna), ou mostramos várias medidas (e então uma das medidas). os eixos da tabela serão ocupados pelos nomes das medidas, e os demais - valores da única dimensão “sem cortes”).

Dê uma olhada na fig. 3 - aqui está uma fatia bidimensional do cubo para uma medida - Vendas unitárias (peças vendidas) e duas dimensões "sem cortes" - Loja (Loja) e Tempo (Tempo).

Arroz. 3. Fatia de cubo 2D para uma medida

Na Fig. A Figura 4 mostra apenas uma dimensão “sem cortes” – Loja, mas apresenta os valores de diversas medidas – Vendas Unitárias (unidades vendidas), Vendas na Loja (valor da venda) e Custo da Loja (despesas da loja).

Arroz. 4. Fatia de cubo 2D para múltiplas medidas

Uma representação bidimensional de um cubo também é possível quando mais de duas dimensões permanecem “sem cortes”. Neste caso, duas ou mais dimensões do cubo “cortado” serão colocadas nos eixos da fatia (linhas e colunas) - ver Fig. 5.

Arroz. 5. Fatia de cubo 2D com múltiplas dimensões em um eixo

Etiquetas

Os valores "colocados" ao longo das dimensões são chamados de membros ou rótulos. Os rótulos são usados ​​tanto para “cortar” o cubo quanto para limitar (filtrar) os dados selecionados – quando em uma dimensão que permanece “sem cortes”, não estamos interessados ​​em todos os valores, mas em um subconjunto deles, por exemplo, três cidades entre várias dezenas. Os valores dos rótulos aparecem na visualização do cubo 2D como títulos de linhas e colunas.

Hierarquias e níveis

Os rótulos podem ser combinados em hierarquias que consistem em um ou mais níveis. Por exemplo, os rótulos da dimensão Loja são naturalmente agrupados em uma hierarquia com níveis:

País

Estado

Cidade

Loja.

Os valores agregados são calculados de acordo com os níveis de hierarquia, por exemplo, volume de vendas para os EUA (nível "País") ou para a Califórnia (nível "Estado"). É possível implementar mais de uma hierarquia em uma dimensão - digamos, para tempo: (Ano, Trimestre, Mês, Dia) e (Ano, Semana, Dia).

Arquitetura de aplicativos OLAP

Tudo o que foi dito acima sobre OLAP diz respeito essencialmente à apresentação multidimensional de dados. A forma como os dados são armazenados, grosso modo, não diz respeito nem ao usuário final nem aos desenvolvedores da ferramenta que o cliente utiliza.

A multidimensionalidade em aplicações OLAP pode ser dividida em três níveis:

• Representação de dados multidimensionais – ferramentas de usuário final que fornecem visualização e manipulação multidimensional de dados; A camada de representação multidimensional abstrai a estrutura física dos dados e os trata como multidimensionais.
• O processamento multidimensional é um meio (linguagem) para formular consultas multidimensionais (a linguagem relacional tradicional SQL é inadequada aqui) e um processador que pode processar e executar tal consulta.
• O armazenamento multidimensional é um meio de organizar fisicamente os dados que garante a execução eficiente de consultas multidimensionais.

Os dois primeiros níveis são obrigatórios em todas as ferramentas OLAP. O terceiro nível, embora generalizado, não é necessário, uma vez que os dados para uma representação multidimensional também podem ser extraídos de estruturas relacionais comuns; O processador de consultas multidimensionais, neste caso, traduz consultas multidimensionais em consultas SQL que são executadas pelo SGBD relacional.

Produtos OLAP específicos, via de regra, são uma ferramenta de representação de dados multidimensional, um cliente OLAP (por exemplo, tabelas dinâmicas no Excel 2000 da Microsoft ou ProClarity da Knosys) ou um servidor multidimensional DBMS, um servidor OLAP (por exemplo, Oracle Express Server ou Microsoft OLAP Services).

A camada de processamento multidimensional geralmente é incorporada ao cliente OLAP e/ou servidor OLAP, mas pode ser isolada em sua forma pura, como o componente Pivot Table Service da Microsoft.

Aspectos técnicos do armazenamento de dados multidimensionais

Conforme mencionado acima, as ferramentas de análise OLAP também podem extrair dados diretamente de sistemas relacionais. Essa abordagem era mais atraente na época em que os servidores OLAP não eram incluídos nas listas de preços dos principais fabricantes de SGBD. Mas hoje, Oracle, Informix e Microsoft oferecem servidores OLAP completos, e até mesmo aqueles gerentes de TI que não gostam de criar um “zoológico” de software em suas redes fabricantes diferentes, pode comprar (mais precisamente, fazer a solicitação correspondente à administração da empresa) um servidor OLAP da mesma marca do servidor de banco de dados principal.

Os servidores OLAP, ou servidores de banco de dados multidimensionais, podem armazenar seus dados multidimensionais de diferentes maneiras. Antes de examinarmos esses métodos, precisamos falar sobre isso aspecto importante, como armazenamento de unidades. O fato é que em qualquer data warehouse - comum e multidimensional - junto com dados detalhados extraídos de sistemas operacionais, também são armazenados indicadores resumidos (indicadores agregados, agregações), como a soma dos volumes de vendas por mês, por categoria de bens, etc. Os agregados são armazenados explicitamente com o único propósito de acelerar a execução das solicitações. Com efeito, por um lado, via de regra, acumula-se no armazém uma quantidade muito grande de dados e, por outro lado, os analistas, na maioria dos casos, não se interessam por indicadores detalhados, mas sim generalizados. E se milhões de vendas individuais tivessem que ser somadas a cada vez para calcular o total de vendas do ano, a velocidade provavelmente seria inaceitável. Portanto, ao carregar os dados em um banco de dados multidimensional, todos os indicadores totais ou parte deles são calculados e salvos.

Mas, como você sabe, você tem que pagar por tudo. E você tem que pagar pela velocidade de processamento de solicitações de dados resumidos, aumentando o volume de dados e o tempo necessário para carregá-los. Além disso, um aumento no volume pode tornar-se literalmente catastrófico - num dos publicados testes padronizados um cálculo completo de agregados para 10 MB de dados originais exigiu 2,4 GB, ou seja, os dados cresceram 240 vezes! O grau de “inchaço” dos dados no cálculo dos agregados depende do número de dimensões do cubo e da estrutura dessas dimensões, ou seja, a razão entre o número de “pais” e “filhos” por níveis diferentes medições. Para resolver o problema de armazenamento de agregados, por vezes são utilizados esquemas complexos, que permitem obter um aumento significativo no desempenho da consulta ao calcular nem todos os agregados possíveis.

Agora, sobre as várias opções para armazenar informações. Tanto os dados granulares quanto os agregados podem ser armazenados em estruturas relacionais ou multidimensionais. O armazenamento multidimensional permite tratar os dados como uma matriz multidimensional, o que garante cálculos igualmente rápidos de indicadores totais e diversas transformações multidimensionais ao longo de qualquer uma das dimensões. Há algum tempo, os produtos OLAP suportavam armazenamento relacional ou multidimensional. Hoje, via de regra, o mesmo produto oferece esses dois tipos de armazenamento, além de um terceiro tipo - misto. Os seguintes termos se aplicam:

• MOLAP(OLAP Multidimensional) - tanto os dados detalhados quanto os agregados são armazenados em um banco de dados multidimensional. Neste caso, obtém-se a maior redundância, uma vez que os dados multidimensionais contêm completamente dados relacionais.
• ROLAP(OLAP Relacional) - os dados detalhados permanecem onde originalmente “viviam” - no banco de dados relacional; os agregados são armazenados no mesmo banco de dados em tabelas de serviço especialmente criadas.
• HOLAP(OLAP híbrido) – os dados detalhados permanecem no local (em um banco de dados relacional) e os agregados são armazenados em um banco de dados multidimensional.

Cada um desses métodos tem suas vantagens e desvantagens e deve ser usado dependendo das condições - o volume de dados, o poder do SGBD relacional, etc.

Ao armazenar dados em estruturas multidimensionais, existe um problema potencial de "inchaço" devido ao armazenamento de valores vazios. Afinal, se em matriz multidimensional Se o espaço for reservado para todas as combinações possíveis de rótulos de medição, mas apenas uma pequena parte for realmente preenchida (por exemplo, vários produtos são vendidos apenas em um pequeno número de regiões), então a maior parte do cubo estará vazia, embora o o espaço estará ocupado. Os produtos OLAP modernos podem lidar com esse problema.

Continua. No futuro, falaremos sobre produtos OLAP específicos produzidos pelos principais fabricantes.

Análise