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 a StockDate
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
Email	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

Data Warehouse Schema

Contents

Introduction

Core concepts

Dimensional design

Naming conventions

Vendors vs buyers

Fact tables

Overview

Grain

Types

Relationships

Currencies

Activity Price Fact

Relationships

Other columns

ActivitySaving Fact

Measurements

Relationships

Other columns

BuyerInvoice Fact

Measurements

Relationships

Other columns

Client Usage Fact

Relationships

Estimate Fact

Measurements

Relationships

Other columns

Orders Fact

Measurements

Relationships

Other columns

ProfitAndLoss Fact

Measurements

Relationships

Other columns

PurchaseOrder Fact

Measurements

Relationships

Other columns

Shipment Fact

Measurements

Relationships

Other columns

Specification Fact

Relationships

Other columns

StockLevel Fact

Measurements

Relationships

Other columns

StockMovement Fact

Measurements

Relationships

Other columns

VendorBuyer Fact

Relationships

VendorInvoice Fact

Measurements

Relationships

Other columns

VendorPerformance Fact

Measurements

Relationships

Other Columns

VendorRating Fact

Measurements

Relationships

Dimension tables

Overview

Hierarchies

Versions

Source columns

Special records

Activity Dimension

Columns

Hierarchies

ActivityPriceNote, CompletedNote, VendorInvoiceNote, VendorInvoiceTerms Dimension