Business Intelligence

Criação de uma base de dados para uma solução de BI

Derek Comingore

Download do código disponível em: SampleCode2009_08.exe(151 KB)

Em uma visão geral:

• Seguindo o modelo dimensional para criar um mart dados
• Mapas de desenvolvimento de processos de ETL e dados
• Usando serviços de integração de ETL
• Criando um projeto Integration Services no APOSTAS

Conteúdo

Noções básicas sobre requisitos
Usando o modelo dimensional
Criando uma matriz de barramento
Criar um mapa de dados
Criando Mart dados
Implantando Mart dados
Desenvolver processos ETL
Criando um projeto do SSIS de APOSTAS
Criando conexões de dados comuns
Desenvolvendo o ETL para dimensões
Definindo um fluxo de dados para extrair e carregar
Desenvolver pacotes adicionais
Desenvolvendo o pacote de tabela Internet fatos de vendas
Etapas finais

Dados é a parte mais importante de qualquer solução de (BI) inteligência comercial. Conforme explicado na Stacia Misner " Planejando a solução de BI Microsoft primeiro"Obtendo dados em sua solução de BI e mantendo-lo depois que ele está lá envolvem várias etapas. Profissionais de BI se referir a essas etapas como extrair, transformar e carregar (ETL) processos. Mesmo se você não pretende buscar uma carreira focalizada no BI, você ainda poderá tirar proveito de ETL técnicas e ferramentas para gerenciar os dados que você precisa informar as decisões diárias feitas no seu trabalho. Neste artigo, mostrarei como projetar e criar um mart dados simples para ilustrar como você pode usar o SSIS (SQL Server 2008 Integration Services) para executar o ETL para sua própria solução de BI.

Noções básicas sobre requisitos

Como com qualquer projeto IT, a melhor maneira de iniciar um projeto ETL é compreender os requisitos gerais de solução de BI que você deseja criar e trabalhar sua maneira de determinar a melhor maneira oferecer suporte a esses requisitos para os dados. O primeiro artigo da série preparar o terreno para a solução de BI empresa fictícia Adventure Works precisa descrevendo os requisitos analíticos como várias perguntas que a empresa precisa responder. Essas perguntas realçar a necessidade de Adventure Works compreender suas vendas de produtos de perspectivas diferentes: lucratividade canal de distribuição (revendedores ou na Internet), a alteração na demanda por produtos com o tempo e a diferença entre as vendas reais e previsões de vendas por produto, vendedor, região geográfica e tipo do revendedor. As respostas a essas perguntas ajudarão a Adventure Works decide o canal de distribuição que a empresa deve se concentrar para aumentar os lucros, como adaptar seus processos de fabricação para atender melhor às exigem e como as alterações na estratégia de vendas podem ajudar a empresa atender às suas metas de vendas. Você verá como os dados oferece suporte ao responder essas perguntas de negócios quando você adiciona SSRS (SQL Server Reporting Services) a solução de BI.

Antes de começar Projetando mart dados para o Adventure Works com esses requisitos em mente, desejo modelo precisam as informações de uma perspectiva comercial. Em outras palavras, design de um mart de dados deve se basear na forma como os usuários pedirá suas perguntas em vez da maneira como os dados provêm de fontes de dados.

Você precisará de banco de dados de exemplo Adventure Works OLTP do SQL Server 2008 para exemplos de código encontrados neste artigo.

Usando o modelo dimensional

Um mart dados normalmente é criada usando um design de modelo dimensional, que é um esquema de banco de dados bem adequado para análise. (Um excelente recurso para aprender sobre modelagem dimensional é kimballgroup.com.) Um modelo dimensional apresenta os dados de forma que é familiar aos usuários e ajuda a criar estruturas de dados que são otimizadas para consultar grandes volumes de dados. Você pode obter essa otimização, denormalizing os dados, que ajuda o mecanismo de banco de dados Selecione rapidamente e agregar com eficiência uma grande quantidade de dados em tempo de consulta. O esquema desnormalizado para a solução Adventure Works, incluirá dois tipos de tabelas: dimensão e de fatos. Uma tabela de dimensão armazena informações sobre entidades comerciais e objetos, tais como revendedores ou produtos. Uma tabela de fatos, que usarei para os dados numéricos de vendas preciso agregar, contém medidas numéricas e as chaves relacionadas fatos às tabelas de dimensão. Explicarei mais sobre tabelas de fatos neste artigo.

Você pode implementar as tabelas de um modelo tridimensional em dois tipos de esquemas: estrela e floco de neve. Em termos simples, um esquema estrela usa uma tabela para cada dimensão, uma consulta por sua vez está relacionado a uma tabela de fatos com uma associação única. Um esquema floco de neve usa duas ou mais tabelas para cada dimensão e, conseqüentemente, requer mais associações em uma consulta para ver todos os dados. Essa coleção de uniões em cascata significa que consultas freqüentemente executar mais lentamente em um esquema floco de neve que em um esquema estrela. Para fins deste artigo, eu irá manter o design simples e usar um esquema estrela.

Criando uma matriz de barramento

Como parte do processo de modelagem dimensional, crio uma matriz de barramento para ajudar a identificar as dimensões associadas com vendas, o foco da solução de BI para Adventure Works. Lembre-se que a Adventure Works tem dois canais de vendas: substancial de vendas para revendedores e vendas individuais através da Internet. Eu também usar a matriz de barramento para identificar a relação de cada dimensão a um ou ambos os tipos de vendas. Figura 1 mostra minha matriz de barramento de amostra do Adventure Works Sales.

Minha próxima etapa é determinar as medidas para a solução. Medidas são os valores numéricos que são necessários para análise. Eles podem vir diretamente da fonte, como dólares de vendas ou os custos de produto, ou ser derivados por meio de um cálculo, como multiplicar uma quantidade por uma quantidade de dólar por um período estendido de venda. Também preciso decidir quais atributos para incluir cada dimensão. Atributos são os elementos individuais em uma dimensão (correspondente à colunas em uma tabela) que você pode usar para dados de grupo ou filtro para análise, como país na dimensão de território de venda ou ano na dimensão de data. Eu não detalhes cada medida identificada ou atributo dimensional neste artigo — apenas perceber que o processo de identificação precisa ocorrer.

Criar um mapa de dados

Antes de criar tabelas físicas de mart dados, que preciso fazer algum planejamento adicional. Especificamente, preciso criar um documento de mapa de dados para mapear cada coluna de destino no esquema mart dados para as colunas no sistema de origem Adventure Works OLTP (o banco de AdventureWorks2008 dados que você pode fazer o download e instalar conforme descrito no artigo de Stacia Misner em p. 31). Você pode usar vários aplicativos para criar um mapa de dados. O formato não é tão importante quanto o conteúdo. Eu gostaria de desenvolver os mapas de dados no Microsoft Office Excel. Figura 2 mostra a guia DimProduct criado no meu mapa de dados. Também criei dados DimCustomer e FactInternetSales mapeia. Cada planilha na pasta de trabalho representa uma das tabelas no meu mart de dados. Em cada folha, simplesmente tenho duas colunas: uma para a coluna de origem e outra para a coluna de destino.

Figura 2 DimProduct dados mapeamento guia

Cada tabela de dimensão (exceto para a dimensão de data) possui uma chave primária conhecida como uma chave substituta (geralmente uma coluna de identidade). Uma vantagem de usar chaves substitutas é que você pode combinar dados de vários sistemas sem o risco de chaves duplicadas.

As tabelas de dimensão tem também uma coluna de chave alternativa. Essas chaves alternativas representam chaves naturais, às vezes chamadas chaves comerciais. Esses são identificadores do sistema de origem. Por exemplo, a coluna CustomerAlternateKey na dimensão cliente mapeia para o campo de banco de dados OLTP do Adventure Works AccountNumber na tabela Sales.Customer. Armazenando essas chaves na tabela de dimensões, eu ter uma maneira para comparar registros já na dimensão com registros extraídos da fonte de sempre que executar um processo de ETL para cada dimensão.

Quase todos os dados mart inclui uma dimensão de data porque análise comercial geralmente compara as alterações em medidas por data, semana, mês, trimestre ou ano. A dimensão de data quase nunca vem de um sistema de origem, portanto, os motivos para usar uma chave IDENTITY–based do SQL Server não se aplicam. Em vez disso, usarei o que é chamado de chave inteligente com um formato de aaaammdd armazenado como uma coluna do SQL Server inteiro. Uma chave inteligente é uma chave é gerada a partir lógica ou scripts em oposição aos sendo uma chave de incremento automático, como uma coluna IDENTITY no SQL Server.

Tenha em mente que a dimensão de data geralmente não mapear para uma tabela de origem. Em vez disso, usarei um script para gerar os dados para carregar registros na tabela.

Como os processos ETL necessários para meu esquema pequena são bastante simples, meu mapa de dados é bem como está. Em um projeto real-world, eu poderia anotar o mapa de dados para destacar quando transformações complexas são necessárias.

Criando Mart dados

Agora que modelagem lógica for concluída, preciso criar um banco de dados host para essas tabelas e tabelas físicas que carregarão os processos ETL. Usarei um script T-SQL básico para criar meu banco de dados e sua dimensão associado e tabelas de fatos. Você pode encontrar todo o script T-SQL no download de acompanhamento para a solução de BI de exemplo no) Downloads de código de 2009).

Para os fins deste artigo, eu criar apenas um subconjunto do esquema de mart de dados de vendas inteiro para que pode cobrir todo o processo ETL no SSIS. Na versão menor do esquema, inclua apenas as medidas OrderQuantity e SalesAmount para a tabela de fatos de vendas de Internet. Além disso, meu esquema menor inclui uma versão simplificada das tabelas de dimensão do cliente, produto e data.

Implantando Mart dados

Para implantar mart dados, simplesmente preciso executar o T-SQL que escrevi anteriormente para instanciar as novas tabelas em uma instância do SQL Server. Para executar o T-SQL, eu iniciar o SQL Server Management Studio (SSMS) clicando em Start\All Programs\Microsoft SQL Server 2008\SQL Server Management Studio. Uma vez aberto o SSMS, digite o nome de minha instância do SQL Server designado e, em seguida, clique em conectar usando autenticação do Windows na caixa de diálogo de conexão. Use o SQL Server Management Studio para abrir o arquivo TECHNET_AW2008SalesDataMart.sql e execute o script.

Desenvolver processos ETL

Projetar e desenvolver processos de ETL é a próxima etapa na criação de uma solução de BI. Para revisar, ETL inclui todos os processos tecnológicos pelos quais dados são extraídos de fontes de dados, transformados e, em seguida, carregado em um repositório de destino. Normalmente, processos de ETL em soluções de BI extrair dados de arquivos simples e bancos de dados operacionais OLTP, alteram os dados para ajustar um modelo tridimensional (por exemplo, um esquema estrela) e carregar os dados resultantes em um mart de dados.

Criando um projeto do SSIS de APOSTAS

A primeira etapa no desenvolvimento de um processo de ETL é para criar um novo projeto de APOSTAS (Business Intelligence Development Studio). APOSTAS vem com o SQL Server 2008 e é instalado quando você selecionar a opção componentes da estação de trabalho durante o processo de instalação. APOSTAS inclui modelos de projeto para SSIS, SSAS e SSRS. Ele também suporta integração de controle de origem, exatamente como o Visual Studio.

Para iniciar APOSTAS, vá para Start\Programs\Microsoft SQL Server 2008\Business Intelligence Development Studio e selecione File\New Project. Você deve ver o modelo de novo projeto mostrado na Figura 3 .

Figura 3 novo modelo de projeto no APOSTAS 2008

Selecione o projeto do Integration Services no painel de modelos, digite ssis_TECHNET_AW2008 na caixa de texto nome e, em seguida, clique em OK. APOSTAS agora devem exibir um projeto aberto do SSIS.

Criando conexões de dados comuns

Outro recurso interessante no SSIS 2008 é a capacidade para criar conexões de fonte de dados fora de pacotes individuais. Você pode definir uma conexão de fonte de dados uma vez e, em seguida, referência a ele em um ou mais pacotes do SSIS dentro da solução. Para saber mais sobre como criar fontes de dados de APOSTAS, consulte" Como: definir uma fonte de dados usando o Assistente de fonte de dados (Analysis Services) ".

Criar duas novas conexões de fonte de dados: um para o banco de dados TECHNET_AW2008SalesDataMart e outro para o banco de dados OLTP AdventureWorks2008. Nomeie as conexões de fonte de dados AW_DM.ds e AW_OLTP.ds, respectivamente.

Desenvolvendo o ETL para dimensões

O ETL para carregar a dimensão do produto é muito simples. Preciso extrair os dados da tabela Adventure Works Production.Product e carregar os dados no banco de dados TECHNET_AW2008SalesDataMart. Primeiro, preciso renomear o pacote padrão que APOSTAS criou para meu projeto do SSIS. (Um pacote é um contêiner para todas as etapas no fluxo de trabalho SSIS executará.) Clique com o botão direito do mouse no pacote padrão no Solution Explorer e selecione Renomear. Digite DIM_PRODUCT.dtsx e, em seguida, pressione ENTER.

Em seguida, preciso criar o pacote local gerenciadores de conexão usando fontes de dados predefinidos. Crie dois gerenciadores de conexão nova referência as fontes de dados criadas anteriormente.

Definindo um fluxo de dados para extrair e carregar

SSIS inclui uma tarefa de fluxo de dados que encapsula tudo o que precisa para implementar o ETL para uma dimensão simples. Basta arrastar uma tarefa de fluxo de dados do Toolbox na superfície do designer de fluxo de controle e renomeie a tarefa para EL (para extrair e carga). Clique a tarefa Data Flow no designer com o botão direito do mouse e selecione Editar. APOSTAS agora exibe o designer de fluxo de dados.

A parte de extrair o pacote de dimensão do produto precisa consultar a tabela AdventureWorks2008 Production.Product. Para configurar essa tarefa, eu arraste um componente da fonte de banco de dados OLE do Toolbox para a superfície designer de fluxo de dados e, em seguida, renomeie o componente da fonte de banco de dados OLE para AW_OLTP.

Em seguida, defino a parte da carga do meu pacote para carregar em mart dados. Eu simplesmente arraste uma nova instância do componente de destino OLE DB para a superfície de designer de fluxo de dados e renomeie-AW_OLTP. Em seguida, clique no componente OLE DB fonte (AW_OLTP) e arraste a seta verde que aparece na fonte de banco de dados OLE para o componente AW_DM OLE DB destino para conectar os dois componentes.

Neste ponto, adicionei os componentes necessários ao fluxo de dados, mas ainda preciso configurar cada componente para que o SSIS sabe como deseja extrair e carregar os dados. Clique o componente AW_DM OLE DB destino com o botão direito do mouse e selecione Editar. Com o OLE DB destino editor aberto, verifique se que AW_DM é selecionada como o Gerenciador de conexão OLE DB. Em seguida, eu expanda o nome da tabela drop-down e selecione a tabela dbo.DimProduct. Finalmente, eu clique na guia mapeamentos para confirmar que os mapeamentos estão corretos. Clique em OK para confirmar os mapeamentos. Esse processo é muito mais fácil quando você tem um mapa de dados pronto para referência, especialmente se você estiver trabalhando com tabelas grandes. Pacote ETL a dimensão de produto está concluída.

Você pode executar o pacote dentro de APOSTAS facilmente. Para testar o pacote de dimensão do produto, abrir o pacote e pressione F5.

Desenvolver pacotes adicionais

Crio o pacote de dimensão de cliente da mesma maneira que fiz o pacote de produto. Eu não reiterar as etapas que você precisa seguir para criar esse novo pacote. Você deve tentar produzi-lo sozinho. Observe que esse pacote usa uma coluna de dados digitados em XML (Person.Person.Demographics) na fonte, o que requer que você analisar saída os atributos individuais demográficos relacionados. Para analisar valores individuais de uma coluna de dados digitados XML do SQL Server, você pode aproveitar um XQuery com o método do tipo de dados XML nativo valor (). Nome do pacote concluído DIM_CUSTOMER.dtsx.

Desenvolvimento de um pacote do SSIS para a dimensão de data é opcional. Como esta dimensão geralmente não têm dados de origem, a maneira mais fácil de carregá-lo é usando um script T-SQL básico. Você pode encontrar o script usado na solução concluída.

Desenvolvendo o pacote de tabela Internet fatos de vendas

O pacote de tabela de fatos Internet vendas consultas para todas as vendas de Internet e retorna as vendas divididas por produto, cliente e a data (ou seja, data do pedido). Ao contrário de um pacote de dimensão, um pacote de tabela de fatos requer uma etapa adicional para pesquisar as teclas inteligentes nas tabelas de dimensão correspondente antes de carregar os dados na tabela de fatos e substituto. Você pode criar um novo pacote e denomine FACT_INTERNET_SALES.dtsx.

A parte de extrair o pacote precisa consultar o banco de dados OLTP AdventureWorks2008 usando o código T-SQL mostrado na Figura 4 .

Figura 4 código T-SQL para Internet vendas por produto, data e cliente

SELECT
       P.ProductID
       ,CONVERT(INT,CONVERT( CHAR(4), YEAR(H.OrderDate) ) + RIGHT('0'+ CONVERT(VARCHAR(3), 
            MONTH(H.OrderDate) ),2) + RIGHT('0'+ CONVERT( VARCHAR(3), 
DAY(H.OrderDate) ),2)) AS OrderDateKey
       ,C.AccountNumber 
       ,SUM(D.OrderQty) AS OrderQuantity
       ,SUM(D.LineTotal) AS SalesAmount
FROM
       [Sales].[SalesOrderDetail] D
INNER JOIN
       [Sales].[SalesOrderHeader] H ON (D.SalesOrderID = H.SalesOrderID)
INNER JOIN
       [Production].[Product] P ON (D.ProductID = P.ProductID)
INNER JOIN
       [Sales].[Customer] C ON (H.CustomerID = C.CustomerID)
WHERE
        H.OnlineOrderFlag = 1
GROUP BY
        P.ProductID
        ,H.OrderDate 
        ,C.AccountNumber

Crie uma nova tarefa de fluxo de dados na superfície de fluxo de controle do pacote. Abra o designer de fluxo de dados e crie um componente da fonte de banco de dados OLE. Nome do componente AW_OLTP e use a consulta na Figura 4 como sua fonte. Esta consulta produz uma agregação (soma) para as medidas OrderQuantity e SalesAmount encontradas nas tabelas de vendas do Adventure Works.

Agora você precisa configurar uma transformação de pesquisa. Arraste duas novas instâncias do componente de transformação de pesquisa da caixa de ferramentas para a superfície de designer de fluxo de dados e nomeie-os produtos e clientes. Configurar primeiro (produto) para pesquisar ProductKey na tabela de dimensão produto associando AlternateKey da tabela de dimensão para o campo ProductID entrado da consulta de origem AW_OLTP.

Configurar o segundo um (cliente) para pesquisar CustomerKey na tabela de dimensão clientes associando AlternateKey da tabela de dimensão para o campo AccountNumber entrado da consulta de origem AW_OLTP.

Etapas finais

A etapa final é carregar os dados na tabela de fatos FactInternetSales, substituindo as chaves naturais para cada dimensão chaves substitutas a transformação de pesquisa encontrada. Arraste uma nova instância do componente de destino OLE DB e denomine AW_DM. Edite o componente OLE DB destino e selecione o Gerenciador de conexão AW_DM. Em seguida, selecione a tabela dbo.FactInternetSales e clique na guia mapeamentos. Certifique-se de que os mapeamentos de aparência como aqueles na Figura 5 . Clique em OK para concluir a lógica do pacote.

Figura 5 mapeamentos de destino do OLE DB para a tabela de SalesFact fato Internet

Para testar o pacote de fatos de vendas de Internet, abra o pacote de APOSTAS e pressione F5.

Agora você compreender os fundamentos de modelagem dimensional e criando pacotes criados pelo ETL com SSIS. O terceiro artigo desta série, você aprenderá a usar um mart dados preenchida para criar dimensões e cubos para um banco de dados do SSAS. Depois que você já criou um cubo, em seguida, você pode desenvolver um pacote do SSIS para atualizar esses objetos continuamente no banco de dados SSAS que sempre que os novos dados ao mart dados. SSIS mesmo pode preparar dados para exibição em um relatório do SSRS quando os requisitos de relatório não podem ser alcançados com uma única consulta. Como você pode ver, o SSIS pode fazer muito mais para ajudar a gerenciar sua solução de BI que apenas processamento do ETL.

Derek Comingore é um arquiteto sênior com ComFrame. Como MVP do SQL Server e um certificado profissional em várias tecnologias da Microsoft, incluindo BI do SQL Server, ele palestras em grupos de usuários locais e nacionais do SQL Server e é um autor publicado sobre tópicos do SQL Server. Derek se concentra na criação e fornecimento de classe empresarial data warehouse e soluções de BI com o SQL Server junto com os outros produtos de software Microsoft BI. Você pode acessar Derek em dcomingore@comframe.com.