none
Como popular tabela Fato

    Pergunta

  • Boa noite,

    Estou tendo dificuldades de fazer a carga dos dados para a tabela Fato a partir da tabelas dimensionais

    Alguém sabe me dizer como faço a carga a partir do código abaixo?

    Obrigado.

     create table Dim_Customer
    (
        sk_Customer integer identity(1,1),
        Name varchar (25),
        Country varchar(100),
        Telephone varchar(20)
    )
    go

    create table Dim_Product
    (
         sk_Product identity(1,1),
         ProductName varchar(50),
         CategoryName varchar(50)   
    )
    go

    create table Dim_Data
    (
        sk_data integer identity(1,1),
        day int,
        Week varchar (25),
        Month varchar (25),
        Year int 
    )
    go

    Create Table Fato_Sales
    (
        sk_Fato_Sales integer identity(1,1),
        sk_Customer integer,
        sk_Product integer,
        sk_data integer,
        Total_Sale money,
        Quantity int
    )
    go

    insert into Fato_Sales
        (
    sk_Fato_Sales, 
    sk_Customer, 
    sk_Product, 
    sk_data,  
    Total_Sale, 
    Quantity
        )   
         SELECT 
       sk_Customer, 
               sk_Product, 
               sk_data,  
               Total_Sale, 
               Quantity
        FROM 
      join ?
         Dim_Customer,
                 Dim_Product,
                 Dim_Data



    Obrigado.


                  
    terça-feira, 6 de março de 2018 01:27

Todas as Respostas

  • Fernando, a tabela Fato não é carregada a partir das dimensões mas sim de fontes externas de dados. Lembre-se de que as chaves das tabelas de origem devem ser convertidas nas chaves substitutas (surrogate keys) das dimensões. Poderia detalhar qual é a fonte dos dados a incluir no DW?

    e-mail       José Diz     Belo Horizonte, MG - Brasil



    • Editado José Diz quarta-feira, 7 de março de 2018 11:29
    quarta-feira, 7 de março de 2018 11:29
  • José,

    Podemos carregar sim uma tabela fato com as chaves primárias das dimensões, as melhores práticas nos dizem para fazer o carregamento através das fontes de dados externas.

    Neste caso a chave primária da tabela fato será a junção das chaves primárias da dimensões, não se esquecendo que não é necessário estabelecer ou melhor definir uma chave artificial.

    Quando fazemos esta escolha de usar as dimensões como fontes para carregar a tabela fato estamos assumindo o risco que pode existir algum dado incorreto ou não confiável.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 7 de março de 2018 18:57
  • José,
    Podemos carregar sim uma tabela fato com as chaves primárias das dimensões, as melhores práticas nos dizem para fazer o carregamento através das fontes de dados externas.

    Pedro, me refiro que as medidas são carregadas a partir das fontes externas de dados; não há como obtê-las nas dimensões. Por "externas" me refiro externas ao DW, mas podem também ser externas à organização.

    Com relação ao uso de chaves substitutas (surrogate keys), em DW é algo fundamental. Aproveito para copiar para este tópico trecho de resposta postado há poucos dias em outro fórum:

    (...) No caso de data warehouse (DW) o enfoque altera um pouco, por causa da temporalidade. Por exemplo, um determinado produto pode ter sua codificação modificada em determinado momento; isso fica transparente caso no DW tenha-se optado pelo uso de chave substituta para identificar os produtos. Mesmo que no banco de dados do OLTP a codificação tenha sido alterada, no banco de dados do DW ela permanece imutável, possibilitando acompanhar o produto ao longo de sua existência.

    Há também outros motivos para a utilização de chave substituta em DW. Um deles é que a informação sobre um mesmo objeto pode ter codificações diferentes, dependendo da origem, quando o DW é alimentado a partir de diversas origens (inclusive de fora da empresa). Desta forma, a chave substituta escolhida para o objeto passa a funcionar como padronização.

    Isto pode ser confirmado pela definição de Surrogate Keys presente no sítio web do Kimball Group, onde é mencionado que Actually, a surrogate key in a data warehouse is more than just a substitute for a natural key. In a data warehouse, a surrogate key is a necessary generalization of the natural production key and is one of the basic elements of data warehouse design.

    Aliás, esse verbete reforça que Every join between dimension tables and fact tables in a data warehouse environment should be based on surrogate keys, not natural key.

     


    e-mail       José Diz     Belo Horizonte, MG - Brasil



    • Editado José Diz quinta-feira, 8 de março de 2018 12:20
    quinta-feira, 8 de março de 2018 12:15
  • José,
    Podemos carregar sim uma tabela fato com as chaves primárias das dimensões, as melhores práticas nos dizem para fazer o carregamento através das fontes de dados externas.

    Pedro, me refiro que as medidas são carregadas a partir das fontes externas de dados; não há como obtê-las nas dimensões. Por "externas" me refiro externas ao DW, mas podem também ser externas à organização.

    Com relação ao uso de chaves substitutas (surrogate keys), em DW é algo fundamental. Aproveito para copiar para este tópico trecho de resposta postado há poucos dias em outro fórum:

    (...) No caso de data warehouse (DW) o enfoque altera um pouco, por causa da temporalidade. Por exemplo, um determinado produto pode ter sua codificação modificada em determinado momento; isso fica transparente caso no DW tenha-se optado pelo uso de chave substituta para identificar os produtos. Mesmo que no banco de dados do OLTP a codificação tenha sido alterada, no banco de dados do DW ela permanece imutável, possibilitando acompanhar o produto ao longo de sua existência.

    Há também outros motivos para a utilização de chave substituta em DW. Um deles é que a informação sobre um mesmo objeto pode ter codificações diferentes, dependendo da origem, quando o DW é alimentado a partir de diversas origens (inclusive de fora da empresa). Desta forma, a chave substituta escolhida para o objeto passa a funcionar como padronização.

    Isto pode ser confirmado pela definição de Surrogate Keys presente no sítio web do Kimball Group, onde é mencionado que Actually, a surrogate key in a data warehouse is more than just a substitute for a natural key. In a data warehouse, a surrogate key is a necessary generalization of the natural production key and is one of the basic elements of data warehouse design.

    Aliás, esse verbete reforça que Every join between dimension tables and fact tables in a data warehouse environment should be based on surrogate keys, not natural key.

     


    e-mail       José Diz     Belo Horizonte, MG - Brasil



    José,

    Neste caso das medidas sim, você esta certo.

    No que diz respeito a chaves artificias eu sei disso, elas são extremamente necessárias, mas o que eu me referi era justamente no ponto de vista eu fazer o carregamento dos dados das próprias dimensões e não das medidas ou métricas.

    Em relação ao comportamento, estrutura e forma de se trabalhar com um DW sei como funciona, alias estudei nos últimos anos este ambiente para justamente poder trabalhar com meus alunos de banco de dados além do conceito relacional.

    Obrigado por suas colocações, isso é muito importante, vamos fortalecendo o conhecimento e enriquecendo nossos saberes.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    quinta-feira, 8 de março de 2018 12:55
  • Júnior e José, obrigado por contribuírem.

    até onde sei o processo ETL começa retirando os dados dos ambientes OLTP e outras fontes de dados e carregados nas Stage Areas, depois são tratados e carregados nas dimensões.

    Eu tinha em mente que o processo era carregar a tabela fato a partir das dimensões até mesmo as medidas, pois as medidas são carregadas nas dimensões também, assim como eu coloquei no meu modelo.

    Vou colocar aqui meu estudo de caso completo que estou montando para me ajudar a entender.

    Bom, se eu estiver enganado por favor me corrijam.

    CREATE TABLE [dbo].[FactProductSales]
    (
    [TransactionId] [bigint] IDENTITY(1,1) NOT NULL, -- Surrogate Key
    [SalesDateKey] [int] NULL, -- Pk [DimDate]
    [StoreID] [int] NOT NULL, -- Pk [DimStores]
    [CustomerID] [int] NOT NULL,-- Pk [DimCustomer]
    [ProductID] [int] NOT NULL, -- Pk [DimProduct]
    [SalesPersonID] [int] NOT NULL,-- Pk [DimSalesPerson]
    [Quantity] [float] NULL,
    [SalesTotalCost] [money] NULL,
    [ProductActualCost] [money] NULL
    ) ON [PRIMARY]
    
    ===========================================================
    Go
    
    CREATE TABLE [dbo].[DimCustomer]
    (
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerAltID] varchar NOT NULL,
    [CustomerName] varchar NULL,
    [Gender] varchar NULL,
    CONSTRAINT [pk_DimCustomer] PRIMARY KEY CLUSTERED
    (
    [CustomerID] ASC
    )
    ) ON [PRIMARY]
    
    ===========================================================
    go
    
    CREATE TABLE [dbo].[DimDate]
    (
    [DateKey] [int] NOT NULL,
    [Date] [datetime] NULL,
    [FullDate] char NULL,
    [DayOfMonth] varchar NULL,
    [DayName] varchar NULL,
    [DayOfWeek] char NULL,
    [DayOfYear] varchar NULL,
    [Month] varchar NULL,
    [MonthName] varchar NULL,
    [Year] char NULL,
    [IsWeekday] [bit] NULL,
    CONSTRAINT [pk_DateKey] PRIMARY KEY CLUSTERED
    (
    [DateKey] ASC
    )
    ) ON [PRIMARY]
    
    ===========================================================
    Go
    
    CREATE TABLE [dbo].[DimProduct]
    (
    [ProductKey] [int] IDENTITY(1,1) NOT NULL,
    [ProductAltKey] varchar NOT NULL,
    [ProductName] varchar NULL,
    [ProductActualCost] [money] NULL,
    [ProductSalesCost] [money] NULL,
    CONSTRAINT [pk_DimProduct] PRIMARY KEY CLUSTERED
    (
    [ProductKey] ASC
    )
    ) ON [PRIMARY]
    
    go
    
    ===========================================================
    CREATE TABLE [dbo].[DimSalesPerson]
    (
    [SalesPersonID] [int] IDENTITY(1,1) NOT NULL,
    [SalesPersonAltID] varchar NOT NULL,
    [SalesPersonName] varchar NULL,
    [StoreID] [int] NULL,
    [City] varchar NULL,
    [State] varchar NULL,
    [Country] varchar NULL,
    CONSTRAINT [pk_DimSalesPerson] PRIMARY KEY CLUSTERED
    (
    [SalesPersonID] ASC
    )
    ) ON [PRIMARY]
    
    go
    
    ===========================================================
    CREATE TABLE [dbo].[DimStores]
    (
    [StoreID] [int] IDENTITY(1,1) NOT NULL,
    [StoreAltID] varchar NOT NULL,
    [StoreName] varchar NULL,
    [StoreLocation] varchar NULL,
    [City] varchar NULL,
    [State] varchar NULL,
    [Country] varchar NULL,
    CONSTRAINT [pk_DimStores] PRIMARY KEY CLUSTERED
    (
    [StoreID] ASC
    )
    ) ON [PRIMARY]
    
    ===========================================================
    
    
     
    SELECT 
           [TransactionId]
          ,[SalesDateKey]
          ,fp.[StoreID]
          ,fp.[CustomerID]
          ,[ProductID]
          ,fp.[SalesPersonID]
          ,[Quantity]
          ,([SalesTotalCost]) 
          ,pt.[ProductActualCost]
    FROM [Sales_DW].[dbo].[FactProductSales]fp
    left JOIN [dbo].[DimDate] dt
    	ON fp.[SalesDateKey] = dt.[DateKey]
    left JOIN [dbo].[DimCustomer] cu
    	ON fp.[CustomerID] = cu.[CustomerID]
    left JOIN [dbo].[DimProduct] pt
    	ON fp.[ProductID] = pt.ProductKey
    left JOIN [dbo].[DimSalesPerson] sp
    	ON fp.[SalesPersonID] = sp.[SalesPersonID]
    left JOIN [dbo].[DimStores] st
    	ON fp.[StoreID] = st.[StoreID]
     

    Vocês mencionaram que posso carregar a fato a partir da origem dos dados (OLPT e outras fontes), como eu posso montar meu SELECT para carregar está a FATO, tentei de algumas formas inclusive está, mas não consegui.

    Obrigado.

    quinta-feira, 8 de março de 2018 13:50
  • Eu tinha em mente que o processo era carregar a tabela fato a partir das dimensões até mesmo as medidas, pois as medidas são carregadas nas dimensões também, assim como eu coloquei no meu modelo.

    Fernando, talvez sua dúvida seja originada por causa desta afirmação: “pois as medidas são carregadas nas dimensões também”. Isto não procede; talvez a dúvida seja originada somente por causa de conceituação.

    O modelo que você postou na mensagem inicial deste tópico me parece correto, com as medidas somente na tabela de fato; não percebi nenhuma medida nas dimensões (o que estaria incorreto).

    Quando se monta o DW, primeiro carregam-se as dimensões. Posteriormente, no dia a dia do DW são carregadas as medidas na(s) tabela de fato(s). Nas dimensões somente se mexe quando há novas linhas a acrescentar. A conversão das chaves primárias das tabelas do sistema OLTP para as chaves substitutas das dimensões do DW pode ser feita através de informações acrescentadas nas dimensões ou através de tabelas auxiliares, na stage area.

    Segue exemplo bem simplificado, em que há tratamento de uma única dimensão, sem uso de tabelas auxiliares de conversão e sem uso da stage area.

    Supondo que no banco de dados "OLTP" existam as seguintes tabelas:

    -- código #1 v2
    -- OLTP
    CREATE TABLE tbProduto (
      IdProduto char(5) primary key,
      DenoProduto varchar(200) not null,
      PreçoUnitário decimal(9,2) not null
    );
    
    INSERT into tbProduto values
      ('HF001', 'Abacaxi', 4.00), ('HF002', 'Melão', 7.15), ('HF003', 'Laranja', 3);
    
    CREATE TABLE tbVenda (
      IdVenda int primary key,
      DataVenda smalldatetime not null
    );
    
    CREATE TABLE tbItemVenda (
      IdVenda int,
      IdProduto char(5),
      Quantidade smallint,
      ValorUnitário decimal(9,2),
      constraint FK_IV_Venda foreign key (IdVenda) references tbVenda,
      constraint FK_IV_Produto foreign key (IdProduto) references tbProduto
    ); CREATE clustered INDEX I1_tbItemVenda on tbItemVenda (IdVenda);
    INSERT into tbVenda values (1, convert(date, '23/5/2012', 103)), (2, convert(date, '27/5/2012', 103)); INSERT into tbItemVenda values (1, 'HF001', 4, (SELECT PreçoUnitário from tbProduto where IdProduto = 'HF001')), (1, 'HF003', 2, (SELECT PreçoUnitário from tbProduto where IdProduto = 'HF003')), (2, 'HF001', 1, (SELECT PreçoUnitário from tbProduto where IdProduto = 'HF001')), (2, 'HF002', 10, (SELECT PreçoUnitário from tbProduto where IdProduto = 'HF002'));


    E que no DW tenhamos:

    -- código #2 v3
    -- DW
    CREATE TABLE dimProduto (
      skProduto int primary key,
      DenoProduto varchar(200),
      IdProduto char(5)
    );  
     
    CREATE unique nonclustered INDEX I2_dimProduto on dimProduto (IdProduto) include (skProduto); CREATE TABLE fatoVenda ( skProduto int not null, Quantidade smallint not null, Valor decimal(9,2) not null );
     

    Além disso, há tabela de controle de carga do DW:

    -- código #3 v5 -- controle de última venda processada
    CREATE TABLE configCarga ( Id tinyint, Valor int );

    INSERT into configCarga values (1, 0);

     

    A seguir as dimensões são tratadas, utilizando as informações de conversão de chave primária do OLTP  para a chave substituta no DW:
    -- código #5 v3
    -- DW
    -- carga da dimensão Produto
    declare @ÚltimoReg int;
    set @ÚltimoReg= coalesce((SELECT max(skProduto) from dimProduto), 0);
    INSERT into dimProduto (skProduto, DenoProduto, IdProduto)
      SELECT (@ÚltimoReg + row_number() over (order by IdProduto)),
             DenoProduto, IdProduto
        from tbProduto as T1
    where not exists (SELECT *
    from dimProduto as T2
    where T2.IdProduto = T1.IdProduto);

    O código acima funciona tanto para a carga inicial das dimensões quanto para atualização das dimensões. Não trata caso em que a chave primária do OLTP tenha sofrido modificação.

     
    Após a carga das dimensões, vem a carga da tabela fato:

    -- código #6 v3
    -- dia a dia - carga da tabela de fato
    declare @ÚltimaVenda int, @AtéVenda int;
    set @ÚltimaVenda= (SELECT Valor from configCarga where Id = 1);
    set @AtéVenda= 2;

    INSERT into fatoVenda
      SELECT dP.skProduto, IV.Quantidade, (IV.Quantidade * IV.ValorUnitário)
        from tbVenda as V
             inner join tbItemVenda as IV on IV.IdVenda = V.IdVenda
             inner join dimProduto as dP on dP.IdProduto = IV.IdProduto
        where V.IdVenda > @ÚltimaVenda
              and V.IdVenda <= @AtéVenda;

    UPDATE configCarga
      set Valor= @AtéVenda
      where Id = 1;

    Como no exemplo não está sendo utilizada stage area, e os dados são carregados diretamente do banco de dados OLTP, temos que controlar até qual venda será importada.



    e-mail       José Diz     Belo Horizonte, MG - Brasil



    quinta-feira, 8 de março de 2018 15:12
  • No que diz respeito a chaves artificias eu sei disso, elas são extremamente necessárias, mas o que eu me referi era justamente no ponto de vista eu fazer o carregamento dos dados das próprias dimensões e não das medidas ou métricas.
    Pois é, o que faltou em minha resposta anterior foi eu ter explicado que me referia às medidas.

    e-mail       José Diz     Belo Horizonte, MG - Brasil



    • Editado José Diz quinta-feira, 8 de março de 2018 17:37
    quinta-feira, 8 de março de 2018 15:14
  • José seu exemplo me tirou muitas dúvidas.

    Como eu faria a integração da dimensão DATA com a fato?

    Suponha que eu tenho a dimensão DATA assim:

    create table Dim_Data
    (
    skData integer identity(1,1),
    Data date,
    Ano smallint,
    Mes smallint,
    Dia smallint,
    Dia_Util char(1)
    )
    go

    Eu tenho essa dimensão populada com datas do ano de 2000 até 2050.

    sexta-feira, 9 de março de 2018 13:46
  • Fernando,

    Você terá que levar para sua Tabela Fato a chave primária da sua dimensão de tempo, as hierarquias não são migradas para tabela Fato, será justamente através da chave primária de tempo que você terá a capacidade de identificar e apresentar esta hierarquia.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    segunda-feira, 12 de março de 2018 22:41