Reddit reviews MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Microsoft Press Training Kit)

Granted I am biased because I work on Microsoft, I would say it's a great place to learn.

You can purchase the developer version of SQL Server which has every BI tool included for $50, and then you can use some of the resources below to learn the tool set. The Kimball book mentioned is great for learning data warehouse principles and there is also a Microsoft specific version. Remember SQL 2012 came out recently so some of the material doesn't line up perfectly, but its a good starting point.

http://learnmsbi.com
http://msbiacadamy.com
http://www.amazon.com/MCTS-Self-Paced-Training-Exam-70-448/dp/0735626367

I found that the examples given in the data mining chapter of the Training Kit book for SQL Server 2008 BI (70-448) was pretty good.

Data mining in SQL server as I see it is about building models that taken some values can either predict an unknown value or find patterns within itself.

The way you make your model is you start by asking yourself what you want to either predict or what type of data you think might hold more information than you see by just looking at it.

The two different types are called directed data mining and undirected data mining.

The directed approach is usually what people will ask you to help them with because they already have an idea of what they want to solve.

OK so where to start, let's say for example you have some data from your factory that grinds coffee beans. Your factory has a system that has information about imports, deliveries, warehousing, production (packeting, weighting, other controls), your employees, your scheduling of deliveries and all that.

Now what you need to solve is a way to early detect problems with keeping your delivery on schedule.

So maybe your model will look like:

Given when our beans show up, how our production capability is at this time of day, and who works here at that time. What are the chances the product will be in the warehouse on time?

Next you have to pick an algorithm and place your information in it, the different algorithms need to be studied individually for one to understand the results.

Then when your model is done you train it using some of the information you already have.

Then you test your model by using it's training to predict the rest of the information you already have. You then look at how well the model predicted those values to see if the model is any good. If it's only slightly better then a blind guess that means that either your algorithm doesn't work for this problem or worse you are trying to solve something that can't be solved, maybe you need to add or remove information used or to use a bigger test sample?

OK so if you are still with me so far, let's assume you like your model and want to use it. You can deploy your model on your SSAS server and use it like a service or a function. I don't really remember what this step looks like but the idea is that you hold all the parameters and want to find out the probability that a delivery gets delayed (and later you want to know what to change in order to improve the odds of making a dead line).

SELECT odds_of_deliverydelay_using_awesome_Model_X(today,bob is working, beans are ok, yadayada)

Also, I googled around a bit and found this which looks promising:
http://msdn.microsoft.com/en-us/sql10r2byfbi-trainingcourse_sql10r2byfbi09_unit.aspx

Let me know how you are doing and if anything of what I just wrote made any sense. :)

Reading this post, two books immediately come to mind:

Microsoft BI Exam Training Kit - This is almost exactly what you described with "I would like someone to give me next steps as suggesting some real world scenarios with (for example) the adventureworks database". It shows you how to build ETL's in SSIS using the adventureworks sample databases, build cubes in SSAS using the stuff you made in SSIS, and how to build reports in SSRS. I think this particular test has been deprecated so and I don't know if you could use this knowledge to turn around an get a MS certificate anymore, but the book itself has some great examples.

The Microsoft Data Warehouse Toolkit - This book is essentially the blue print of how to implement a Kimball-style DW/BI solution using the SQL Server tools you are already learning. This book is the closest I've read to what life is like for B.I. folks (granted it assumes starting a new project from the ground up, not maintaining an existing solution which comes with its own set of headaches learning opportunities). Downside here is it obviously doesn't expose you to the other primary BI ethos of Bill Inmon. However in my opinion there is plenty of info for free on the web about the ever present Kimball vs Inmon methods.