Discoverer Users List
To update the Discoverer Users List, the Users need to be extracted from the Discoverer End User Layer using the attached SQL coding in each of the Discoverer Databases -
- Campus Solutions - CSRPT - csrpt_eul.eul5_qpp_stats
- Finance - ADMAPPS / GLSNAP - glsnap_eul10g.eul5_qpp_stats
- Human Resources - HRPPRD - HRRPT_eul.Eul5_Qpp_Stats
This data is joined with a master file DISCOVERER_USER LINKING_DATA -The Discoverer USERID is mapped to MaineStreet USERID and EMPLID (much of this was done manually and may need updating) Discoverer USERIDs are not necessarily MaineStreet USERID.
The result is joined to an active employee file from HR
UMS_DTS_EMPS_FOR_DISC_USAGE which provides employee demographics
This can also be used to join with the MaineStreet query access data to compare access between the databases.
UMS_DTS_QUERY_ACCESS_CSRPT
UMS_DTS_QUERY_ACCESS_HRPRD
UMS_DTS_QUERY_ACCESS_FINANCE
UMS_DTS_ROLES_DESCRIPTIONS is a Public Query in FNPRD that returns all roles for review
Discoverer Query Inventory
-- ADVR ADVANCE eul_admin.eul5_qpp_stats 02_10_17
select distinct '' PURPOSE,
'' FREQUENCY,
'' PRIORITY,
'' SHARED_WITH_DARTS,
'ADVANCE' DISCOVERER_DATABASE,
qs.qs_doc_name query_name,
qs.qs_doc_owner query_author,
qs.qs_created_by query_user,
to_char(qs.qs_created_date, 'mm-dd-yy') query_run_day,
to_char(qs.qs_created_date, 'mm-yy') query_run_month,
to_char(qs.qs_created_date, 'yyyy') query_run_year
from eul_admin.eul5_qpp_stats qs
where qs.qs_created_date > '31-dec-2014'
and (qs.qs_created_by in('BILL') or
qs.qs_doc_owner in('BILL'))
order by qs.qs_doc_name
-- CSRPT_eul.eul5_qpp_stats 06_20_17
select distinct '' PURPOSE,
'' FREQUENCY,
'' PRIORITY,
'' SHARED_WITH_DARTS,
'CSRPT' DISCOVERER_DATABASE,
qs.qs_doc_name query_name,
qs.qs_doc_owner query_author,
qs.qs_created_by query_user,
to_char(qs.qs_created_date, 'mm-dd-yy') query_run_day,
to_char(qs.qs_created_date, 'mm-yy') query_run_month,
to_char(qs.qs_created_date, 'yyyy') query_run_year
from csrpt_eul.eul5_qpp_stats qs
where qs.qs_created_date > '31-dec-2014'
and (qs.qs_created_by in('BILL') or
qs.qs_doc_owner in('BILL'))
order by qs.qs_doc_name
--glsnap_eul10g.eul5_qpp_stats 06_20_17
select distinct '' PURPOSE,
'' FREQUENCY,
'' PRIORITY,
'' SHARED_WITH_DARTS,
'GLSNAP' DISCOVERER_DATABASE,
qs.qs_doc_name query_name,
qs.qs_doc_owner query_author,
qs.qs_created_by query_user,
to_char(qs.qs_created_date, 'mm-dd-yy') query_run_day,
to_char(qs.qs_created_date, 'mm-yy') query_run_month,
to_char(qs.qs_created_date, 'yyyy') query_run_year
from glsnap_eul10g.eul5_qpp_stats qs
where qs.qs_created_date > '31-dec-2014'
and (qs.qs_created_by in('BILL') or
qs.qs_doc_owner in('BILL'))
order by qs.qs_doc_name
-- HRRPT_eul.Eul5_Qpp_Stats 06_20_17
select distinct '' PURPOSE,
'' FREQUENCY,
'' PRIORITY,
'' SHARED_WITH_DARTS,
'HRRPT' DISCOVERER_DATABASE,
qs.qs_doc_name query_name,
qs.qs_doc_owner query_author,
qs.qs_created_by query_user,
qs.qs_created_date,
to_char(qs.qs_created_date, 'mm-dd-yy') query_run_day,
to_char(qs.qs_created_date, 'mm-yy') query_run_month,
to_char(qs.qs_created_date, 'yyyy') query_run_year
from HRRPT_eul.Eul5_Qpp_Stats qs
where qs.qs_created_date > '31-dec-2014'
and (qs.qs_created_by in('PASHBY','PAMASHBY','EMICHAUD','JDMURPHY') or
qs.qs_doc_owner in('PASHBY','PAMASHBY','EMICHAUD','JDMURPHY'))
order by qs.qs_created_by, qs.qs_doc_name