SQL and Power BI Driver for Tix ticketing

Ideas (en)
,
1

Tix provides ticketing.

Tix is Swagger-based as described on Swagger UI.

2

As a workaround, a query like the following can be used:

create or replace view tix_settings
as
select 'the-secret-token' token
,      500 pagesize
,      true diskCache
,      3600 diskCacheMaxAgeSec
;
create or replace view tix_customers
as
select rge.value
,      jte.*
from   tix_settings@datadictionary stg
join   range(400000 / stg.pagesize, 0)@datadictionary rge
join   HTTPDOWNLOAD@DataDictionary
       ( url => 'https://crmapi.tixnl.nl/v3/customers?pageSize=' || to_char(stg.pagesize) || '&page=' || to_char(rge.value)
       , method => 'GET'
       , acceptMimeType => 'application/json'
       , headers => 'Authorization=Bearer ' || stg.token
       , diskCache => stg.diskCache
       , diskCacheMaxAgeSec => stg.diskCacheMaxAgeSec
       ) htp
join   jsontable
       ( 'Data[*]'
         passing htp.CONTENTS_CHAR
         columns id              int      path 'Id'
         ,       gender          int      path 'Gender'
         ,       Name            varchar2 path 'Name'
         ,       Email           varchar2 path 'Email'
         ,       FirstName       varchar2 path 'FirstName'
         ,       LastName        varchar2 path 'LastName'
         ,       LastNamePrefix  varchar2 path 'LastNamePrefix'
         ,       CompanyName     varchar2 path 'CompanyName'
         ,       CRN             varchar2 path 'CRN'
         ,       SSN             varchar2 path 'SSN'
         ,       AddressOne      varchar2 path 'AddressOne'
         ,       AddressTwo      varchar2 path 'AddressTwo'
         ,       ZipCode         varchar2 path 'ZipCode'
         ,       City            varchar2 path 'City'
         ,       StreetName      varchar2 path 'StreetName'
         ,       HouseNumber     varchar2 path 'HouseNumber'
         ,       HouseExtension  varchar2 path 'HouseExtension'
         ,       Mobile          varchar2 path 'Mobile'
         ,       HomePhone       varchar2 path 'HomePhone'
         ,       WorkPhone       varchar2 path 'WorkPhone'
         ,       Description     varchar2 path 'Description'
         ,       DateOfBirth     date     path 'DateOfBirth'
         ,       AlternativeEmails varchar2 path 'AlternativeEmails'
         ,       Country         varchar2 path 'Country'
         ,       CountryCode     varchar2 path 'CountryCode'
         ,       Title           varchar2 path 'Title'
         ,       Initials        varchar2 path 'Initials'
         ,       Latitude        decimal  path 'Latitude'
         ,       Longitude       decimal  path 'Longitude'
         ,       DisallowMerge   boolean  path 'DisallowMerge'
         ,       IsAnonymized    boolean  path 'IsAnonymized'
         ,       Created         datetime path 'Created'
         ,       Edited          varchar2 path 'Edited'
         ,       IsDeleted       boolean  path 'IsDeleted'
         ,       DeletedDate     datetime path 'DeletedDate'
         ,       LanguageId      int      path 'LanguageId'
) jte
;

For all tables, columns, rate limiting and filtering capabilities, a driver can be added.

3

De driver voor Tixly is beschikbaar via Verbind Power BI met Tixly, 180 dagen gratis.