Case Study #1:

A client wanted to extract data from Printanista / FM Audit to update Web Help Desk. Here is the data analysis that happened prior to integration.

Unique data? Part 1

    SELECT COUNT(SerialNumber) AS SerialCount, SerialNumber
    FROM [PrintanistaDownload].[dbo].[Devices]
    GROUP BY SerialNumber
    HAVING COUNT(SerialNumber) > 1

This returns serial numbers from Printanista / FM Audit that are duplicated. The result is 0 records.

Unique data? Part 2

    SELECT COUNT(SERIAL_NUMBER) AS SerialCount, SERIAL_NUMBER
    FROM [WHD].[dbo].[ASSET]
    WHERE DELETED IS NULL OR DELETED = '0'
    GROUP BY SERIAL_NUMBER
    HAVING COUNT(SERIAL_NUMBER) > 1

This returns serial numbers from Web Help Desk that are duplicated. The result is 0 records.

Data not matching?

    SELECT [DeviceID]
        ,[Description]
        ,[ServiceContact]
        ,[ContractID]
    FROM [PrintanistaDownload].[dbo].[Devices]
    WHERE NOT([Description] = 'Not On Contract' AND [ServiceContact] = 'Not On Contract' AND [ContractID] = 'Not On Contract')
        AND NOT([Description] LIKE 'On Contract%' AND [ServiceContact] LIKE 'On Contract%' AND [ContractID] LIKE 'On Contract%')
        AND NOT([Description] NOT LIKE '%Contract%' AND [ServiceContact] LIKE 'On Contract%' AND [ContractID] LIKE 'On Contract%')
        AND NOT([Description] NOT LIKE '%Contract%' AND [ServiceContact] LIKE 'Not On Contract' AND [ContractID] LIKE 'Not On Contract')

This returns devices from Printanista / FM Audit with conflicting contract status. The result is 28 records.

How many devices?

    SELECT *
    FROM [PrintanistaDownload].[dbo].[Devices] AS d
        LEFT JOIN (SELECT MAX([Date]) AS 'Last_Date', FMAMeters_Id
                   FROM [PrintanistaDownload].[dbo].[MeterReadings]
                   GROUP BY [FMAMeters_Id]) AS m ON m.FMAMeters_Id = d.FMADevice_Id
    ORDER BY d.SerialNumber

This returns the devices from Printanista / FM Audit with their most recent meter reading. The result is 1050 records.

Matching devices?

    SELECT *
    FROM [WHD].[dbo].[ASSET] AS a
        INNER JOIN [WHD].[dbo].[MODEL] AS m ON m.MODEL_ID = a.MODEL_ID
        INNER JOIN [WHD].[dbo].[ASSET_TYPE] AS t ON t.ASSET_TYPE_ID = m.ASSET_TYPE_ID
        INNER JOIN [WHD].[dbo].[ASSET_STATUS] AS s ON s.ID = a.STATUS_ID
        INNER JOIN [PrintanistaDownload].[dbo].[Devices] AS p ON p.SerialNumber = a.SERIAL_NUMBER
    WHERE s.INACTIVE = '0'
        AND s.NAME = 'In Service'
        AND (t.DELETED IS NULL OR t.DELETED = '0')
        AND t.ASSET_TYPE IN ('Copier', 'Printer')
        AND (m.DELETED IS NULL OR m.DELETED = '0')
        AND (a.DELETED IS NULL OR a.DELETED = '0')

This returns in service copiers and printers from Web Help Desk that haven’t been deleted with a matching record in Printanista / FM Audit. The result is 891 records.

Extra devices?

    SELECT *
    FROM [PrintanistaDownload].[dbo].[Devices] as d
        LEFT JOIN [WHD].[dbo].[ASSET] AS a ON a.SERIAL_NUMBER = d.SerialNumber
    WHERE a.SERIAL_NUMBER IS NULL
    ORDER BY d.ContractID

This returns devices in Printanista / FM Audit that don’t match anything in Web Help Desk. There are 113 records. 40 of those are not on contract. 73 are on contract.

Not in service devices?

    SELECT *
    FROM [WHD].[dbo].[ASSET] AS a
        INNER JOIN [WHD].[dbo].[MODEL] AS m ON m.MODEL_ID = a.MODEL_ID
        INNER JOIN [WHD].[dbo].[ASSET_TYPE] AS t ON t.ASSET_TYPE_ID = m.ASSET_TYPE_ID
        INNER JOIN [WHD].[dbo].[ASSET_STATUS] AS s ON s.ID = a.STATUS_ID
        INNER JOIN [PrintanistaDownload].[dbo].[Devices] AS p ON p.SerialNumber = a.SERIAL_NUMBER
    WHERE NOT(s.INACTIVE = '0' AND s.NAME = 'In Service')
        AND (t.DELETED IS NULL OR t.DELETED = '0')
        AND t.ASSET_TYPE IN ('Copier', 'Printer')
        AND (m.DELETED IS NULL OR m.DELETED = '0')
        AND (a.DELETED IS NULL OR a.DELETED = '0')
    ORDER BY p.ContractID

This returns copiers and printers that are not in service from Web Help Desk that haven’t been deleted with a matching record in Printanista / FM Audit. The result is 42 records. 28 of those are not on contract. 14 are on contract.

Devices that aren't printers?

    SELECT *
    FROM [WHD].[dbo].[ASSET] AS a
        INNER JOIN [WHD].[dbo].[MODEL] AS m ON m.MODEL_ID = a.MODEL_ID
        INNER JOIN [WHD].[dbo].[ASSET_TYPE] AS t ON t.ASSET_TYPE_ID = m.ASSET_TYPE_ID
        INNER JOIN [WHD].[dbo].[ASSET_STATUS] AS s ON s.ID = a.STATUS_ID
        INNER JOIN [PrintanistaDownload].[dbo].[Devices] AS p ON p.SerialNumber = a.SERIAL_NUMBER
    WHERE s.INACTIVE = '0'
        AND s.NAME = 'In Service'
        AND (t.DELETED IS NULL OR t.DELETED = '0')
        AND NOT t.ASSET_TYPE IN ('Copier', 'Printer')
        AND (m.DELETED IS NULL OR m.DELETED = '0')
        AND (a.DELETED IS NULL OR a.DELETED = '0')
    ORDER BY p.ContractID

This returns devices that are not copiers or printers that are in service from Web Help Desk that haven’t been deleted with a matching record in Printanista / FM Audit. The result is 3 records. 3 of those are not on contract. 0 are on contract.

Deleted devices?

    SELECT F.*, a.*
    FROM (SELECT d.*, a.SERIAL_NUMBER
          FROM [PrintanistaDownload].[dbo].[Devices] AS d
          LEFT JOIN [whd].[dbo].[ASSET] AS a ON a.SERIAL_NUMBER = d.SerialNumber
          WHERE a.DELETED = '1') AS F
        LEFT JOIN (SELECT d.*, a.SERIAL_NUMBER
                   FROM [PrintanistaDownload].[dbo].[Devices] as d
                   LEFT JOIN [whd].[dbo].[ASSET] AS a ON a.SERIAL_NUMBER = d.SerialNumber
                   WHERE (a.DELETED IS NULL OR a.DELETED = '0')) AS S ON F.SerialNumber = S.SerialNumber
        INNER JOIN [whd].[dbo].[ASSET] AS a ON a.SERIAL_NUMBER = F.SERIAL_NUMBER
    WHERE S.SERIAL_NUMBER IS NULL

This returns devices from Web Help Desk that have been deleted (and do not have an undeleted record) with a matching record in Printanista / FM Audit. The result is 1 record. It is on contract.

At this point, it is important to note the record counts for the various checks we have performed. There are 1050 Printanista / FM Audit records. We found 891 direct matches between Printanista / FM Audit and Web Help Desk. We found 113 Printanista / FM Audit records that don't match anything in Web Help Desk. There are 42 records that match against devices that are not in service. There are 3 records that match against devices that aren't printers or copiers. There is 1 record that matches to a deleted item in Web Help Desk. 891 + 113 + 42 + 3 + 1 = 1050. All records have been accounted for.

The data analysis has located many different errors in the system, some of which have potential financial implications for the client. Recurring reports can help keep the data clean.