The DGI Basic Analytics is available to all eautomate users at no additional monthly fee. Contact your DGI account manager for further information on how to get Analytics installed.
The PRO version exposes additional analytics data/metrics (see below for explanation of the difference between the Basic Version and the "PRO" version) requires an additional license key from DGI and has a monthly fee associated with that license to open up the additional reports and eviews to that PRO data.
This was the original analytical component developed, and is part of the basic analytics package.
The contract profitability is primarily based on the ardCustTransDetails and ardCustTransDistributions tables as data sources. The focus of contract profitability is to query these 2 tables for contract related transaction details and present it in a series of Crystal Reports to show detail on contract profitability and meter volume trending broken out by various qualifiers like Meter Type Category, Contract Type, Customer, Model, Equipment etc.
These tables are populated incrementally; that is, each time the analytics process is executed, only new information is added to the tables (the summary table is always rebuilt from scratch).
The tables associated with this component are:
This table contains a list of all Customer Invoices in e-automate. It is also used to show the accounts receivable aging for each invoices and breaks it out by Days Aging buckets for Current, 1-30 days past due, 31-60 days past due, 61-90 days past due, 91-120 days past due and over 120 days past due.
The primary purpose of this table is to contain Customer Invoice Details at the Invoice Line Detail level and to include all types of Invoices in a single table. This make it much easier to do customer transaction analysis regards of the invoice detail type. I.e. labor, items, overages can all be queried from a single table in one query. This table also contains several foreign keys to enable the data analyst to linked to other tables to query additional information as it pertains to specific invoice detail or types.
This table contains distributes the invoice detail for certain transaction to a lower level. For certain records where cost for example is allocated to more than one department, this table will contain a record for each cost department with the associated cost amount. It also distributes contract billing amounts for longer-than-monthly billing cycles down to a monthly component which makes it possible to do trending analysis and profitability analysis regardless of whether a given month corresponds to a contracts invoiced month or not.
This table provides a summary view of the transaction details in the ardCustTransDetails table by customer. It shows the customer profitability for each customer for all the transaction history.
Population: ardInvoices table is populated directly from e-automate live tables. The ardCustTransDetails table is populated from various e-automate table source which differ from invoice type to invoice type and invoice detail type to invoice detail type.
Please refer to the companion document, Contract Analytics.docx, for a detailed definition from the e-view perspective.
This component, which is also part of the basic analytics package, provides support for the diagnostics portion of the MRI report facility.
The Diagnostics collection of tables listed below were designed to make the “Diagnostics” section of the well known Digital Gateway report “Digital MRI” available solely from the Analytics database. These tables makes it possible to more easily add additional diagnostic related metrics to Analytics through a common framework – thus improves maintainability, scalability and performance.
Diagnostics can be used not only to alert the user of a list of diagnosed issues, but also offer steps to remedy the situation. The Dianostics can also be used for Feature Utilization, i.e. to set up alerts that indicate whether certain software features are used or not.
The tables associated with this component are:
Contains a list of SQL query definitions used to check for certain issues in the data
Contains a list of table sources used in connection with the SQL checks in aidDiagnosticsConstraints
The records in the aidDiagnosticConstraints each represent a SQL check that could result into 0, 1 or more issues, these data issues however can be grouped into different collections of issues which are referred to as “Diagnostic Groups”, and these groups are defined in the aidDiagnosticGroups table
The issues generated by the checks performed as per table aidDiagnosticConstraints are listed in this table
This table contains a list of remedies and corrective measures that can be taken to address the issues, and related notes that can be displayed along with the issues in table aidDiagnosticIssues.
This component, which is also part of the basic analytics package, provides support for sales analytics.
Sales Metrics is a collection of sales related data, i.e. Sales Invoice Details, in a de-normalized manner. The primary source of Sales Metrics data is the ardCustTransDetails table discussed earlier in this document. Sales Metrics involves Digital Analytics and Acsellerate where Digital Analytics serves as the data source that feeds the Acsellerate Data Visualization product. As a result the actual “Metrics” of sales data comes from the Acsellerate presentation tier.
The tables associated with this component are:
General Ledger Details
This component, which is also part of the basic analytics package, provides support for financial analysis.
The General Ledger Details component was originally created to be used with e-agent as a schedule batch process that populates a single table with very low level detail records that represent all journal entries in e-automate. This table was originally used to serve as the data source that feeds the “Presidents Report” in e-automate. This table makes it possible to determine which Customer, Equipment, Contract, Item etc. are associated with any journal entry. This means that it is possible to create sophisticated financial statements that can show very low level detail associated with any journal entry. For example one can create a P&L not only based on Branch or Department, but also on Customers, Models, Contracts and even Items!
This pre-populated table (as part of the Digital Analytics update process) also drastically speeds up report generation based on this data because all the data processing has been done already.
The table associated with this component is:
This table is populated using logic that is similar to that actually used to populate the General Ledger journal table (GLJournalDetails) in the e-automate database.
Service Call Metrics (PRO Version)
This component can be subscribed to as part of the PRO analytics package, and provides current-time metric values for Service Calls within the e-automate database.
There is only a single table associated with this component: scdServiceCallMetrics that contains partially de-normalized information about each Service Call.
The metric values consist of:
- Lapsed business hours (i.e. operational hours) between Calls, Corrective and Preventative Maintenance Calls and includes:
- Time lapsed since previous Call, CM Call and PM Call on same equipment.
- Time lapsed to next Call, PM Call, CM Call on same equipment.
- Copies made between call and previous/next calls for Call, PM Call and CM Call and also broken out by Meter Type Category and includes: Total, B/W, Color, Scanner and Other.
- Also determines and shows call “chain” information, i.e. MasterCallID, FinalCallID, RescheduledCallID.
- Shows ‘down hours’ associated with current call.
- Ability to display trend information such as average time/copies between calls for a specific unit of equipment, a specific make/model or for all units.
The table will keep track of several distinct ‘chains’ of calls:
- Incomplete chains, which begin with a specific Call and continue until there are no more callbacks. A Call that is completed without a callback will not be part of any incomplete chain. The number of incomplete chains can be easily counted by determining the number of unique incomplete chain ‘master’ identifiers that exist.
- Corrective Maintenance (CM) chains contain all Calls, in created order, associated with a specific unit of Equipment, and there is only one such chain per unit of Equipment.
- Preventative Maintenance (PM) chains contain all Calls, in created order, associated with a specific unit of Equipment, and there shall only be one such chain per unit of Equipment.
- Equipment Call chains contain all Calls associated with a specific unit of Equipment, and there shall be one such chain per unit of Equipment.
Each chain shall be represented in each Service Call Metric record by means of three (3) identifiers: the master [CallID] for each chain type; the next [CallID] in that chain type; and the previous [CallID] in that chain type.
For each chain type there shall also be a set of metric values stored in each Service Call Metric record:
- Elapsed Time
- Copies Between Calls
- Black & White
Machines In Field Metrics (PRO Version)
This component can be subscribed as part of the PRO analytics package.
Currently the Machines In Field (MIF) metrics provides a snapshot of the current equipment population that is under Contract. The counts are based on the list of Equipment that is referenced from active Contract details, removing any duplicates (allowing for the same piece of Equipment being referenced from multiple concurrently active Contract details). The resulting list is then grouped by qualifying file values and the counts placed into aging buckets based on the installation date for the unit of Equipment. The qualifying column values are:
There is a single table associated with the Machines In Field Metrics: scdMIFMetrics. Since a new ‘snapshot’ of the MIF count is created each distinct day that the analytics process is executed, a [CollectionDate] column is used to identify all of the records associated with a specific snapshot.
Another table is associated with an earlier incarnation of this component: scdMachinesInFieldMetrics. This table contains similar columns, but also attempted to include Contact level qualifications. Since the same unit of Equipment can be associated with multiple concurrently active Contract details, this resulted in counts that were inaccurate and too high. This table remains in the analytics schema only because some existing products currently depend upon it being there. Once the revised version of the MIF Metrics is in place, this table can be removed.
Meter Reading Metrics (PRO Version)
This component can be subscribed as part of the PRO Analytics package.
The meter collection information is separated into four (4) main areas as defined below:
- Past Due = Meters that are past due and should have been collected before the current day
- Outstanding = Meters that are not past due but are due through the end of the calendar month
- Collected = Meters that have been collected and entered but have not been billed
- Billed = Meters that have been collected, entered and billed
Rather than reporting the number of Meters with readings that fall into the above categories, the metrics have been designed to report the number of units of Equipment that have one or more Meters that fall into these categories. This is based on the expectation that all Meters on an individual unit of Equipment should be read at the same time; to allow for the case where different Meters are read at different times, each unit of Equipment is placed into the first category into which any of its Meters falls, based on the order listed above.
In addition to reporting the number of units of Equipment in each of the categories above, the counts are grouped by three (3) qualifying column values, listed below.
It should be noted that currently only Meters associated with unit of Equipment that are associated with an active Contract detail are counted.
There is a single table associated with the Meter Reading Metrics, mtdMeterReadingMetrics. Since a new ‘snapshot’ of the Meter Reading counts is created each distinct day that the analytics process is executed, a [CollectionDate] column is used to identify all of the records associated with a specific snapshot
There are two (2) tables associated with an older incarnation of this component. The initial incarnation attempted to report individual Meter counts as well as the associated number of Contracts or Equipment that were affected. Since the same unit of Equipment can be associated with more than one active Contract detail, this was changed and the above implementation replaced the initial one.
- mtdMeterCollectionMetrics – this table contains the current ‘snapshot’ of information related to meter collections. Note that while this table may contain several records, all of the records are associated with the same collection date; re-processing for the same date will erase and replace the records in this table.
- mtdMeterCollectionHistory – this table contains the historical ‘snapshots’ of information related to meter collections. Each time a collection is to be added to the above metrics table and there is a collection for a previous date in that table, the previous collection is ‘rolled up’ and the information added to this history table as a condensed snapshot.