Time period in relational Extract but values for each day needed. How to solve this?

    Time period in relational Extract but values for each day needed. How to solve this?

    Hi,

    use-case (other than mine but it explains the problem in an easy way)
    I have a relational table that contains the leaf days of the employees of our company (just a fictive example).
    But there is not an entry for every leaf day, there are periods stored. for example:
    NAME | DATE_FROM | DATE_TO
    John | 2017-02-02 | 2017-02-02
    Tom | 2017-03-01 | 2017-03-03
    Maria | 2017-03-03 | 2014-03-08
    (...)

    Now I want to create a cube with the measure "leaf days".
    But I only have start and end date of a "leaf day period".
    A PALO query should later give back for 2017-03-05 the value "1" in the example above. For 2017-03-03 "2" should be returned. And so on..

    For my normalisation I would need this to be in format
    DATE DIMENSION | NAME | MEASURE (leaf day)
    (...)
    2017-01-01 | John | 0
    2017-01-01 | Tom | 1
    2017-01-01 | Maria | 0
    2017-01-02 | John | 0
    2017-01-02 | Tom | 1
    2017-01-02 | Maria | 0
    2017-01-03 | John | 0
    2017-01-03 | Tom | 1
    2017-01-03 | Maria | 1
    (...)

    I tried to do some experiments with Looptable Transform but these where not successful. Is there a way to solve this via sql queries? Or are there other transforms jedox offers that can be useful here?

    Does anyone have an idea how to solve this?

    Thanks in advance for all hints...
    @Seaben thanks for your reply.
    I already found sql calendar tables. But that is not really a solution for me because I do not have a db available where i can create these tables.

    I thougt about a Date dimension with years and days but tableloop cannot work with this. Having just the days in Format yyyy-MM-dd in a constanttable would work with tableloop I think but I cannot create a constanttable with the range 2000-01-01 to 2099-01-01 ;)
    Actually I have solved this problem the following way:
    • a small helper script (php) created a csv with all day values from 2000-01-01 to 2099-12-31 (possible date range)
    • This content is stored as excelfile in report designer
    • File is loaded as excel extract
    • tablejoin joins days with sql-query (with le and ge operator on beginn and end date) and gives back the result I need.
    But your hint is also interesting (I tried it but I had was not sucessful) - I'll try this again. The idea to convert date dimension via transform to a table is much better.

    Thanks a lot!
    Update: Using this solution ran me into an memory allocation error and also the performance was quite poor.
    So I found a more efficient solution I want to share if someone needs something like this, too.
    Now I solved this using common table expressions. The only limitation is that common table expressions can only manage up to 32767 lines but 40y back and in future is enough for my use case.

    Source Code

    1. WITH MINMAX AS
    2. (SELECT DATEADD(YEAR,-40, CONVERT (date,GETDATE())) AS MinDate,
    3. DATEADD(YEAR,40, CONVERT (date,GETDATE())) AS MaxDate),
    4. DAYS AS
    5. (SELECT MinDate AS Z_DATUM_YQMD
    6. FROM MINMAX
    7. UNION ALL SELECT DATEADD(DAY,1, Z_DATUM_YQMD) AS Z_DATUM_YQMD
    8. FROM DAYS
    9. WHERE DATEADD(DAY,1, Z_DATUM_YQMD)<=
    10. ( SELECT MaxDate
    11. FROM MINMAX) )
    12. SELECT cal.Z_DATUM_YQMD AS Z_DATUM_YQMD,
    13. (...)
    14. FROM
    15. (...)
    16. INNER JOIN days cal ON cal.Z_DATUM_YQMD <= pkzr.bis
    17. AND cal.Z_DATUM_YQMD >= pkzr.von OPTION (MAXRECURSION 32767)


    is 7 times faster than joining in a TableJoin in jedox integrator.