How to model jobs/work orders

I'm just getting started with using SQL Server Analysis Services and I could use some help figuring out how to model some things.

Our OLTP system has the following tables.

--First a jobs table
CREATE TABLE [Jobs] (
[Job] [char] (8),
[JobClassification] [char] (4) ,
[JobType] [char] (1) ,
[MasterJob] [char] (8) ,
[StockCode] [char] (30) ,
[Warehouse] [char] (2) ,
[Customer] [char] (7) ,
[JobDeliveryDate] [datetime] NULL ,
[JobStartDate] [datetime] NULL ,
[ActCompleteDate] [datetime] NULL ,
[Complete] [char] (1) ,
[QtyToMake] [decimal](10, 3) NULL ,
[QtyManufactured] [decimal](10, 3) NULL ,
[HrsPostedToDate] [decimal](7, 2) NULL,
[Route] [char] (1) ,
[SalesOrder] [char] (6) ,
[SalesOrderLine] [decimal](4, 0) NULL ,
CONSTRAINT [JobsKey] PRIMARY KEY CLUSTERED
(
[Job]
) ON [PRIMARY] ,
) ON [PRIMARY]
GO

--Then a job operations table
CREATE TABLE [JobOperations] (
[Job] [char] (8) ,
[Operation] [decimal](4, 0) NOT NULL ,
[ExpectedHours] [decimal](10, 3) NULL ,
[HoursPosted] [decimal](7, 2) NULL ,
[OperationCompleted] [char] (1) ,
[PlannedStartDate] [datetime] NULL ,
[PlannedEndDate] [datetime] NULL ,
[ActualStartDate] [datetime] NULL ,
[ActualFinishDate] [datetime] NULL ,
[WorkCenter] [char] (6) ,
CONSTRAINT [JobOperationsKey] PRIMARY KEY CLUSTERED
(
[Job],
[Operation]
) ON [PRIMARY] ,
CONSTRAINT [JobOperations_Jobs] FOREIGN KEY
(
[Job]
) REFERENCES [Jobs] (
[Job]
) NOT FOR REPLICATION
) ON [PRIMARY]
GO

--Then a table to hold labor records charged to each operation
CREATE TABLE [dbo].[JobOperationPost](
[Job] [char](8) NOT NULL,
[Operation] [decimal](4, 0) NULL,
[Line] [decimal](7, 0) NOT NULL,
[WorkCentre] [char](6) NULL,
[WorkCentreDesc] [char](30) NULL,
[Hours] [decimal](7, 2) NULL,
[Rate] [decimal](12, 4) NULL,
[Employee] [decimal](6, 0) NULL,
[WcRateInd] [char](1) NULL,
[EmployeeRatInd] [char](1) NULL,
[TrnValue] [decimal](14, 2) NULL,
[TrnDate] [datetime] NULL,
[TrnTime] [decimal](4, 0) NULL,
[Journal] [decimal](5, 0) NULL,
[PostYear] [decimal](4, 0) NULL,
[PostMonth] [decimal](2, 0) NULL,
[Reference] [char](9) NULL,
[AddReference] [char](30) NULL,
CONSTRAINT [WipJobPostKey] PRIMARY KEY CLUSTERED
(
[Job] ASC,
[Line] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Basically I'm trying to figure out what goes in fact tables and what goes in dimension tables.

It seems obvious that the JobOperationPost table data would be placed into a fact table. What about the data in the Jobs and JobOperatons tables?

For example, since the Jobs table has some information that could be aggregated (such as QtyToMake, QtyManufactured, HrsPostedToDate- which is updated whenever a record is added to JobOperationPost) would it seems that it could be a fact table too. Is it acceptable to put data like this in a fact table and then use it as a dimension on the other two?

These are the questions I can see users asking - how many jobs were completed, how many do we have to make (QtyToMake), how many have been finished (QtyManufactured - a job could be for 10 and only 2 finished), how many hours were expected, how many hours were posted.

Thanks in advance for the help,

Kevin

Things you add or count are facts. Things that describe or add context to facts are dimensions.

Things that are facts do not generally end up in dimensions.

I see at least two facts, one on the job and one for actuals. This is similar to the actuals versus forecast pattern.

OK, thanks. I'll take a look at forecast examples.

I've looked at some actual vs budget models but I'm still not sure how to apply these examples to my case. It seems strange that I can find no examples for modeling jobs and their operations.

It looks like these are my options -

1) Make 2 fact tables, one for Jobs and one for JobOperations with appropriate dimension tables. The JobOperations table will have some of the same foreign keys that are found in the Jobs table.

2) Make 1 fact table for the Job Operations and 1 dimension table for the Jobs (along with other appropriate dimension tables).

If I understand correctly, if needed I could make a second (or third depending on 1 or 2 above) fact table for JobOperationPost. Again either including some of the same foreign keys found in the Jobs table or with a key to a Jobs dimension.

What do you recommend?

Hi,

it will be better if you go for two fact tables for forecast and actual, as there will be cases where you may do a comparisn on what was forecast and what finally worked in (an in same cases you may want to measure how many times it was changed ).

thanks

What I'm struggling with is this - if users are asking questions regarding Jobs (how many were completed, etc) it makes it seem like this should be a fact table. If they are also asking questions regarding the job operations (which operations for completed jobs had more hours charged than expected, etc) it seem like it should be another fact table. If this is correct, does that mean the Job table columns such as Customer, JobClass, JobType, Complete, Route, QtyToMake, and QtyManufactured, SalesOrder, and SalesOrderLine belong in both fact tables? Or should these columns be in dimension tables?

OK. I've got to get started on this. Here's what I plan to do. Note that I have both a [FactJobs] table and a [DimJob] table. Any comments or suggestions for improvement?

CREATE TABLE [FactJobs]
(
[JobKey] [int] NOT NULL,
[StockCodeKey] [int] NOT NULL,
[WarehouseKey] [int] NOT NULL,
[CustomerKey] [int] NOT NULL,
[SalesOrderKey] int NOT NULL,
[JobCompletedKey] int NOT NULL,
[JobDeliveryDate] [datetime] NULL , --non-additive fact
[JobStartDate] [datetime] NULL , --non-additive fact
[ActCompleteDate] [datetime] NULL , --non-additive fact
[QtyToMake] [decimal](10, 3) NULL ,
[QtyManufactured] [decimal](10, 3) NULL ,
[HrsPostedToDate] [decimal](7, 2) NULL,
)

CREATE TABLE [FactJobOperations]
(
[JobOperationKey [int] NOT NULL,
[JobKey] [int] NOT NULL,
[StockCodeKey] [int] NOT NULL,
[WarehouseKey] [int] NOT NULL,
[CustomerKey] [int] NOT NULL,
[SalesOrderKey] int NOT NULL,
[WorkCenterKey] [int] NOT NULL,
[OperationCompletedKey] int NOT NULL,
[Operation] [decimal](4, 0) NOT NULL , --non-additive fact
[PlannedStartDate] [datetime] NULL , --non-additive fact
[PlannedEndDate] [datetime] NULL , --non-additive fact
[ActualStartDate] [datetime] NULL , --non-additive fact
[ActualFinishDate] [datetime] NULL , --non-additive fact
[ExpectedHours] [decimal](10, 3) NULL ,
[HoursPosted] [decimal](7, 2) NULL ,
)

CREATE TABLE [DimJob]
(
[JobKey] [int] NOT NULL,
[Job] [char] (8) NOT NULL,
[JobClassification] [varchar] (30) NOT NULL,
[JobType] [char] (1) NOT NULL,
[MasterJob] [char] (8) NOT NULL,
[Route] [varchar] (30) ,
[SalesOrder] [char] (6) ,
[SalesOrderLine] [decimal](4, 0) NULL
)

CREATE TABLE [DimSalesOrder]
(
[SalesOrderKey] int NOT NULL,
[SalesOrder] [char] (6) NOT NULL,
[SalesOrderLine] [decimal](4, 0) NOT NULL
[Contract] [varchar] (30) NOT NULL,
]

CREATE TABLE [DimJobCompleted]
(
[JobCompletedKey] int NOT NULL,
[JobCompleted] [char] (1) NOT NULL --Y or N
)

CREATE TABLE [DimStockCode]
(
[StockCodeKey] [int] NOT NULL,
[StockCode] [varchar] (30) NOT NULL,
[StockCodeDescription] [varchar] (30) NOT NULL,
[ProductClass] [varchar] (30) NOT NULL,
)

CREATE TABLE [DimCustomer]
(
[CustomerKey] [int] NOT NULL,
[Customer] [varchar] (30) NOT NULL,
[CustomerDescription] [varchar] (30) NOT NULL,
)

CREATE TABLE [DimWarehouse]
(
[WarehouseKey] [int] NOT NULL,
[Warehouse] [varchar] (30) NOT NULL,
[WarehouseDescription] [varchar] (30) NOT NULL,
)

CREATE TABLE [DimWorkCenter]
(
[WorkCenterKey] [int] NOT NULL,
[WorkCenter] [char] (6) NOT NULL,
[WorkCenterDescription] [varchar] (30)
)

CREATE TABLE [DimOperationCompleted]
(
[OperationCompletedKey] int NOT NULL,
[OperationCompleted] [char] (1) NOT NULL --Y or N
)

[DimJobCompleted] and [DimOperationCompleted] will only have two rows each. Is that OK?


Learning as I go,

Kevin

Edit: Added keys to [FactJobOperations].

Here's version 2. Note [DimJob] no longer exists. Comments or suggestions?

CREATE TABLE [FactJobs]
(
[JobKey] [int] NOT NULL,
[Job] [char] (8) NOT NULL, --degenerate dimension
[StockCodeKey] [int] NOT NULL,
[WarehouseKey] [int] NOT NULL,
[CustomerKey] [int] NOT NULL,
[SalesOrderKey] int NOT NULL,
[RouteKey] [int] NOT NULL,
[JobCompletedKey] int NOT NULL,
[MasterJob] [char] (8) NOT NULL, --degenerate dimension
[JobClassKey] [int] NOT NULL,
[JobTypeKey] [int] NOT NULL,
[JobDeliveryDate] [datetime] NULL , --non-additive fact
[JobStartDate] [datetime] NULL , --non-additive fact
[ActCompleteDate] [datetime] NULL , --non-additive fact
[QtyToMake] [decimal](10, 3) NULL ,
[QtyManufactured] [decimal](10, 3) NULL ,
[HrsPostedToDate] [decimal](7, 2) NULL,
)

CREATE TABLE [FactJobOperations]
(
[JobOperationKey [int] NOT NULL,
[Job] [char] (8) NOT NULL, --degenerate dimension
[StockCodeKey] [int] NOT NULL,
[WarehouseKey] [int] NOT NULL,
[CustomerKey] [int] NOT NULL,
[SalesOrderKey] int NOT NULL,
[RouteKey] [int] NOT NULL,
[WorkCenterKey] [int] NOT NULL,
[OperationCompletedKey] int NOT NULL,
[MasterJob] [char] (8) NOT NULL, --degenerate dimension
[JobClassKey] [int] NOT NULL,
[JobTypeKey] [int] NOT NULL,
[Operation] [decimal](4, 0) NOT NULL , --non-additive fact
[PlannedStartDate] [datetime] NULL , --non-additive fact
[PlannedEndDate] [datetime] NULL , --non-additive fact
[ActualStartDate] [datetime] NULL , --non-additive fact
[ActualFinishDate] [datetime] NULL , --non-additive fact
[ExpectedHours] [decimal](10, 3) NULL ,
[HoursPosted] [decimal](7, 2) NULL ,
)

CREATE TABLE [DimJobClass]
(
[JobClassKey] [int] NOT NULL,
[JobClass] [char] (4) NOT NULL,
[JobClassDescription] [varchar] (30) NOT NULL,
)

CREATE TABLE [DimJobType]
(
[JobTypeKey] [int] NOT NULL,
[JobType] [char] (1) NOT NULL,
[JobTypeDescription] [varchar] (30) NOT NULL,
)

CREATE TABLE [DimRoute]
(
[RouteKey] [int] NOT NULL,
[Route] [char] (1) NOT NULL,
[RouteDescription] [varchar] (30) NOT NULL,
)

CREATE TABLE [DimSalesOrder]
(
[SalesOrderKey] int NOT NULL,
[SalesOrder] [char] (6) NOT NULL,
[SalesOrderLine] [decimal](4, 0) NOT NULL
[Contract] [varchar] (30) NOT NULL,
]

CREATE TABLE [DimJobCompleted]
(
[JobCompletedKey] int NOT NULL,
[JobCompleted] [char] (1) NOT NULL --Y or N
)

CREATE TABLE [DimStockCode]
(
[StockCodeKey] [int] NOT NULL,
[StockCode] [varchar] (30) NOT NULL,
[StockCodeDescription] [varchar] (30) NOT NULL,
[ProductClass] [varchar] (30) NOT NULL,
)

CREATE TABLE [DimCustomer]
(
[CustomerKey] [int] NOT NULL,
[Customer] [varchar] (30) NOT NULL,
[CustomerDescription] [varchar] (30) NOT NULL,
)

CREATE TABLE [DimWarehouse]
(
[WarehouseKey] [int] NOT NULL,
[Warehouse] [varchar] (30) NOT NULL,
[WarehouseDescription] [varchar] (30) NOT NULL,
)

CREATE TABLE [DimWorkCenter]
(
[WorkCenterKey] [int] NOT NULL,
[WorkCenter] [char] (6) NOT NULL,
[WorkCenterDescription] [varchar] (30)
)

CREATE TABLE [DimOperationCompleted]
(
[OperationCompletedKey] int NOT NULL,
[OperationCompleted] [char] (1) NOT NULL --Y or N
)


Kevin