Pular para o conteúdo principal

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 TableRelated TableRelated FieldCardinality
entitydeviceentity_keyOne entity can link to multiple devices.
entityinstallentity_keyOne entity can link to multiple installs.
Opportunity Finderentityentity_keyOne entity links to exactly one opportunity calculation.

Field Definitions

Field NameSourceSample ValuesDescription
created_atDatabase2024-03-28 14:28:46.931The timestamp when the entity was first created in the system.
dealer_keyDatabase66057e9eb14c827f0864d18eThe unique identifier for the dealer associated with the entity.
dealer_nameDatabaseMy Dealer LLCThe name of the dealer associated with the entity.
Device CountCalculated ColumnThe total number of devices associated with the entity.
Entity URLCalculated ColumnA URL linking to the entity's page in the Print Tracker interface.
entity_keyDatabase66057e9eb14c827f0864d18ePrint Tracker's unique identifier for each entity.
expires_atDatabase2024-03-28 14:28:46.931The timestamp when the entity's subscription expires.
is_dealerDatabasetrue, falseA true/false value indicating if the entity is a dealer.
labelsDatabase{'Key': 'Value'}Custom key-value pairs defined by users in the Print Tracker interface for the entity.
last_synced_atDatabase2024-03-28 14:28:46.931The timestamp when the entity's information was last synced to the Postgres database.
Link to WebMeasureView in WebText reading "View in Web" that can be configured as a hyperlink to the Print Tracker interface.
nameDatabaseCustomer A, Customer BThe name of the entity.
parent_entity_keyDatabase66057e9eb14c827f0864d18eThe 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 TableRelated TableRelated FieldCardinality
entitydeviceentity_keyOne entity can link to multiple devices.
devicesupplydevice_keyOne device can link to multiple supplies (currently installed and previously replaced).
devicevw_meter_datadevice_keyOne device can link to multiple meter readings containing counters and supplies.
devicemeterdevice_keyOne device can link to multiple meter readings.

Field Definitions

Field NameSourceSample ValuesDescription
asset_idDatabaseAB1234, 98765A user-defined identifier for a device, typically used to match an internal equipment tag/identifier.
created_atDatabase2024-03-28 14:28:46.931The timestamp when the device was first created in Print Tracker.
device_keyDatabase66057e9eb14c827f0864d18ePrint Tracker's unique identifier for each device.
entity_keyDatabase37a57e9ca9e87106eac9f612Print Tracker's unique identifier for each entity.
firmwareDatabaseThe firmware version as reported by the device.
integration_idDatabase12345, a8uw33A user-defined identifier typically used for integrations with third-party systems.
ip_addressDatabase192.168.0.10The private IP address of the device.
labelsDatabase{'Key': 'Value'}Custom key-value pairs defined by users in the Print Tracker interface.
Last Meter ReadMeasure0, 2024-03-28 14:28:46.931The date of the latest meter reading for each device.
last_synced_atDatabase2024-03-28 14:28:46.931The timestamp when the device's information was last synced to the Postgres database.
locationDatabaseOffice, Copier RoomThe device's location as reported by the device or input through the Print Tracker interface.
mac_addressDatabase00:26:28:9e:29:12The MAC address of the device.
makeDatabaseHP, Konica MinoltaThe manufacturer of the device.
managedDatabasetrue, falseIndicates whether the device is actively being tracked by Print Tracker (true/false).
managed_textCalculated ColumnManaged, Non-ManagedA text description of the device's managed status.
modelDatabaseHP LaserJet M477 FDWThe model name of the device.
notesDatabaseCustom notes entered by users in the Print Tracker interface.
Pct Reporting DevicesMeasure0%, 25%, 100%The percentage of devices that have reported a meter during the current calendar month.
reporting_deviceCalculated Column0, 1Indicates whether a device has reported a meter during the current calendar month (1 = Yes, 0 = No).
serial_numberDatabasePHDCF08261The 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 TableRelated TableRelated FieldCardinality
entityinstallentity_keyOne entity can link to multiple installs

Field Definitions

Field NameSourceSample ValuesDescription
Active Install CountMeasureReturns the number of installs that have checked in within the past 7 days.
created_atDatabase2024-03-28 14:28:46.931The timestamp when the install was first created in the system.
Current DCA VersionMeasureCurrent version: 2.68.1Returns "Current version: " plus the latest version of the software.
entity_keyDatabase66057e9eb14c827f0864d18ePrint Tracker's unique identifier for the associated entity.
install_keyDatabase66057e9eb14c827f0864d18ePrint Tracker's unique identifier for each installed instance of the software.
Is Latest VersionCalculated Column0, 1Indicates whether the install is running the latest version of the software (1 = yes, 0 = no).
is_laptopDatabasetrue, falseA true/false value indicating if the install is on a laptop.
is_laptop_numberCalculated Column0, 1A numerical version of is_laptop where 1 = true and 0 = false.
last_check_in_atDatabase2024-03-28 14:28:46.931The timestamp when the install last checked in with the system.
last_synced_atDatabase2024-03-28 14:28:46.931The timestamp when the install's information was last synced to the Postgres database.
machineDatabaseDESKTOP-0V5KS22The machine name of the computer where Print Tracker is installed.
online_installCalculated Column0, 1Indicates whether the install has checked in within the last day (1 = yes, 0 = no).
osDatabaseMicrosoft Windows 11 ProThe operating system of the host computer.
os_versionDatabase10.0.22631.4169 Build 22631.4169The specific version of the operating system on the host computer.
Pct Online InstallMeasureReturns the percentage of installs that have checked in within the last day.
private_ip_addressDatabase192.168.0.1The private IP address of the device where Print Tracker is installed.
public_ip_addressDatabase216.254.10.10The public IP address of the device where Print Tracker is installed.
Time Since Last Check InCalculated Column1 day, 1-7 days, 7-30 daysReturns the time range since each install last checked in (1 day, 1-7 days, 7-30 days, 30+ days).
versionDatabase2.64.0, 2.68.1The 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 TableRelated TableRelated FieldCardinality
devicesupplydevice_keyOne device can link to multiple supplies (currently installed and previously replaced)

Field Definitions

Field NameSourceSample ValuesDescription
attributesDatabase{color: {value: black, displayName: Color}}A JSON object with all the attributes (e.g., type, color, part number, yield, etc.) of the supply.
colorDatabaseBlack, CyanThe color of the supply.
created_atDatabase2024-03-28 14:28:46.931The timestamp when the supply was first identified by Print Tracker.
Currently LowMeasureReturns the number of supplies currently below their low supply threshold.
depleted_atDatabase2024-03-28 14:28:46.931The timestamp the supply was marked as depleted.
descriptionDatabaseBlack Drum HP W9015MCThe supply's description as collected from the device.
device_keyDatabase66057e9eb14c827f0864d18ePrint Tracker's unique identifier for the associated device.
displayable_nameDatabaseBlack Toner, Cyan DrumA standardized, display-friendly description of the supply.
Estimated ReplacementsMeasureReturns the number of supplies expected to deplete during the remainder of the current calendar month.
estimated_depletion_atDatabase2024-03-28 14:28:46.931The estimated date when the supply will be fully depleted.
eventsDatabaseA JSON object with the current event status (e.g., low, replaced) and status details of the supply.
expected_yieldDatabaseThe expected yield of the supply, if available from the device or input by a user in the Print Tracker interface.
fill_rateDatabaseThe calculated fill rate of the supply based on collected data or user input.
installed_atDatabase2024-03-28 14:28:46.931The timestamp when the supply was installed in the device.
last_synced_atDatabase2024-03-28 14:28:46.931The timestamp when the supply's information was last synced to the Postgres database.
low_atCalculated Column2024-03-28 14:28:46.931The timestamp when the supply was first marked as low.
part_numberDatabaseThe part number of the supply, if available from the device or user input.
previous_supply_keyDatabase66057e9eb14c827f0864d18ePrint Tracker's unique identifier for the previously installed supply of the same type and color.
remainingDatabaseThe percentage remaining in the supply.
Replaced This MonthMeasureReturns the number of supplies replaced during the current calendar month.
replaced_atDatabase2024-03-28 14:28:46.931The timestamp when the supply was replaced.
serial_numberDatabaseThe serial number of the supply, if available from the device.
Supplies MonitoredMeasureReturns the total number of currently installed supplies.
supplyDatabaseThe name of the supply as stored in the Print Tracker database (primarily used for matching purposes).
supply_keyDatabase66057e9eb14c827f0864d18ePrint Tracker's unique identifier for each supply.
Total ReplacementsMeasureReturns the running total of supplies replaced during the current calendar month.
typeDatabaseToner, Ink, Drum, FuserThe type of supply installed in the device (e.g., toner, drum).
volume_by_formatDatabaseA 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 TableRelated TableRelated FieldCardinality
devicevw_meter_datadevice_keyOne device can link to multiple meter readings containing counters and supplies

Field Definitions

Field NameSourceSample ValuesDescription
total_XXXXDatabaseThe meter value collected from the device at the time of the meter reading.
total_XXXX_volumeDatabaseThe difference between the meter value collected from the device and the previously collected value.
cumulative_total_XXXXMeasureThe cumulative meter volume for the current month to date.
current_monthMeasureJan 2024Returns the current calendar month formatted as MMM YYYY.
Device HierarchyCalculated ColumnHP / HP LaserJet Pro MFPReturns a hierarchy comprised of the device's make and model.
Is Device SelectedMeasure0, 1Identifies if any device has been selected and is used for filtering specific visualizations.
asset_idDatabaseAB1234, 98765A user-defined identifier for a device, typically used to match an internal equipment tag/identifier.
device_keyDatabase66057e9eb14c827f0864d18ePrint Tracker's unique identifier for each device.
Entity URLCalculated ColumnA URL linking to the entity's page in the Print Tracker interface.
entity_keyDatabase37a57e9ca9e87106eac9f612Print Tracker's unique identifier for each entity.
firmwareDatabase1.0.7, 2.3.4The firmware version as reported by the device.
integration_idDatabase12345, a8uw33A user-defined identifier typically used for integrations with 3rd party systems.
ip_addressDatabase192.168.0.10The private IP address of the device.
locationDatabaseOffice, Copier RoomThe device's location as reported on the device or input through the Print Tracker interface.
mac_addressDatabase00:26:28:9e:29:12The MAC address of the device.
makeDatabaseHP, Konica MinoltaThe make of the device.
managedDatabasetrue, falseIndicates whether the device is actively being tracked by Print Tracker (true/false).
managed_numberCalculated Column0, 1Numeric representation of whether the device is actively being tracked by Print Tracker (1 = yes, 0 = no).
meter_keyDatabase66057e9eb14c827f0864d18ePrint Tracker's unique identifier for each meter reading.
modelDatabaseHP LaserJet M477 FDWThe model name of the device.
nameDatabaseCustomer A, Customer BThe name of the entity.
notesDatabaseCustom notes entered by users in the Print Tracker interface.
serial_numberDatabasePHDCF08261The serial number of the device.
meter_read_dateDatabase2024-03-28The date when the meter reading was collected.
meter_read_datetimeDatabase2024-03-28 14:28:46.931The timestamp when the meter reading was collected.
MTD Volume ChangeMeasure500, 1000Returns 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_dateCalculated Column0, 1Indicates if a meter reading date falls within the current or previous month (1 = yes, 0 = no).
Show Current Meter OnlyCalculated Column0, 1Indicates if a meter reading is the most recently collected for each device (1 = yes, 0 = no).
blackDatabaseThe percentage remaining in the black toner or ink cartridge.
cyanDatabaseThe percentage remaining in the cyan toner or ink cartridge.
magentaDatabaseThe percentage remaining in the magenta toner or ink cartridge.
yellowDatabaseThe percentage remaining in the yellow toner or ink cartridge.
wasteDatabaseThe 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 TableRelated TableRelated FieldCardinality
devicemeterdevice_keyOne device can link to multiple meter readings
metermeter_supplymeter_keyOne meter can link to multiple supplies collected during each meter reading
metermeter_countermeter_keyOne meter can link to multiple counters collected during each meter reading

Field Defintions

Field NameSourceSample ValuesDescription
consoleDatabase"Replace Black Toner Soon"The complete console message displayed on the device at the time of the meter reading collection.
created_atDatabase2024-03-28 14:28:46.931The timestamp when the meter reading was first created in Print Tracker.
Device SelectedMeasure0, 1Identifies if any device has been selected and is used for filtering specific visualizations.
device_keyDatabase66057e9eb14c827f0864d18ePrint Tracker's unique identifier for each device.
last_synced_atDatabase2024-03-28 14:28:46.931The timestamp when the meter reading data was last synced to the Postgres database.
meter_keyDatabase76057f9db14c827f0864a28fPrint 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 TableRelated TableRelated FieldCardinality
metermeter_supplymeter_keyOne meter can link to multiple supplies collected during each meter reading
meter_supply_typemeter_supplymeter_supply_type_keyOne supply type (e.g., black toner) can link to multiple supplies collected

Field Definitions

Field NameSourceSample ValuesDescription
meter_keyDatabase76057f9db14c827f0864a28fPrint Tracker's unique identifier for each meter reading.
meter_supply_type_keyDatabase86057g9dc14d827g0964b29gPrint Tracker's unique identifier for each supply type associated with the meter reading.
remainingDatabaseThe 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 TableRelated TableRelated FieldCardinality
meter_supply_typemeter_supplymeter_supply_type_keyOne supply type (e.g., black toner) can link to multiple supplies collected

Field Definitions

Field NameSourceSample ValuesDescription
colorDatabaseBlack, CyanThe color of the supply associated with the meter reading.
meter_supply_type_keyDatabase86057g9dc14d827g0964b29gPrint Tracker's unique identifier for each meter supply type.
supplyDatabaseBlack Toner, Cyan DrumThe name of the supply as stored in the Print Tracker database (primarily used for matching purposes).
typeDatabaseToner, DrumThe 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 TableRelated TableRelated FieldCardinality
metermeter_countermeter_keyOne meter can link to multiple counters collected during each meter reading
meter_counter_typemeter_countermeter_counter_type_keyOne counter type (e.g., Total Color) can link to multiple counters collected

Field Definitions

Field NameSourceSample ValuesDescription
displayable_nameCalculated ColumnTotal Pages, Black PagesA standardized, display-friendly name for the type of meter counter copied directly from the meter_counter_type table.
meter_counter_type_keyDatabase96057h9eb18d837g0465b22gPrint Tracker's unique identifier for each meter counter type.
meter_keyDatabase86057g9dc14d827g0964b29gPrint Tracker's unique identifier for each meter reading.
TotalCalculated ColumnThe total counter at the time of the meter reading and is used for specific visualizations. Not recommended for general use.
Total BlackCalculated ColumnThe total black counter at the time of the meter reading and is used for specific visualizations. Not recommended for general use.
Total ColorCalculated ColumnThe total color counter at the time of the meter reading and is used for specific visualizations. Not recommended for general use.
valueDatabaseThe 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 TableRelated TableRelated FieldCardinality
meter_counter_typemeter_countermeter_counter_type_keyOne counter type (e.g., Total Color) can link to multiple counters collected

Field Definitions

Field NameSourceSample ValuesDescription
counterDatabasetotalBlack, totalColorThe name of the counter as stored in the Print Tracker database (primarily used for matching purposes).
displayable_nameDatabaseTotal Black, Total ColorA standardized, display-friendly name for the counter.
formatDatabasedefaultThe format of the collected meter reading (default is the only format synced to the Postgres database).
meter_counter_type_keyDatabase96057h9eb18d837g0465b22gPrint 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.