Microsoft Power BI
The Print Tracker Data Gateway can be connected to third-party applications such as Microsoft Power BI to enable deeper analytic insights and reporting. This guide will outline the data structures and fields avialble for use in the Power BI Print Tracker Dashboard Template.
Data Objects
Microsoft Power BI utilizes tables and relationships to store data and make data stored in different tables accessible in the same visualization.
Tables
Tables in Power BI serve as the foundational structures for organizing, storing, and managing data. Each table represents a distinct dataset, often segmented by subject areas such as devices, entities, or meter readings. Tables are composed of rows and columns, where rows represent individual records and columns represent data fields.
Relationships
Table relationships in Power BI connect data from different tables based on shared fields, enabling a more organized and efficient way to analyze related data. For example, the devices table contains an entity_key field that corresponds to a similar field in the entity table, allowing the entity name to be identified for each device without needing to duplicate datapoints in both tables.
entity Table
The entity table stores information about individual customers or organizations, referred to as entities, that are managed within the Print Tracker system. Each entity represents a customer, dealer, or another organization and is uniquely identified by an entity_key. The table captures key details about each entity, such as the entity’s name, creation date, and whether the entity is classified as a dealer. Additionally, the table tracks relationships between entities, such as parent-child hierarchies through the parent_entity_key, and includes calculated fields and measures to provide insights into the entity's associated devices and other relevant data. The entity table serves as a central repository for managing customer data and is linked to other tables like devices, installs, and supplies via the entity_key.
Relationships
| Source Table | Related Table | Related Field | Cardinality |
|---|
| entity | device | entity_key | One entity can link to multiple devices. |
| entity | install | entity_key | One entity can link to multiple installs. |
| Opportunity Finder | entity | entity_key | One entity links to exactly one opportunity calculation. |
Field Definitions
| Field Name | Source | Sample Values | Description |
|---|
| created_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the entity was first created in the system. |
| dealer_key | Database | 66057e9eb14c827f0864d18e | The unique identifier for the dealer associated with the entity. |
| dealer_name | Database | My Dealer LLC | The name of the dealer associated with the entity. |
| Device Count | Calculated Column | | The total number of devices associated with the entity. |
| Entity URL | Calculated Column | | A URL linking to the entity's page in the Print Tracker interface. |
| entity_key | Database | 66057e9eb14c827f0864d18e | Print Tracker's unique identifier for each entity. |
| expires_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the entity's subscription expires. |
| is_dealer | Database | true, false | A true/false value indicating if the entity is a dealer. |
| labels | Database | {'Key': 'Value'} | Custom key-value pairs defined by users in the Print Tracker interface for the entity. |
| last_synced_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the entity's information was last synced to the Postgres database. |
| Link to Web | Measure | View in Web | Text reading "View in Web" that can be configured as a hyperlink to the Print Tracker interface. |
| name | Database | Customer A, Customer B | The name of the entity. |
| parent_entity_key | Database | 66057e9eb14c827f0864d18e | The unique identifier of the entity's parent entity. |
Device Table
The device table contains information about individual printing devices and equipment managed through the Print Tracker system. Each device is uniquely identified by a device_key and the table includes key details such as the device’s make, model, serial number, firmware, and location. This table serves as a central hub for all device-related data, providing a foundation for analyzing and managing device behavior, metrics, and status in the Print Tracker system.
Relationships
| Source Table | Related Table | Related Field | Cardinality |
|---|
| entity | device | entity_key | One entity can link to multiple devices. |
| device | supply | device_key | One device can link to multiple supplies (currently installed and previously replaced). |
| device | vw_meter_data | device_key | One device can link to multiple meter readings containing counters and supplies. |
| device | meter | device_key | One device can link to multiple meter readings. |
Field Definitions
| Field Name | Source | Sample Values | Description |
|---|
| asset_id | Database | AB1234, 98765 | A user-defined identifier for a device, typically used to match an internal equipment tag/identifier. |
| created_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the device was first created in Print Tracker. |
| device_key | Database | 66057e9eb14c827f0864d18e | Print Tracker's unique identifier for each device. |
| entity_key | Database | 37a57e9ca9e87106eac9f612 | Print Tracker's unique identifier for each entity. |
| firmware | Database | | The firmware version as reported by the device. |
| integration_id | Database | 12345, a8uw33 | A user-defined identifier typically used for integrations with third-party systems. |
| ip_address | Database | 192.168.0.10 | The private IP address of the device. |
| labels | Database | {'Key': 'Value'} | Custom key-value pairs defined by users in the Print Tracker interface. |
| Last Meter Read | Measure | 0, 2024-03-28 14:28:46.931 | The date of the latest meter reading for each device. |
| last_synced_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the device's information was last synced to the Postgres database. |
| location | Database | Office, Copier Room | The device's location as reported by the device or input through the Print Tracker interface. |
| mac_address | Database | 00:26:28:9e:29:12 | The MAC address of the device. |
| make | Database | HP, Konica Minolta | The manufacturer of the device. |
| managed | Database | true, false | Indicates whether the device is actively being tracked by Print Tracker (true/false). |
| managed_text | Calculated Column | Managed, Non-Managed | A text description of the device's managed status. |
| model | Database | HP LaserJet M477 FDW | The model name of the device. |
| notes | Database | | Custom notes entered by users in the Print Tracker interface. |
| Pct Reporting Devices | Measure | 0%, 25%, 100% | The percentage of devices that have reported a meter during the current calendar month. |
| reporting_device | Calculated Column | 0, 1 | Indicates whether a device has reported a meter during the current calendar month (1 = Yes, 0 = No). |
| serial_number | Database | PHDCF08261 | The serial number of the device. |
Install Table
The install table contains information about specific instances of the Print Tracker software installed on client machines. Each installation is uniquely identified by an install_key and the table includes details about the computer on which the software is installed and provides the status of the install. This table is used for monitoring the deployment of the Print Tracker software, tracking machine health, and ensuring that installations are up to date and operational.
Relationships
| Source Table | Related Table | Related Field | Cardinality |
|---|
| entity | install | entity_key | One entity can link to multiple installs |
Field Definitions
| Field Name | Source | Sample Values | Description |
|---|
| Active Install Count | Measure | | Returns the number of installs that have checked in within the past 7 days. |
| created_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the install was first created in the system. |
| Current DCA Version | Measure | Current version: 2.68.1 | Returns "Current version: " plus the latest version of the software. |
| entity_key | Database | 66057e9eb14c827f0864d18e | Print Tracker's unique identifier for the associated entity. |
| install_key | Database | 66057e9eb14c827f0864d18e | Print Tracker's unique identifier for each installed instance of the software. |
| Is Latest Version | Calculated Column | 0, 1 | Indicates whether the install is running the latest version of the software (1 = yes, 0 = no). |
| is_laptop | Database | true, false | A true/false value indicating if the install is on a laptop. |
| is_laptop_number | Calculated Column | 0, 1 | A numerical version of is_laptop where 1 = true and 0 = false. |
| last_check_in_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the install last checked in with the system. |
| last_synced_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the install's information was last synced to the Postgres database. |
| machine | Database | DESKTOP-0V5KS22 | The machine name of the computer where Print Tracker is installed. |
| online_install | Calculated Column | 0, 1 | Indicates whether the install has checked in within the last day (1 = yes, 0 = no). |
| os | Database | Microsoft Windows 11 Pro | The operating system of the host computer. |
| os_version | Database | 10.0.22631.4169 Build 22631.4169 | The specific version of the operating system on the host computer. |
| Pct Online Install | Measure | | Returns the percentage of installs that have checked in within the last day. |
| private_ip_address | Database | 192.168.0.1 | The private IP address of the device where Print Tracker is installed. |
| public_ip_address | Database | 216.254.10.10 | The public IP address of the device where Print Tracker is installed. |
| Time Since Last Check In | Calculated Column | 1 day, 1-7 days, 7-30 days | Returns the time range since each install last checked in (1 day, 1-7 days, 7-30 days, 30+ days). |
| version | Database | 2.64.0, 2.68.1 | The currently installed version of Print Tracker on the machine. |
Supply Table
The supply table contains data about the consumable supplies installed in printing devices, such as toner, ink, drums, and other replaceable components. Each record represents an individual supply unique identified by a supply_key, providing detailed information about its current status, including remaining levels, installation and depletion dates, and expected yield. This table is used for monitoring the lifecycle of supplies, predicting when they will need replacement, and tracking usage over time.
Relationships
| Source Table | Related Table | Related Field | Cardinality |
|---|
| device | supply | device_key | One device can link to multiple supplies (currently installed and previously replaced) |
Field Definitions
| Field Name | Source | Sample Values | Description |
|---|
| attributes | Database | {color: {value: black, displayName: Color}} | A JSON object with all the attributes (e.g., type, color, part number, yield, etc.) of the supply. |
| color | Database | Black, Cyan | The color of the supply. |
| created_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the supply was first identified by Print Tracker. |
| Currently Low | Measure | | Returns the number of supplies currently below their low supply threshold. |
| depleted_at | Database | 2024-03-28 14:28:46.931 | The timestamp the supply was marked as depleted. |
| description | Database | Black Drum HP W9015MC | The supply's description as collected from the device. |
| device_key | Database | 66057e9eb14c827f0864d18e | Print Tracker's unique identifier for the associated device. |
| displayable_name | Database | Black Toner, Cyan Drum | A standardized, display-friendly description of the supply. |
| Estimated Replacements | Measure | | Returns the number of supplies expected to deplete during the remainder of the current calendar month. |
| estimated_depletion_at | Database | 2024-03-28 14:28:46.931 | The estimated date when the supply will be fully depleted. |
| events | Database | | A JSON object with the current event status (e.g., low, replaced) and status details of the supply. |
| expected_yield | Database | | The expected yield of the supply, if available from the device or input by a user in the Print Tracker interface. |
| fill_rate | Database | | The calculated fill rate of the supply based on collected data or user input. |
| installed_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the supply was installed in the device. |
| last_synced_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the supply's information was last synced to the Postgres database. |
| low_at | Calculated Column | 2024-03-28 14:28:46.931 | The timestamp when the supply was first marked as low. |
| part_number | Database | | The part number of the supply, if available from the device or user input. |
| previous_supply_key | Database | 66057e9eb14c827f0864d18e | Print Tracker's unique identifier for the previously installed supply of the same type and color. |
| remaining | Database | | The percentage remaining in the supply. |
| Replaced This Month | Measure | | Returns the number of supplies replaced during the current calendar month. |
| replaced_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the supply was replaced. |
| serial_number | Database | | The serial number of the supply, if available from the device. |
| Supplies Monitored | Measure | | Returns the total number of currently installed supplies. |
| supply | Database | | The name of the supply as stored in the Print Tracker database (primarily used for matching purposes). |
| supply_key | Database | 66057e9eb14c827f0864d18e | Print Tracker's unique identifier for each supply. |
| Total Replacements | Measure | | Returns the running total of supplies replaced during the current calendar month. |
| type | Database | Toner, Ink, Drum, Fuser | The type of supply installed in the device (e.g., toner, drum). |
| volume_by_format | Database | | A JSON object containing the total number of pages printed by the supply, broken out by format (e.g., engine, life, equiv). |
vw_meter_data Table
The vw_meter_data table is a preformatted table combining data from entities, devices, meters, and supplies and should be the primary source for reporting on both current and historical meters and supplies.
Relationships
| Source Table | Related Table | Related Field | Cardinality |
|---|
| device | vw_meter_data | device_key | One device can link to multiple meter readings containing counters and supplies |
Field Definitions
| Field Name | Source | Sample Values | Description |
|---|
| total_XXXX | Database | | The meter value collected from the device at the time of the meter reading. |
| total_XXXX_volume | Database | | The difference between the meter value collected from the device and the previously collected value. |
| cumulative_total_XXXX | Measure | | The cumulative meter volume for the current month to date. |
| current_month | Measure | Jan 2024 | Returns the current calendar month formatted as MMM YYYY. |
| Device Hierarchy | Calculated Column | HP / HP LaserJet Pro MFP | Returns a hierarchy comprised of the device's make and model. |
| Is Device Selected | Measure | 0, 1 | Identifies if any device has been selected and is used for filtering specific visualizations. |
| asset_id | Database | AB1234, 98765 | A user-defined identifier for a device, typically used to match an internal equipment tag/identifier. |
| device_key | Database | 66057e9eb14c827f0864d18e | Print Tracker's unique identifier for each device. |
| Entity URL | Calculated Column | | A URL linking to the entity's page in the Print Tracker interface. |
| entity_key | Database | 37a57e9ca9e87106eac9f612 | Print Tracker's unique identifier for each entity. |
| firmware | Database | 1.0.7, 2.3.4 | The firmware version as reported by the device. |
| integration_id | Database | 12345, a8uw33 | A user-defined identifier typically used for integrations with 3rd party systems. |
| ip_address | Database | 192.168.0.10 | The private IP address of the device. |
| location | Database | Office, Copier Room | The device's location as reported on the device or input through the Print Tracker interface. |
| mac_address | Database | 00:26:28:9e:29:12 | The MAC address of the device. |
| make | Database | HP, Konica Minolta | The make of the device. |
| managed | Database | true, false | Indicates whether the device is actively being tracked by Print Tracker (true/false). |
| managed_number | Calculated Column | 0, 1 | Numeric representation of whether the device is actively being tracked by Print Tracker (1 = yes, 0 = no). |
| meter_key | Database | 66057e9eb14c827f0864d18e | Print Tracker's unique identifier for each meter reading. |
| model | Database | HP LaserJet M477 FDW | The model name of the device. |
| name | Database | Customer A, Customer B | The name of the entity. |
| notes | Database | | Custom notes entered by users in the Print Tracker interface. |
| serial_number | Database | PHDCF08261 | The serial number of the device. |
| meter_read_date | Database | 2024-03-28 | The date when the meter reading was collected. |
| meter_read_datetime | Database | 2024-03-28 14:28:46.931 | The timestamp when the meter reading was collected. |
| MTD Volume Change | Measure | 500, 1000 | Returns the difference in the total volume printed during the current month to date compared to the same time period in the previous month. |
| prev_and_month_to_date | Calculated Column | 0, 1 | Indicates if a meter reading date falls within the current or previous month (1 = yes, 0 = no). |
| Show Current Meter Only | Calculated Column | 0, 1 | Indicates if a meter reading is the most recently collected for each device (1 = yes, 0 = no). |
| black | Database | | The percentage remaining in the black toner or ink cartridge. |
| cyan | Database | | The percentage remaining in the cyan toner or ink cartridge. |
| magenta | Database | | The percentage remaining in the magenta toner or ink cartridge. |
| yellow | Database | | The percentage remaining in the yellow toner or ink cartridge. |
| waste | Database | | The percentage remaining in the waste toner collection unit. |
meter Table
The meter table contains metadata about each historial meter reading and is primarily used for linking to other tables which contain the values collected during each meter readings. Each record in the table is uniquely identified by a meter_key.
Relationships
| Source Table | Related Table | Related Field | Cardinality |
|---|
| device | meter | device_key | One device can link to multiple meter readings |
| meter | meter_supply | meter_key | One meter can link to multiple supplies collected during each meter reading |
| meter | meter_counter | meter_key | One meter can link to multiple counters collected during each meter reading |
Field Defintions
| Field Name | Source | Sample Values | Description |
|---|
| console | Database | "Replace Black Toner Soon" | The complete console message displayed on the device at the time of the meter reading collection. |
| created_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the meter reading was first created in Print Tracker. |
| Device Selected | Measure | 0, 1 | Identifies if any device has been selected and is used for filtering specific visualizations. |
| device_key | Database | 66057e9eb14c827f0864d18e | Print Tracker's unique identifier for each device. |
| last_synced_at | Database | 2024-03-28 14:28:46.931 | The timestamp when the meter reading data was last synced to the Postgres database. |
| meter_key | Database | 76057f9db14c827f0864a28f | Print Tracker's unique identifier for each meter reading. |
meter_supply Table
The meter_supply table contains the percentage remaining value for each supply associated with each meter reading.
Relationships
| Source Table | Related Table | Related Field | Cardinality |
|---|
| meter | meter_supply | meter_key | One meter can link to multiple supplies collected during each meter reading |
| meter_supply_type | meter_supply | meter_supply_type_key | One supply type (e.g., black toner) can link to multiple supplies collected |
Field Definitions
| Field Name | Source | Sample Values | Description |
|---|
| meter_key | Database | 76057f9db14c827f0864a28f | Print Tracker's unique identifier for each meter reading. |
| meter_supply_type_key | Database | 86057g9dc14d827g0964b29g | Print Tracker's unique identifier for each supply type associated with the meter reading. |
| remaining | Database | | The percentage remaining in the supply at the time of the associated meter reading. |
meter_supply_type Table
The meter_supply_type table identifies the type and color of each supply collected during each meter reading.
Relationships
| Source Table | Related Table | Related Field | Cardinality |
|---|
| meter_supply_type | meter_supply | meter_supply_type_key | One supply type (e.g., black toner) can link to multiple supplies collected |
Field Definitions
| Field Name | Source | Sample Values | Description |
|---|
| color | Database | Black, Cyan | The color of the supply associated with the meter reading. |
| meter_supply_type_key | Database | 86057g9dc14d827g0964b29g | Print Tracker's unique identifier for each meter supply type. |
| supply | Database | Black Toner, Cyan Drum | The name of the supply as stored in the Print Tracker database (primarily used for matching purposes). |
| type | Database | Toner, Drum | The type of supply associated with the meter reading. |
meter_counter Table
The meter_counter table contains the raw values collected for each counter during each meter reading.
Relationships
| Source Table | Related Table | Related Field | Cardinality |
|---|
| meter | meter_counter | meter_key | One meter can link to multiple counters collected during each meter reading |
| meter_counter_type | meter_counter | meter_counter_type_key | One counter type (e.g., Total Color) can link to multiple counters collected |
Field Definitions
| Field Name | Source | Sample Values | Description |
|---|
| displayable_name | Calculated Column | Total Pages, Black Pages | A standardized, display-friendly name for the type of meter counter copied directly from the meter_counter_type table. |
| meter_counter_type_key | Database | 96057h9eb18d837g0465b22g | Print Tracker's unique identifier for each meter counter type. |
| meter_key | Database | 86057g9dc14d827g0964b29g | Print Tracker's unique identifier for each meter reading. |
| Total | Calculated Column | | The total counter at the time of the meter reading and is used for specific visualizations. Not recommended for general use. |
| Total Black | Calculated Column | | The total black counter at the time of the meter reading and is used for specific visualizations. Not recommended for general use. |
| Total Color | Calculated Column | | The total color counter at the time of the meter reading and is used for specific visualizations. Not recommended for general use. |
| value | Database | | The meter value collected from the device at the time of the meter reading. |
meter_counter_type Table
The meter_counter_type table identifies the name of each counter collected during each meter reading.
Relationships
| Source Table | Related Table | Related Field | Cardinality |
|---|
| meter_counter_type | meter_counter | meter_counter_type_key | One counter type (e.g., Total Color) can link to multiple counters collected |
Field Definitions
| Field Name | Source | Sample Values | Description |
|---|
| counter | Database | totalBlack, totalColor | The name of the counter as stored in the Print Tracker database (primarily used for matching purposes). |
| displayable_name | Database | Total Black, Total Color | A standardized, display-friendly name for the counter. |
| format | Database | default | The format of the collected meter reading (default is the only format synced to the Postgres database). |
| meter_counter_type_key | Database | 96057h9eb18d837g0465b22g | Print Tracker's unique identifier for each type of meter counter. |
meter_read_dates Table
The meter_read_dates table contains a row for every calendar day in the dataset and is required to make certain Power BI visualization work.
Opportunity Finder Table
The Opportunity Finder table is a specialized table that is only used to populate data in the scatter plot on the Devices tab and is not intended for use in other visualizations.
Columns Table
The Columns table is a specialized table used by Power BI to provide a list of columns users can select from to customize the table on the Device Details tab and provides no analytic value.
Columns Install
The Columns Install is a specialized table used by Power BI to provide a list of columns users can select from to customize the table on the Installs tab and provides no analytic value.
Columns Install Bar Chart
The Columns Install Bar Chart a specialized table used by Power BI to provide a list of columns users can select from to customize the bar chart on the Installs tab and provides no analytic value.