October 28, 2008 at 10:42 am
Hi. I'm designing our DW now, and I see lots of value in using surrogate keys since we'll have data coming in from several different systems. My question though is that surrogate keys are supposed to be completely business independent (according to Ralph Kimball's books and articles), but if we were to open Analysis Services to the end users wouldn't they see these surrogate keys? That's what I want to avoid.. I want these to be completely behind the scenes, yet I want the end users to have the ability to create ad hoc reports.
Is there a middle ground I'm missing? I'm pretty new to the concepts of data warehousing, but given this task I'm trying to educate myself on all the industry standards. I've already picked-up several of Ralph Kimball's books and have started working with the Adventure Works databases Microsoft offers for testing, including the AdventureWorksDW database which is very helpful. If the info I'm looking for actually lies in any of these resources and I'm just missing it, I'd appreciate any guidance.
Thanks for your time and help...
Sam Alex
October 31, 2008 at 5:18 am
Kimball's books are about data warehousing and not about cubes. The way I look at DW is that star schema is implemented in a database (in our case SQL Server) but Analysis Services is only a presentation layer for allowing users access to the data. The star schema in the database fits very nicely into AS as dimensions and cubes. AS is not the only way they can access the data but generally works well especially if there are large volumes of data. The data in the star schema could also be presented to the users via Reporting Services or other interfaces such as query analyser (if appropriate) especially if the requirement is for lists of data.
The use of surrogate keys does not mean that users will see them. In AS, you choose what the dimension levels are i.e. the columns from the dimension tables. If you don't put the surrogate key in the dimension then the users will not see it - the surrogate key is there purely to link a fact row to a dimenion row and has no meaning at all and shouldn't be included in the dimension.
Some of my users use QA to access the star schema, but I put a view on top of the star schema which does all the joins for them and the users query against that view rather than against the star schema. Again, the surrogate keys would not be included in the view so users would not see them.
HTH
Jez
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply