Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Date to year, quartal, month

  1. #1
    Join Date
    Oct 2007

    Default Date to year, quartal, month


    My testing with CD continues and I hope to get some advice to carry on.

    I want to get dimension;
    - Year
    - - Quartal
    - - - Month
    - - - - Week

    But what can I do when I have only one date field (ddmmyyyy) in my database (sales).

    Is that date dimension possible in CD or do I need to install some new program mondrian, workbench...?

  2. #2
    Join Date
    Oct 2007

    Default Maybe calculated Members?

    CD user guide says that CD

    Mondrian features not supported

    1. Shared Dimensions
    2. - Aggregation
    3. - Calculated Members
    4. - Inline Tables
    5. - Multiple cubes within the same schema file

    Is that "Calculated Members" what I can use to make Date dimension?

    Last edited by Juuso75; 10-08-2007 at 03:14 AM.

  3. #3

    Default Tempory solution

    I've got the exact same problem. I solved it by creating a view in our mySQL database on the table.
    something like

    create view 'somename' as (select year(datecol) as year, month(datecol) as month, day(datecol) as day, t.* from sometable t)

    There's probably a better way to do it though. But this way at least works for me

  4. #4
    Join Date
    Oct 2006

    Default Hello!

    You have stumbled on the fantastic world of Data Warehousing!

    To really use any BI software you are going to want a data warehouse. I consider a data warehouse the core of any BI application. This as an example will solve your problem with the date dimensions so that you can transform a simple date 01/01/2007 into: Year, Quarter, Month, day, month description, day of week ect ect ect.

    I would suggest that if you want to build a good BI solution that you look into creating Data warehouses first! Only then will you really see the power of BI applications and analysis! Read up my friends!

    If you want some more real time help you can always come and join us in our unofficial IRC chatroom on in the ##pentaho channel.

    Good luck! - Ivy Information Systems

    Come join the non official Pentaho IRC chat room on in the ##pentaho channel

  5. #5
    Join Date
    Jan 2007

    Default Re: Time Dimension

    Hi.. I´m not an expert, but you apparently are missing a "Time" Dimension table (containing Year, Quarter, MonthName, MonthNumber, Week) for every date that exists in your database (sales). This Time Dimension Table should be joined to your sales table at "date".Hope this complements hazamonzo´s reply. By the way,on Datawarehousing, look for material covering "Star Schemas" will give you a better idea.regards and good luck!

  6. #6
    Join Date
    Jan 2007

    Default For User = Juuso75: DW Model Concepts

    I thought you might be interested in looking up the following sites to get more info on Dimensional Modeling:

    An then of course you can take a look at any tutorial on MDX (Mutidimensional Query Language), which in their introductions start off with basic concepts that you will need to elaborate further on you queries agaist "cubes".

    Hope this helps.

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.