Clarity

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

    Posted 8 days ago
    Edited by Sneha Raina 7 days ago
    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 7 days ago
    Edited by Paul Schofield 7 days ago
    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 7 days ago
    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 7 days ago
    Edited by Paul Schofield 7 days ago

    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 6 days ago
    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 6 days ago
    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 6 days ago
    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 yesterday
    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 yesterday
    Edited by Paul Schofield yesterday
    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 yesterday
    Edited by Paul Schofield yesterday
    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