I have a fundamental dimensional modeling question.
I am working on a project where the customer wants to have a dimension that lists the product specs including the product's physical properties. The thing I am struggling with is how to model the physical properties.The physical properties are grouped. Each Group called a "Lot Group" has 1 or more Tests. And each Test has 1 or more measurement Types.
And all of this needs to tie back into a Product Dimension.
Any ideas on how to model this type of thing? In my staging database, I have this data loaded into a single table. I can write a query joining with a product table to get 1-to-n rows for each product code that lists the Lot Group and each Test along with the specification numbers for that test. I was thinking about using the PIVOT transform to get this into a nice wide table, but am struggling with what to pivot on, how to order the data, etc...
The results of the query look something like this:
| Product Code |
Lot Group |
Lot Test |
Meas 1 |
Meas 2 |
| 100 |
Porosity |
2 Micron |
0.25 |
125.5 |
| 100 |
Porosity |
5 Micron |
0.5 |
10 |
| 100 |
Strength |
A-Axis |
0.75 |
100.2 |
| 100 |
Strength |
B-Axis |
100 |
200 |
| 101 |
Stability |
Shrinkage |
10 |
20 |
| 101 |
Porosity |
2 Micron |
100 |
200 |
| 101 |
Porosity |
5 Micron |
25 |
35 |
| 101 |
Porosity |
20 Micron |
.009 |
.019 |
| 101 |
Porosity |
Total |
90 |
100 |
Hopefully, this makes some sense...
Any suggestions would be greatly appreciated.