The report allows comprehensive on SAIT Staffing. It is in workspace ws_SABOG. The pipeline contains ws_SABOG [Development], ws_SABOG [Test], ws_SABOG. Datasets can be edited in ws_SABOG [Development]. Tedi Tehrani is the author. The report StaffingWorkspace_AllDemographics.pbix is separated from the datasets dataset_StaffingALL_BT.pbix
dataset_StaffingALL_BT
The dataset is a basically star schema using StaffingFact as Empjob Fact Tableas the fact table. Snowflake extensions apply to a few Employee and empstartpoistion dimensions. The dataset users parameters that are set as rules in the pipeline to connect to dev, test, prod dataflows. As of this publishing the dataset contains 11 13 tables.
Dataset Model Diagram
...
Table | Source (dataflow, entity, workspace) | Account | datawarehouse, account dataflow details | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
CareerTracks_Titlecodes_3 | CrareerTracks,CareerTracks_Titlecodes_3 , ws_SABOG | Control Point | datawarehouse, account | |||||||||||||
Empjob Fact table | Empjob, empjob, BT_ Org | |||||||||||||||
Employee | df_UCPath, UCPath Emp Demographic", ws_payroll | |||||||||||||||
Fiscal Calendar | Calendars, Julian Fiscal, ws_public data | |||||||||||||||
IDMSecurityBTStaffing | IDMSecurtiy, IDMSecurtiytBTStaffing, ws_SABOG | Cost Centers | GMC Costing, costcenters, ws_SABOG | Cost Types | GMC Costing, costTypes, ws_SABOG | Employee | df_UCPath,UCPath Emp Demographic", ws_payroll | Fund | datawarehouse, Fund , ws_SABOG | Project Codes | GMC Costing,projectCodes,ws_SABOG | StaffingFact | Sub | UC Path Job Funding | UC Path Position | |
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 |
Dataflow Sources
CareerTracks_Titlecodes_3
\\sa8\Data\PowerBI\BT_Org\CareerTracks_Titlecodes_3.csv
Empjob Fact table
warehouse.ucsb.edu\polaris,1660,waredept
SELECT *
FROM dbo.emp_job
WHERE job_dept_code in ('SAIS', ‘CITO', 'IBRM', 'INFO', 'INFR', 'IPLA', 'ISEC', 'ITEC', 'ITSS', 'TELE','CCTR','IDCL','ARIT')
Employee
warehouse.ucsb.edu\polaris,1660,waredept
;WITH cteservice AS
(
SELECT employee_id, employee_name,uc_curr_bal, uc_curr_bal/12 AS YearsofService,primary_job_dept_desc,
ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY ASofdate DESC) AS rownbr
FROM
dbo.emp_accrual_balance_ucp
where acc_desc = 'Employment Service Months'
)
SELECT *
FROM dbo.emp_person -- UCPath Emp Demographic x
LEFT JOIN
(
SELECT * from cteservice
WHERE cteservice.rownbr = 1
) xxx
ON xxx.employee_id = emp_person.employee_id
UNION
SELECT
0,'0','Vacant',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1
Fiscal Calendar
isis.sql.sa.ucsb.edu,2433,isis
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' )
)