Guides
Data exports and BI tools
On this page
Bizisy delivers one analytics dataset to the company's own data tools: scheduled exports to its BigQuery, Google Cloud Storage, Amazon S3 or Azure Blob Storage, and a read-only OData v4 feed for Power BI, Excel and Looker Studio. The company is the recipient: files land in its own storage and its own reports read the feed.
The dataset#
data.schema.get lists the tables, their keys and typed columns (string, date, integer, number, boolean, timestamp). Schema version 1; a change of meaning or type bumps it, new columns don't.
| Table | Key | What |
|---|---|---|
people | person_id | Everyone employed on the day, with their job that day |
jobs | job_id | Job history: one row per period with the same details |
teams, locations, legal_entities | their ids | The structure |
positions | position_id | Open, on-hold, filled and cancelled positions (never drafts) |
headcount_monthly | month | Headcount, FTE, joiners, leavers, turnover, average tenure and span of control, 36 months |
headcount_by_team | month, unit_id | People per team at each month end (rows add up to the month) |
checklists | checklist_id | Checklists with task counts |
pay | pay_id | Pay history, only while pay sharing is on |
Custom fields of the public and job tiers are columns named custom_<key> on their table. Tables join on Bizisy's ids (person_id, unit_id, manager_id, …).
What is never in it: anyone's private details (home address, birthday, emergency contacts, bank, tax and social security details), why someone left, and anything planned. A row starting after the day is left out, an end after the day reads as open, people who haven't started and outsiders aren't there, a position filled by someone who hasn't started reads as open, and a checklist appears once its day has come (offboarding ones once the last day has passed). Pay is there only after an owner, admin or HR member turns on Share pay with data tools (Integrations → Power BI and Excel → Access); it is recorded in Activity.
Erasing a person removes them from later deliveries and from the feed. Files and tables already delivered, and data a report already loaded, are the company's own copies: delete them there if needed.
The OData feed (Power BI, Excel)#
Address: https://api.bizisy.com/odata/v1/. Authenticate with the company's BI token (created once by an owner or admin in Integrations → Power BI and Excel → Access, shown once): HTTP Basic with any user name and the token as password (what Power BI and Excel ask for), or Authorization: Bearer <token>. The token can only read the feed. A developer API key is not a BI token.
curl -u bi:$BIZISY_BI_TOKEN "https://api.bizisy.com/odata/v1/People?\$select=person_id,given_name,unit_id&\$filter=employment_type%20eq%20'full_time'&\$top=100"Entity sets: People, Jobs, Teams, Locations, LegalEntities, Positions, HeadcountMonthly, HeadcountByTeam (key id), Checklists, and Pay while pay is shared. People takes the custom option as_of=YYYY-MM-DD (never after today).
What the feed implements of OData 4.01:
- Part 1, Protocol: the service document and
$metadata(§11.1), reading entity sets with system query options (§11.2.5), server-driven paging with@odata.nextLink, 1,000 rows a page (§11.2.5.7),/$count(§11.2.10), theOData-Version: 4.0header. - Part 2, URL conventions:
$select,$filter(eq,ne,gt,ge,lt,le,and,or,not, parentheses,contains,startswith,endswith,tolower,toupper,trim,length,year,month,day; string, number, boolean,null, date and date-time literals),$orderby,$top,$skip,$count=true. - JSON format:
odata.metadata=minimal,@odata.context,@odata.count,@odata.nextLink, and errors as{ "error": { "code", "message" } }. - CSDL XML: one entity type per set with its key,
Edm.String,Edm.Date,Edm.Int32,Edm.Double,Edm.Boolean,Edm.DateTimeOffset, and each column's description.
Not implemented: $expand, $search, $apply, $compute, delta links (501), and any write (405).
Limits: 120 requests a minute per BI token and 600 per client address; ten wrong tokens a minute from one address are refused for the rest of the minute (429 with Retry-After). A paused or closing company's feed answers 403.
Looker Studio#
Bizisy's community connector reads the same feed (one data source per table) with the BI token as its key. It is listed in Integrations once available on Bizisy's side.
Scheduled exports#
Owners and admins connect a destination in Integrations (BigQuery, Google Cloud Storage, Amazon S3, Azure Blob Storage) and choose when (every day or a weekday, at an hour in the company's time zone; default every day at 06:00), which tables, and for files the format (Parquet or CSV) and folder. Deliver now sends one at once. Each connection's state, delivery log (30 days, never naming anyone) and health are in integrations.connections.get; failing deliveries back off and show in Home after three in a row.
Files (Cloud Storage, S3, Azure) are written under new names each time, so write-only access is enough:
<folder>/<table>/dt=<YYYY-MM-DD>/<table>_<HHMMSS>.parquet (or .csv)
<folder>/_schema/v1/schema_<YYYY-MM-DD>_<HHMMSS>.jsondt= is a Hive-style day partition, read as such by BigQuery external tables, Athena and Synapse. CSV follows RFC 4180 (UTF-8, comma, header row; cells are written as they are, so open delivered CSV files in a spreadsheet only as data, or use the OData feed in Excel); Parquet has typed columns (dates as DATE, instants as TIMESTAMP_MILLIS). BigQuery receives one load job per table into <dataset>.<table>, replacing the table each time with the dataset's schema and column descriptions.
Keyless access for Google Cloud and AWS#
Bizisy is an OpenID Connect issuer (https://api.bizisy.com/federation, ES256 keys published at /federation/jwks.json). Each delivery Bizisy signs a five-minute token whose subject is org:<company id> and whose audience is unique to the company; data.settings.get returns both, and the setup steps in the app show them filled in.
- Google Cloud (Workload Identity Federation): create a pool and an OIDC provider with that issuer and allowed audience, mapping
google.subject=assertion.sub, then grant the principalprincipal://iam.googleapis.com/projects/<number>/locations/global/workloadIdentityPools/<pool>/subject/org:<company id>Storage Object Creator on the bucket, or BigQuery Data Editor on the dataset and BigQuery Job User on the project. No service account key exists. - AWS (IAM OIDC provider): add the issuer as an OpenID Connect provider with the company's audience, and a role whose trust policy allows
sts:AssumeRoleWithWebIdentityon the conditions<issuer host>/federation:audand:sub, withs3:PutObjecton the bucket. No access key exists. - Azure Blob Storage: a container SAS (not an account SAS) with Create and Write only and an expiry (Bizisy reminds you 30 days before), pasted once and kept encrypted.
Connecting checks the access by writing a check file under _bizisy/.
Something missing or wrong on this page? Write to hello@bizisy.com.