US and Worldwide: +1 (866) 660-7555
Results 1 to 10 of 10

Thread: Date Dimension with One Table

  1. #1
    Join Date
    May 2014
    Posts
    9

    Default Date Dimension with One Table

    Hi,

    I have only one table with data and one field is a timestamp (2014-06-12 00:42:12.5)

    i'm trying to create a dimension based on that field (date), but to group the records on year or month.

    So the result will be something like that

    2014-04 5
    2014-05 10
    2014-06 6

    or something like that
    2014 21

    but because of different times for every record i get 21 different rows with count 1 for every row....

    I dont' have access to database schema to create another table (like in examples date_time).

    Is there any way to create the Mondrian schema with the date dimension based only in that table ???

    Thanks
    Kostas

  2. #2
    Join Date
    May 2014
    Posts
    9

    Default Date Dimension with One Table

    Finaly i found a solution through degenerate dimensions and create for timestamp every level.
    I can see the results of the levels as dimensions in cube but the query results nothing.

    Is something else i must do ??? that i forget it.
    How mondrian or saiku made the join between original the record with timestamp field and the part month ??


    <Dimension type="StandardDimension" visible="true" highCardinality="false" name="DATE" caption="DATE CAPTION">
    <Hierarchy name="DATE_H" visible="true" hasAll="true">
    <Level name="YEAR_LVL" visible="true" column="START" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" caption="YEAR CAPTION">
    <KeyExpression>
    <SQL dialect="generic">
    <![CDATA[EXTRACT(YEAR FROM START)]]>
    </SQL>
    </KeyExpression>
    </Level>
    <Level name="MONTH_LVL" visible="true" column="START" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" caption="MONTH CAPTION">
    <KeyExpression>
    <SQL dialect="generic">
    <![CDATA[EXTRACT(MONTH FROM START)]]>
    </SQL>
    </KeyExpression>
    </Level>
    <Level name="DAY_LVL" visible="true" column="START" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" caption="DAY CAPTION">
    <KeyExpression>
    <SQL dialect="generic">
    <![CDATA[EXTRACT(DAY FROM START)]]>
    </SQL>
    </KeyExpression>
    </Level>
    <Level name="HOUR_LVL" visible="true" column="START" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" caption="HOUR CAPTION">
    <KeyExpression>
    <SQL dialect="generic">
    <![CDATA[EXTRACT(HOUR FROM START)]]>
    </SQL>
    </KeyExpression>
    </Level>
    <Level name="MINUTE_LVL" visible="true" column="START" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" caption="MINUTE CAPTION">
    <KeyExpression>
    <SQL dialect="generic">
    <![CDATA[EXTRACT(MINUTE FROM START)]]>
    </SQL>
    </KeyExpression>
    </Level>
    <Level name="SECOND_LVL" visible="true" column="START" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" caption="SECOND CAPTION">
    <KeyExpression>
    <SQL dialect="generic">
    <![CDATA[EXTRACT(SECOND FROM START)]]>
    </SQL>
    </KeyExpression>
    </Level>
    <Level name="MICRO_LVL" visible="true" column="START" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" caption="MICROSECOND CAPTION">
    <KeyExpression>
    <SQL dialect="generic">
    <![CDATA[EXTRACT(MICROSECOND FROM START)]]>
    </SQL>
    </KeyExpression>
    </Level>
    </Hierarchy>
    </Dimension>




    Quote Originally Posted by kstsstrg148 View Post
    Hi,

    I have only one table with data and one field is a timestamp (2014-06-12 00:42:12.5)

    i'm trying to create a dimension based on that field (date), but to group the records on year or month.

    So the result will be something like that

    2014-04 5
    2014-05 10
    2014-06 6

    or something like that
    2014 21

    but because of different times for every record i get 21 different rows with count 1 for every row....

    I dont' have access to database schema to create another table (like in examples date_time).

    Is there any way to create the Mondrian schema with the date dimension based only in that table ???

    Thanks
    Kostas

  3. #3
    Join Date
    Jan 2013
    Posts
    476

    Default

    I'm not sure what's going on, but you might be able to learn something by looking at the generated SQL. Try enabling DEBUG on mondrian.sql in log4j.xml.

    <category name="mondrian.sql">
    <priority value="DEBUG"/>
    <appender-ref ref="SQLLOG"/>
    </category>

  4. #4
    Join Date
    May 2014
    Posts
    9

    Default

    Hi again,

    thanks for your reply...

    Although the SQL Syntax seems well and SQL seems to result two rows ...
    2014-06-10 10:15:05,423 DEBUG [mondrian.sql] 121: , exec+fetch 5 ms, 2 rows
    saiku server returns "NO RESULT"

    Below is the log file of the saiku server .....
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    2014-06-10 10:15:01,092 DEBUG [org.saiku.web.rest.resources.QueryResource] TRACK /query/63BF2E49-1D9C-EBA4-A0CE-C9E7622C07E6/axis/COLUMNS/dimension/GENDER/hierarchy/[GENDER.GENDER_H]/[GENDER.GENDER_H].[Gender_LVL] POST
    2014-06-10 10:15:01,092 DEBUG [org.saiku.service.olap.OlapQueryService] move query: 63BF2E49-1D9C-EBA4-A0CE-C9E7622C07E6 dimension GENDER to axis COLUMNS position0
    2014-06-10 10:15:05,341 DEBUG [org.saiku.web.rest.resources.QueryResource] TRACK /query/63BF2E49-1D9C-EBA4-A0CE-C9E7622C07E6/axis/ROWS/dimension/DATE/hierarchy/[DATE.DATE_H]/[DATE.DATE_H].[YEAR_LVL] POST
    2014-06-10 10:15:05,341 DEBUG [org.saiku.service.olap.OlapQueryService] move query: 63BF2E49-1D9C-EBA4-A0CE-C9E7622C07E6 dimension DATE to axis ROWS position0
    2014-06-10 10:15:05,397 DEBUG [org.saiku.web.rest.resources.QueryResource] TRACK /query/63BF2E49-1D9C-EBA4-A0CE-C9E7622C07E6/resultflattened GET
    2014-06-10 10:15:05,397 INFO [org.saiku.service.olap.OlapQueryService] runId:28 Type:QM:
    SELECT
    NON EMPTY {Hierarchize({[GENDER.GENDER_H].[Gender_LVL].Members})} ON COLUMNS,
    NON EMPTY {Hierarchize({[DATE.DATE_H].[YEAR_LVL].Members})} ON ROWS
    FROM [EuropeanCube_New]
    2014-06-10 10:15:05,402 DEBUG [mondrian.sql] 117: SqlTupleReader.readTuples [[GENDER.GENDER_H].[Gender_LVL]]: executing sql [select (case when GROPGNRAL0GROPGNRAL7_SEX = 'sex1' then 'ΑΝΔΡΑΣ' else 'ΓΥΝΑΙΚΑ' end) as `c0`, `exhibition_form_core`.`GROPGNRAL0GROPGNRAL7_SEX` as `c1` from `exhibition_form_core` as `exhibition_form_core` group by (case when GROPGNRAL0GROPGNRAL7_SEX = 'sex1' then 'ΑΝΔΡΑΣ' else 'ΓΥΝΑΙΚΑ' end), `exhibition_form_core`.`GROPGNRAL0GROPGNRAL7_SEX` order by ISNULL(`exhibition_form_core`.`GROPGNRAL0GROPGNRAL7_SEX`) ASC, `exhibition_form_core`.`GROPGNRAL0GROPGNRAL7_SEX` ASC]
    2014-06-10 10:15:05,408 DEBUG [mondrian.sql] 117: , exec 5 ms
    2014-06-10 10:15:05,409 DEBUG [mondrian.sql] 117: , exec+fetch 7 ms, 2 rows
    2014-06-10 10:15:05,409 DEBUG [mondrian.sql] 118: SqlTupleReader.readTuples [[DATE.DATE_H].[YEAR_LVL]]: executing sql [select EXTRACT(YEAR FROM START) as `c0` from `exhibition_form_core` as `exhibition_form_core` group by EXTRACT(YEAR FROM START) order by ISNULL(EXTRACT(YEAR FROM START)) ASC, EXTRACT(YEAR FROM START) ASC]
    2014-06-10 10:15:05,411 DEBUG [mondrian.sql] 118: , exec 2 ms
    2014-06-10 10:15:05,412 DEBUG [mondrian.sql] 118: , exec+fetch 3 ms, 1 rows
    2014-06-10 10:15:05,413 DEBUG [mondrian.sql] 119: SqlStatisticsProvider.getQueryCardinality: executing sql [select count(*) from (select distinct (case when GROPGNRAL0GROPGNRAL7_SEX = 'sex1' then 'ΑΝΔΡΑΣ' else 'ΓΥΝΑΙΚΑ' end) as `c0` from `exhibition_form_core` as `exhibition_form_core`) as `init`]
    2014-06-10 10:15:05,415 DEBUG [mondrian.sql] 119: , exec 1 ms
    2014-06-10 10:15:05,415 DEBUG [mondrian.sql] 119: , exec+fetch 2 ms, 1 rows
    2014-06-10 10:15:05,415 DEBUG [mondrian.sql] 120: SqlStatisticsProvider.getQueryCardinality: executing sql [select count(*) from (select distinct EXTRACT(YEAR FROM START) as `c0` from `exhibition_form_core` as `exhibition_form_core`) as `init`]
    2014-06-10 10:15:05,416 DEBUG [mondrian.sql] 120: , exec 0 ms
    2014-06-10 10:15:05,416 DEBUG [mondrian.sql] 120: , exec+fetch 1 ms, 1 rows
    2014-06-10 10:15:05,418 DEBUG [mondrian.sql] 121: Segment.load: executing sql [select (case when GROPGNRAL0GROPGNRAL7_SEX = 'sex1' then 'ΑΝΔΡΑΣ' else 'ΓΥΝΑΙΚΑ' end) as `c0`, EXTRACT(YEAR FROM START) as `c1`, count(distinct `exhibition_form_core`.`_URI`) as `m0` from `exhibition_form_core` as `exhibition_form_core` where EXTRACT(YEAR FROM START) = '2014' group by (case when GROPGNRAL0GROPGNRAL7_SEX = 'sex1' then 'ΑΝΔΡΑΣ' else 'ΓΥΝΑΙΚΑ' end), EXTRACT(YEAR FROM START)]
    2014-06-10 10:15:05,422 DEBUG [mondrian.sql] 121: , exec 4 ms
    2014-06-10 10:15:05,423 DEBUG [mondrian.sql] 121: , exec+fetch 5 ms, 2 rows
    2014-06-10 10:15:05,424 INFO [org.saiku.service.olap.OlapQueryService] runId:28 Size: 0/0 Execute: 27ms Format: 0ms Total: 27ms
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    Best Regards
    Kostas

  5. #5
    Join Date
    Jan 2013
    Posts
    476

    Default

    Have you tried a query for just [YEAR_LVL]? Something like

    select [Date].[YEAR_LVL].members on 0 from cubename

  6. #6
    Join Date
    May 2014
    Posts
    9

    Default Date Dimension with One Table

    SQL Query has no problem
    If I write the query to MySQL SQL Editor then returns data....
    Also as you can see in Mondrian SQL DEBUG Log File it returns 2 rows...which is right (one row for sex1 and one row for sex2)
    2014-06-10 10:15:05,423 DEBUG [mondrian.sql] 121: , exec+fetch 5 ms, 2 rows

    But with no data...

    Also I test it with Pentaho Pivot4J and JPIVOT and SAIKU and the filter returns the two distinct years (2014,2015)...
    but No Results... In OLAP environment I can see Years months etc but no measure results in cells ...

    Any ideas ???


    Quote Originally Posted by mcampbell View Post
    Have you tried a query for just [YEAR_LVL]? Something like

    select [Date].[YEAR_LVL].members on 0 from cubename

  7. #7
    Join Date
    Jan 2013
    Posts
    476

    Default

    Just to confirm, if you run a query for just the [YEAR_LVL] you get empty cells as well (even without Gender or anything else in context)? Trying to eliminate variables.

    I still don't have any good guesses about why your seeing this. One thing I notice in your schema-- you've got both column and KeyExpression defined on the levels. It could be worth getting rid of the column attribute (although I think keyExpression should override the column).

  8. #8
    Join Date
    May 2014
    Posts
    9

    Default Date Dimension with One Table

    How can I do that with saiku ??? If I use on Columns only YEAR and in ROWS the Measure then i get the same "No Results"

    You can try this if you want a 62.38.193.198:8090 admin/admin (Unfortunately it is only in Greek)
    The cube Is EuropeanCube_New (Schema23)
    The Year_LVL is the 11th Dimension "Ημερομηνία Συμμετοχής"

    I beleive that KeyExpression override ordinalColumn not Column ...

    What else can I do to create a date dimension (Year / Month / Day) based on a TimeStamp
    Is there a standard methodology instead of create a second dim_date table in the database ???

    Thanks again...


    Quote Originally Posted by mcampbell View Post
    Just to confirm, if you run a query for just the [YEAR_LVL] you get empty cells as well (even without Gender or anything else in context)? Trying to eliminate variables.

    I still don't have any good guesses about why your seeing this. One thing I notice in your schema-- you've got both column and KeyExpression defined on the levels. It could be worth getting rid of the column attribute (although I think keyExpression should override the column).

  9. #9
    Join Date
    Jan 2013
    Posts
    476

    Default

    Typically you would split your time attributes out into a separate table, rather than using degenerate dims. But in principle I don't see why what you're doing won't work.

    One thing I noticed in your schema-- you've set "String" as the type for the attributes like year. Give "Integer" a try.

  10. #10
    Join Date
    May 2014
    Posts
    9

    Default Date Dimension with One Table

    Hi again,

    nothing happened with INTEGER also ... So I will try with a separate table...

    Do you have any detail documentation of how I will do that ?? How do i create the dynamic table (with dates) in mydb so it includes every possible date I insert in my fact table ??

    Thanks again for your time...

    Kostas


    Quote Originally Posted by mcampbell View Post
    Typically you would split your time attributes out into a separate table, rather than using degenerate dims. But in principle I don't see why what you're doing won't work.

    One thing I noticed in your schema-- you've set "String" as the type for the attributes like year. Give "Integer" a try.

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
  •