How to get open timesheet records in data warehouse?

[Sneha Raina]

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

[Paul Schofield]

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:

[Sneha Raina]

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.

[Paul Schofield]

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.

[Sneha Raina]

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

[Paul Schofield]

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.

[Sneha Raina]

Hi Paul,

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

Thanks & regards,
Sneha

[Sneha Raina]

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

[Paul Schofield]

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

[Paul Schofield]

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

[Paul Schofield]

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