Zoomed Image
USA
800 691 9120
UK
01225 704844

Federated Data

xAssets Configuration Guide
Integration and Data Operations with Transformations

Federated Data

Data federation displays data from an external system within xAssets queries, without importing or copying that data into the xAssets database. The external data is queried live from another system on the same LAN or from a web service, and the results appear alongside xAssets data in queries and on forms.

This page explains how federation works and when to use it instead of importing data through a transformation.

Prerequisites

  • A linked server configured in SQL Server that connects to the external database
  • Permission to create User SQL queries in xAssets (see System and User SQL Queries)
  • Knowledge of the external system's database schema

How Federation Works

Federation uses SQL Server's OPENQUERY command or linked server syntax to query external data sources at runtime. Because the query runs live, the results always reflect the current state of the external system -- there is no synchronisation delay or stale data.

No transformation is needed for federated data. Instead, you create a User SQL query that joins the external data to xAssets tables.

When to Use Federation vs. Import

Approach Use When
Federation You need real-time data that must always be current; the data volume is small enough for live queries to perform well; the external system is on the same network
Import (Transformation) The external system is remote or slow; you need to query the data frequently and performance matters; you need to apply data cleaning or mapping rules; you need an offline copy

Warning: Federated queries run live against the external database every time the query is viewed. If the external system is slow or the data volume is large, this can cause noticeable delays in the xAssets user interface. For large datasets, consider importing the data on a schedule instead.

Example

The following User SQL query lists CommVault backup information on the Asset Entry Screen by joining the external CommVault database to the xAssets Asset table:

SELECT a.JobID, a.Status, a.TimeStart, a.TimeEnd,
       a.UncompSize, a.CompSize, a.NumOfObjects,
       a.AtmptFailures, a.AtmptInterruptions,
       a.FailureReason, a.ClientName, a.SubClientName,
       a.Modified, b.AssetID
FROM OPENQUERY([CMV-CCCC.MYCO.COM\COMMVAULTQINETIX],
    'Select JobID, Status, TimeStart, TimeEnd, UncompSize,
     CompSize, NumOfObjects, AtmptFailures, AtmptInterruptions,
     FailureReason, ClientName, SubClientName, Modified
     from commserv..cnJobBkpHistory') a
RIGHT OUTER JOIN dbo.Asset b
    ON a.ClientName COLLATE database_default
     = b.spcComputerName COLLATE database_default
WHERE b.AssetID = %assetid%
  AND a.JobID IS NOT NULL
ORDER BY a.TimeStart DESC

In this example:

  • OPENQUERY executes a query against the linked CommVault server
  • The results are joined to the xAssets Asset table on the computer name field
  • The %assetid% parameter filters the results to the currently displayed asset
  • The COLLATE database_default clause handles potential collation differences between the two databases

Setting Up a Linked Server

Linked servers are configured in SQL Server Management Studio, not in xAssets. The steps are:

  1. In SQL Server Management Studio, expand Server Objects > Linked Servers
  2. Right-click and choose New Linked Server
  3. Configure the connection to the external database
  4. Grant the xAssets database service account permission to query the linked server
  5. Test the connection

Once the linked server is configured, you can reference it in xAssets User SQL queries using OPENQUERY or four-part naming (LinkedServer.Database.Schema.Table).