1. Greenhouse Support
  2. What’s new
  3. Release notes
  4. Business Intelligence Connector updates

Business Intelligence Connector update – November 2, 2022

We are making updates to the BI Connector schema on November 2, 2022. 

Download the latest schema file by clicking the attachment on the right side of the page.

Note: An invalid schema file for these updates was available on the date of publishing (November 2nd). The file has been updated. You can download the corrected file on the right side of the page.

Overview of changes

In the Business Intelligence Connector, the attributes table was generated as a unique list of values with an id that changed each time the data was refreshed. Then, to correctly match the list of values to a more specific table containing relevant Greenhouse Recruiting data, organizations had to write an additional JOIN to ensure the correct values were returned in the query.

With this update, the following changes will occur:

  • The id column will become a real, primary key ID as it exists in Greenhouse Recruiting, removing the need for the additional JOIN statement
  • The id will be static, and won't update with each data refresh
  • The created_at and updated_at columns provide the actual created_at and updated_at times instead of a null value

In addition to the changes outlined above, the following changes will occur on the jobs_attributes and scorecards_attributes tables:

  • The jobs_attributes table now will contain name and category columns
  • The jobs_attributes_id and jobs_attributes.attributes_id will contain matching values
  • The scorecards_attributes table will contain attribute_name and attribute_category columns
  • The attibute_id now joins to jobs_attributes.id instead of attributes_id

After this change, the job_attributes table contains the same data as the attributes table. The attributes table will be deprecated in a future release. Organizations should plan to update their queries with the following mapping.

attributes column Matching job_attributes column
attributes.id jobs_attributes.id
attributes.organization_id jobs_attributes.organization_id
attributes.name jobs_attributes.name
attributes.created_at jobs_attributes.created_at
jobs_attributes.updated_at jobs_attributes.updated_at

Note: Since the updated table will reflect the actual values in your Greenhouse Recruiting organization, this table will slightly increase in size.

Example query

When the interviews table contained a generated id, an additional JOIN statement was needed to query the tables.

SELECT * 

  FROM scorecards_attributes

  JOIN attributes ON attributes.id = scorecard_attributes.attribute_id

 WHERE attributes.name = 'Public Speaking'

After the above updates, the ID in the Business Intelligence Connector will match the ID Greenhouse Recruiting, providing a stable id for the table and allowing you to query each table directly without the additional JOIN: 

SELECT * 

  FROM scorecards_attributes

 WHERE attribute_name = 'Public Speaking'

Downloads