Rapidly Changing Dimension for Manufacturing DWH

[Markus627]

Hello,

I'm designing a dimensional model for a manufacturing DWH. How should I take care for the products which are proccessed during the production-proccess? I think about a Product_X dimension but how should i track the state-history (processed from 6:00am to 6:15at at machine_A, processed from 7:00am to 7:30am at machine_B)?
The think is that I'm not having a standard product like a mobile-phone. Every product is individual with individual information (like a configurated puppet with a name and a special colored t-shirt).

Do anybody have a example for a manufacturing DWH? I've read several books about dimensional modeling but I always read about sales DWH and store DWH.

Kind regards
Markus

[ngalemmo]

What is rapidly changing on the dimension?

Don't confuse process with dimensional attributes. The dimensions represent context. In the case of a product, the context is the product being manufactured, so dimensional attributes relating to the product would end-state values... i.e. 'this is what is being built'. HOW it is built is process, which is represented by facts.

If you are tracking WIP, process steps are represented by rows in one or more fact tables. The context would include such things as the product being produced, date, time, machine, WIP step, work order, material consumed, etc...

In the case of custom manufacturing, 'product' may be represented as a kit (multivalued dimension) or some other collection of structures. But the concepts are the same... product represents the end state.

[Markus627]

Hi,

thanks for your reply. I have to think about it and have to do some prototype modeling (to figure out more confusing and questions ;-) ).

Greetings
Markus

[Markus627]

Okay, one short question.
How should I handle the "production_step" dimension when it is not always a machine? The production_step sounds to "abstract" and I couldn't include the machines in the diagramm.

Greetings
Markus

[ngalemmo]

WIP is usually governed by a work order which usually has a routing associated with it. Work step (or routing step) could be a simple degenerate dimension (1, 2, 3, etc...) or it could be a full dimension with information about the work performed in the step... it all depends. Machine would usually be an independent dimension with an entry for N/A in steps where a machine is not used. It could be degenerate as well, if all you have is a machine ID. Again, it all depends on what you want to track and what information is available to you.

[Markus627]

I will use just one "work_step" dimension.

Next question. A customer want a product. He sends us an application. I will store this information in an extra application dimension. After a few "work_steps" (stored in the "application_work_steps" facttable) the application becomes a "product" (new dataset in my product-dimension). Now I have to do some further work-steps. I want to log the work steps using another facttable "product_work_steps".
Is there a better way to model this manufacturing-process?
How should i link the application to the product?

greetings
Markus

[Markus627]

Hey,
using a "FACT_WORK_STEPS" dimension which tracks the production-process for one document (dims. are product and work_step [list of possible work_steps]).
Is it a good idea to hold one "actual_step" attribute in the "product-dim" which changes (type 1 - scd) everytime when a new entry (the production step changes) is added to the "FACT_WORK_STEPS" facttable?
Is there a better way to get the information "which product is at which production step, now"?

thanks and greetings

[Markus627]

After reading Chapter 5 (DWH Toolkit [2002]) I think the correct answer to my question is: Accumulation Snapshot Fact Tables (for the production-process).

Greetings
Markus

[BoxesAndLines]

Yes, it's a great method to tracking a workflow process.

[Markus627]

Okay, but when one work_step failures and I have do it again and I want to log this "do one workstep again - event" I couldn't do it with Accumulation Snapshot Fact Tables. How should I handle this?

Greetings

[BoxesAndLines]

Insert a new row. Flag the old one as a Not completed or failed.

[Markus627]

Same topic but a new question.
It's about the start of the manufacturing process, the order mgmt. I often read about the suggestion to store data at the most atomic level. For order management it's the "line_item" (an order contains one or many line_items). But I also often read about the Quantity fact. How could I store the quantity fact when I always use the most atomic level?
The same is for shippment. When I have the line_item linked to the shippment fact, and I have the Shippment-Quantity fact it's not the same grain, I think.

Greetings
Markus

[ngalemmo]

You mean quanity measure? If you are storing lines, why would the quantity on the line not be the quantity measure? I do not understand why you would think there would be a problem.

As far as shipments go, it is another fact describing a different event. Sure, the shipment line will reference the order line (as degenerate dimensions), but that is as far as it goes. Just record what was shipped and don't worry about it.

When you query, and want to compare orders and shipments, you query each fact individually, aggreate on common attriutes (both must aggregate on the same attributes) and combine the results. If needed you can create an aggregate table to improve performance.

[Markus627]

You mean quanity measure? If you are storing lines, why would the quantity on the line not be the quantity measure? I do not understand why you would think there would be a problem.

The quantity would be a quantity measure, that's right. But it would be always 1. What I mean is, if the grain is line_item based and I track each line_item by its own, there is no possibilty to have a quantity of 3 at one line. I

Greetings
Markus

[ngalemmo]

So everything is custom one-of work? Ok, fine... Just use a quantity of 1. I would not design a fact table without a quantity column. You never know when you will need it.

[Markus627]

Hi,

I did some modelling and I need to know if i did something serious wrong. I hope this is not outrageous.

- Every order is unique (like a car-order with a special configuration for color, nameemblem on a seat and so on). So I handle it like a individual deal-dimension.
- I use a (role-based) date-dim and a time-attribute for the timestamp.
- I just have one product. But every product is unique (because of the order specific configuration). Maybe I can integrate it in the FACT_PRODUCTION_PROCESS table, but I need to link the product with the FACT_SHIPMENT.
- I have to track the production process for every single process-step. So I use a accumulating snapshot table with timestamps (date-dim and time-attribute) for every step.
- In addition I use a process-step dimension. Because it's important to know which is the actual process-step for a product.

Here is my model.



What do you think about it?

Thank you and greetings
Markus

[Sponsored content]