(Part 2) Top products from r/SQLServer

• MDX Solutions by George Spofford and others - I consider this one a bible of sorts for MDX. Lots of explanations and in the MDX reference section LOTS of practical examples. A little dated as it's for 2005 but still very very useful. For $10 used on Amazon, don't pass it up.
  
• Practical MDX Queries by Art Tennick - A much smaller but very dense "learning by example" reference for MDX. I love this book. Used for $17 on Amazon.
  
• MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli - A really, really good book on MDX. Really great examples.
  
• Expert Cube Development with SSAS Multidimensional Models by Chris Webb and Marco Russo - I don't have this book but considering the authors I have to think it's an awesome book from a great publisher (Packt).
  
• There's also a great reference site called MDXpert which covers most if not all MDX functions and syntax although not in terribly great detail.
  
  The MDX language hasn't really changed all that much, so don't worry too much about getting an older reference for 2005/2008.
  
  You're not going to find an "all-in-one" reference for OLAP and MDX. You'll need more than one reference just because there's so much to cover. And my advice is don't assume that any SQL knowledge you have will translate to OLAP/MDX. It's a totally different animal. It took me years to master and I had 10+ years of experience with SQL Server and Oracle when I started learning OLAP/MDX....which didn't mean squat.

Cool - it's good to see such enthusiasm for databases! I was also creating web pages at 14, and they all looked terrible (but no different from any other website in those days!)

It sounds like at this stage, you're not in a DBA role exactly - you don't seem to have any responsibilty for looking after the database server or the data. Of course, there's nothing stopping you from learning about these tasks, and perhaps moving into the DBA team!

To assist with your analyses, I'd suggest an advanced querying book, such as Inside SQL Server 2005: T-SQL Querying. It's not free, but the 2005 version is quite old now, and used copies from Amazon are very cheap. This book goes into advanced detail, such as logical query processing orders, and goes over more advanced topics that you might find useful. For example, windowing functions (such as ROW_NUMBER() and NTILE()), Common Table Expressions and Pivoting.

With a good grasp of these advanced functions, you can do some awesome things with SQL, and you might learn something your colleagues can learn from you!

It's hard to say. A lot depends on where you are located. Another problem is that you're wearing many hats... but perhaps not very deeply. Again it's hard to say without getting into lots and lots of specifics of exactly what you're doing every day.

In smaller companies its common to see one person taking on multiple roles. In larger companies... not so much.

Part of your challenge will be to specialize. Do you want to do QA 5 days a week? Work with DB's 5 days a week? Manage software rollouts? Project management?

Besides deciding where you might want to focus will be the challenge of increasing your depth of skill in that particular area. This is necessary, especially for more senior jobs at much bigger companies (eg. you're probably not ready to manage the rollout of software to 1000's of desktops).

You might also benefit from The Rules of Work. I really liked it and it made a ton of sense to me.

Getting your resume in shape and start interviewing for new work might not be a bad idea. Even if you're not hired the experience of going on interviews and seeing what sort of questions are being asked would be useful. You've got a job so you're under less pressure... which should let you enjoy the process more. If your current company finds out you're interviewing just explain "you're exploring options for more responsibility".

Asking for more responsibility and more pay is also not a bad idea (with an eye to your company hiring a replacement for you). But it's a small company... what jobs/positions are going to be available? Getting some certifications might not be a bad idea (DBA or even go for a PMI project management one).

On project management... having a widely varied background can be a good foundation. You can talk to business folks and talk to technical folks and figure out what each is saying (and what either side is not hearing).

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. :)

This is the real answer.

While everyone else is talking about which reporting tool to implement, you still need to learn about the architectural structure of the environment (extraction, staging, cleaning, conforming, loading) and then how to structure the final fact and dimension tables.

The great thing is that once you get your "back room" in order, the SQL structures should be able to plug into any sort of presentation layer you want to have, whether it's SSAS, SSRS, Tableau, or PowerBI.

I'd recommend The Kimball Group Reader, Relentlessly Practical... to give you a pretty good starting base on just about anything related to data warehousing.

I would also recommend asking for a data analyst. As a developer/engineer, you should be building structures and processes that will allow for a specialist to get the data the business needs. Instead of business clients bugging you incessantly forcing you to context switch, they will instead call upon the analyst to tweak the reports and conduct the deep-dives on questions they have.

EDIT: the cookbook parent might be talking about is Data Warehouse Design Solutions. They talk about common designs based on several common industries and different reporting levels as your CxO suite will view things at a higher level than your individual specialized department heads.

Moving your Access tables up should be pretty straightforward, Microsoft offers SQL Server Migration Assistant (SSMA) for download.

As for learning MS SQL administration, it is a life long process but these resources should get you up to speed quickly:

• Administering Microsoft SQL Server 2012/14 Databases
  
  
• SQL Server 2012/2014 Security Best Practices. Take this with a grain of salt: you will not implement every strategy in here.
  
  There's a slew of other resources, but these will cover the fundamentals the fastest. Since this is a new role for you, ask your company if they will provide training opportunities.
  Edit: fixed link

Grab a copy of dimensional modeling book from the Kimball group. This one deals with MS technology specifically.

To get a better understanding of data visualization theory and techniques I suggest reading all the material from Stephen Few and Edward Tufte. Few's book on dashboard is fantastic.

Network and meet people in the industry through events thrown by The Data Warehouse Institute. Download free datasets online and use those to gain insight into subjects you're interested in as well as to practice the skills you learn as you play around with SQL Server.

I can add more later but I have to go for the day. Feel free to post more specific questions in this thread and I'll try to answer.

SQL Server Developer, BI Backend Developer, Data Architect, stuff like that.

The Kimball Group Reader (Amazon link, without a referral or whatever) is a must read in the business of DWH and BI. Coincidentally, I haven't read it (impossible to get where I live), so don't ask me for an opinion if it's any good!

> I try to use the same principles of code cleanliness, separation of concerns, etc from .Net dev.

The first thing I imagine when I hear something like that is the person happily converting code into user defined scalar functions (I hope you don't use them). Not all rules from procedural development apply to SQL, remember that!

The Full mode should be used only if data changes frequently (i.e. a system where data is entered constantly) and you want to be able to restore to a certain point in time (by performing Log backup/restore). If your data does not change during the day then its perfectly ok to use Simple recovery mode. No promo but the best explanation Ive found for this is in this book

Thanks man, had to hunt down the UK link but definitely appreciate it!

I've been working my way through chunks of this book for the better part of the day. I think my main stumbling block was that I thought I had reporting services "all installed" -- I have Sql Server, I see the SSRS windows service running, I have the SSRS Configuration manager on my start menu, and I can navigate to localhost/reports. Finally, when I create a new project in Visual Studio, one of my options is "Reports Application". All of this gave me the [false] impression that I was good to go. Apparently, I still needed to install Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013
to get the CORRECT Visual Studio templates. Up until that point, in all the tutorials I would select Reports Application (when the tuts said Reporting Services Project) thinking it was close enough.

I'd say both at once. For someone just getting started exploring the DBA role, I'd recommend Craig Mullins book, Database Administration. It's a platform agnostic overview of the role, responsibilities and knowledge needed. It's a great beginners book because it really does outline the stuff you'll need to study without getting into the specifics of platform or version.

https://www.amazon.com/Database-Administration-Complete-Practices-Procedures/dp/0321822943/ref=sr_1_2?ie=UTF8&qid=1482318494&sr=8-2&keywords=craig+mullins

If you don't have it or are new to Data warehousing. I'd recommend Ralph Kimball's - The Data Warehouse Toolkit. Its been my bible of Business Intelligence.

Also, subscribe to /r/BusinessIntelligence

This is a good start for learning about general database query tuning.

If it's something you might want to learn more on (as back end dev work can be very lucrative), I'd recommend a book, "The art of SQL". It's more than a simple text book or cookbook, it teaches you how to think about sql problems in the context of real world applications.

https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945

edit Also pinal dave of SQL authority is a life saver, check out his blog at:
https://blog.sqlauthority.com/

> Cached plans are still very much a black box to me.

I'm still there with you learning most of this myself. If you more detailed info, check out this SQL Server Internals book. I've been reading it and its helped out alot.

How can you not want this book:

https://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905