Data Warehouse Schema
Contents
Introduction
This document outlines the schema for the data warehouse. It is intended as a reference for clients who wish to connect directly to the data warehouse and build their own reports either using the Report Designer (if the reports are to be published to the reporting server), or using another reporting tool.
Core concepts
Dimensional design
Most reports can be displayed as a graph, say with total sales running up the left edge and months running along the bottom. The two axis on the graph represent dimensions, with measurements (or facts) making up the points on the graph.
The data warehouse is structured to make producing these reports as simple as possible. So there are two core types of table in the data warehouse: Facts and Dimensions:
Facts contain the measurements like cost and quantity that you will typically be summarising in your reports. Each fact is linked to multiple dimensions.
Dimensions contain the things you are measuring and provide different ways of slicing, dicing and constraining the same facts.
Dimensions can typically be wide (with a lot of columns), allowing you to drill down into the data. Examples of dimensions include Products, Dates and Users.
Facts are very specific and are typically narrower (with fewer columns), but link to lots of other things. Examples of facts include Orders, Shipments and Invoices.
In data warehousing parlance, this is known as a star schema, as the diagrams below will make clear. It enables you to easily link together the data you need in an efficient manner for highly scalable reports.
Naming conventions
In the existing ETL code one can notice differing naming conventions for facts, e.g. fct_shipment versus purchaseorder. This is a work in progress migration to align naming conventions.
Ideally all dimensions should be prefixed with dim_ and all facts should be prefixed with fct_
Vendors vs buyers
Most of the facts in the data warehouse represent a transaction, with a Vendor on one side and a Buyer on the other. Beware: these are not always the same thing!
If you are a managed service provider getting a quote from suppliers, you will be on the Buyer side of the relationship and they will be on the Vendor side. But if you are sending a quote to a customer the roles will be reversed: you will be the Vendor, they will be the Buyer.
We will make clear which when explaining the relationships for each fact.
Fact tables
Overview
Grain
Each fact table contains information captured at a specific level of detail. For instance, the Invoice fact contains one row for each Product going to a particular delivery address. This is known as the grain.
If you are joining two fact tables you should ensure that their grain always matches, or you will end up with duplicate rows.
Types
There are three types of fact in a typcial data warehouse and at Claritum this holds true: Transaction, Periodic Snapshots and Accumulating Snapshots.
Transaction. This does what it says on the tin, something happens in Claritum and is captured in the fact table and (ideally) never changes. An example is
fct_buyerinvoice: it is added once an invoice has been issued and never changes again.- Periodic Snapshot. Encompasses snapshots taken at regular intervals over time to reflect the state of the system at that point in time. Typically a daily snapshot. An example is
fct_stocklevel: a daily snapshot of levels of stock within the Claritum system segregated by aStockDate Accumulating Snapshot. A fact that captures the pipeline lifecycle of a part of the Claritum system. Inserted when new and updated (or deleted/re-inserted) as the item in question moves through a process. An example is
fct_activityprice: a project has multiple stages in its lifecycle that are captured here from creation, through to submitted quotes, ordered, completed and received. Along the way as there are changes to the project the fact table is updated with the latest dates, statuses and amounts against it.
Relationships
Fact tables link to dimensions via foreign keys. These are always named in such a way that finding the correct table to link to is easy. We describe these relationships using the following notation:
[Fact].[ForeignKey] = [Dimension].[PrimaryKey]
An example might be:
fct_order.BuyerID = Buyer.BuyerID
This is saying that the BuyerID column for the Order table links to the BuyerID column in the Buyer table. Once you are used to the convention working out the joins should be straightforward.
Currencies
Any fact that contains prices will include a CurrencyCode column, indicating which currency the fact reflects. If you are summarising rows you should always constrain the results to a specific currency so your results reflect the correct amounts.
For instance, to view the Euro value of invoices, be sure to constrain your queries with something similar to:
CustomerInvoice.CurrencyCode = 'EUR'
Activity Price Fact
Records job bag activity and extra dates, prices and ratings. Note that in the production system ratings are entered once for all suppliers activities on a particular job. These are duplicated across each vendor's activities in this fact. If you are looking to find average vendor ratings use the VendorRating fact so this does not weigh the results.
Object name: fct_activityprice
Type: accumulating snapshot
Grain: 1 row per distinct project, supplier, activity/extra, specification section, quantity and currency
Partition: CurrencyCode
Code: Facts/Fct ActivityPrice
Reports :
Measurements
DaysToSubmit | The number of days between the RFQ being sent to the vendor and them responding |
HoursToSubmit | The number of hours between the RFQ being sent to the vendor and them responding |
DaysToComplete | The number of days between the PO being sent to the vendor and the activity being marked as complete |
HoursToComplete | The number of hours between the PO being sent to the vendor and the activity being marked as complete |
DaysOverdue | The number of days after the activity's due date that it was marked complete. If not completed, the number of days is calculated based on the current date |
HoursOverdue | The number of hours after the activity's due date that it was marked complete. If not completed, the number of hours is calculated based on the current date |
Quantity | The quantity of the activity |
| PreviousCost | Sale price entered on the 'Savings' tab of the project |
TotalCost | The total cost price of the activity |
UnitCost | The total cost price of the activity divided by the quantity |
POValue | The purchase order value of the activity, if approved |
TotalSale | The total sale price of the activity |
UnitSale | The total sale price of the activity divided by the quantity |
InvoiceNet | The invoiced value of the activity |
InvoiceTax | The invoiced tax value of the activity |
ReceivedNet | The value of all supplier invoices received for the activity |
Profit | The total sale price minus the total cost price |
Quality | The "quality" rating entered for the vendor. Ranges from -2 (lowest) to 2 (highest) |
OnBudget | The "on budget" rating entered for the vendor. Ranges from -2 (lowest) to 2 (highest) |
OnSchedule | The "on schedule" rating entered for the vendor. Ranges from -2 (lowest) to 2 (highest) |
Relationships
fct_activityprice.ProjectID = project.ProjectID | Links the fact to the project |
fct_activityprice.ProductID = product.ProductID | Links the fact to the product |
fct_activityprice.VendorID = vendor.VendorID | Links the fact to the vendor supplying the goods / services |
fct_activityprice.BuyerID = buyer.BuyerID | Links the fact to the contact ordering the project |
fct_activityprice.ActivityID = activity.ActivityID | Links the fact to the activity type |
fct_activityprice.ActivityPriceNoteID = activitypricenote.ActivityPriceNoteID | Links the fact to the supplier notes / extra description |
fct_activityprice.UncompetitiveReasonID = uncompetitivereason.UncompetitiveReasonID | Links the fact to the reason given for not choosing cheapest suppliers |
| fct_activityprice.PLAccountID = placcount.PLAccountID | Links the fact to the P&L Account |
fct_activityprice.DateCreated = datecreated.DateCreated | Links the fact to the date it was created |
fct_activityprice.DateRequested = daterequested.DateRequested | Links the fact to the date the RFQ was sent |
fct_activityprice.DateSubmitted = datesubmitted.DateSubmitted | Links the fact to the date the quote was received from the supplier |
| fct_activityprice.DateOrdered = dateordered.DateOrdered | Links the fact to the date the activity was ordered |
fct_activityprice.DateDue = datedue.DateDue | Links the fact to the date the activity is due to be completed |
fct_activityprice.DateCompleted = datecompleted.DateCompleted | Links the fact to the date the activity was marked completed |
| fct_activityprice.DateReceived = datereceived.DateReceived | Links the fact to the date the order was received |
| fct_activityprice.CostExchangeRateID = costexchangerate.CostExchangeRateID | Links the fact to the Unit Cost exchange rate |
| fct_activityprice.SaleExchangeRateID = saleexchangerate.SaleExchangeRateID | Links the fact to the Sale Cost exchange rate |
| fct_activityprice.PurchaseOrderExchangeRateID = purchaseorderexchangerate.PurchaseOrderExchangeRateID | Links the fact to the PO Value exchange rate |
| fct_activityprice.BuyerInvoiceExchangeRateID = buyerinvoiceexchangerate.BuyerInvoiceExchangeRateID | Links the fact to the Buyer Invoice exchange rate |
| fct_activityprice.VendorInvoiceExchangeRateID = vendorinvoiceexchangerate.VendorInvoiceExchangeRateID | Links the fact to the Vendor Invoice exchange rate |
fct_activityprice.ProjectID = specification.ProjectID AND fct_activityprice.sectionnumber = specification.SectionNumber | Links the fact to the specification it relates to |
Other columns
ScenarioNumber | A number indicating which scenario the activity belongs to |
| SectionNumber | |
| PriceNumber | |
Section | A textual description of the specification section the act |
| Source | |
VendorRef | Reference entered by supplier when entering quote |
IsDeclined | If 'Y', the supplier has declined to quote |
IsSelected | If 'Y', the activity is in the selected scenario |
IsOrderedQuantity | If 'Y', the prices are for the quantity the customer has ordered |
CurrencyCode | The 3-letter currency code used for the measures |
VendorRef | The reference the supplier entered when setting prices |
InvoiceDate | The latest date that a customer invoice was issued |
InvoiceNumbers | Invoice numbers for the activity as comma-separated list |
PONumber | The purchase order number for the activity |
| SavingsReason | The savings reason for the activity |
| IsDeclined | |
| IsSelected | |
| IsOrderedQuantity | |
| id_batch | Links the fact to the transformation log table of the ETL load |
ActivitySaving Fact
Captures the savings associated with each activity on a job at the point when all customer invoices have been issued, along with other suppliers and their prices.
Object name: fct_activitysaving
Type: accumulating snapshot (taken when invoicing is complete. In a normal circumstance it will be loaded just once, but if there are subsequent changes to the fct_activityprice part then this will be deleted/inserted)
Grain: 1 row per distinct project, activity/extra, selected supplier and currency
Partition: CurrencyCode
Code: Facts/Fct ActivitySaving
Reports :
Measurements
| Quantity | The quantity of the activity |
| ExtraCost | |
POValue | Total value of POs authorised for selected supplier and given activity |
InvoiceNet | Net value of customer invoices for given activity |
InvoiceTax | Tax value of customer invoices for given activity |
| ReceivedNet | The value of all supplier invoices received for the activity |
Profit | Net value of invoice less PO value |
| PreviousCost | Sale price entered on the 'Savings' tab of the project |
LowestCost | Lowest supplier quote received for given activity |
AverageCost | Average (mean) of supplier quotes received for given activity |
HighestCost | Highest supplier quote received for given activity |
Savings | Highest supplier quote less PO value |
Vendor1Cost - Vendor10Cost | Supplier quotes received for given activity, from lowest to highest |
Relationships
fct_activitysaving.ProjectID = project.ProjectID | Links the fact to the project |
fct_activitysaving.ProductID = project.ProductID | Links the fact to the product |
fct_activitysaving.BuyerID = buyer.BuyerID | Links the fact to the buyer |
fct_activitysaving.ActivityID = activity.ActivityID | Links the fact to the activity |
fct_activitysaving.SelectedVendorID = selectedvendor.SelectedVendorID | Links the fact to the vendor chosen for the activity |
fct_activitysaving.LowestVendorID = lowestvendor.LowestVendorID | Links the fact to the cheapest vendor for the activity |
fct_activitysaving.HighestVendorID = lowestvendor.HighestVendorID | Links the fact to the most expensive vendor for the activity |
fct_activitysaving.Vendor[1-10]ID = vendor[1-10]ID | Links the fact to suppliers who submitted quotes for this activity, from lowest to highest |
fct_activitysaving.UncompetitiveReasonID = uncompetitivereason.UncompetitiveReasonID | Links the fact to the reason chosen for not choosing the cheapest supplier |
fct_activitysaving.DateOrdered = dateordered.DateOrdered | Links the fact to the date the project was ordered |
fct_activitysaving.DateApproved = dateapproved.DateApproved | Links the fact to the date the PO was approved |
fct_activitysaving.InvoiceDate = invoicedate.InvoiceDate | Links the fact to the date customer invoicing was completed |
| fct_activitysaving.AuditID = dim_audit.AuditID | Links the fact to the audit dimension |
Other columns
| SavingsReason | |
CurrencyCode | 3-letter currency code used for prices |
Quantity | Quantity ordered |
| InvoiceNumbers | Comma separated list of invoice numbers for the activity |
BuyerInvoice Fact
Records invoices and credit notes sent to the customer. Invoices will have positive values, credit notes negative values.
Object name: fct_buyerinvoice
Type: transaction (taken when invoicing is issued)
Grain: 1 row per distinct invoice item, delivery and currency
Partition: CurrencyCode
Code: Facts/Fct BuyerInvoice
Reports :
Measurements
Quantity | The quantity of the line item |
NetValue | The net value of the line item |
TaxValue | The tax value of the line item |
GrossValue | The net value plus tax value for the line item |
Relationships
fct_buyerInvoice.RecipientID = recipient.RecipientID | Links the fact to the recipient specified in the invoice address section |
fct_buyerInvoice.BuyerID = buyer.BuyerID | Links the fact to the customer contact for the project or order |
fct_buyerInvoice.ApproverID = Approver.ApproverID | Links the fact to the administrator who approved the invoice |
fct_buyerInvoice.IssuerID = Issuer.IssuerID | Links the fact to the administrator who issued the invoice |
fct_buyerInvoice.InvoiceTermsID = InvoiceTerms.InvoiceTermsID | Links the fact to the terms selected when the invoice issued |
fct_buyerInvoice.ProductID = Product.ProductID | Links the fact to the product the line item is for |
fct_buyerInvoice.ProjectID = Project.ProjectID | Links the fact to the project the line item is for |
fct_buyerInvoice.LineItemID = LineItem.LineItemID | Links the fact to the textual description for the line item |
fct_buyerInvoice.DeliveryToID = DeliveryTo.DeliveryToID | Links the fact to the line item's delivery destination |
fct_buyerInvoice.DeliveryFromID = DeliveryTo.DeliveryFromID | Links the fact to the line item's source address, if known |
fct_buyerInvoice.OrderCodeID = OrderCode.OrderCodeID | Links the fact to the Ordering Code |
fct_buyerInvoice.PLAccountID = PLAccount.PLAccountID | Links the fact to the P&L Account selected when invoice was issued |
fct_buyerInvoice.TaxCodeID = TaxCode.TaxCodeID | Links the fact to the tax code selected when the invoice was issued |
fct_buyerInvoice.ExchangeRateID = ExchangeRate.ExchangeRateID | Links the fact to the exchange rate used for calculating the line item value |
fct_buyerInvoice.DateOrdered = DateOrdered.DateOrdered | Links the fact to the date the line item was ordered |
fct_buyerInvoice.DateApproved = DateApproved.DateApproved | Links the fact to the date the invoice was approved |
fct_buyerInvoice.DateIssued = DateIssued.DateIssued | Links the fact to the date the invoice was issued |
fct_buyerInvoice.InvoiceDate = InvoiceDate.InvoiceDate | Links the fact to the invoice date entered when invoice issued |
Other columns
InvoiceNumber | The invoice number generated when the invoice was issued |
NominalLedger | The nominal ledger code entered when the invoice was issued |
TaxNumber | The tax number selected when the invoice was issued |
CurrencyCode | The 3-letter currency code used for the measures |
Client Usage Fact
Summarises the numbers of users and transactions over time
Object name: clientusage
Type: transaction
Grain: 1 row per distinct contact, usage date and currency
Partition: -
Code: Facts/ClientUsage
Reports : -
Measurements
NumCustomers | Number of customers |
NewCustomers | Number of new customers |
NumCustomerUsers | Number of customer users |
NewCustomerUsers | Number of new customer users |
ActiveCustomerUsersWeek | Number of active customer users in the last week |
ActiveCustomerUsersMonth | Number of active customer users in the last month |
ActiveCustomerUsers3Month | Number of active customer users in the last 3 months |
ActiveCustomerUsers6Month | Number of active customer users in the last 6 months |
AvgConcurrentCustomerUsers | Average number of concurrent customer users |
MaxConcurrentCustomerUsers | Maximum number of concurrent customer users |
NumSuppliers | Number of suppliers |
NewSuppliers | Number of new suppliers |
NumSupplierUsers | Number of supplier users |
NewSupplierUsers | Number of new supplier users |
ActiveSupplierUsersWeek | Number of active supplier users in the past week |
ActiveSupplierUsersMonth | Number of active supplier users in the past month |
ActiveSupplierUsers3Month | Number of active supplier users in the past 3 months |
ActiveSupplierUsers6Month | Number of active supplier users in the past 6 months |
AvgConcurrentSupplierUsers | Average number of concurrent supplier users |
MaxConcurrentSupplierUsers | Maximum number of concurrent supplier users |
NumAdmins | Number of administrators |
NewAdmins | Number of new administrators |
ActiveAdminsWeek | Number of active administrator in the past week |
ActiveAdminsMonth | Number of active administrators in the past month |
ActiveAdmins3Month | Number of active administrators in the past 3 months |
ActiveAdmins6Month | Number of active administrators in the past 6 months |
| AvgConcurrentAdmins | |
| MaxConcurrentAdmins | |
| NumUsers | |
| NewUsers | |
| ActiveUsersWeek | |
| ActiveUsersMonth | |
| ActieUsers3Month | |
| ActiveUsers6Month | |
| AvgConcurrenctUsers | |
| MaxConcurrentUsers | |
NewProjectParts | Number of new project parts |
NumberRequests | Number of requests sent |
NumberQuotes | Number of quotes |
TotalQuoteValue | Total value of all quotes |
NumberOrders | Number of orders |
TotalOrderValue | Total value of all orders |
NumberPurchaseOrders | Number of purchase orders |
TotalPOValue | Total value of all purchase orders |
AverageQuoteValue | Average value of all quotes |
AverageOrderValue | Average value of all orders |
AveragePOValue | Average value of all purchase orders |
TotalRFXValue | Total value of all requests sent |
AverageRFXValue | Average value of all requests sent |
Relationships
clientusage.ContactID = Contact.ContactID | Links the fact to the contact |
clientusage.UsageDate = UsageDate.UsageDate | Links the fact to the usage date |
| clientusage.AuditID = dim_audit.AuditID | Links the fact to the audit dimension |
Estimate Fact
Summarises estimates sent to the customer. Since estimates typically cover several quantities the columns reflect the average, minimum and maximum unit prices making comparison of estimates for different quantities possible.
Object name: fct_estimate
Type: transaction (when estimate sent to customer)
Grain: 1 row per supplier, buyer, project, activity/extra, date requested and date submitted per currency
Partition: CurrencyCode
Code: Facts/Fct Estimate
Reports : -
Measurements
MinQuantity | The minimum of the quantities quoted |
MaxQuantity | The maximum of the quantities quoted |
AverageQuantity | The average of the quantities quoted |
MinUnitPrice | Minimum price per unit |
MaxUnitPrice | Minimum price per unit |
AverageUnitPrice | Average price per unit |
Relationships
fct_estimate.VendorID = vendor.VendorID | Links the fact to the administrator of the project at the time it was quoted |
fct_estimate.BuyerID = buyer.BuyerID | Links the fact to the customer contact at the time it was quoted |
fct_estimate.ProjectID = project.ProjectID | Links the fact to the project, as it was at the time it was quoted |
fct_estimate.ProductID = product.ProductID | Links the fact to the product |
fct_estimate.ExchangeRateID = exchangerate.ExchangeRateID | Links the fact to the exchange rate in use when it was quoted |
fct_estimate.DateRequested = daterequested.DateRequested | Links the fact to the date the user submitted their request for a quote |
fct_estimate.DateRequested = daterequested.DateRequested | Links the fact to the date the administrator sent the quote to the customer |
fct_estimate.DateDue = datedue.DateDue | Not currently used |
Other columns
DateTimeRequested | Date and time requested. Use for calculating elapsed times. |
DateTimeSubmitted | Date and time submitted. Use for calculating elapsed times. |
DateTimeDue | Not currently used. |
CurrencyCode | The 3-letter currency code used for the measures |
Orders Fact
Captures orders placed by the customer.
Object name: fct_order
Type: transaction (when an order is placed by the customer)
Grain: 1 row per project, product, delivery address, date ordered and quantity per currency
Partition: CurrencyCode
Code: Facts/Fct Order
Reports : -
Measurements
Quantity | The quantity of the item |
CostPrice | The cost price of the item |
SalePrice | The sale price of the item |
Relationships
fct_order.VendorID = vendor.VendorID | Links the fact to the administrator of the product at the time it was quoted |
fct_order.BuyerID = buyer.BuyerID | Links the fact to the customer contact at the time it was ordered |
fct_order.ProductID = product.ProductID | Links the fact to the product, as it was at the time it was ordered |
fct_order.ProjectID = project.ProjectID | Links the fact to the project, as it was at the time it was ordered |
fct_order.DeliveryFromID = deliveryfrom.DeliveryFromID | Links the fact to the source delivery address, if available |
fct_order.DeliveryToID = deliveryto.DeliveryToID | Links the fact to the destination delivery address |
fct_order.SaleExchangeRateID = saleexchangerate.SaleExchangeRateID | Links the fact to the exchange rate in use when it was quoted |
fct_order.DateRequested = daterequested.DateRequested | Links the fact to the date the user submitted their request for a quote |
fct_order.DatePlaced = dateplaced.DatePlaced | Links the fact to the date the order placed |
fct_order.DateDue = datedue.DateDue | Links the fact to the expected delivery date for the order |
Other columns
CurrencyCode | The 3-letter currency code used for the measures |
ProfitAndLoss Fact
For job parts, captures the final cost and sale prices of a product once all customer invoices have been issued and all supplier invoices received. Note that it is possible to add extras after invoicing has been completed. In this case these will appear as extra rows in the fact table. For stock orders the table is populated once all customer invoices for a given delivery have been issued.
Object name: profitandloss
Type: transaction (when all customer invoices issued and supplier invoices received)
Grain: 1 row per project, product, delivery address and completion per currency
Partition: -
Code: Facts/ProfitAndLoss
Reports :
Measurements
Quantity | The quantity of the line item |
| POCost | The cost value of the purchase order |
NetCost | The cost value of the line item before tax |
TaxCost | The tax cost of the line item |
GrossCost | The net cost plus tax cost for the line item |
NetSale | The sale value of the line item before tax |
TaxSale | The tax sale of the line item |
GrossSale | The net sale plus tax sale for the line item |
NetProfit | (NetSale - NetCost) / NetSale |
GrossProfit | (GrossSale - GrossCost) / GrossSale |
Relationships
profitandloss.BuyerID = buyer.BuyerID | Links the fact to the customer contact for the project or order |
profitandloss.ProductID = product.ProductID | Links the fact to the product the line item is for |
profitandloss.DeliveryToID = deliveryto.DeliveryToID | Links the fact to the line item's delivery destination |
profitandloss.PLAccountID = placcount.PLAccountID | Links the fact to the P&L Account selected when invoice was issued |
profitandloss.TaxCodeID = taxcode.TaxCodeID | Links the fact to the tax code selected when the invoice was issued |
profitandloss.DateOrdered = dateordered.DateOrdered | Links the fact to the date the line item was ordered |
profitandloss.LastIssuedDate = lastissueddate.LastIssuedDate | Links the fact to the date when the last customer invoice was issued |
| profitandloss.LastBuyerInvoiceDate = lastbuyerinvoicedate.LastBuyerInvoiceDate | Links the fact to the most recent customer invoice date |
profitandloss.LastReceivedDate = lastreceiveddate.LastReceivedDate | Links the fact to the date when the last supplier invoice was received. |
| profitandloss.LastVendorInvoiceDate = lastvendorinvoicedate.LastVendorInvoiceDate | Links the fact to the most recent supplier invoice date |
Other columns
CurrencyCode | The 3-letter currency code used for the measures |
| InvoiceNumbers | Comma separated list of invoice numbers for the part |
PurchaseOrder Fact
Captures the value of supplier purchase orders as they are approved. Revoked POs will appear twice, first as the original approved PO, then as a negative entry cancelling the original amounts.
Object name: purchaseorder
Type: transaction (when a purchase order is approved)
Grain: 1 row per distinct PO item and currency
Partition: -
Code: Facts/PurchaseOrders
Reports : Stock Orders
Measurements
Quantity | The quantity of the line item |
NetValue | The net value of the line item |
Relationships
purchaseorder.VendorID = vendor.VendorID | Links the fact to the supplier the purchase order has been sent to |
purchaseorder.BuyerID = buyer.BuyerID | Links the fact to the customer the job is for |
purchaseorder.ApproverID = approver.ApproverID | Links the fact to the administrator who approved the PO |
purchaseorder.RevokerID = revoker.RevokerID | Links the fact to the administrator who revoked the PO (if revoked) |
purchaseorder.ProductID = product.ProductID | Links the fact to the product the PO is for |
purchaseorder.ProjectID = project.ProjectID | Links the fact to the project the PO is for |
purchaseorder.ActivityID = activity.ActivityID | Links the fact to the activity the line item is for |
purchaseorder.POItemID = poitem.POItemID | Links the fact to a textual description of the line item |
purchaseorder.DeliveryFromID = deliveryfrom.DeliveryFromID | Links the fact to the source delivery address, if available |
purchaseorder.UncompetitiveReasonID = uncompetitivereason.UncompetitiveReasonID | Links the fact to the reason given for not choosing cheapest suppliers |
purchaseorder.PLAccountID = placcount.PLAccountID | Links the fact P&L account selected when the PO approved |
purchaseorder.ExchangeRateID = exchangerate.ExchangeRateID | Links the fact to the exchange rate used for calculating the line item value |
purchaseorder.DateApproved = dateapproved.DateApproved | Links the fact to the date the PO was approved |
purchaseorder.DateRevoked = daterevoked.DateRevoked | Links the fact to the date the PO was revoked (if applicable) |
purchaseorder.DateDue = datedue.DateDue | Links the fact to the date the work is expected |
| purchaseorder.AuditID = dim_audit.AuditID | Links the fact to the audit dimension |
Other columns
PONumber | The purchase order number generated when the PO was approved |
NominalLedger | The nominal ledger code entered when the PO was approved |
CurrencyCode | The 3-letter currency code used for the measures |
Shipment Fact
Captures products delivered to the customer
Object name: fct_shipment
Type: transaction (when all deliveries are complete)
Grain: 1 row per project, product, delivery address, date received and quantity against currency
Partition: CurrencyCode
Code: Facts/fct_shipment
Reports : Stock Orders
Measurements
UnitQuantity | The quantity of the shipment in units |
PackageQuantity | The number of boxes, etc shipped |
Cost | The delivery cost plus product cost |
Sale | The delivery sale plus product sale |
Relationships
fct_shipment.VendorID = vendor.VendorID | Links the fact to the administrator of the product at the time it was shipped |
fct_shipment.BuyerID = buyer.BuyerID | Links the fact to the customer contact at the time it was shipped |
fct_shipment.RecipientID = recipient.RecipientID | Links the fact to the person the item was shpped to |
fct_shipment.ProductID = product.ProductID | Links the fact to the product that was shipped |
fct_shipment.ProjectID = project.ProjectID | Links the fact to the project |
fct_shipment.DeliveryFromID = deliveryfrom.DeliveryFromID | Links the fact to the source delivery address, if available |
fct_shipment.DeliveryToID = deliveryto.DeliveryToID | Links the fact to the destination delivery address |
fct_shipment.SaleExchangeRateID = exchangerate.ExchangeRateID | Links the fact to the exchange rate in used for the line item |
fct_shipment.DateOrdered = dateordered.DateOrdered | Links the fact to the date the line item was ordered |
fct_shipment.DatePacked = datepacked.DatePacked | Links the fact to the date the order was packed, if available |
fct_shipment.DateDue = datedue.DateDue | Links the fact to the expected delivery date for the shipment |
fct_shipment.DateReceived = datereceived.DateReceived | Links the fact to the actual delivery date for the shipment |
Other columns
PackagingType | The type of packaging (ie 'Boxed', 'Shrink wrapped') |
PackagingUnits | The number of units per box, etc. |
CurrencyCode | The 3-letter currency code used for the measures |
| DeliveryRef | A reference text entered on the delivery |
Specification Fact
A pseudo-fact linking projects to various specification sections. This fact contains no measurements. New in 1.9.2.16.
Object name: specification
Type: transaction (when a project is created or updated)
Grain: one row per specification section and project - i.e. for Cover and Text jobs there will be a row for the "Cover" and a row for the "Text"
Partition: -
Code: Dimensions/Specification, Dimensions/SpecificationNoSpro
Reports : -
Relationships
specification.ProjectID = project.ProjectID | Links the fact to the project |
specification.SpecBindingID = specbinding.SpecBindingID | Links the fact to details about the binding instructions |
specification.SpecColorsID = speccolors.SpecColorsID | Links the fact to details about the colours used |
specification.SpecDetailsID = specdetails.SpecDetailsID | Links the fact to textual details about the specification itself |
specification.SpecGiftAndPremiumID = specgiftandpremium.SpecGiftAndPremiumID | Links the fact to details about the gift and premium products |
specification.SpecLaminatingID = speclaminating.SpecLaminatingID | Links the fact to details about lamination |
specification.SpecLayoutID = speclayout.SpecLayoutID | Links the fact to details about dimensions and orientation |
specification.SpecMediaID = specmedia.SpecMediaID | Links the fact to details about the media used |
specification.SpecPackingID = specpacking.SpecPackingID | Links the fact to details about the packing instructions |
specification.SpecPostPressID = specpostpress.SpecPostPressID | Links the fact to details about the post press processing |
| specification.AuditID = dim_audit.AuditID | Links the fact to the audit dimension |
Other columns
| SectionNumber | Number designation for the section |
| Section | The section this applies to (can be "Back Cover", "Cover", "Front Cover", "Text" for cover and text jobs or "Section 1", "Section 2" etc for multi-section jobs such as NCR pads. |
StockLevel Fact
A rollup fact showing the stocklevel of all products on the given date. Note that the Units and Costs in this fact are not additive - you can average them, or get maximum and minimum values, but summing them up will not give sensible results.
The stock levels are taken at midnight UTC on Saturday. The table is populated during the ETL run on Sundays.
Use this fact in conjunction with the StockMovement fact to work out exact levels on specific dates.
Type: Rollup fact
Grain: On row per buyer, product, warehouse location and currency for given date.
Measurements
UnallocatedUnits | Number of units which have not been reserved by any orders. Negative amounts indicate a backorder |
UnallocatedPackages | Number of boxes which have not been reserved by any orders. Negative amounts indicate a backorder |
PhysicalUnits | Number of units remaining on the shelf - i.e. the amount that has not been despatched. Negative amounts indicate a backorder |
PhysicalPackages | Number of boxes remaining on the shelf - i.e. the amount that has not been despatched. Negative amounts indicate a backorder |
UnallocatedCost | The cost value of the stock not reserved by any orders |
PhysicalCost | The cost value of the stock remaining on the shelf |
UnallocatedSale | The sale value of the stock not reserved by any orders |
PhysicalSale | The sale value of the stock remaining on the shelf |
Relationships
StockLevel.BuyerID = Buyer.BuyerID | Links the fact to the buyer organisation. If the same stock item is linked to multiple customers there will be separate rows for each customer. |
StockLevel.ProductID = Product.ProductID | Links the fact to the stock product |
StockLevel.WarehouseID = Warehouse.WarehouseID | Links the fact to the warehouse |
StockLevel.LastProjectID = LastProject.LastProjectID | Links the stock to the last project which ordered it. Use this to find how long ago it was last despatched. |
StockLevel.LastReorderID = LastReorder.LastReorderID | Links stock additions to the last project used to replenish the stock |
StockLevel.StockDate = StockDate.StockDate | Links the fact to the date the stock level was taken |
StockLevel.FirstCreatedDate= FirstCreatedDate.FirstCreatedDate | Links the fact to the date when the stock first became available |
StockLevel.LastOrderedDate = LastOrderedDate.LastOrderedDate | Links the fact to the date when the stock was last ordered |
Other columns
| CurrencyCode | Three digit currency code for the cost and sale values |
StockMovement Fact
Records additions and subtractions of stock from the system. Stock replenishments appear with positive quantities and values, stock orders appear as negative.
If you are looking for stock levels of each product, consider using the stocklevel fact, which sums all stockmovements for a product on a given date.
Type: Accumulating snapshot. The WarehouseID, IsBackorder and IsUnallocated columns update as the stock is processed.
Grain: On row per stock addition or subtraction and currency
Measurements
Units | Unit quantity. Stock replenishments have a positive quantity, stock orders a negative quantity. |
Packages | Number of boxes. Stock replenishments have a positive quantity, stock orders a negative quantity. |
UnitCost | The cost per unit. Stock replenishments have a positive price, stock orders a negative price. |
UnitSale | The sale per unit. Stock replenishments have a positive price, stock orders a negative price. |
TotalCost | The total cost for the quantity represented. Stock replenishments have a positive price, stock orders a negative price. |
TotalSale | The total sale for the quantity represented. Stock replenishments have a positive price, stock orders a negative price. |
Relationships
StockMovement.VendorID = Vendor.VendorID | Links the fact to the vendor. For orders, this will either be the warehouse supplier (if specified) or the system owner. For replenishments this will be the system owner. |
StockMovement.BuyerID = Buyer.BuyerID | Links the fact to the buyer. For orders, this is the user placing the order. For replenishments this will be the warehouse supplier (if specified) or the system owner. |
StockMovement.ProductID = Product.ProductID | Links the fact to the stock product |
StockMovement.WarehouseID = Warehouse.WarehouseID | Links the fact to the warehouse |
StockMovement.ModifierID = Modifier.ModifierID | Links the fact to the user responsible for the addition or subtraction |
StockMovement.ReorderID = Reorder.ReorderID | Links stock additions to the project used to replenish the stock |
StockMovement.ExchangeRateID = ExchangeRate.ExchangeRateID | Links the fact to the exchange rate used to calculate currency values |
StockMovement.DateCreated = DateCreated.DateCreated | Links the fact to the date when it was created |
StockMovement.DateActive = DateActive.DateActive | Links the fact to the date when the stock became available |
Other columns
| IsBackorder | 'Y' if the quantity is on backorder |
| IsUnallocated | 'Y' if the quantity has not yet been despatched. Use this to filter out stockmovements that have not yet been shipped. |
| PackageType | "Boxes" or empty |
VendorBuyer Fact
A pseudo-fact table for mapping relationships between vendors and buyers. This fact contains no measurements.
Type: snapshot taken daily
Grain: one row per unique vendor / buyer relationship
Relationships
VendorBuyer.VendorID = Vendor.VendorID | Links the fact to the vendor |
VendorBuyer.BuyerID = Buyer.BuyerID | Links the fact to the buyer |
VendorInvoice Fact
Captures the value of supplier invoices as they are received.
Type: snapshot taken when the supplier invoice received
Grain: 1 row per purchase order and currency
Measurements
NetValue | Invoice amount before tax |
TaxValue | Tax amount |
GrossValue | Net value plus tax value |
Relationships
VendorInvoice.VendorID = Vendor.VendorID | Links the fact to the supplier the invoice has been received from |
VendorInvoice.BuyerID = Buyer.BuyerID | Links the fact to the customer the job was for |
VendorInvoice.ApproverID = Approver.ApproverID | Links the fact to the administrator who approved the PO |
VendorInvoice.ReceiverID = Receiver.ReceiverID | Links the fact to the administrator or supplier who entered the invoice |
VendorInvoice.ProjectID = Project.ProjectID | Links the fact to the project the PO was for |
VendorInvoice.ProductID = Product.ProductID | Links the fact to the product the PO was for |
VendorInvoice.POItemID = InvoiceItem.POItemID | Links the fact to a textual description of the line item |
VendorInvoice.VendorInvoiceTermsID = VendorInvoiceTerms.VendorInvoiceTermsID | Links the fact to the terms for the invoice |
VendorInvoice.VendorInvoiceNoteID = VendorInvoiceNote.VendorInvoiceNoteID | Links the fact to any notes on the invoice |
VendorInvoice.UncompetitiveReasonID = UncompetitiveReason.UncompetitiveReasonID | Links the fact to the reason given for not choosing cheapest suppliers |
VendorInvoice.PLAccountID = PLAccount.PLAccountID | Links the fact to P&L Account selected when the PO approved |
VendorInvoice.ExchangeRateID = ExchangeRate.ExchangeRateID | Links the fact to the activity the line item is for |
VendorInvoice.DateApproved = DateApproved.DateApproved | Links the fact to the date the PO was approved |
VendorInvoice.DateReceived = DateReceived.DateReceived | Links the fact to the date the invoice was received |
VendorInvoice.InvoiceDate = InvoiceDate.InvoiceDate | Links the fact to the invoice date entered when received |
Other columns
InvoiceNumber | The invoice number entered when the invoice was received |
NominalLedger | The nominal ledger code entered when the PO was approved |
CurrencyCode | The 3-letter currency code used for the measures |
VendorPerformance Fact
Summarises the time taken between requests and quotes and quotes and orders, as well as the total values of orders.
Type: snapshot taken when modified
Grain: 1 row per vendor, date requested and currency code
Measurements
NumberRequested | The number of RFQs sent to the supplier |
NumberSubmitted | The number of quotes sent by this supplier |
NumberOrdered | The number of those quotes that were ordered. |
NumberUnquoted | The number of cancelled quoted |
NumberPending | The number of pending quotes |
NumberDeclined | Number of quotes declined |
NumberLost | Number of lost orders |
RequestedToQuotedHours | The number of working hours between receipt of the RFX and sending the quote |
RequestedToQuotedDays | The number of working days between receipt of the RFX and sending the quote |
QuoteToOrderHours | The number of working hours between the supplier quote and the order being placed |
QuoteToOrderDays | The number of working days between the supplier quote and the order being placed |
QuoteValue | The total 'value' of the quotes sent |
OrderValue | The total value of the orders sent to this supplier |
PendingValue | The total value of the pending orders |
LostValue | The total value of the lost orders |
Relationships
VendorPerformance.VendorID = Vendor.VendorID | Links the fact to the supplier |
VendorPerformance.DateRequested = DateRequested | Links the fact to the date the user submitted a quote request |
Other Columns
CurrencyCode | The 3-letter currency code used for the measures |
VendorRating Fact
Captures the ratings administrators give to suppliers
Type: snapshot taken when ratings entered
Grain: 1 row per project and vendor
Measurements
Quality | The "quality" rating entered for the vendor. Ranges from -2 (lowest) to 2 (highest) |
OnBudget | The "on budget" rating entered for the vendor. Ranges from -2 (lowest) to 2 (highest) |
OnSchedule | The "on schedule" rating entered for the vendor. Ranges from -2 (lowest) to 2 (highest) |
Relationships
VendorRating.ProjectID = Project.ProjectID | Links the fact to the project being rated |
VendorRating.VendorID = Vendor.VendorID | Links the fact to the supplier being rated |
VendorRating.SpecificationID = Specification.SpecificationID | Links the fact to the specification being rated |
Dimension tables
Overview
Many of the facts above share the same dimensions. In addition the same information is presented in a number of "role playing" dimensions. For instance, the Vendor, Approver, Receiver tables linked to the VendorInvoice fact are convenient views on the same contact data.
Hierarchies
Dimensions can contain one or more hierarchies. These enable you to summarise information at different levels - for example, by country, region or city. These drill-downs are represented below using the following notation:
Top Level > Secondary Level > Next Level
Where applicable we provide example hierarchies for each dimension. These are informational only - you can group your reports however you want!
Where a dimension does not contain any useful hierarchies it is referred to as a "junk dimension". Typical examples of these are user-entered comments, such as in the ActivityPriceNote dimension. While junk dimensions are not useful for grouping and summarising, they are present in the warehouse so you can drill down to that level of detail if required.
Versions
Most dimensions also capture changes in the production system over time. If a specification is edited between the quote being sent to the customer and the order being placed, the Estimate fact and the Order fact will be linked to different versions of the same specification.
These slowly changing dimensions contain additional columns to track these changes:
| Version | The version number, starting at 1 |
| VersionStart | The date the version was valid from (can be arbitrarily far in the past for version 1) |
| VersionEnd | The date the version is valid to (can be arbitrarily far in the future, for the latest version) |
| IsLatesstVersion | Y/N, indicating whether this row is the latest version |
Note that the granularity of the changes captured depends on the frequency that the data warehouse is updated from the production system. At time of writing that is nightly, though we have plans to increase that soon.
Source columns
Most dimensions also include columns starting with 'Source'. These are used to identify the records in the production database and will be of limited use unless you need to identify all the different versions of the same item.
Special records
Most dimensions contain records to represent unknown or missing data. Typically these have a surrogate key of less than 1 (ie 0, -1), although in the case of date dimensions special dates are used.
Activity Dimension
Contains information about the production step added to the supplier pricing section of a job.
Columns
ActivityID | Surrogate key |
Type | The main type of the activity (ie Print, Paper) |
Subtype | The subtype of the activity (currently only for Print) |
Hierarchies
Type > Subtype | Drill down to split print activity into digital, litho, etc |
ActivityPriceNote, CompletedNote, VendorInvoiceNote, VendorInvoiceTerms Dimension
Junk dimension containing user-entered text from the "Supplier Notes" and "Extra Description" fields
Columns
ActivityPriceNoteID/CompletedNoteID/etc | Surrogate key |
Details | The user-entered text |
Approver, Buyer, Completor, HighestVendor, Issuer, LowestVendor, Modifier, Receiver, Recipient, Revoker and Vendor Dimensions
Role-playing dimensions based on the same core 'contact' table. Combines information from all customers, suppliers and administrators in the production system.
Columns
ApproverID/BuyerID/etc | Surrogate key |
IsDeleted | Y/N, depending on whether the contact has been deleted |
IsCreditStopped | Y/N, depending on whether the customer has been marked as 'Credit Stopped' |
IsOnHold | Y/N, depending on whether the supplier has been marked 'On hold' |
Status | Text field showing status of organisation. Currently 'Preferred' or 'Normal' |
Type | Text field showing type of contact: 'Administrator', 'Supplier' or 'Customer' |
Organisation | Top level organisation. For customers, this is the customer name. For suppliers, the supplier name. For administrators it is the main system name. |
OrganisationUID | The organisation's unique identifer - referred to as the 'External ID' on the production system |
Organisation Unit | Secondary organisation. For customers, the cost centre. Not used for suppliers. For administrators the 'Role' the admin has been assigned to. |
AccountRef | The reference assigned to the organisation for an external accounting system (eg Sage, JDE, SAP) |
PLAccount | The name of the P&L Account assigned to the organisation |
NominalLedger | The nominal ledger code assigned to the organisation |
RepCode | The rep code assigned to the organisation |
TaxNumber | The tax number assigned to the organisation |
Timezone | The time zone the contact is in (ie 'Europe/London') |
Language | The contact's language identifier (ie 'en_GB') |
ContactUID | The contact's unique identifer - referred to as the 'External ID' on the production system |
Name | The contact's full name |
The contact's email address | |
Phone | The contact's phone number |
SpendLimit | The spend limit assigned to the contact |
Company | The company, as entered in the contact's address |
Street | The (multi-line) street address |
City | City name |
Region | State / Province / County name |
Country | Country name |
Hierarchies
Organisation > Organisation Unit > Name | Drill down to split print activity into digital, litho, etc |
Country > Region > City | Drill down to summarise by geographical location |
Special records
0 | Unknown contact |
-1 | Empty contact |
DateActive, DateApproved, DateCompleted, DateDue, DateIssued, DateOrdered, DateReceived, DateRequested, DateRevoked, DateStarted, DateSubmitted and InvoiceDate Dimensions
Role-playing dimensions based on the same core 'calendar date' table. These enable you to summarise information by year, quarter, etc, as well as providing descriptive names for months and weekdays.
Columns
DateApproved/DateDue/etc | Surrogate key |
Year | Year, as integer |
Quarter | Quarter, as integer (1-4) |
Month | Month number (1-12) |
WeekOfYear | The week number (1-52) |
WeekOfMonth | The week number in month (1-4) |
Day | The day of month (1-31) |
DayInYear | The day in year (1-365) |
DayInWeek | The day in week (1-7) |
DaysInMonth | The number of days in the current month |
DaysInYear | The number of days in the current year |
DaysInQuarter | The number of days in the current quarter |
IsLeapYear | Y/N, indicating if current year is a leap year |
IsWeekend | Y/N, indicating if current day falls on a weekend |
DayOfWeekLong_en | The full weekday name (Monday, Tuesday, etc) |
DayOfWeekShort_en | The short weekday name (Mon, Tue, Wed, etc) |
MonthLong_en | The full month name (January, February, etc) |
MonthShort_en | The short month name (Jan, Feb, etc) |
Year_en | The year as a digit. Includes 'Unknown', 'TBC' etc for special dates. We recommend always using this instead of 'Year', unless numeric calculations are called for. |
Hierarchies
Year_en > Quarter > MonthShort_en > Day | Drill down to group by specific periods |
Special records
1970-01-01 | Unknown date |
2100-01-01 | TBC |
2100-01-02 | Not set |
DeliveryFrom, DeliveryTo Dimensions
Role-playing dimensions representing a delivery using a specific carrier to a specific address
Columns
DeliveryFromID/DeliveryToID | Surrogate key |
Carrier | The name of the carrier (FedEx, etc) |
Name | The 'attention' name entered for the delivery |
Company | The company name as entered on the address |
AddressLine1 | The first line of the street address |
AddressLine2 | The second line of the street address |
AddressLine3 | The third line of the street address |
City | The city name |
Region | The state / province / county name |
PostCode | The post / zip code |
Country | The country name |
Hierarchies
Country > Region > City | Geographical hierarchy |
Special records
0 | Not available / missing |
Event, StartEvent, CompletedEvent
Role-playing dimensions representing project events.
EventID / StartEventID / CompletedEventID | Surrogate key |
Checklist | Title of the checklist event belongs to |
Title | Title of the event |
Type | Type of system event which triggers completion, if applicable. See below for list of event codes |
IsMandatory | Indicates this item must be completed before subsequent events can be started |
SortOrder | Number indicating position in checklist sequence |
Hierarchies
Checklist > Title | Groups events into checklists |
Special records
0 | Unknown event |
Event codes
The following event codes correspond to the system events selected when setting up a checklist in the production system.
Code | Description |
jobpart_activity_sent | RFQs Sent |
jobpart_activity_received | Quotes Received |
jobpart_quoted | Quoted to Customer |
jobpart_ordered | Order Placed |
jobpart_all_pos_approved | All Supplier Orders Sent |
jobpart_proof_started | Proofing In Progress |
jobpart_proof_approved | Proofs Approved |
goodsin_complete | Delivered |
invoice_issued | Invoiced |
po_created | PO created |
po_approved | PO approved |
ExchangeRate Dimensions
Contains information about the exchange rate used for converting between currencies
Columns
ExchangeRateID | Surrogate key |
FromCurrency | Three-letter currency code to convert from (ie 'EUR') |
ToCurrency | Three-letter currency code to convert to (ie 'GBP') |
ExchangeRate | The exchange rate |
InvoiceItem, POItem Dimensions
Contains a textual representation of an invoice or purchase order line item
Columns
InvoiceItemID/POItemID | Surrogate key |
Type | Type of item ('Public Extra', 'Cost Extra', 'Activity', 'Purchase Order') |
Description | Textual description |
InvoiceTerms Dimension
Contains information about the terms specified on customer invoices. Only terms selected from a drop down list are presented here.
Columns
InvoiceTermsID | Surrogate key |
TermsCode | The code, as assigned on the 'Edit Customer' screen. |
Description | The short description, as it appears in the drop-down |
Terms | The full text of the invoice terms |
OrderCode Dimension
Contains information about the order code selected when placing an order. Although all order codes are stored, this table is most useful when the ordering code has been assigned a set of fixed values that a user can select from.
Columns
OrderCodeID | Surrogate key |
SourceRef | The reference assigned to the order code (ie 'po') |
Name | The name of the order code (ie 'Purchase Order', etc) |
Value | The value entered |
PLAccount Dimension
The P&L account, as set up in the system configuration section and as selected for customers, invoices and purchase orders
Columns
PLAccountID | Surrogate key |
PLAccount | The P&L Account code |
Description | The textual description, as it appears in drop downs |
TaxNumber | The tax number associated with the P&L account |
Product Dimension
Contains details of items that can be ordered by a customer, whether bespoke items, PDF templates, rate cards or stock items
Columns
ProductID | Surrogate key |
Type | 'Bespoke', 'PDF Template', 'Rate Card' or 'Stock' |
FullCategory | The full path of the product in the catalogue, separated by ' / ' (ie 'RFx / Boooklets / Self covered') |
Category1 | The top level category of the product in the catalogue (ie 'RFx') |
Category2 | The next level category of the product in the catalogue (ie 'Booklets') |
Category3 | The next level category of the product in the catalogue (ie 'Self covered') |
Category4 | The next level category of the product in the catalogue |
Category5 | The next level category of the product in the catalogue |
Category6 | The next level category of the product in the catalogue |
VendorRef | The vendor's reference for the product |
ProductCode | The product code, if available |
SKUCode | The SKU code, if available |
TaxCode | The tax code assigned to the product (ie 'VAT17') |
TaxRate | The tax rate assigned to the product, as a percent |
Name | The name of the product |
Description | The description of the product |
ReorderLevel | The reorder level for the product (stock only) |
ReorderQuantity | The quantity to reorder (stock only) |
MinOrderQuantity | The minimum quantity that can be ordered (stock only) |
Hierarchies
AdminOrganisation > AdminUnit > Admin | Drill down to group by administrator |
Category1 > Category2 > Category3 etc | Drill down to group by catalogue location |
Special records
0 | No product information available |
Project Dimension
Contains information specific to job parts and orders on the system. Prior to 08/2014 this contained some information duplicating the Product dimension, such as the categories. Please join on the Product dimension for this information.
Columns
ProjectID | Surrogate key |
Type | "Job"/"Stock". Indicates the type of project. |
IsDeleted | Y/N. Indicates whether project has been deleted |
IsRejected | Y/N. Indicates whether the buyer has rejected the project |
IsClosed | Y/N. Indicates whether the project is closed |
DateTimeCreated | Date the project was created |
DateTimeRFQSent | Date when first RFQ was sent to a supplier |
DateTimeRFQDue | Last date when RFQs are due to be received |
DateTimeRFQReceived | Date when latest RFQ received |
DateTimeQuoted | Date when quote sent to customer |
DateTimeOrdered | Date when order placed by customer |
DateTimeApproved | Date when supplier purchase order approved |
DateTimePacked | Date when item was packed |
DateTimeDespatched | For stock orders, the date the order was despatched. For jobs, the 'Delivered' date |
DateTimeInvoiced | Date when customer invoiced |
DateTimeInvoiceReceived | Date when supplier invoice received |
AdminOrganisation | The organisation for the administrator assigned to the project |
AdminUnit | The organisation unit for the administrator assigned to the project |
AdminName | The full name of the administrator assigned to the project |
AccountManager | The name of the account manager for the product's associated customer (job part products only) |
RepCode | The rep code for the product's associated customer (job part products only) |
Priority | The priority of the project ('Normal', 'Medium', 'High') |
QuoteRef | The quote reference for the project |
JobRef | The job reference for the project |
JobPO | Purchase order entered for job |
Title | The title given to the project |
PartTypeRef | The catalogue reference for the part |
PartType | The catalogue name for the part |
PartNumber | The sequential number of the part in the project |
PartProductCode | The product code entered for the part |
PartQuoteRef | The quote reference for the part |
PartJobRef | The job reference for the part |
PartPO | Purchase order entered for part |
PartTitle | The title entered for the part |
TaxCode | The tax code assigned to the product (ie 'VAT17') |
TaxRate | The tax rate assigned to the product, as a percent |
Hierarchies
AdminOrganisation > AdminUnit > AdminName | Drill down to group by administrator |
Special records
0 | No product information available |
SpecBinding Dimension
Contains details about the binding specification. New in 1.9.2.16.
Columns
SpecBindingID | Surrogate key |
BindingMethod | The binding method requested (i.e. "WiroBound") |
SpecColors Dimension
Contains details about the binding specification. New in 1.9.2.16.
SideOne is a generic term for the front or outside of the finished product. SideTwo is a generic term for the back or inside of the finished product. SideThree is only used for envelopes at present, and refers to the flap.
Columns
SpecColorsID | Surrogate key |
SideOneNumColors | The number of process colours requested on the front or outside of the finished product. |
SideOneSpotColors | The number of spot colours requested on the front or outside of the finished product. |
SideOneBleed | 'Y' if bleed has been requested on the front or outside of the finished product. |
SideOneCoating | The coating requested on the front or outside of the finished product, i.e. "MattVarnish" or "SpotUV". Note: for laminating details, see the SpecLaminating dimension. |
SideOneCoatingPercent | The coating coverage requested on the front or outside of the finished product. For overall varnish, this will be 100. |
SideTwoNumColors | The number of process colours requested on the back or inside of the finished product. |
| SideTwoSpotColors | The number of spot colours requested on the back or inside of the finished product. |
| SideTwoBleed | 'Y' if bleed has been requested on the back or inside of the finished product. |
| SideTwoCoating | The coating requested on the back or inside of the finished product |
| SideTwoCoatingPercent | The coating coverage requested on the back or inside of the finished product. |
| SideThreeNumColors | The number of process colours requested on the flap of the finished product. |
| SideThreeSpotColors | The number of spot colours requested on the flap of the finished product. |
| SideThreeBleed | 'Y' if bleed has been requested on the flap of the finished product. |
| SideThreeCoating | The coating requested on the flap of the finished product |
| SideThreeCoatingPercent | The coating coverage requested on the flap of the finished product. |
SpecDetails Dimension
Contains textual details entered on the specification. New in 1.9.2.16.
Columns
SpecDetailsID | Surrogate key |
BaseType | The type of form used to generate the specification |
JobType | The name of the RFx item used to generate the specification |
Details | Free-text details entered on the specification |
VendorInstructions | Supplier special instructions entered on the specification |
BuyerInstructions | Customer special instructions entered on the specification |
SpecGiftAndPremium Dimension
Contains details from gift and premium specifications. New in 1.9.2.16.
Columns
SpecGiftAndPremiumID | Surrogate key |
DeliveryTerms | DDP / CIF / etc |
LabTests | EN 71 1/2/3, Cadnium, etc |
Packaging | Free text |
ProductLocation | Europe / China / Other |
ProvideCOC | 'Y' or 'N' |
ProvideOther | Free text |
ProvideQCReport | 'Y' or 'N' |
ProvideSocialAudit | 'Y' or 'N' |
ProvideTestReports | 'Y' or 'N' |
Printing | Free text |
PurchaseFamily | Accessories / Bags / etc |
PurchaseType | Ad hoc / Stock / Coordination |
Receiver | Internal / Demonstrator / etc |
RequestedBy | Date quote requested by |
SafetyLevel | 0, 1, 2, 3 |
Size | Free text |
SocialAudit | None / Smeta / SA8000 / etc |
TargetPrice | Free text |
TargetPriceType | Global / Unit |
SpecLaminating Dimension
Contains details about lamination. New in 1.9.2.16.
Columns
SpecLaminatingID | Surrogate key |
Surface | The surface to be laminated (i.e. "Front", "Back" or "Both") |
Texture | The texture of the lamination front and back (i.e. "Gloss", "Gloss / Matt") |
SpecLayout Dimension
Contains details about the layout of the product. New in 1.9.2.16.
Columns
SpecLayoutID | Surrogate key |
Dimensions | The dimensions of the product before any processing (i.e. "420 x 297") |
DimensionDescription | The common name for the dimensions (i.e. "A3") |
FinishedDimensions | The finished dimensions of the product after processing (i.e. "210 x 297") |
FinishedDimensionDescription | The common name for the finished dimensions (i.e. "A4") |
FinishedPageOrientation | The orientation of the finished product ("Portrait" or "Landscape") |
PrintedPages | The total number of sides to be printed |
Sheets | The total number of sheets |
SpecMedia Dimension
Contains details about the media. New in 1.9.2.16.
Columns
SpecMediaID | Surrogate key |
Color | The colour of the media requested |
FSCPercent | The percentage of Forestry Stewardship Council approved material |
MediaType | The type of media requested |
PCWPercent | The percentage of post-consumer waste requested |
WeightGSM | The weight of the media in grams-per-square meter. Only one of WeightGSM or WeightLbs will be populated |
WeightLbs | The weight of the media in pounds. Only one of WeightGSM or WeightLbs will be populated |
SpecPacking Dimension
Contains details about the product is to be packaged. New in 1.9.2.16.
Columns
SpecPackingID | Surrogate key |
Type | The type of packing requested (i.e. "Boxed", "Cartons", "ShrinkWrap") |
Quantity | The number of products per package |
SpecPostPress Dimension
Contains details about additional processing the product requires. New in 1.9.2.16.
Columns
SpecPostPressID | Surrogate key |
AcetateFront | "Y" if the product requires an acetate front cover |
Collating | "Y" if the product requires collating |
Crease | "Y" if the product requires a crease |
DieCut | "Y" if the product requires die cut shape making |
Drilling | "Y" if the product requires drilling |
Fold | "Y" if the product requires folding |
Glue | "Y" if the product requires glueing |
HolePunching | "Y" if the product requires hole punching |
Holes | The number of holes that should be drilled or punched |
HorseshoeGum | "Y" if the product requires horseshoe gumming |
LowTackSeal | "Y" if the product requires a low tack seal |
MakeShape | "Y" if the product requires shape making |
PackSeparate | |
Serialization | "Y" if the product requires serialisation |
StrawboardBack | "Y" if the product requires a strawboard back |
Trim | "Y" if the product requires trimming |
TaxCode Dimension
Contains information about tax codes and rates
Columns
TaxCodeID | Surrogate key |
TaxCode | The code for the tax (ie 'VAT17') |
TaxName | The name of the tax (ie 'VAT' or 'GST') |
TaxDescription | The descriptive text of the tax, as it appears in drop-downs |
TaxRate | The tax rate, as a percent |
Hierarchies
TaxName > TaxDescription | Drill down by tax type |
TaxNumber Dimension
Outrigger dimension containing tax numbers assigned to buyers
Columns
TaxNumberID | Surrogate key |
BuyerID | Surrogate key of buyer the tax number is assigned to |
Name | The name of the tax number |
Code | The tax number |
UncompetitiveReason Dimension
Contains the reason entered when authorising a PO when the selected scenario is not the cheapest
Columns
UncompetitiveReasonID | Surrogate key |
Code | The code chosen (ie 'NRA') |
Reason | Textual description of reason |
Warehouse Dimension
Contains information about stock warehouses
Columns
WarehouseID | Surrogate key |
Vendor | The owner of the warehouse |
Company | The company name, as entered on the warehouse address |
AddressLine1 | First line of the street address |
AddressLine2 | Second line of the street address |
AddressLine3 | Third line of the street address |
City | City |
Region | County / State / Province |
PostCode | |
Location | The stock location within the warehouse. Every warehouse has a special "- Backorder -" location for linking stock that is on backorder. |
Hierarchies
Vendor > Company > Location | DaysToSubmit | The number of days between the RFQ being sent to the vendor and them responding |
HoursToSubmit | The number of hours between the RFQ being sent to the vendor and them responding | |
DaysToComplete | The number of days between the PO being sent to the vendor and the activity being marked as complete | |
HoursToComplete | The number of hours between the PO being sent to the vendor and the activity being marked as complete | |
DaysOverdue | The number of days after the activity's due date that it was marked complete. If not completed, the number of days is calculated based on the current date | |
HoursOverdue | The number of hours after the activity's due date that it was marked complete. If not completed, the number of hours is calculated based on the current date | |
Quantity | The quantity of the | |
TotalCost | The total cost price of the activity | |
UnitCost | The total cost price of the activity divided by the quantity | |
POValue | The purchase order value of the activity, if approved | |
TotalSale | The total sale price of the activity | |
UnitSale | The total sale price of the activity divided by the quantity | |
InvoiceNet | The invoiced value of the activity | |
InvoiceTax | The invoiced tax value of the activity | |
ReceivedNet | The value of all supplier invoices received for the activity | |
Profit | The total sale price minus the total cost price | |
Quality | The "quality" rating entered for the vendor. Ranges from -2 (lowest) to 2 (highest) | |
OnBudget | The "on budget" rating entered for the vendor. Ranges from -2 (lowest) to 2 (highest) | |
OnSchedule | The "on schedule" rating entered for the vendor. Ranges from -2 (lowest) to 2 (highest) | |
ActivityPrice.ProjectID = Project.ProjectID | Links the fact to the project | |
ActivityPrice.ProductID = Product.ProductID | Links the fact to the product | |
ActivityPrice.VendorID = Vendor.VendorID | Links the fact to the vendor supplying the goods / services | |
ActivityPrice.BuyerID = Buyer.BuyerID | Links the fact to the contact ordering the project | |
ActivityPrice.ActivityID = Activity.ActivityID | Links the fact to the activity type | |
ActivityPrice.ActivityPriceNoteID = ActivityPriceNote.ActivityPriceNoteID | Links the fact to the supplier notes / extra description | |
ActivityPrice.UncompetitiveReasonID = UncompetitiveReason.UncompetitiveReasonID | Links the fact to the reason given for not choosing cheapest suppliers | |
ActivityPrice.DateCreated = DateCreated.DateCreated | Links the fact to the date it was created | |
ActivityPrice.DateRequested = DateRequested.DateRequested | Links the fact to the date the RFQ was sent | |
ActivityPrice.DateSubmitted = DateSubmitted.DateSubmitted | Links the fact to the date the quote was received from the supplier | |
ActivityPrice.DateDue = DateDue.DateDue | Links the fact to the date the activity is due to be completed | |
ActivityPrice.DateCompleted = DateCompleted.DateCompleted | Links the fact to the date the activity was marked completed | |
ActivityPrice.ProjectID = Specification.ProjectID AND ActivityPrice.SectionNumber = Specification.SectionNumber | Links the fact to the specification it relates to | |
ScenarioNumber | A number indicating which scenario the activity belongs to | |
Section | A textual description of the specification section the act | |
VendorRef | Reference entered by supplier when entering quote | |
IsDeclined | If 'Y', the supplier has declined to quote | |
IsSelected | If 'Y', the activity is in the selected scenario | |
IsOrderedQuantity | If 'Y', the prices are for the quantity the customer has ordered | |
CurrencyCode | The 3-letter currency code used for the measures | |
VendorRef | The reference the supplier entered when setting prices | |
InvoiceDate | The latest date that a customer invoice was issued | |
InvoiceNumbers | Invoice numbers for the activity as comma-separated list | |
PONumber | The purchase order number for the activity | |
POValue | Total value of POs authorised for selected supplier and given activity | |
InvoiceNet | Net value of customer invoices for given activity | |
InvoiceTax | Tax value of customer invoices for given activity | |
Profit | Net value of invoice less PO value | |
LowestCost | Lowest supplier quote received for given activity | |
AverageCost | Average (mean) of supplier quotes received for given activity | |
HighestCost | Highest supplier quote received for given activity | |
Savings | Highest supplier quote less PO value | |
Vendor1Cost - Vendor10Cost | Supplier quotes received for given activity, from lowest to highest | |
HoursOverdue | The total number of hours an item is overdue. Before an event has been completed, this shows the difference between the expected date/time and the date/time at the point the fact was updated. After the event has been completed this shows the difference between the expected and actual completion date/times. | |
DaysOverdue | The total number of days an item is overdue. | |
HoursToComplete | The total number of hours from the start event to the completed event. Until the event is completed this will be NULL. | |
DaysToComplete | The total number of days from the start event to the completed even | |
ProjectStatus.ProjectID = Project.ProjectID | Links the fact to the project it the events are for | |
ProjectStatus.ProductID = Product.ProductID | Links the fact to the product the project was created from | |
ProjectStatus.StartEventID = StartEvent.StartEventID | Links the fact to the start event it captures | |
ProjectStatus.CompletedEventID = CompletedEvent.CompletedEventID | Links the fact to the event whose completion it captures | |
ProjectStatus.CompletedNoteID = CompletedNote.CompletedNoteID | Links the fact to any free text notes entered for the event | |
ProjectStatus.BuyerID = Buyer.BuyerID | Links the fact to the customer contact for the project | |
ProjectStatus.CompletorID = Completor.CompletorID | Links the fact to the contact who completed the event | |
ProjectStatus.DateStarted = DateStarted.DateStarted | Links the fact to the date the start event was completed | |
ProjectStatus.DateCompleted = DateCompleted.DateCompleted | Links the fact to the completion date | |
DateTimeStarted | The date and time the start event was completed | |
DateTimeExpected | The date and time the event is expected to complete (due date) | |
DateTimeCompleted | The date and time completed | |
| Version | The version number, starting at 1 | |
| VersionStart | The date the version was valid from (can be arbitrarily far in the past for version 1) | |
| VersionEnd | The date the version is valid to (can be arbitrarily far in the future, for the latest version) | |
| IsLatesstVersion | Y/N, indicating whether this row is the latest version | |
EventID / StartEventID / CompletedEventID | Surrogate key | |
Checklist | Title of the checklist event belongs to | |
Title | Title of the event | |
Type | Type of system event which triggers completion, if applicable. See below for list of event codes | |
IsMandatory | Indicates this item must be completed before subsequent events can be started | |
SortOrder | Number indicating position in checklist sequence | |
Checklist > Title | Groups events into checklists | |
0 | Unknown event | |
Code | Description | |
jobpart_activity_sent | RFQs Sent | |
jobpart_activity_received | Quotes Received | |
jobpart_quoted | Quoted to Customer | |
jobpart_ordered | Order Placed | |
jobpart_all_pos_approved | All Supplier Orders Sent | |
jobpart_proof_started | Proofing In Progress | |
jobpart_proof_approved | Proofs Approved | |
goodsin_complete | Delivered | |
invoice_issued | Invoiced | |
po_created | PO created | |
po_approved | PO approved |