Tugger makes available the following tables from the Mentor CASH application for the production of Power BI reports..
Note:
- In order to maintain effeciciancy, not all columns from the tables are extracted and made available. Click on the table links to see the available columns.
- Date values from tugger are sent to Power BI without any daylight saving adjustments being made, i.e. 05/05/2022 00:00:00 (which is during British Summer Time) would appear within PowerBI as 04/05/2022 23:00:00 (i.e. Greenwich Mean Time). This can be corrected in Power Query by following the steps in the Cash - Daylight Saving correction. Reports supplied by Tugger already make this adjustments for you.
- In order to support the 'Incremental Refresh' feature of Power BI, rows within the Tugger database are never 'updated', instead, whenever a row is updated a new version of the row is inserted into the database and the old version of the row is marked as deleted using the 'Deleted' column. As the Incremental Refresh feature is only relavant to those users who have tables with very many millions of rows, the 'deleted' rows need to be filtered out in Power Query for all normal users. Reports supplied by Tugger already implement this filter.
The tables are grouped into the following sections:
CASH configuration tables which can be managed directly from the CASH application from the 'Company Codes' submenu under the 'System Admin' main menu option 'Stock Movements' submenu under the 'Stock Control' main menu option
| Table | Description |
|---|---|
| CFAreas | Lookup of general areas - e.g. where a customer is located |
| CFBranches | Lookup of branches |
| CFCallReasons | Lookup of reasons for the call |
| CFCallTypes | Lookup of the types of call |
| CFContactTypes | Lookup of contac types |
| CFEngineeringAreas | Lookup of an engineers working area, i.e. his 'patch' |
| CFEngineers | Lookup of engineers |
| CFPremisesTypes | Lookup of types of premises |
| CFProjectManagers | Lookup of project mnagers |
| CFSalesEnquiryWonOrLostReasons | Lookup of reasons why an enquiry was won or lost |
| CFSalesmen | Lookup of salesmen |
| CFSourceOfLeads | Lookup of source of sales leads |
| CFSystemTypes | Lookup up system types |
| DirectCosts | List of direct costs that can be used in pricing a job |
| Locations | Lookup table for decoding location in StockAuditTrails table |
| NominalCodes | List of accounting nominal codes |
Cash data tables which are updated by CASH application during the course of normal operations logging and updating calls, jobs, stocks etc.
| Table | Description |
|---|---|
| CallActionCharges | Detailed charges that have been applied to a specific call. Child table of CallActions table |
| CallActions | Details of actions that have been taken been taken on a specific call. Child table of Calls table |
| Calls | Main table giving an overview of the calls that have been recieved. |
| CompanyInformations | Information about your companies within the CASH application. |
| Customers | Details of all customers |
| InvoiceHeaders | Main table giving an overview of invoices |
| InvoiceLineAnalysis | Accounting nominal code that applies to an invoice, Child table of InvoiceHeaders table. |
| InvoiceLines | Details of items that have been in a specifc invoice. Child table of InvoiceHeaders table. |
| JobCostingDirectCostAudits | Audit trail of changes made to a jobs direct costs |
| JobCostingDirectCosts | Direct costs for a specifc job. Child table of JobCostings table. |
| JobCostingInvoices | Invoices that have been applied to a particulsr job. The details of the invoices can be found in the InvoiceHeaders table. |
| JobCostingProducts | Costs and details of products that are for a specific job. Child table of JobCostings table. |
| JobCostings | Main table for jobs. |
| Products | Main Products table |
| PurchaseInvoiceHeaders | Main table for purchase invoices |
| PurchaseOrderHeaders | Main table giving an overview of purchase orders |
| PurchaseOrderLineAnalysis | Accounting nominal code that applies to items in a purchase orders, Child table of PurchaseOrderHeaders table. |
| SalesEnquires | Main table for sales enquires |
| SalesEnquiryValues | Values which realte to a sales enquiry eg hours, cost, margins etc. Chaild table of SalesEnquires table. |
| SiteCharges | Regular charges for each maintatined system at each customers site |
| SiteSystems | Systems maintained as each customers site |
| Sites | Details of each customers sites |
| StockAtLocations | Product stock levels at locations |
| StockAuditTrails | Audit trail of stock movements i.e. added, removed or tranferred |
| Suppliers | details of suppliers |
| SystemMaintenanceSchedules | Schedule when systems are next due for maintenance |
Addditional Lookup tables created by Tugger
| Table | Description |
|---|---|
| CallStatus | Text descriptions of call status codes in the Calls table |
| JobStatus | Text description of job status codes in the JobCostings table |
| LUDeliveryStatus | Lookup for decoding the delivery status in the PurchaseOrderHeaders table |
| LUOrderStatus | Lookup for decoding the order status in the PurchaseOrderHeaders table |
| LUStockAuditTransactionTypes | Lookup table for decoding transaction type in the StockAuditTrails table |
| LUSupplierAccountStatus | Lookup table for decoding account status in the Suppliers table |
| PostcodeRegions | Lookup table for decoding the postcode region into broad geographic regions e.g. 'North West', 'Wales' etc |
| SalesEnquiryHistoricalData | Stores each day a copy of the rows in the SalesEnquires and SalesEnquiryValues table where the Status is neither 'Won' nor 'Lost'. This allows a day by review of how a sales enquiry changed. Once the sales enquiry is either 'Won' or 'Lost' then daily copy is stopped at the end of the month in which the enquiry was won or lost. As these daily copies can create very many rows in the database, it is an optional feature which can be enabled in Tugger website. |
| SalesEnquiryStatus | Lookup table for decoding the status in the SalesEnquires table |