How to model jobs/work orders

View previous topic View next topic Go down

How to model jobs/work orders

Post  morrisk on Thu Apr 19, 2012 9:57 am

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


morrisk

Posts : 6
Join date : 2012-04-19

View user profile

Back to top Go down

Re: How to model jobs/work orders

Post  BoxesAndLines on Thu Apr 19, 2012 10:38 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How to model jobs/work orders

Post  morrisk on Tue Apr 24, 2012 12:42 pm

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

morrisk

Posts : 6
Join date : 2012-04-19

View user profile

Back to top Go down

Re: How to model jobs/work orders

Post  morrisk on Wed May 16, 2012 6:56 pm

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?

morrisk

Posts : 6
Join date : 2012-04-19

View user profile

Back to top Go down

How to model jobs/work orders

Post  hkandpal on Thu May 17, 2012 7:55 am

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: How to model jobs/work orders

Post  morrisk on Thu May 17, 2012 9:35 am

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?

morrisk

Posts : 6
Join date : 2012-04-19

View user profile

Back to top Go down

Re: How to model jobs/work orders

Post  morrisk on Thu May 17, 2012 11:12 am

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].

morrisk

Posts : 6
Join date : 2012-04-19

View user profile

Back to top Go down

Re: How to model jobs/work orders

Post  morrisk on Thu May 17, 2012 11:44 am

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

morrisk

Posts : 6
Join date : 2012-04-19

View user profile

Back to top Go down

Re: How to model jobs/work orders

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum