Power BI OData Connector

The Power BI OData Connector allows you to link ClickTime’s robust labor cost and time tracking data directly to your business intelligence environment. By connecting Power BI to the ClickTime OData v4 API facade, you can create custom reports, dashboards, and advanced data models tailored to your organization's analytics needs.

Prerequisites

Before beginning the connection process, ensure you have gathered the following credentials and connection points:

• Active ClickTime Account: A valid set of login credentials for the ClickTime platform.
• ClickTime API Token: A valid ClickTime API v2 token is required for authentication.
• OData Service URL: The unique ClickTime OData server URL provided by your administrator.

Authentication Security

The ClickTime OData connector maintains maximum data safety through API token passthrough authentication. Your unique ClickTime API token is safely securely passed in the Authorization header. This guarantees that all data accessed within Power BI remains perfectly consistent with your established ClickTime user permissions.

Available ClickTime Objects

The ClickTime OData v4 API provides direct access to the following entities for rich data reporting and downstream analysis:

• TimeEntries — Time Entry records containing hours, dates, and related associations.
• Users — Employee and user profile records.
• Jobs — Project and job configuration records.
• Clients — Customer and client profiles.
• Tasks — Task-level categorization records.
• Divisions — Division and organizational structural records.
• Timesheets — Container records for grouped time entries (timesheets).

Step-by-Step Connection Process (Power BI Online/Service)

You can connect an OData feed to Power BI Service (Online) by executing the following steps:

1. Log into your Power BI Service. On the navigation pane, select Get Data, search for OData, and select the OData data source.

2. Enter the following Connection settings in the configuration window:
   • URL: Input your unique ClickTime OData service URL.
   • Connection name: ClickTime OData Connection
   • Authentication kind: Select Basic
3. Enter your security credentials as follows:
   • Username: Enter the literal text clicktime (all lowercase).
   • Password: Paste your valid ClickTime API Token.
4. Click Next (or Sign In) to establish the secure connection and retrieve your metadata schema.

5. Once connected, the available ClickTime objects via OData will display on the left navigation panel. Select the tables you wish to import or edit, then click Load or Transform Data.

Important Technical Notes

To optimize your data architecture and query speed, please review these key technical guidelines:

• Query Filtering & Performance
  • For optimal retrieval performance, we highly recommend setting up filters, especially when working with massive historical datasets like Time Entries.
  • The ClickTime OData connector fully supports standard OData query operations, including $filter, $select, and $orderby. Use these early in your Power Query steps to let ClickTime's database handle the filtering workload before downloading records.
• Handling Custom Fields
  • Custom fields are currently returned within the API as a single nested JSON blob (CustomFields) or as a stringified version (CustomFieldsStr).
  • We recommend utilizing the stringified CustomFieldsStr column combined with Power BI's built-in JSON parsing capabilities to easily expand individual custom fields into independent reporting columns.