Reporting data mismatch between Creatio integration hub and external BI tool

BPM Creatio
, , , , , , , ,
1

We’re syncing reporting data from Creatio integration hub to our external BI tool (Tableau) for executive dashboards. The data sync completes successfully, but we’re seeing significant mismatches in numerical values and date fields between Creatio reports and Tableau dashboards.

For example, Creatio shows 1,250 closed cases for Q4, but Tableau shows 1,187 for the same period. Date-based filters seem off by several hours. Here’s the sync configuration:


Source: Creatio Integration Hub
Target: Tableau Cloud
Sync Schedule: Daily at 02:00 UTC
Transformation: Field mapping with date conversion

The field mapping validates without errors in the integration hub, but the actual data doesn’t match. We’ve verified that both systems are querying the same date ranges, yet the numbers differ by 5-8%. This is undermining confidence in our reporting accuracy. Has anyone dealt with field mapping validation or transformation logic issues when syncing Creatio data to external BI platforms?

2

The 5-8% variance suggests filtering or transformation issues rather than complete sync failure. Check if your field mapping includes all required filter fields. Sometimes the integration hub transforms date fields differently than the BI tool expects, causing date range queries to miss records at boundary conditions.

3

Time zone issues are extremely common in BI integrations. If Creatio is in one timezone and Tableau is interpreting dates in another, you’ll get exactly the kind of boundary mismatches you’re describing. A case closed at 11:30 PM in EST might appear as next day in UTC, shifting it into a different reporting period. Check your integration hub transformation logic to ensure explicit timezone handling. The date conversion should preserve the original timezone context, not just convert the timestamp value.

4

I checked and the transformation logic does convert timestamps to UTC during sync. But Creatio stores dates in the server’s local timezone. This could explain why cases closed late in the day are shifting to the next day in Tableau. How do I fix the field mapping to preserve timezone context?

5

That’s a good point. I noticed the date fields are being converted during sync. Creatio stores dates in one format and Tableau expects another. Could the transformation logic be dropping records that fall outside the converted date range? Also, should we be concerned about time zone alignment between systems?

6

You need to review the entire transformation pipeline. Field mapping validation only checks that fields exist and types match - it doesn’t validate the transformation logic correctness. For date fields, use explicit timezone conversion functions in the integration hub. Also, some calculated fields in Creatio use different aggregation logic than standard SQL, so if you’re mapping calculated fields, you might need to recreate the calculation logic in Tableau rather than syncing the pre-calculated values. This often causes the numerical mismatches you’re seeing.

7

Here’s a comprehensive solution addressing all three focus areas:

Field Mapping Validation: The integration hub’s automatic validation checks syntax but not semantic correctness. You need to manually validate that each mapped field actually contains the expected data:

  1. Export your current field mapping configuration from Integration Hub > Connections > [Your Tableau Connection] > Field Mappings
  2. For each mapped field, verify in Creatio that it contains the data you expect (not null, correct data type, populated for all records)
  3. Pay special attention to calculated fields - these often use Creatio-specific aggregation logic that doesn’t translate directly

For your case count mismatch, check if the ‘ClosedCases’ field you’re mapping is a calculated field or a direct database field. If it’s calculated, the calculation might use filters (like excluding certain case statuses) that aren’t obvious in the field name. Create a custom SQL query in Creatio to verify the exact count matches what you expect before syncing.

Transformation Logic Review: Your transformation logic has several issues that explain the data mismatch:


// Current problematic transformation:
DateClosed = CONVERT_TO_UTC(SourceDateClosed)
// This loses timezone context

// Corrected transformation:
DateClosed = CONVERT_TIMEZONE(SourceDateClosed, 'America/New_York', 'UTC')
DateClosedOriginal = SourceDateClosed
SourceTimezone = 'America/New_York'
// This preserves context for accurate filtering

The key is syncing both the converted timestamp AND the original timezone information. This allows Tableau to correctly interpret dates for filtering. Also review your numeric field transformations - if you’re using ROUND() or TRUNCATE() functions, you might be losing precision that accumulates across thousands of records.

For calculated fields, don’t sync the pre-calculated value. Instead, sync the source data and recreate the calculation in Tableau using Tableau’s calculation syntax. This ensures consistent aggregation logic.

Time Zone Alignment: This is likely your primary issue. Implement explicit timezone handling throughout the sync pipeline:

  1. In Creatio Integration Hub, configure the source connection with explicit timezone: System Designer > Integration Hub > Connections > [Creatio Source] > Advanced Settings > Source Timezone = ‘America/New_York’ (or your server timezone)

  2. In your transformation logic, use timezone-aware conversion functions for all date fields:


CreatedDate = AT_TIMEZONE(SourceCreatedDate, 'America/New_York', 'UTC')
ModifiedDate = AT_TIMEZONE(SourceModifiedDate, 'America/New_York', 'UTC')
ClosedDate = AT_TIMEZONE(SourceClosedDate, 'America/New_York', 'UTC')

  1. In Tableau, configure the data source to interpret all date fields as UTC: Data Source > [Your Connection] > Date Properties > Timezone = UTC

  2. Create calculated fields in Tableau for display in local timezone:


Closed Date (Local) = DATEADD('hour', -5, [Closed Date])
// Adjust offset for your timezone

Testing the Fix: After implementing these changes:

  1. Run a test sync with a small date range (single day)
  2. Manually verify record counts in both systems for that day
  3. Check boundary cases - records at midnight, early morning, late evening
  4. Compare not just counts but actual record IDs to identify which records are missing or duplicated

Your 5-8% variance (63 cases out of 1,250) likely represents cases closed between 7 PM and midnight EST that are being shifted to the next day in UTC conversion, placing them outside your Q4 filter in Tableau. Proper timezone handling will resolve this. The field mapping validation needs manual review to ensure calculated fields are handled correctly, and transformation logic must preserve timezone context throughout the pipeline.

8

Don’t forget to validate the actual field mappings themselves. Sometimes the integration hub maps fields by name similarity rather than explicit configuration, and you end up syncing the wrong source field to your target. Export your field mapping configuration and review it line by line against your Creatio schema.