September 30, 2011 at 1:35 pm
I need to produce reports of manufacturing progress which includes percent complete based on actual hours/expected hours, the number of part shortages, etc.
The entities I'm working with include the following -
Customer
CustomerName
etc.
CustomerPO
CustomerPONumber
CustomerPOLine
Program - "Program" is a way of grouping work
Program
ProgramName
SalesOrder
SalesOrderNumber
SalesOrderLine
DateDueToCustomer
ExpectedShipDate
Price
Job
JobNumber
ProductCode
JobQty
ExpectedStartDate
ExpectedDeliveryDate
ActualCompletionDate
Product
ProductCode
ProductDescription
JobOperations
Job
OperationNumber
WorkCenter - WorkCenter1, WorkCenter2, ...
ExpectedHours
ActualHours
Currently I'm loading information above into a table similar to the one that follows and using Reporting Services to report from the table.
JobProgress
CustomerPONumber
CustomerPOLine
Program
SalesOrderNumber
SalesOrderLine
DateDueToCustomer
ExpectedShipDate
Price
JobNumber
ProductCode
JobQty
ExpectedStartDate
ExpectedDeliveryDate
ActualCompletionDate
ExpectedHours
ActualHours
WorkCenter1OperationsComplete - This will be Yes or No
NonWorkCenter1OperationsComplete - This will be Yes or No
I'm wondering if I should be moving this into Analysis Services since the data could easily be viewed by CustomerPO, SalesOrder, Job, Program, Product, etc. If so, I would appreciate any recommendations for the table design or any resources that would help me come up with one. Most examples I see are for sales data so I'm unsure if SSAS is appropriate for "manufacturing" data like this.
I'm running SQL Server 2008 R2 Standard, SSRS, SSIS with SSAS available.
Thanks in advance,
Kevin
September 30, 2011 at 1:40 pm
Also, if it matters, the percent complete, shortages, etc. will change daily. I'm not sure how you handle that with Analysis Services.
Here is representation of the current process -
OLTP tables (SalesOrder, Job, etc.) ===> SSIS job ===> reporting table (JobProgress).
What would an SSAS process look like?
OLTP tables (SalesOrder, Job, etc.) ===> SSIS job ===> data warehouse table ===> SSIS job ===> cube ===> SSRS/Excel/etc?
Kevin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply