...
Table | Source (dataflow, entity, workspace) | dataflow details |
|---|---|---|
CareerTracks_Titlecodes_3 | CrareerTracks,CareerTracks_Titlecodes_3 , ws_SABOG | |
Empjob Fact table | Empjob, empjob, wsBT_ SABOGOrg | |
Employee | df_UCPath, UCPath Emp Demographic", ws_payroll | |
Fiscal Calendar | Calendars, Julian Fiscal, ws_public data | |
IDMSecurityBTStaffing | IDMSecurtiy, IDMSecurtiytBTStaffing, ws_SABOG | |
Order of YOS | manually entered table | |
Remote_Hybrid | HybridRemoteOnSiteGoogle, Remote_Hybrid, ws_SABOG [Development] | |
RLS Manager | ? Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText( | |
Salary Diff Percentile |
| |
Sorting Annual Salary Category | manually entered table | |
Time in Position | ? Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText( | |
UCPath Position | Datawarehouse, UCPath Position, ws_SABOG |
...
SELECT *
FROM dbo.emp_job
WHERE job_dept_code in ('SAIS', 'CITO‘CITO', 'IBRM', 'INFO', 'INFR', 'IPLA', 'ISEC', 'ITEC', 'ITSS', 'TELE','CCTR','IDCL','ARIT')
Employee
warehouse.ucsb.edu\polaris,1660,waredept
...
SELECT [YYYYMMDD_ID]
,[Date]
,[Year]
,[YrNN]
,[YYYYMM]
,[Month_No]
,[Day]
,[Week_Day_No]
,[Week_Day_Name]
,[Week_Day_Name2]
,[Week_Day_Name3]
,[Julian_Day]
,[Julian_Week]
,[Week_No]
,[Quarter_No]
,[Last_Day_Of_Month]
,[Last_Date_Of_Month]
,[Fiscal_Month_No]
,[Fiscal_Quarter_No]
,[Fiscal_Quarter]
,[Fiscal_Year_Name]
,[Month]
,[Fiscal_Year]
,[FiscalYearFromCurrent]
,[FiscalYearLabel]
FROM [dbo].[vw_pbiJulianFiscal]
IDMSecurityBTStaffing
securityadministration.sql.sa.ucsb.edu,2433 securityadministration
DECLARE @return_value int
EXEC @return_value = [Ent_Security].[usp_GetPowerBIDepartmentPermissions]
@ApplicationName = N'Ucsb.Sa.DataServices.PowerBI.BTStaffing'
SELECT 'Return Value' = @return_value
UCPath Position
warehouse.ucsb.edu\polaris,1660,waredept
SELECT distinct
CONCAT(AL1.position_no , '-',AL1.position_eff_date,'-',j.employee_id,j.emp_record) AS position_key
,j.employee_id
,j.emp_record
,j.employee_name
,AL1.position_no -- position_nbr Position No
,AL1.position_eff_status -- eff_status Effective Status
,AL1.position_title -- position_data.descr
,AL1.position_dept_code -- Position Code
,AL1.position_dept_desc -- Position Title
,AL1.position_eff_date
,AL1.position_eff_date_f0
,AL1.position_eff_date_f1
,AL1.position_eff_date_f2
,AL1.position_eff_date_f3
,AL1.position_status_desc -- posn_status
,AL1.job_code -- Job Code
,AL1.job_desc -- Job Desc
,AL1.job_code_desc --Job Code n Desc
,AL1.position_status_code -- posn_status Position Status
,AL1.position_status_code_desc -- posn_status Position Status Desc
,AL1.reports_to_position_no -- reports_to Reports To Position
,AL1.reports_to_position_title
,AL1.physical_location -- location Location
,AL1.std_hours
,AL1.union_code
,AL1.union_desc
,AL1.barg_unit
,AL1.barg_unit_desc
,AL1.manager_level
,AL1.flsa_status_code
,AL1.flsa_status_desc
,AL1.class_indc
,AL1.fte
,AL1.fte_position_minus_job_current
,AL1.position_pool_id
,AL1.budgeted_position_flag
| Code Block |
|---|
,AL1.fixed_variable_code |
,AL1.fixed_variable_desc
,AL1.head_count_max
,AL1.head_count_max_job_current
,AL1.head_count_max_times_fte
,AL1.sal_plan_code
,AL1.sal_plan_desc
,AL1.sal_plan_code_desc
,AL1.step
,AL1.union_code_desc
,AL1.position_filled_currently_flag
,AL1.personnel_program_code
,AL1.personnel_program_desc
,AL1.grade
,AL1.reg_temp_desc
from emp_position AL1
left outer join emp_job j on j.position_no = al1.position_no and j.job_record_status = 'current'
and j.job_code = al1.job_code
where (
| Code Block |
|---|
al1.position_record_status not in ('past')
and
al1.position_eff_status = 'A'
and al1.position_eff_date_f1 |
= (select max(b.position_eff_date_f1)
from dbo.emp_position b
where b.position_no= al1.position_no
and al1.job_code = b.job_code
and al1.position_filled_currently_flag = b.position_filled_currently_flag
AND b.position_record_status = 'current' )
)