MCSE - Business Intelligence Thread

DatabaseHead

A little background.

Failed 70-461 2 times already however I strictly went with the book the first 2 times, not a good idea, at least for me.

See below my history so far on studying for the 3rd time.

I am using CBT Nuggets and the book, more Nuggets.

I've been back at it for 3 days and I am through chapter 5, not all chapters align with the training kit, but I look at the training kit more as a knowledge filler. Hopefully I can deliver the full MCSE BI. I am feeling it this time and have full buy in from my family.

I'll keep this thread updated and I will bring home a passing score for 70-461.

DatabaseHead

Went through some additional training through the virtual lab, I prefer my production SQL client at work or my home express version, but the scripts are set up and can be very convenient especially through the chapters that are more review.

The virtual lab screen is relatively small and the performance was very slow tonight. Still I learned a lot going over all the constraints. I find that this additional knowledge can further bring me along in my journey.

Next up is Triggers, not very useful, but part of the exam so I'll go through the lesson plan and review the training kit if time permits.

DatabaseHead

Right now my approach is going to be 70-461 then 70-463 then 70-462. The reason for this strategy is due to my work experience.

I would really like to do 466 next because that is where I am at right now, but........ For the sake of getting the MCSA first I will go with the original plan. Now on to work and studying.

DatabaseHead

Completed CBT Nugget chapter 5 Triggers, nested, recursive, update, delete, insert. Everything you want to know about triggers and then some, even shared a script sending out an email utilizing the SP_Configured Stored Proc to email users when tables have been modified. This is a new concept to me so it was a good learning nugget. Normally I would use SSIS to do these sorts of things, but it's still cool to know.

Next up Querying Data using SELECT (59 min)


And

Implementing Subqueries (34 min)

And

Implementing Data Types (40 min)



3 listed above are next, I won't be labbing those, just watching the videos. After those 3 then comes the XML and I will be spending quite a bit of time on that. If I remember correctly, both of my fails had 7 XML questions.

Overall I like the Nuggets but 84 dollars a month is a lot so.......... I'm not sure I endorse it fully. I am going to keep it until I take the 70-461 exam. If I pass then it will make a believer out of me and I'll roll into 463.

DatabaseHead

Just finished up Querying Data Using Select. Most of this was an overview but did go over some really interesting tips and tricks using the case statement, such as in the order by etc. It went over some windowing functions, rank over and row number. It was a useful nugget.

Next up will be another review subqueries and data types. Plan is to plow through those two tomorrow and then spend a few days working through the XML and then eventually finishing out the training. I'll be scheduling my test for 461 in the next week or so.

Still plan on skipping 462 and moving onto 463 once I pass this exam.

DatabaseHead

Finished up sub queries, just discovered the 1.5x speed option, not sure why I just discovered this..... This module covered Pivot, Unpivot CTE's and Sub queries. It was a good refresher but mostly review, ~90% review. Plan on banging out Implementing Data Types tonight when I get home and then tomorrow spending ~ a week on XML. I really have to spend a lot of time on this module. I plan on reading the training kit as well in down times covering XML.

My free trial ends today and so i'll be on an 84 USD re occuring charge until I pass. I'll be able to use their practice test once the trial ends so I am looking forward to that. I'll post again once I complete the next module.

So far I give the training a B, it's much better than the Lynda.com one.

nachodba

You seem to be making good progress. I need to complete my MCSE Data Platform so your in progress review of the CBT nuggets material is helpful.

DatabaseHead

Yeah man - Keep at it love that another person is going through this trial with me. It's not easy, if anyone could do it everyone would have the certification and not many do.

Thanks for chiming in I appreciate it.

DatabaseHead

Just completed implementing data types, used some work time to power through. Fairly informative, I'll lab a little bit for the data types I am not familiar with, such as spatial queries and the variant. I am going to review the aggregation module next getting close to to finishing up, lesson 10 out of 20 so.......

Hopefully I'll have one more post before I am done for the night.

I ended up getting through 10 minutes before burning out, heading home to get something to eat and run. Once things settle down I'll get back on and finish up this module, ~30 minutes left but I plan on running through the lab to hone my skills a bit.

I'm putting in quite a bit of effort hopefully this time I get a pass.

DatabaseHead

Just completed advanced aggregates, covering grouping sets, group by, windowing and spatial aggregation. ~45 minutes, it was very informative, I learned a lot from this nugget. I plan on moving into XML next, I am going to review the book now (throughout today) and then move on to the CBT nugget. Pace is the name of the game now, I don't want to burn out. I'll take it slow this next chapter via book and cbt nugget.

DatabaseHead

Made it through half the XML nugget. Brutal, elements and attributes! I'm going to the chiro and then exercising hopefully I'll have time to get on this. Painful.........

I'm going to do it but painful.....

DatabaseHead

Tried to get through it tonight after some sprints at the track. Brutal, got back and just didn't have it. Oh well always tomorrow

DatabaseHead

Pass or Fail I am learning a little bit here and there from viewing the material, which directly helps me with my job. Just saying

DatabaseHead

Took the weekend off, beginning to review the XML training module on the CBT Nugget series. On this particular chapter I am taking detailed notes, very detailed. I will follow up once I complete this lesson today. I will continue to review for the next couple of days though before moving on to Stored Procedures.

DatabaseHead

Completed XML - CBT Nuggets

Went over a lot of material in 45 minutes

XML RAW, AUTO, Root, Elements, Name Spaces, Attributes, Indexs, Primary, Secondary, XML Schema Collections, BCP and OpenXml and With OPENROWSET.

I'm going to continue to read the Murach SQL Server book in regards to XML. I gotta keep at this for a while. I am of the belief if I can master the XML component and review the ALL, Ranking etc I will pass this exam.

DatabaseHead

Completed Stored Procedures.

This was a really good nugget, very informative. Covered all the basics, inputs, outputs and data layer S procs. Most of this I learned in school and on the job, but the instructor did uncover some neat coding techniques when working with branching logic. Overall a good module, probably better for real world issues than the exam.

nachodba

I'm going to have to check out this training. It sounds very good.

DatabaseHead

So far so good..... I am hoping this training does the trick.

nachodba

DatabaseHead wrote: »

Pass or Fail I am learning a little bit here and there from viewing the material, which directly helps me with my job. Just saying

That's how I am with the 70-458 exam I take on Sunday (the day it expires), if I pass, I have my MCSA. If I fail, I will have had a 5 day crash course on Data Warehousing.

DatabaseHead

Sounds like a plan! Honestly any of this stuff isn't worth much unless you have real world experience. That's why I am so reluctant to take 462 when it comes time. 463 is in my wheelhouse, but we shall see how far I get with 461.

DatabaseHead

Just completed Modifying Data with T-SQL, Nugget # 13 out of 20. Took the day off to get some stuff done and thought I would utilize a work free morning (usually NEVER the case) to get one completed, depending how I feel I may roll into # 14 Combine Data Sets. It's only 22 minutes ( the shortest of the nuggets) and with the 1.6x speed turned on 13.75 minutes.........


Lot's of good real world examples in this. TBH I feel like this training is better for real world than the exam. I'll refocus on triggers and XML once I complete all nuggets. I remember both of those being on the exam so...... Def need to know your triggers and XML.

DatabaseHead

Completed (Joining Datasets) Nugget 15 out of 20

Covered Union, Union All, Intersect, Except and Merge.

Union and Union All was review, Union is distinct whereas All is All, pretty easy to remember. Intersect and Except is easy as well. I did like when the instructor used an intersect/except in the IN statement on a subquery.

IN ( SELECT EmployeeID FROM Employees
Except
SELECT EmployeeID from Sales
)

The Merge statement is extremely powerful and useful. Love being able to insert, update and delete using WITH statements like an a Case. Also learned about using $action, to show in OUTPUT your action that occurred. I believe you can alias it if you would like.

DatabaseHead

Plan today is to review the XML section in the training guide. Get used to reviewing the elements/tagging and other components of XML documents. Focusing on the output piece now, Auto, Path, Raw. The next video is ~45 minutes so that will have to wait unless for some crazy reason I can free up some bandwidth at work.

DatabaseHead

Tried getting through the functions tonight. ROUGH...... Just nothing there for me never used one may never use one. I may skip this nugget and roll onto another. I want this to be enjoyable and that wasn't at all....... Got about 15 minutes through a 45 minute nugget. Wow rough!

DatabaseHead

Completed Nugget ~18 skipped over a few, this one was ~22 minutes long. This one went over Cursors, UDF's and the difference between Row and Set theory. It was fairly light although some of the scripting was a tad bit daunting, especially if you never used it before, but the instructor covered it well. I may grab one more training session before I head out. Currently smoking a brisket which takes a long time so this frees up some Saturday bandwidth.

DatabaseHead

I was able to get another nugget in before I head out, error catching and error raising. Went over commits, roll backs, XACT_ABORT and some syntax, when and where you would use it. How to create custom error messages through stored procedures.

So far it's shedding some knowledge on this particular area of SQL. This one was the shortest ~15 minutes long. I have 3 - 4 more nuggets left to watch and then I'll probably go through them one more time.

DatabaseHead

Just completed Optimizing Queries. ~34 minutes

Covered 4 forms of locking and commit and rollback. Pretty straight forward if you have experience with SQL. I'm going to actually hit the sack early tonight, I am worn out from the weekend. Lots of rum and house work, however I did get 3 modules in.

I'm going to have to purchase another 70-461 books. Maybe I can look for another training book, for some reason I hate that book.

Anyway I am down to 3 more nuggets and I will have completed the course. Like I mentioned earlier next time I am going to run through each lab and incorporate some reading and then eventually take practice test.......

DatabaseHead

Stalling, had someone leave our team and I have been building automation solutions like crazy. Still reviewing but focusing more on cardio, trying to mitigate the stress, watching video doesn't do that same unless I hammer a few beers.

DatabaseHead

Hit Murach's book for XML page 602. Legit chapter, nowhere near done ~3 pages in, 3 kids and a wife. However they are on summer hours and I am heading to bed so hopefully I can knock out 10 pages in the AM and get another 10 in at lunch. I believe the whole chapter is ~80 pages so I should be able to get through that in the next week. Following up with the XML video after the fact and some labbing. This is ~25% of my studying focus.

DatabaseHead

Back to the XML CBT Nugget, ~43 minutes. Covering all the XML material in the nugget which aligns with the 70-461 test. Keeping it going strong.....

Really digging deep into Schema's. Pretty crazy how you can list out the elements and attributes but link to the database with a schema coded into your XML document.

DatabaseHead

Complete the XML nuggets, starting over again from nugget 2, create tables. Just making sure I understand the complete material. Moving onto creating and altering views. This is a lot of review, but covering schemabinding and other elements to views will be helpful, I hope!

Side note, downloaded the practice files and added the solution to solution explorer. Never used it before, I really like how it organizes your code, if you haven't looked into this I would highly recommend. You can group your folders and add your scripts along with connection strings to other environments.