Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Time Dimmension for Fact Transaction [Data Warehouse]

  1. #1
    Join Date
    Jan 2014
    Posts
    1

    Default Time Dimmension for Fact Transaction [Data Warehouse]

    Hello,

    I'm a beginner in Pentaho Data Integration [Kettle],
    and i'm trying to explore it. in my sample i have plan for doing ETL process
    from conventional database model to Data Warehouse database model.


    Let say I have Header and Detail Transaction Table in Convensional Database.
    in my header transaction table i have date of transaction field, i want copy certain records with valid date to Data Warehouse Fact Table, but before i insert transaction records to data warehouse's fact table, i must check to time dimension table, whether the transaction's time is exist, if not, new record for time dimension should be created, with day field, mount field, year field, quarter field of transaction's time and with auto-increment time id. and then i must throw dimension time id to stream fields which will copied to fact table.


    So, My Fact Table Should be like this :


    id
    transacton_id
    customer_id
    product_id
    time_id
    product_price
    qty
    total


    So i use Table Input Step for Selecting from Convensional Database and of course i use Table Output Step after i check dimension time table for insert the transaction's data to data warehouse, but i don't know what should i use when i check dimension table and return its time id to stream field before i use Table Output, whether used Dimension Lookup/ Update Step or another..


    Can anyone help me ?

    Thanks

  2. #2

    Default

    You can use the Lookup step to fetch from dimension table.

    Regards.

    Bruno Condemi

  3. #3
    Join Date
    Jul 2009
    Posts
    476

    Default

    A lot of people pre-build their date dimensions by generating a sequence of days starting at the earliest date and going as far out into the future as they think they need. If you are doing business dates, your date dimension might go from 1/1/2000 to 12/31/2040 or something like that. You can create a PDI transformation with a sequence generator step to do this.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.