DEI Workforce Diversity SpotApp

SpotApps are ThoughtSpot’s out-of-the-box solution templates built for specific use cases and data sources. They are built on ThoughtSpot Modeling Language (TML) Blocks, which are pre-built pieces of code that are easy to download and implement directly from the product.

The DEI Workforce Diversity SpotApp mimics the BambooHR or Workday data model. When you deploy it, ThoughtSpot creates several Worksheets, Answers, and Liveboards, based on your BambooHR or Workday data in your cloud data warehouse.

This is a sample Liveboard, created after you deploy the DEI Workforce Diversity SpotApp:

DEI Workforce Diversity SpotApp Liveboard

Use the DEI Workforce Diversity SpotApp to get invaluable insight into your workforce diversity data from your HR application.

Prerequisites

Before you can deploy the DEI Workforce Diversity SpotApp, you must complete the following prerequisites:

  • Review the required tables and columns for the SpotApp.

  • Ensure that your columns match the required column type listed in the schema for your SpotApp.

  • Sync all tables and columns from BambooHR or Workday to your cloud data warehouse. You can sync only the required tables and columns, but ThoughtSpot recommends syncing all tables and columns from BambooHR or Workday to your CDW. The columns can be BambooHR or Workday’s out-of-the-box columns, or any custom columns that you are using instead of the out-of-the-box columns.

    If you are using an ETL/ELT tool or working with another team in your organization to move data, our recommendation is that you sync all columns from the tables listed in the SpotApp.
  • Obtain credentials and SYSADMIN privileges to connect to your cloud data warehouse. The cloud data warehouse must contain the data you would like ThoughtSpot to use to create Answers, Liveboards, and Worksheets. Refer to the connection reference for your cloud data warehouse for information about required credentials:

  • The connection name for each new SpotApp must be unique.

  • Administrator access to BambooHR or Workday

  • Access to all BambooHR or Workday data, in your cloud data warehouse. Refer to DEI Workforce Diversity SpotApp schema for more details. You may need to transfer the BambooHR or Workday data to your cloud data warehouse, if it is not there already.

  • Run the required SQL commands in your cloud data warehouse. Refer to Run SQL commands.

Run SQL commands

The following SQL commands help standardize data types and column names. Replace the database name, schema name, disability column name, grade column name, LGBTQI+ column name, age column name (Workday only), and state column name (Workday only) with your specific information.

The following SQL examples are for the Snowflake cloud data warehouse. You may need to modify the code for the SQL requirements of your specific cloud data warehouse.

SQL commands for BambooHR:

Click on the dropdown to view the SQL commands for BambooHR.
create or replace view "<DATABASE NAME>"."<SCHEMA NAME>"."EMPLOYEE_FACT" as select
EMPLOYEENUMBER as EMPLOYEE_NUMBER,
FULLNAME1 as FULL_NAME,
EMPLOYMENTHISTORYSTATUS as EMPLOYMENT_STATUS,
LOCATION as CURRENT_LOCATION,
DEPARTMENT as CURRENT_DEPARTMENT,
JOBTITLE as CURRENT_JOB_TITLE,
cast(AGE as int) as AGE,
CITY as CITY,
COUNTRY as COUNTRY,
DIVISION as REGION,
<DISABILITY_COLUMN_NAME> as DISABILITY,
ETHNICITY as ETHNICITY,
GENDER as GENDER,
<GRADE_COLUMN_NAME> as GRADE,
TO_DATE(HIREDATE) as HIRE_DATE,
<LGBTQI+_COLUMN_NAME> as "LGBTQI+",
STATE as STATE,
STATUS as STATUS,
SUPERVISORID as SUPERVISOR_ID,
TO_DATE(TERMINATIONDATE) as TERMINATION_DATE,
WORKEMAIL as WORK_EMAIL from "<DATABASE NAME>"."<SCHEMA NAME>"."CUSTOM_REPORTS_STREAM";

If you have any values such as 0000-00-00 for the TERMINATIONDATE, replace W.TERMINATION_DATE as TERMINATION_DATE, in this SQL script with:

case when TERMINATIONDATE='0000-00-00' then NULL else TO_DATE(TERMINATIONDATE) end as TERMINATION_DATE.

SQL commands for Workday:

Click on the dropdown to view the SQL commands for Workday.
create or replace view "<DATABASE NAME>"."<SCHEMA NAME>"."EMPLOYEE_FACT"
as select
W.ID as EMPLOYEE_NUMBER,
CONCAT(PN.FIRST_NAME,' ', PN.LAST_NAME) as FULL_NAME,
ER.END_EMPLOYMENT_REASON as EMPLOYMENT_STATUS,
ORG.LOCATION as CURRENT_LOCATION,
ORG.NAME as CURRENT_DEPARTMENT,
PS.POSITION_TITLE as CURRENT_JOB_TITLE,
<AGE_COLUMN_NAME> as AGE,
AD.CITY as CITY,
AD.COUNTRY as COUNTRY,
AD.COUNTRY_REGION as REGION,
PD.DISABILITY_GRADE as DISABILITY,
PI.ETHNICITY as ETHNICITY,
PI.GENDER as GENDER,
<GRADE_COLUMN_NAME> as GRADE,
W.HIRE_DATE as HIRE_DATE,
<LGBTQI+_COLUMN_NAME> as "LGBTQI+",
<STATE_COLUMN_NAME> as STATE,
W.ACTIVE as STATUS,
PS.SUPERVISORY_ORGANIZATION_ID as SUPERVISOR_ID,
W.TERMINATION_DATE as TERMINATION_DATE,
EMAIL.EMAIL_ADDRESS as WORK_EMAIL from "<DATABASE NAME>"."<SCHEMA NAME>"."WORKER" W
LEFT OUTER JOIN "<DATABASE NAME>"."<SCHEMA NAME>"."ORGANIZATION" ORG ON W.ID = ORG.MANAGER_ID
LEFT OUTER JOIN "<DATABASE NAME>"."<SCHEMA NAME>"."POSITION" PS ON W.ID = PS.WORKER_ID
LEFT OUTER JOIN "<DATABASE NAME>"."<SCHEMA NAME>"."POSITION_END_EMPLOYMENT_REASON" ER ON W.ID = ER.WORKER_ID
LEFT OUTER JOIN "<DATABASE NAME>"."<SCHEMA NAME>"."PERSONAL_INFORMATION" PI ON W.ID = PI.ID
LEFT OUTER JOIN "<DATABASE NAME>"."<SCHEMA NAME>"."PERSON_NAME" PN ON PI.ID = PN.PERSONAL_INFO_SYSTEM_ID
LEFT OUTER JOIN "<DATABASE NAME>"."<SCHEMA NAME>"."ADDRESS" AD ON PI.ID = AD.PERSONAL_INFO_SYSTEM_ID
LEFT OUTER JOIN "<DATABASE NAME>"."<SCHEMA NAME>"."PERSON_DISABILITY" PD ON PI.ID = PD.PERSONAL_INFO_SYSTEM_ID
LEFT OUTER JOIN "<DATABASE NAME>"."<SCHEMA NAME>"."PERSON_CONTACT_EMAIL_ADDRESS" EMAIL ON PI.ID = EMAIL.PERSONAL_INFO_SYSTEM_ID;

If you have any values such as 0000-00-00 for the TERMINATIONDATE, replace W.TERMINATION_DATE as TERMINATION_DATE, in this SQL script with:

case when TERMINATIONDATE='0000-00-00' then NULL else TO_DATE(TERMINATIONDATE) end as TERMINATION_DATE.

Deploy the DEI Workforce Diversity SpotApp

After you complete the prerequisites, you are ready to deploy the DEI Workforce Diversity SpotApp and begin leveraging its pre-built content.

To deploy the DEI Workforce Diversity SpotApp, refer to Deploying SpotApps.

After you deploy the DEI Workforce Diversity SpotApp

After you deploy the DEI Workforce Diversity SpotApp, you must update the formulas in the Worksheet the SpotApp created with your specific information. Your department names may not match the exact names used in the formula for Functional Groups, for example, so you must replace the names in the formula with your own department names.

You must update the following formulas in the Employee Data Worksheet in ThoughtSpot:

  • Employee Count(Full Time Employee)

  • Functional Groups

  • Level Buckets

  • Level Groups

DEI Workforce Diversity SpotApp schema

The following table describes the schema for the DEI Workforce Diversity SpotApp. The EMPLOYEE_FACT table is created when you run the SQL commands in your cloud data warehouse.

Table Column Column type Required column

EMPLOYEE_FACT

CITY

VARCHAR

N

EMPLOYEE_FACT

COUNTRY

VARCHAR

Y

EMPLOYEE_FACT

CURRENT_DEPARTMENT

VARCHAR

Y

EMPLOYEE_FACT

CURRENT_JOB_TITLE

VARCHAR

N

EMPLOYEE_FACT

CURRENT_LOCATION

VARCHAR

Y

EMPLOYEE_FACT

DISABILITY

VARCHAR

Y

EMPLOYEE_FACT

EMPLOYEE_NUMBER

VARCHAR

Y

EMPLOYEE_FACT

EMPLOYMENT_STATUS

VARCHAR

Y

EMPLOYEE_FACT

ETHNICITY

VARCHAR

Y

EMPLOYEE_FACT

FIRST_GENDER

VARCHAR

Y

EMPLOYEE_FACT

FULL_NAME

VARCHAR

N

EMPLOYEE_FACT

GRADE

VARCHAR

Y

EMPLOYEE_FACT

HIRE_DATE

DATE

Y

EMPLOYEE_FACT

LGBTQI+

VARCHAR

Y

EMPLOYEE_FACT

REGION

VARCHAR

N

EMPLOYEE_FACT

STATE

VARCHAR

N

EMPLOYEE_FACT

STATUS

VARCHAR

Y

EMPLOYEE_FACT

SUPERVISOR_ID

VARCHAR

N

EMPLOYEE_FACT

TERMINATION_DATE

DATE

Y

EMPLOYEE_FACT

WORK_EMAIL

VARCHAR

N

EMPLOYEE_FACT

AGE

NUMBER

Y