Skip to content

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.

TableKeyWhat
peopleperson_idEveryone employed on the day, with their job that day
jobsjob_idJob history: one row per period with the same details
teams, locations, legal_entitiestheir idsThe structure
positionsposition_idOpen, on-hold, filled and cancelled positions (never drafts)
headcount_monthlymonthHeadcount, FTE, joiners, leavers, turnover, average tenure and span of control, 36 months
headcount_by_teammonth, unit_idPeople per team at each month end (rows add up to the month)
checklistschecklist_idChecklists with task counts
paypay_idPay 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.

Shell
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), the OData-Version: 4.0 header.
  • 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:

Text
<folder>/<table>/dt=<YYYY-MM-DD>/<table>_<HHMMSS>.parquet   (or .csv)
<folder>/_schema/v1/schema_<YYYY-MM-DD>_<HHMMSS>.json

dt= 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 principal principal://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:AssumeRoleWithWebIdentity on the conditions <issuer host>/federation:aud and :sub, with s3:PutObject on 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.

Developer docs