Clarity

 View Only
Expand all | Collapse all

How to get open timesheet records in data warehouse?

  • 1.  How to get open timesheet records in data warehouse?

    Posted Jun 20, 2022 03:34 AM
    Edited by Sneha Raina Jun 21, 2022 02:25 AM
    Hello,

    We wish to export the resource timesheet data into the data warehouse tables so we can use it for reporting purposes in power BI so we can't write queries to pull data or create views.
    Currently we are able to view submitted and posted timesheets for all resources.

    We are facing issue wrt the open timesheets. As per my understanding, the Timesheet id gets generated only when the user clicks on current timesheet icon (clock icon on the top right corner). So we have observed discrepancies between data in clarity and dwh tables. All the open timesheets aren't populating in warehouse tables.

    Is there a solution to generate timesheet ids for all resources for the current time period. or is there any other table which stores all open timesheet data? We are currently referencing DWH_TME_SHEET, DWH_TME_ENTRY, DWH_TME_ENTRY_FACTS tables

    Thanks,
    Sneha Raina


  • 2.  RE: How to get open timesheet records in data warehouse?

    Posted Jun 21, 2022 04:59 AM
    Edited by Paul Schofield Jun 21, 2022 04:59 AM
    Hello again,

    You're correct in that timesheets that haven't yet been initiated (by clicking on the clockface) do not yet exist in the database - so they don't have rows in the prtimesheet table in the transactional database, and hence they don't appear in the data warehouse database.

    In my system I have 3 timesheets for myself, 2 of which are "open":


    In the database though only the final two shown above actually exist:


    You'd have to generate the "missing" rows in prtimesheet . I'm sure someone can come up with a better bit of SQL but one way is as follows (where you'd inject the 5003001 value as a parameter in the real world):

    select *
    from
    (
    select t.prid timesheet_id, t.prresourceid resource_id, p.prid timeperiod_id from prtimesheet t
    join prtimeperiod p on t.prtimeperiodid = p.prid
    where t.prresourceid = 5003001
    union
    select 0, 5003001, p.prid
    from prtimeperiod p
    where not exists
    ( select 1 from prtimesheet t
    where prresourceid = 5003001
    and t.prtimeperiodid = p.prid)
    )
    where resource_id = 5003001

    We now see the missing timesheet too:







  • 3.  RE: How to get open timesheet records in data warehouse?

    Posted Jun 21, 2022 05:29 AM
    Thank you so much for your response Paul. Happy to know that it can be done. But one question I have is that, is it possible to replicate this in Power BI??

    For reference, we use Odata feed to import the dwh tables in our Power BI reports.



  • 4.  RE: How to get open timesheet records in data warehouse?

    Posted Jun 21, 2022 05:39 AM
    Edited by Paul Schofield Jun 21, 2022 05:40 AM

    I guess you connect to the DWH schema from Power BI? If so, you can still access the transactional database via the database link (or linked server in MS SQL Server). You'd use the same syntax I mentioned in the missing currencies reply. The query you'd need to embed would then look like this (where the @clarity references the table in the transactional database as that's the name of the DB link):

    select *
    from
    (
    select t.prid timesheet_id, t.prresourceid resource_id, p.prid timeperiod_id from prtimesheet@clarity t
    join prtimeperiod@clarity p on t.prtimeperiodid = p.prid
    where t.prresourceid = 5003001
    union
    select 0, 5003001, p.prid
    from prtimeperiod@clarity p
    where not exists
    ( select 1 from prtimesheet@clarity t
    where prresourceid = 5003001
    and t.prtimeperiodid = p.prid)
    )
    where resource_id = 5003001


    ​As I say, you could probably do something with outer joins but the above should do what you need. In SQL Server the syntax is different but the idea is the same.


  • 5.  RE: How to get open timesheet records in data warehouse?

    Posted Jun 21, 2022 11:47 AM
    Hi Paul,

    Actually we can't run SQL queries in Power BI and we use Clarity SaaS and hence, we have no access to database. In power bi query editor, We can do simple modifications on dwh tables and use joins to a small extent.

    Do you think it's possible to get the open timesheet data under these conditions?

    Kind regards,
    Sneha



  • 6.  RE: How to get open timesheet records in data warehouse?

    Posted Jun 22, 2022 04:45 AM
    Hi again,

    I've never used Power BI, but if you can create queries in the Query Editor you should be able to execute more or less any query I'd assume. One thing though, is that the "missing" timesheets obviously won't have any hours on them so why are they needed? The sample query above will show which ones are missing for a given user - and it could be generalised to show the missing ones for all users. You could then "union" the results of the query with whatever query your running now to pull the non-missing timesheets in.


  • 7.  RE: How to get open timesheet records in data warehouse?

    Posted Jun 22, 2022 06:30 AM
    Hi Paul,

    Really appreciate your time and effort with this question. Will try to figure something out.

    Thanks & regards,
    Sneha


  • 8.  RE: How to get open timesheet records in data warehouse?

    Posted Jun 27, 2022 06:35 AM
    Hi Paul,

    Just wanted to clarify one thing here. In your example, you have used only 1 resource id or as you put it 'injected the 5003001 value as a parameter in the real world'. Will this query work for all resource ids? i.e. without resource id as a parameter because we want to fetch all resources with open timesheets.

    Thanks,
    Sneha


  • 9.  RE: How to get open timesheet records in data warehouse?

    Posted Jun 27, 2022 06:46 AM
    Edited by Paul Schofield Jun 27, 2022 06:46 AM
    That would be a rather more complicated query, as it would need to check that each resource was open for time entry (and find their start and end dates dates) and then perform a join with the prtimeperiods table to generate the full set of timesheets. It would then have to subtract the ones that already exist to return the "missing" ones.

    What database are you using? I have access to Oracle and SQL Server instances, but not to Postgres.

    Paul


  • 10.  RE: How to get open timesheet records in data warehouse?

    Posted Jun 27, 2022 07:34 AM
    Edited by Paul Schofield Jun 27, 2022 08:03 AM
    I posted another reply with another query but it's being vetted. It should appear later today I hope.

    In the meantime try this:

    select res.prid resource_id, srr.full_name resource_name, ptp.prid time_period_id, ptp.prstart period_start, nvl(pts.prid,0) timesheet_id
    from prtimeperiod ptp
    cross join prj_resources res
    join srm_resources srr on res.prid=srr.id
    left outer join prtimesheet pts on pts.prresourceid = srr.id and pts.prtimeperiodid = ptp.prid
    where res.prisopen = 1 -- Resource is enabled for time entry
    and ptp.prisopen = 1 -- Time period is open
    and srr.id = res.prid
    and nvl(srr.date_of_hire,'01-Jan-2000') <= ptp.prstart
    and nvl(srr.date_of_termination,'31-Dec-2099') >= ptp.prfinish
    order by 2





  • 11.  RE: How to get open timesheet records in data warehouse?

    Posted Jun 28, 2022 05:27 PM
    Here is an attempt (and I'm sure others can do better than this).

    If a timesheet exists (albeit with 0 hours on it) its ID is returned. If one doesn't then a zero comes back together with the resource and period IDs.

    A slight complication is that the prtimeperiod table stores the end date of the period as 1 day later than the actual end date, but that probably isn't an issue. There could well be other restrictions to consider but this should get you off the ground. You can select other columns from the tables to give you the resource name and the period start date rather than just their IDs.

    select res.prid resource_id, ptp.prid time_period_id, nvl(pts.prid,0) timesheet_id
    from prtimeperiod ptp
    cross join prj_resources res
    join srm_resources srr on res.prid=srr.id
    left outer join prtimesheet pts on pts.prresourceid = srr.id and pts.prtimeperiodid = ptp.prid
    where res.prisopen = 1 -- Resource is enabled for time entry
    and ptp.prisopen = 1 -- Time period is open
    and srr.id = res.prid
    and nvl(srr.date_of_hire,'01-Jan-2000') <= ptp.prstart  -- Check resource is around before the period start date
    and nvl(srr.date_of_termination,'31-Dec-2099') >= ptp.prfinish -- Check resource is around after the period end date
    order by 3 desc


    Good luck,

    Paul