ݺߣ

ݺߣShare a Scribd company logo

Invoicing Bus Matrix

?
?
Ryan Casey
Ryan Casey

This document describes the dimensions and facts in a data warehouse for TricorBraun's invoicing data. It lists 40 dimensions and 4 fact tables, along with the relationships between the dimensions and measures in each fact table. The dimensions include accounts, dates, customers, invoices, items, and other related entities. The fact tables contain measures like sales amounts, costs, profits, and forecasts.

1 of 19
TricorBraun Invoicing 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 DimAccountCoordinator DimBranch DimBranchOfficeLocation DimCompany DimCurrency DimCustomer DimCustomerGroup DimCustomerSegment DimDate DimDivision DimExternalItems DimForecastType DimGLAccount DimInventoryLocation DimInvoice DimItemReceipts DimItems DimOSUser DimPackagingConsultant DimReforecastCode DimRegion DimSalesOrders DimSalesResponsible DimSource DimVendors FactInvoiceDetail X X X X X X X X X X X X X X X X X X DimBranchOfficeLocation had No Surroga FactForecastTransactions X X X X X FactForecastMonthly X X X FactCustomerSnapShot X X X X X X X X X X X X AccountCoordinatorKey CompanyCurrencyKey InvoicedCurrencyKey CustomerKey GLAccountRevenueKey GLAccountConsumptionKey InventoryLocationKey InvoiceKey ItemsKey OSUserKey PackagingConsultantKey SalesOrdersKey SalesResponsibleKey SourceKey VendorsKey BranchKey RegionKey DivisionKey CompanyKey InvoicedCustomerPCKey ReforecastCodeKey CurrencyKey ForecastTypeKey CustomerGroupKey CustomerSegmentKey FromAccountNumber FactInvoiceDetail X X X X X X X X X X X X X X X X X X X X FactForecastTransactions X X X X X FactForecastMonthly X X X X FromAccountNumber not a true Surrogat FactCustomerSnapShot X X X X X X X X X X X PostingDateKey DocumentDateKey BillingDateKey ReceiptDateConfirmed ReceiptDateRequested ShippingDateRequested ShippingDateConfirmed DeliveryDate DateKey FactInvoiceDetail X X X X X X X X FactForecastTransactions FactForecastMonthly Dimensions Surrogate Keys Dates
FactCustomerSnapShot X UnitPriceUSD UnitCostUSD SalesAmountUSD CostPostedAmountUSD CostAdjustedAmountUSD UnitPriceMST UnitCostMST SalesAmountMST CostPostedAmountMST CostAdjustedAmountMST UnitPriceCUR UnitCostCUR SalesAmountCUR CostPostedAmountCUR CostAdjustedAmountCUR CostAmountUSD CostAmountMST CostAmountCUR GPAmountUSD GPAmountMST GPAmountCUR GrossProfitMST GrossProfitUSD GrossMargin SalesMST GPMST SalesUSD GPUSD MTDSalesUSD MTDGPUSD YTDSalesUSD YTDGPUSD TTMSalesUSD TTMGPUSD LY_MTDSalesUSD LY_MTDGPUSD LY_YTDSalesUSD LY_YTDGPUSD LY_TTMSalesUSD LY_TTMGPUSD LY_SalesUSD LY_GPUSD MTDForecastSalesUSD MTDForecastGPUSD YTDForecastSalesUSD YTDForecastGPUSD FactInvoiceDetail X X X X X X X X X X X X X X X X X X X X X FactForecastTransactions X X X FactForecastMonthly X X X X FactCustomerSnapShot X X X X X X X X X X X X X X X X X X LineNum Qty IsProForma IsDropShipLine PriceUnit IsInGPReports IsDropShipSalesOrder IsDeferredBilling IsDropShipCorrected ForecastYear ExchangeRate Comment RECID Period IncludeInBudget EffectivePeriod Proforma CurrentLocation FactInvoiceDetail X X X X X X X X X FactForecastTransactions X X X X FactForecastMonthly X X X X FactCustomerSnapShot X X Measures Attributes
Invoicing Bus Matrix
TTMFcstSalesUSD TTMFcstGPUSD AnnualForecastSalesUSD AnnualForecastGPUSD AnnualReforecastGPUSD MTDSalesMST MTDGPMST YTDSalesMST YTDGPMST TTMSalesMST TTMGPMST LY_MTDSalesMST LY_MTDGPMST LY_YTDSalesMST LY_YTDGPMST LY_TTMSalesMST LY_TTMGPMST LY_SalesMST LY_GPMST MTDForecastSalesMST MTDForecastGPMST YTDForecastSalesMST YTDForecastGPMST TTMFcstSalesMST TTMFcstGPMST AnnualForecastSalesMST AnnualForecastGPMST AnnualReforecastGPMST X X X X X X X X X X X X X X X X X X X X X X X X X X X X
TricorBraun Invoicing 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 ACKey BranchKey N/A CompanyKey CurrencyKey CustomerKey CustGroupKey CustSegmentKey DateKey DivisionKey ExternalItemsKey ForecastTypeKey GLAcctKey InventLocationKey InvoiceKey ItemReceiptsKey ItemKey OSUserKey PCKey ReforecastCodeKey RegionKey SalesOrderKey SalesResponsibleKey SourceKey VendorKey DimAccountCoordinator X DimBranch X DimBranchOfficeLocation X DimCompany X DimCurrency X DimCustomer X DimCustomerGroup X DimCustomerSegment X DimDate X DimDivision X DimExternalItems X DimForecastType X DimGLAccount X DimInventoryLocation X DimInvoice X DimItemReceipts X DimItems X DimOSUser X DimPackagingConsultant X DimReforecastCode X DimRegion X DimSalesOrders X DimSalesResponsible X DimSource X DimVendors X Surrogate Keys Business Keys
AccountCoordinatorID CompanyID BusinessUnitCode BranchCode CurrencyCode DimCustomer.AccountNum CustomerGroupDescription CustomerSegmentDescription CalendarDate DivisionId ExternalItemID ForecastType AcctNum InventDimID InvoiceNum ItemID ItemNum OSUserID PackagingConsultantID ReasonID RegionId SalesID SalesResponsibleID SourceDescription DimVendors.AccountNum DimAccountCoordinator X X DimBranch X X DimBranchOfficeLocation X DimCompany X DimCurrency X DimCustomer X DimCustomerGroup X DimCustomerSegment X DimDate X DimDivision X DimExternalItems X DimForecastType X DimGLAccount X X DimInventoryLocation X X DimInvoice X DimItemReceipts X DimItems X DimOSUser X X DimPackagingConsultant X X DimReforecastCode X DimRegion X DimSalesOrders X DimSalesResponsible X X DimSource X DimVendors X AccountCoordinatorName EmployeeID BusinessUnitName RegionId RegionDescription DivisionId DivisionDescription DivisionOrder IsActive CompanyBusinessUnit BranchOffice IsMainBranchOffice BranchExtension BranchPhone BranchPhoneTollFree BranchFax BranchAddress1 BranchAddress2 BranchAddress3 BranchCity BranchStateProv BranchPostalCode BranchCountry CompanyDescription CompanyCurrency IsSalesCompany CurrencyDescription CustomerName CustomerGroup PaymentTerms CashDiscount SalesGroup SalesResponsible Blocked Branch DimAccountCoordinator X X
DimBranch X X X X X X X X DimBranchOfficeLocation X X X X X X X X X X X X X DimCompany X X X DimCurrency X DimCustomer X X X X X X X X DimCustomerGroup DimCustomerSegment DimDate DimDivision X X DimExternalItems DimForecastType DimGLAccount DimInventoryLocation DimInvoice DimItemReceipts DimItems DimOSUser DimPackagingConsultant DimReforecastCode DimRegion X X DimSalesOrders DimSalesResponsible DimSource DimVendors CustomerLegacyCreateDate DimAccountCoordinator DimBranch DimBranchOfficeLocation DimCompany DimCurrency DimCustomer X DimCustomerGroup DimCustomerSegment DimDate DimDivision DimExternalItems Dates
DimForecastType DimGLAccount DimInventoryLocation DimInvoice DimItemReceipts DimItems DimOSUser DimPackagingConsultant DimReforecastCode DimRegion DimSalesOrders DimSalesResponsible DimSource DimVendors
36 37 38 39 40
DimCustomerGroup.CustomerGroupDescription is a Snowflake of DimCustomer.CustomerGroup EXCEPT for FactCustomerSnapShot DimCustomerSegment.CustomerSegmentDescription is a Snowflake of Customer.SegmentID and DimCustomer.SubsegmentiD EXCEPT for FactCustomerSnapShot DimExternalItems is a Snowflake of DimItem.ItemID DimItemReceipts is a Snowflake of DimExternalItems.ItemID DeliveryTerms DeliveryMode InvoiceStreet InvoiceCity InvoiceState InvoiceZipCode InvoiceCountry PaymentMode AccountCoordinator SegmentID SubsegmentiD CompanyChain CustomerLegacyNumber DataareaId OSUser InventLocation InventSiteID DOY ShortDate YearNbr YearMonth DayFullName DayAbbv MonthNbr MonthFullName MonthAbbv LastDOMFlag BillingYearNbr BillingYearMonth BillingMonthNbr BillingMonthName BillingMonthAbbv BillingDaysInMonth BillingDayNbr LastDOBMFlag SortOrder ModuleType CustVendRelation ExternalItemTXT AcctName InventTypeID Attributes
X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X
Invoicing Bus Matrix
Invoicing Bus Matrix
InventSiteID InventLocationID InventorySiteBranch InventorySiteName WarehouseName WarehouseCity WarehouseState WarehouseCountry InventTypeName IsMainWarehouse WarehouseZipCode ParentInvoiceNum IsInterCompany IsProForma ShipCarrierCode ShipCarrierName TMSShipmentId DeliveryAddress DeliveryCity DeliveryState DeliveryZipCode DeliveryCountry DATEPHYSICAL ItemName ItemGroup ItemType PrimaryVendor ItemStatus LegacyItemid CasePackUM CasePack MoldReference Color WineMold GramWeight Shape Material Style SubStyle StockingSegment PalletQty Attributes
X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X
Invoicing Bus Matrix
Invoicing Bus Matrix
NeckSize NeckFinish NeckDimension NeckDimensionUM SIZE SizeUM PODStyle PODSubStyle PackageType CartonPrint DecoDescription PackOut Feature Feature2 WineSpec OSUserName OSUserFlag PackagingConsultantName ReasonDescription Active SalesOrderType SalesStatus CustomerPO BlanketSO ShipToStreet ShipToCity ShipToState ShipToZipCode ShipToCountry SalesResponsibleName VendorName VendorGroup CompanyChain VendorLegacyNumber VendorLegacyCreateDate
X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X

More Related Content

Invoicing Bus Matrix