July 28, 2008 at 8:48 am
Hi,
I'm going to keep my example fairly simple as I'm trying to figure out what's best.......
The problem is thus: we have a data warehouse (mainly financial data) and want the user to be able to decide what currency to use. Thankfully, it's either $, £ or € so fairly limited. Easiest way (and best performance) for storing this (I think) is simply having 3 columns, which are calculated during ETL based on a yearly fixed exchange rate. That's the simple bit.
My problem is what's the best way for storing the user preference? Adding a parameter to every stored procedure seems a little dumb. Storing the users choice in a table would mean a select statement for every procedure. Has anyone used extended properties as it seems that I could add a "Currency" extended property to each user and get this value every time? Is there a better way to do this? Anybody got any ideas?
Thanks.
July 28, 2008 at 9:55 am
Why would adding a select or a parameter seem like a bad idea? Does the extended property somehow imply less resources are used? It's likely a select in and of itself. And how would you store this for multiple users?
The way I'd do it is to store this in session somewhere on the web server and then pass it in as a parameter when needed. Or Keep the exchange rate and preference in session and calculate it on the client and not the db server.
July 28, 2008 at 10:23 am
Hi Steve,
Steve Jones - Editor (7/28/2008)
Why would adding a select or a parameter seem like a bad idea?
Basically, we've got a lot of stored procedures set up already, with a DAL inbetween so I was hoping to minimize the amount of changes to the application by just doing everything on the database. Obviously, if necessary we'd go through all of them but didn't really want to!!
Does the extended property somehow imply less resources are used? It's likely a select in and of itself. And how would you store this for multiple users?
I was guessing that the extended properties might be a select in and out. Doesn't seem like a great idea. Wasn't sure about the mutiple users, was hoping each user could have a "currency" extended property set for them.
Seems like a better idea may be to have a table with the users currency in it and then simply do a select from this table to get the users currency? It's probably the same thing anyway??
The way I'd do it is to store this in session somewhere on the web server and then pass it in as a parameter when needed. Or Keep the exchange rate and preference in session and calculate it on the client and not the db server.
Yeah, only problem with that is the fact that the exchange rate changes, so unless we returned the year in every resultset this wouldn't be possible. Plus, by doing it during ETL we don't get the extra performance overhead by converting currency at runtime.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply