none
Design the Fact table RRS feed

  • Question

  • Hi Experts ,

    I have 3 tables which are handling data on product level. So lets take an example I have a product Burger and there are 3 tables to support the product burger. In 1 table i have the product and product total, 2nd table processingCost and ProductID and 3rd Materials

    Now please suggest how can i design a fact table to contain all the 3 tables numeric data together referenced to a single Product Dimension or some other suitable design using which i can relate them together.

    ProductID Product ProductTotal
    1 Burger 10,000

    ProductID  ProcessingCost
    1 0.5
    1 0.6
    1 0.7
    1 0.8
    1 0.9

    ProductID  Material Bill Material
    1 5000 BurgerBun
    1 5000 Potato

    Please advice.

    Thanks

    Priya

    Wednesday, December 16, 2015 7:13 PM

Answers

  • Based on your requirement you can design table like below, but ideally you should have MaterialId instead of MaterialName and UnitId instead of UnitName and here what i mean to say is in your transaction system there must be two tables which holds information of Material and Unit, if yes then replace MaterialId and UnitId instead of UnitName. So it would be better if you post all the tables related to this module so that we can tell you the correct modelling steps.

    ProductId, MaterialName, MaterialWeight, MaterialCost, UnitName, Cost, ProdcutDate, have data like below


    Cheers,

    Amit Tomar

    ---------------------------------------------------

    Please mark this as answer if it solved your query

    Please vote this as helpful if it solved your query

    ---------------------------------------------------

    My Blog My Wiki Page


    Thursday, December 17, 2015 2:15 AM

All replies

  • What is the granularity and what is the measure you are considering? What are the transactional tables? Product will be a dimension. To analyse, you need a measure. Why is processing cost different for same product? What makes it change? There are several factors to consider before deciding on Fact.

    What are all the dimensions that will be part of DM/WH? What schema are you looking for?

    Wednesday, December 16, 2015 7:19 PM
  • Hi,

    Please find the requested details below :

    1.The Dimensions are Product,Plant,Date ,Material & Unit

    I actually tried to simplified the requirement which i get it didnt made much of sense.

    So my product have different Units and are produced at the plant.Each of the product has its own volume .So example a product burger will be measured in volume and veg burger in the plant will have seperate processing units and processing unit will have its cost which will be related to the product again.A product will also require certain raw material for manufacturing which again need to be related back to the produced product volume.

    Example Product Veg Burger -- 2500 volume

    Different Processing cost for the product volume  of 2500

         Cost        UnitName

           .8           Chopping

             .5          Washing

            .1          Packing

    Material in required in kgs for 2500 volume

     Material       Material name

        20            Potato

       40             Buns

    Now i need to design a fact table that can support the volume of the product,processing unit cost and material weight.

    Please let me know if some further information is required.

    Thanks

    Priya

    Wednesday, December 16, 2015 7:42 PM
  • Can you please post DDL and some sample data? What is the fact you are trying to track? and the frequency? per day/hour/minutes/months etc? 
    Wednesday, December 16, 2015 7:57 PM
  • Hi,

    Please find the DDL below

    CREATE TABLE Products
    (ProductID INT,
     ProductName VARCHAR(30),
     ProductDate DATE)
    
     INSERT INTO Products (ProductID,ProductName,ProductDate)
     Values (1,'Burger',CAST(getdate() AS Date))
    
    CREATE TABLE ProductVolume
    (ProductID INT,
     Volume INT
     ,ProductDate DATE )
    
      INSERT INTO ProductVolume(ProductID,Volume,ProductDate)
     Values (1,2500,CAST(getdate() AS Date))
    
     CREATE TABLE ProductUnit 
     (ProductID INT,
     UnitName Varchar(30),
     Cost Decimal(5,4),
     ProductDate DATE)
    
     INSERT INTO ProductUnit (ProductID,UnitName,Cost,ProductDate)
     Values(1,'Cleansing',0.2,CAST(getdate() AS Date))
      INSERT INTO ProductUnit (ProductID,UnitName,Cost,ProductDate)
     Values
     (1,'Packaging',0.1,CAST(getdate() AS Date))
      INSERT INTO ProductUnit (ProductID,UnitName,Cost,ProductDate)
     Values
     (1,'Storing',1.2,CAST(getdate() AS Date))
     
     CREATE  TABLE ProductMaterial
    
     (ProductID INT, 
      MaterialName Varchar(30),
      MaterialWeight INT,
      MaterialCost INT,ProductDate Date)
    
      INSERT INTO ProductMaterial (ProductID,Materialname,Materialweight,MaterialCost,ProductDate)
      Values (1,'Potato',30,4,CAST(getdate() AS Date)),
      (1,'Bun',50,1,CAST(getdate() AS Date))
    
    

    Please note i have a seperate time dimension for handling the dates . I need to create a fact for the reporting purposes that could show the data for processing unit and also for the material in use and cost.

    Simple how can i store the data for the productmaterial and productcost in the same fact table.If the volume duplicates its fine but other numeric data like productcost and material shouldnt duplicate.

    Thanks

    Priya

    Wednesday, December 16, 2015 8:24 PM
  • Are you looking for the cost to be aggregated for a product? I am still not clear of what you are trying to achieve? Show data from all the tables or are you trying to build a real data warehouse?

    Based on your tables and data, assuming product unit as a transactional table. Not sure if this is what you are looking for. 

    DECLARE @Products AS TABLE
    (ProductID INT,
     ProductName VARCHAR(30),
     ProductDate DATE)
    
     INSERT INTO @Products (ProductID,ProductName,ProductDate)
     Values (1,'Burger',CAST(getdate() AS Date))
    
    DECLARE @ProductVolume AS TABLE
    (ProductID INT,
     Volume INT
     ,ProductDate DATE )
    
      INSERT INTO @ProductVolume(ProductID,Volume,ProductDate)
     Values (1,2500,CAST(getdate() AS Date))
    
     DECLARE @ProductUnit  AS TABLE
     (ProductID INT,
     UnitName Varchar(30),
     Cost Decimal(5,4),
     ProductDate DATE)
    
     INSERT INTO @ProductUnit (ProductID,UnitName,Cost,ProductDate)
     Values(1,'Cleansing',0.2,CAST(getdate() AS Date))
      INSERT INTO @ProductUnit (ProductID,UnitName,Cost,ProductDate)
     Values
     (1,'Packaging',0.1,CAST(getdate() AS Date))
      INSERT INTO @ProductUnit (ProductID,UnitName,Cost,ProductDate)
     Values
     (1,'Storing',1.2,CAST(getdate() AS Date))
     
    DECLARE @ProductMaterial AS TABLE
    
     (ProductID INT, 
      MaterialName Varchar(30),
      MaterialWeight INT,
      MaterialCost INT,ProductDate Date)
    
      INSERT INTO @ProductMaterial (ProductID,Materialname,Materialweight,MaterialCost,ProductDate)
      Values (1,'Potato',30,4,CAST(getdate() AS Date)),
      (1,'Bun',50,1,CAST(getdate() AS Date))
    
    
      SELECT *
      FROM @Products
    
      SELECT *
      FROM @ProductVolume
    
      SELECT *
      FROM @ProductUnit
    
      SELECT *
      FROM @Products P 
      INNER JOIN @ProductVolume PV ON P.ProductID = PV.ProductID
      INNER JOIN @ProductUnit PU ON P.ProductID = PU.ProductID
      INNER JOIN @ProductMaterial PM ON PM.ProductID = PV.ProductID
    
      SELECT P.ProductID
    --         ,ProductName
             ,SUM(Cost) AS totalCostForBurger
             ,PU.ProductDate
             ,PV.Volume * SUM(Cost) AS FullCostOfVolume
      FROM @Products P
      INNER JOIN @ProductUnit PU ON P.ProductID = PU.ProductID
      INNER JOIN @ProductVolume PV ON P.ProductID = PV.ProductID
      GROUP BY P.ProductID
             ,ProductName
             ,PU.ProductDate
             ,PV.Volume

    Wednesday, December 16, 2015 9:37 PM
  • Based on your requirement you can design table like below, but ideally you should have MaterialId instead of MaterialName and UnitId instead of UnitName and here what i mean to say is in your transaction system there must be two tables which holds information of Material and Unit, if yes then replace MaterialId and UnitId instead of UnitName. So it would be better if you post all the tables related to this module so that we can tell you the correct modelling steps.

    ProductId, MaterialName, MaterialWeight, MaterialCost, UnitName, Cost, ProdcutDate, have data like below


    Cheers,

    Amit Tomar

    ---------------------------------------------------

    Please mark this as answer if it solved your query

    Please vote this as helpful if it solved your query

    ---------------------------------------------------

    My Blog My Wiki Page


    Thursday, December 17, 2015 2:15 AM
  • Hi Harsha/Amit,

    Harsha : Thank you so much for the detailed code.The final temp table output is very similar to what am looking for.

    Amit: Thank you for understanding my problem and its very similar to actual requirement.

    I was unable to provide all the details required but still both of you figured correctly what am i require.

    Thanks a again it gives me a great head start.

    So based on the ouput both of you provided.I require assistance in

    1.Design a Fact table.

    2.Containing surrogate Keys from specific entity dimension.

    3.Design the load pattern for loading data into the fact table and introduce denormilization in it and perform incremental inserts using tsql.

    4. Will be performing any calculation or distinct selection from the query end for displaying the report.

    Apologies for again posting a scenerio  below

    1.A Product has a Volume suppose X which is stored in its own transactional table.

    2.Transcation table storing date for processing cost which can be joined with Volume but my volume will repeat for every cost in the table for the given product as its 1 to many relationship.

    3.Transcation table for storing Material Cost and weight which can be joined with Volume but my volume will repeat for every cost in the table for the given product as its 1 to many relationship.

    Need to get both ProductCost and MaterialCost respective surrogate keys from the dimensional tables and load into the fact tables.

    Kindly dont put your efforts in designing dimension tables as i have dimension tables Like Product,PoductItems and ProductMaterial just populating the fact table and loading data for both the productitems and productmaterial

    is required. I repeated the requirement multiple times apologizes for the lengthy description.

    Thanks again

    Priya

    Thursday, December 17, 2015 3:36 AM