Workspace Demographics PBI Report

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 Workspace_Demographics.pbix is separated from the datasets dataset_StaffingALL_BT.pbix

dataset_StaffingALL_BT

The dataset is a basically star schema using Empjob Fact Tableas the fact table. Snowflake extensions apply to a 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 13 tables.

Dataset Model Diagram

 

Table

Source (dataflow, entity, workspace)

dataflow details

Table

Source (dataflow, entity, workspace)

dataflow details

CareerTracks_Titlecodes_3

CrareerTracks,CareerTracks_Titlecodes_3 , ws_SABOG

 

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

 

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

Salary Diff Percentile = GENERATESERIES(0, 1, 0.01)

 

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

,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 (

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' )
)