| Data cube problem [message #249762] |
Wed, 25 July 2007 11:55  |
Eclipse User |
|
|
|
Originally posted by: fabinader.gmail.com
Hi all,
I have the following table being used currently:
Process | Product | Machine | Checklist Item | Was checked? |
-----------|---------|---------|----------------|----------- ---|
Proc. A | Prod. A | Mach. 1 | Broken | Yes |
Proc. A | Prod. A | Mach. 1 | Painting | No |
Proc. A | Prod. A | Mach. 1 | Fissure | Yes |
Proc. A | Prod. B | Mach. 2 | Broken | No |
Proc. A | Prod. B | Mach. 2 | Painting | No |
Proc. A | Prod. B | Mach. 2 | Fissure | Yes |
Proc. B | Prod. A | Mach. 3 | Broken | Yes |
Proc. B | Prod. A | Mach. 3 | Painting | No |
Proc. B | Prod. A | Mach. 3 | Fissure | No |
Proc. B | Prod. B | Mach. 4 | Broken | Yes |
Proc. B | Prod. B | Mach. 4 | Painting | No |
Proc. B | Prod. B | Mach. 4 | Fissure | Yes |
However, the checklist items must be presented as columns on the table,
in the following way:
Process | Product | Machine | Broken | Painting | Fissure |
-----------|---------|---------|--------|----------|-------- -|
Proc. A | Prod. A | Mach. 1 | Yes | No | Yes |
Proc. A | Prod. B | Mach. 2 | No | No | Yes |
Proc. B | Prod. A | Mach. 3 | Yes | No | No |
Proc. B | Prod. B | Mach. 4 | Yes | No | Yes |
The checklist items are not static, they come from a table and are
related to the checking table via a M x N relation (auxiliary table). I
tried to use a data cube, with two groups (checking ID and ckeckitem ID)
and one summarizing field (was checked?), and a cross tab, but I cannot
add the product name and machine fields... Also, I cannot ad a column
header for process...
Does anybody knows how can I create such table?
Regards,
Fuad Abinader
|
|
|
| Re: Data cube problem [message #249904 is a reply to message #249762] |
Wed, 25 July 2007 23:28  |
| Eclipse User |
|
|
|
Hi Abinader
The cube should be based on the star schema. The "Was checked"(measure) is
from the fact table. And the dimensions are "Process", "Product",
"Machine", and "Checklist Item". In crosstab, the first three dimensions
are in row area and the last "Checklist Item" dimension should be in
column area.
If your tables are not like star schema, you can join them into one fact
table using our joint dataset or write a query to join them as below:
The fact table:
Process | Product | Machine | Checklist Item | Was checked? |
-----------|---------|---------|----------------|----------- ---|
Proc. A | Prod. A | Mach. 1 | Broken | Yes |
Proc. A | Prod. A | Mach. 1 | Painting | No |
Proc. A | Prod. A | Mach. 1 | Fissure | Yes |
Proc. A | Prod. B | Mach. 2 | Broken | No |
Proc. A | Prod. B | Mach. 2 | Painting | No |
Proc. A | Prod. B | Mach. 2 | Fissure | Yes |
Proc. B | Prod. A | Mach. 3 | Broken | Yes |
Proc. B | Prod. A | Mach. 3 | Painting | No |
Proc. B | Prod. A | Mach. 3 | Fissure | No |
Proc. B | Prod. B | Mach. 4 | Broken | Yes |
Proc. B | Prod. B | Mach. 4 | Painting | No |
Proc. B | Prod. B | Mach. 4 | Fissure | Yes |
Then define the cube and crosstab.
Regards!
|
|
|
Powered by
FUDForum. Page generated in 0.03452 seconds
Home