Howdy all. Ive been a MSSQL DBA for a while now but don't have tons of design experience, so please bear with these dumb questions.

Im at a point in my career where I need to start doing some new stuff. My current employer will never afford me that opportunity, so short of finding some that wants to pay me while they teach me, Im stuck to buying some books and trying to teach myself. I have two interests/ options:

1; Learn Oracle.
2; Learn some sort of data warehousing technology.

Im well aware that me teaching myself either of these could result in oppening up no new possibilities, but I gotta take a shot.
I'm leaning towards number 2. Now for my questions.

1; Lots of different DW/ BI tools out there. Which one to pick? Analysis Services would seem like a sure bet to a guy in my shoes, but I have nothing to compare it too.
2; Instead of using an actual tool, would I be better off to spend my time learning to design data warehouses? After all, it would seem to me that the tool wouldn't matter if the warehouse was designed improperly.
3; Is a data warehouse/ BI guy actually a programmer? While I am quite good at tuning SQL code, I have no desire whatesoever to write it all day. Is it possible to survive in the DW world without doing tons of coding?

...not as far-fetched as you might think. I personally value the desire and ability to learn new skills as highly as previously aquired knowledge.

Learning Oracle would be more profitable.
Learning data warehousing would be more fun.

Do not make the mistake of confusing the tool with the concept. Analysis Services is NOT data warehousing. It is a tool that can be used with data warehouses. If you want to know what data warehouses are, read one of Inmon's books.

But if you not interested in writing SQL code, then you limit yourself to being a DBA (database administrator), not a DBA (database architect). And if you want a career doing pure database administration (set ups, recovery strategy, optimization), then you are better off spending your time learning Oracle, and leave the other stuff to those of us who love writing SQL code all day.

I'm gathering by this then, that it could be benificial to learn how to design a DW, without actually using a tool like AS? Would it be possible to design a DW to be used by plain old SQL Server, without AS or another DW tool?

Urg... so DW'ing is mainly writing code then?

how is metadata different from data.
I learnt that a metadata is a structured information.
like when 2 company's share same data then that type of data is a metadata.
can someone justify this as i'am not clear with this.
advanced thanks.

Analysis Services is NOT a data warehouse.

Analysis Services does not even BELONG in a data warehouse. Nor is it used to CREATE a data warehouse. It is a tool to analyze data which may (or may not) reside in a data warehouse.

You do not seem to have a clear concept of what a data warehouse is (don't feel bad--most people are fuzzy on the subject).

Designing schemas is central to data warehouse development. And nobody has any business desiging database schemas of they are not a proficient SQL coder. You can get away with not knowing a lot of SQL if you confine yourself to pure database administration, but if you dislike SQL then you have chosen the wrong career.

I should probably clarify, I like writing SQL code, and Im excellent at tuning it. I just don't like being a full time developer, which is what Im trying to avoid.

I haven't chosen anything yet. Im just on a fact finding mission at this point, which you are certainly helping with, thanks.

You would do lots and lots of SQL coding if you dont use any ETL tool (like Ascential's Datastage, Informatica, OWB...etc).

But, you learn so much more (and im not just talking about SQL) by getting your hands dirty. i work in a (currently) small telco company and we have built our datawarehouse from scratch using SQL and PLSQL. after some time, we even built our own portal and reports (at one point even able to slice and dice data...but it was alot of effort and time), all this from scratch.

now that the BO has seen the results of what we can do with so little, they made it a priority to invest for the long term on the data warehouse and BI projects. this of course means we will get new toys to play with - by now we shouldnt have too much of a problem getting used to it.

My humble advice is to learn the concepts of Star Schemas and Data warehousing (like Kilani et al) then you can venture off to Vendor specific Solutions like Oracle Warehouse Builder etc... and since you are an expert in perfromance tuning, you would be much needed in a datawarehouse environment

hope this helps,
abdul

If it wasn't built from scratch, it wouldn't be a datawarehouse.

Ive never used those tools. Are you implying that you dont need to write code in those tools? Are they just point/ click?

This makes a lot of sense to me. It would seem that if the basic concepts arent ingrained, being able to use a vendor specific tool wouldn't really matter all that much.

Thanks!

There are quite a few good minds out there that have produced data warehousing books. Visit an IT Bookstore or amazon.com and do a search.

Ultimately you will determine whether you actually like data warehousing before you dedicate yourself to any specific vendor. That will save you the most time. And you'll have the concepts in your head to boot.

Actually, i was talking about the report system, the CMS, and the portal - all were built using PHP and aggregated MySql. the graphs and other graphical output were made using PhpChartDir, a small but very powerful graphical output.

but yes, youre right, if it were'nt built from scratch it wouldnt be a datawarehouse

Ive never used any of these tools either, but i suppose they make ETL alot easier than just coding and maybe more conveniant. But you would probably use some code.

You have remember that DW is a conceptual design, not a collection of tools. One big piece of advice that I have for you if you decide to pursue DW is that it is an art and not a science. One design that works for one warehouse might be the “best fit” for another. You will get many different opinions when you ask a question. I’ve found most people in DW are very opinionated lol.

There are two main gurus of DW; Inmon and Kimbal. I would pick up books by each and understand the differences between their concepts. I would recommend starting with Kimbal because that seems to be the concept that is most frequent.

Most ETL tools have some form of GUI. A major advantage of ETL tools is that they can extract from many different data sources with one skill set, but you don’t necessarily have to have an ETL tool to do ETL; but it makes it a little easier.

A few things on the DBA end that you will use in DW are indexing strategies, partitioning strategies, bulk inserts / direct path inserts, and query tuning.


Take care and have fun learning!

I'd recommend starting with Inmon, because Kimball will only show you how to create data marts, not warehouses. Inmon will show you the true concept of data warehousing, whereas Kimball will try to sell you the one-size-fits-all just-build-a-star-schema easy-way-out method.

I haven't a clue where you got that idea.