Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lazerath
Constraint Violating Yak Guru
343 Posts |
 Posted - 2009-10-07 : 18:05:31
|
| In a DW design I'm working on, one of the requirements is that a user is able to switch system of measure (imperial, metric, US) at run-time. The current architecture repeats each measure in a FACT table for each system of measure (ML & OZ, LB & KG, etc..). As this requires the presentation layer to receive much more data and dynamically decide which value to show, I am just not convinced it's optimal.I'm curious what other's do in this situation. I'm considering the idea of establishing a partition on the SoM and converting every FACT row into all SoM in the ETL. Any experience with this approach (good or bad)? All suggestions & comments appreciated. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-10-08 : 08:13:22
|
| Can you store it in one Som and convert it dynamically when you select it? It's only a conversion factor on the select after all. Depending on your requirements you may be able to create some views and have metric, imperial and US schemas. |
 |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2009-10-08 : 13:00:49
|
| Thanks for the feedback LoztInSpace. A couple points:1.) It's not a single conversion factor as different fields store volume and weight in various increments. Having multiple dynamic conversion factors can be achieved but ends up making the views more complex. 2.) Every user will ALWAYS have an explicit SoM.An approach using views and dynamic conversion means that the presentation layer has to be aware of the SoM and switch between the views based on whichever one it needs at run time. It's either that or union the various views together like partitions, but there would be a performance penalty there I'd have to thoroughly evaluate.These are certainly options, but my hypothesis is that a partitioned table design would out perform both methods while vastly simplifying front end implementation. The latter is due to the fact that the user's SoM will be stored in the DB and can be included transparently, whereas any special front end logic would have to be replicated across each report. Obviously, the major opportunity cost with partitioning is storage, but my understanding of the way it works indicates that it shouldn't negatively impact I/O as long as the SoM is supplied on every query.Have you experience with partitioning? Are there pitfalls here that I am not appreciating?I will certainly do my due diligence with regard to testing the performance of my options so I have something concrete, but it helps to have experts out there who can help me limit the scope.Thanks again for your help. |
|
|
|
|
|
|
|
|
Partitioning by System of Measure?