All You Need To Know To Start Learning SQL Server Analysis Services

I have this rare opportunity to provide basic SSAS training to developers from another team. Being busy with my own projects, my manager and their manager agreed to have the training for only 3 hours. I met the would be trainees and their manager to understand their expectations, the developer profiles and the modeling technique their project use.

The developers have intermediate to advanced T-SQL skills and they expect that at the end of the 3-hour training, they would be able to comfortably navigate around the existing SSAS solution, understand basic concepts of dimensional modeling, create new dimensions and measures, modify the existing cubes and have the basic knowledge they need to dig deeper into SSAS themselves. Whew! Investigating further, I found their current solution uses multidimensional approach with SQL Server 2012. They have calculations and perspectives but do not (or maybe not yet) define partitions, aggregations and translations.

With those constraints in mind, I prepared the outline of my training using XMind.

Basic SSAS Training

Gathering your materials and pre-requisites:

The developers told me they are willing to do some homework before the training so they can take the most out of it. Below will be their homework:

1. Watch these videos in TechNet on Introduction to Microsoft Business Intelligence.

2. Install AdventureWorks2012, AdventureWorksDW2012 on their Virtual Machine. MDFs are available here. READ and perform Step 2 from this page.  If necessary, perform Step 3.

Since we are doing this training in office, we can have VMs with Enterprise edition softwares. If you are learning SSAS at home, I am assuming that you have your own VM, if not, you should know how to build one yourself. Every Software Engineer should know how to create his own VM. No excuses. It is always a good practice to separate your “working OS” with your personal one in case *something* happens. Finally, DON’T install pirated softwares. Invest in or request for an MSDN subscription from  your company.

Sample Projects:

The first 30 minutes of my training will be discussions on BI Overview and Data Warehousing Concepts. The next will be querying, deploying and processing cubes. The rest will be a pattern of concept definition, demo and practice on each Analysis Services Objects.

Fortunately, Microsoft has provided us with 10 Lessons to kickstart our SSAS Development studies. It comes with an Online Tutorial and a very convenient PDF version. I will use these sample projects as my base material and tailor them according to our priority topics.

What’s next?

After doing the Tutorial mentioned above, you now have a taste of what SSAS development is like. Some of it you will understand, some would be vague. I now encourage you to proceed further with the following topics:

  1. Understand the Architecture of SSAS. This article is a good start.
  2. Learn MDX. Yet again, MSDN has provided us with decent materials to do so.
  3. Read further on Multidimensional Modeling in SSAS. Some of the materials will be  a duplicate of what you have seen before, but it will help you learn deeper and absorb the concepts.
  4. Read more SQL Server Online books. I always prefer the PDF versions of MSDN articles because they are more print-friendly.
  5. Not necessarily the last step and specially if you intend to proceed with a career on Business Intelligence, I recommend you to purchase “The Data Warehouse Toolkit” by Ralph Kimball and Margy Ross. This book is not technology specific, not even OLAP biased. I have read this book being recommended over and over again in different books and articles I’ve seen on BI and I understood why after purchasing a hard copy for myself.

If you are in Singapore, acquire a membership on the National Library Board. They have quite a collection of latest SQL Server books you can borrow for max of 42 days at a time.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s