July 19, 2011 at 11:41 am
What I have is less of an actual technical issue and more of a design question...a fairly "simple" one I guess.
Are there any "real" advantages to utilizing a named query to populate a dimension versus using an actual table? From my perspective, I can see te advanteages of both....and the cons.
One con that pops into my head is the need to write the SSIS pacakge to populate "real" tables, whereas using the named query will just use the same query used to populate real tables.
IS it mostly just a matter of preference, or are there true performance/maintenance advantages with using one over the other?
Any input would be appreciated!
Thank you!
AMR
July 20, 2011 at 3:21 pm
I guess the simple answer is "It depends" 🙂
Depends on what kind of system you have, what are the size of dimensions, how often they are used/queried, what's the size of dimension etc...
If you have a fairly large dimension , which is used quite heavily, then having real table might be a better option as you can have whatever indexes you like on the real table, but if that's not the case ,then may be named query is ok.
In my personal experience, I have found real tables always better. We are doing a lot of MI and trying to move towards giving as much real-time or near-real-time info as possible. So, our approach is that we keep on incrementally building our dims through out the day in the warehouse and replicating changes to marts to make it available for reporting. Plus the advantage of having indexes...
My 2 cents...hope it helps.
Thanks.
July 27, 2011 at 5:36 am
Anthony Robinson (7/19/2011)
Are there any "real" advantages to utilizing a named query to populate a dimension versus using an actual table? From my perspective, I can see te advanteages of both....and the cons.
Call me old-school if you want but I like my core FACT and DIM tables to be physical ones - always.
A dimension populated on demand adds overhead each time a query hits it - as DBAs we are also responsible for performance so why would we like to design in a way where performance takes a hit "by design"?
Having said that, on other RDBMS there are more immediate and practical reasons why to use a physical table like the "star transformation" feature in Oracle databases.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply