SQL SERVER – Learning SSAS (SQL Server Analysis Services) Online in 6 Hours – Top Down Designing and Bottom Up Designing

Those who are following me on Twitter and Facebook know that recently I am reenforcing my own concept for SQL Server Analysis Services (SSAS). Like many of us, I worked with Analysis Services in early years. In an earlier job, I got many projects for relational database performance tuning and over time, I lost touch with SSAS. This does not mean that I forgot all of the concepts but the ‘real’ hands-on experience was gathering dust. Looking back at the last five years, I realized that I have deep experience with relational performance tuning but there are a few new things which I have yet to explore and learn.

My Learning

One weekend I decided to learn SQL Server Analysis Services. I woke up early in the morning at 6 AM and by 7 AM I was sitting with coffee at my dual screen monitor computer. I had a free subscription to Pluralsight so on one screen I turned on their Analysis Services Fundamentals course. This course is well organized and I attended first six modules by 1 PM. After having a quick lunch with my family, I reviewed all the tasks and lessons given in the course. As it was the weekend and I had properly digested all the concepts, I decided to continue the remaining four modules the next day. The next day, the same routine: I followed and finished all the remaining modules along with the exercises. At the end of six hours of video learning and practicing for more than 8 hours, I felt very comfortable with the subject. I re-discovered my knowledge of SSAS which I used to practice a few years ago, before I opted for performance tuning.

Course Details

You can read the official details of the course here yourself. Here is my view on this course. This course is very well structured. I have found many books and attended SSAS training where the training starts with Bottom Up Designing methodology. I have never had any course where the instructor started from Top Down Designing methodology. This course really got my attention as it covers the all the core concepts in a very reasonable time with crisp and clear instructions. After that it jumps to very easy to understand and follow-along demos where one can practice their skills. I am personally a big fan of the AdventureWorks database as that makes our life much easier and everybody has access to that database for free. Thanks to the instructor, Stacia, for using the same database for every single demonstration and exercise.

I have seen many demonstrations where someone takes an already-built cube and imports that into the system and starts explaining various concepts of the cube. I personally do the same when I have to demonstrate some cool new feature of the SSAS in a one hour presentation. However for in-depth learning, the whole game is different and I prefer to learn from the beginning. I prefer to learn how dimensions are built, how measures are categorized and how cubes are built from nothing. One always learns properly when the lesson is explained in structured manner. In this course Stacia has done the same thing – starting from no elements on screen and very quickly building all the modules from scratch. A perfect experience for beginners and experts.

Course Content

This course covers various topics and you can read all the documentations on the official course page. However, as an outline, here are the module names. I am also attaching the image which demonstrates that I have attended every single module of the course. If you stop the course any place during the lesson, when you come back the course content page tracks your progress; this is very convenient.

  • Introduction to Analysis Services
  • Dimensional Model Development
  • Dimension Development I
  • Dimension Development 2
  • Cube Development I
  • Cube Development 2
  • Calculations 1
  • Calculations 2
  • Database Processing
  • Security

Future Expectations

The title of the course says this is an Introduction to Analysis Services. However, I thought this was quite a comprehensive course. I am assuming that there will be another version of this course. Stacia  also hinted couple of times during the course what will be in the advanced course. Well, what I expect in the advanced course is that the quality and presentation stays the same.

One Last Thing

Well, when I watched this course – I only had an annual subscription. Since then I have upgraded to the Pluralsight Annual Plus subscription, which gives me complete access to all the files, exercises and downloadable videos. I just wish I had changed before I started this course as it changes the experience.

Win A 30 Day Subscription

I have one 30 day Pluralsight OnDemand learning subscription to give away. To win you just need to leave a comment to this blog post suggesting which course you want to take and why. One person will be randomly picked as a winner for this 30 day subscription.

Reference: Pinal Dave (http://blog.SQLAuthority.com

About these ads

SQLAuthority News – Pluralsight On-Demand FREE for SQL Server Course

The Moral of Story

You can watch the most popular SQL Server – TSQL course on Pluralsight On-Demand for FREE for the next 48 hours. It starts NOW!

The Story

Learning is always difficult. After learning how to apply your knowledge, learning in real life is even more difficult. Technology is moving faster than the speed of light and new technologies are always emerging – this is now the reality of the new technology world. Between all of this, I personally have very little time to learn new technology. I do not like eBooks (this statement warrants a whole new blog posts – some other time), I prefer regular books. It is becoming more and more difficult to carry around the books and read them with passion. It happened to me once that by the time I finished the book, there was a new version of the product out in the market.

Recently I noticed that I am reading more blogs than books. Blogs are great but they are not structured. Structured learning is the key when one wants to understand technology properly. From experience I have learned that watching videos at my convenience (on-demand) and following the labs along with the video is way easier. A good teacher cannot be replaced by 1,000 books. I find that I can learn fastest using online tutorial videos that are well structured.

All the attendees of the technology mega-event in India got free passes from Pluralsight On-Demand training. I watched two Pluralsight courses online and I found they are well structured and have lots of supporting material (lab, scripts, ppts). When I reported my good feedback to Pluralsight, they offered 48 hours free access to their MOST popular SQL Server – TSQL course, exclusively for SQLAuthority.com readers. I cannot let this offer pass when it can help so many of my readers.

The Beginning of the Real World

Try out the SQL Server – TSQL course right away as it is freely available for the next 48 hours only. Let me know your feedback about the course. This course is for every developer who uses SQL Server in their applications. It covers the fundamentals of using SQL Server from T-SQL to the CLR, to automating processing with SQL Server Integration services.

UPDATE: You do not need login or create account to watch T-SQL course. Just go to SQL Server – TSQL and click on links.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Public Training and Private Training – Differences and Similarities – Public Training vs Private Training

Earlier this year, I was on Road SQL Server Seminars. I did many SQL Server Performance Trainings and SQL Server Performance Consultations throughout the year but I feel the most rewarding exercise is always the one when instructor learns something from students, too.

I was just talking to my wife, Nupur – she manages my logistics and administration related activities – and she pointed out that this year I have done 62% consultations and 38% trainings. I was bit surprised as I thought the numbers would be reversed. Every time I review the year, I think of training done at organizations. Well, I cannot argue with reality, I have done more consultations (some would call them projects) than training.

I told my wife that I enjoy consultations more than training. She promptly asked me a question which was not directly related but made me think for long time, and in the end resulted in this blog post. Nupur asked me: what do I enjoy the most, public training or private training? I had a long conversation with her on this subject.

I am not going to write long blog post which can change your life here. This is rather a small post condensing my one hour discussion into 200 words.

Public Training is fun because…

  • There are lots of different kinds of attendees
  • There are always vivid questions
  • Lots of questions on questions
  • Less interest in theory and more interest in demos
  • Good opportunity of future business

Private Training is fun because…

  • There is a focused interest
  • One question is discussed deeply because of existing company issues
  • More interest in “how it happened” concepts – under the hood operations
  • Good connection with attendees
  • This is also a good opportunity of future business

Here I will stop my monologue and I want to open up this question to all of you:

Question to Attendees -

Which one do you enjoy the most – Public Training or Private Training?

Question to Trainers -

What do you enjoy the most – Public Training or Private Training?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Index Created on View not Used Often – Limitation of the View 12

I have previously written on the subject SQL SERVER – The Limitations of the Views – Eleven and more…. This was indeed a very popular series and I had received lots of feedback on that topic. Today we are going to discuss something very interesting as well.

During my recent performance tuning seminar in Hyderabad, I presented on the subject of Views. During the seminar, one of the attendees asked a question:

We create a table and create a View on the top of it. On the same view, if we create Index, when querying View, will that index be used?

The answer is NOT Always!

(There is only one specific condition when it will be used. We will write about that later in the next post).

Let us see the test case for the same. In our script we will do following:

USE tempdb
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
GO
-- Create SampleTable
CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))
INSERT INTO mySampleTable (ID1,ID2,SomeData)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),
ROW_NUMBER() OVER (ORDER BY o2.name),
o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create View
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
GO
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO
-- Select from view
SELECT ID1,ID2,SomeData
FROM SampleView
GO

Let us check the execution plan for the last SELECT statement.

You can see from the execution plan. That even though we are querying View and the View has index, it is not really using that index.

In the next post, we will see the significance of this View and where it can be helpful.

Meanwhile, I encourage you to read my View series: SQL SERVER – The Limitations of the Views – Eleven and more….

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – A Successful Performance Tuning Seminar at Pune – Dec 4-5, 2010

This is report to my third of very successful seminar event on SQL Server Performance Tuning.

SQL Server Performance Tuning Seminar in Colombo was oversubscribed with total of 35 attendees. You can read the details over hereSQLAuthority News – SQL Server Performance Optimizations Seminar – Grand Success – Colombo, Sri Lanka – Oct 4 – 5, 2010.

SQL Server Performance Tuning Seminar in Hyderabad was oversubscribed with total of 25 attendees. You can read the details over here SQL SERVER – A Successful Performance Tuning Seminar – Hyderabad – Nov 27-28, 2010.

The same Seminar was offered in Pune on December 4,-5, 2010. We had another successful seminar with lots of performance talk. This seminar was attended by 30 attendees.

The best part of the seminar was that along with the our agenda, we have talked about following very interesting concepts.

  • Deadlocks Detection and Removal
  • Dynamic SQL and Inline Code SQL Optimizations
  • Multiple OR conditions and performance tuning
  • Dynamic Search Condition Building and Improvement
  • Memory Cache and Improvement
  • Bottleneck Detections – Memory, CPU and IO
  • Beginning Performance Tuning on Production
  • Parametrization
  • Improving already Super Fast Queries
  • Convenience vs. Performance
  • Proper way to create Indexes
  • Hints and Disadvantages

I had great time doing the seminar and sharing my performance tricks with all. The highlight of this seminar was I have explained the attendees, how I begin doing performance tuning when I go for Performance Tuning Consultations.

Pinal Dave at SQL Performance Tuning Seminar

Pinal Dave at SQL Performance Tuning Seminar

SQL Server Performance Tuning Seminar

SQL Server Performance Tuning Seminar

Pinal Dave at SQL Performance Tuning Seminar

Pinal Dave at SQL Performance Tuning Seminar

Pinal Dave at SQL Performance Tuning Seminar

Pinal Dave at SQL Performance Tuning Seminar

SQL Server Performance Tuning Seminar

SQL Server Performance Tuning Seminar

SQL Server Performance Tuning Seminar

SQL Server Performance Tuning Seminar

This seminar series are 100% demo oriented and no usual PowerPoint talk. They are created from my experiences of various organizations for performance tuning.

I am not planning any more seminar this year as it was great but I am booked currently for next 60 days at various performance tuning engagements.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – A Successful Performance Tuning Seminar – Hyderabad – Nov 27-28, 2010 – Next Pune

My recent SQL Server Performance Tuning Seminar in Colombo was oversubscribed with total of 35 attendees. You can read the details over here SQLAuthority News – SQL Server Performance Optimizations Seminar – Grand Success – Colombo, Sri Lanka – Oct 4 – 5, 2010. I had recently completed another seminar in Hyderabad which was again blazing success. We had 25 attendees to the seminar and had wonderful time together.

There is one thing very different between usual class room training and this seminar series. In this seminar series we go 100% demo oriented and real world scenario deep down. We do not talk usual theory talk-talk. The goal of this seminar to give anybody who attends a jump start and deep dive on the performance tuning subject. I will share many different examples and scenarios from my years of experience of performance tuning.

The beginning of the second day is always interesting as I take attendees the server as example of the talk, and together we will attempt to identify the bottleneck and see if we can resolve the same. So far I have got excellent feedback on this unique session, where we pick database of the attendees and address the issues. I plan to do the same again in next sessions.

The next Seminar is in Pune.I am very excited for the same.

Date and Time: December 4-5, 2010. 10 AM to 6 PM

The Pride Hotel
05, University Road,
Shivaji Nagar,
Pune – 411 005
Tel: 020 255 34567

Click here for the agenda of the seminar.

Instead of writing more details, I will let the photos do the talk for latest Hyderabad Seminar.

Hotel Amrutha Castle

Hotel Amrutha Castle

King Arthur's Court

King Arthur's Court

Pinal Presenting Seminar

Pinal Presenting Seminar

Pinal Presenting Seminar

Pinal Presenting Seminar

Seminar Attendees

Seminar Attendees

Pinal Presenting Seminar

Pinal Presenting Seminar

Group Photo of Hyderabad Seminar Attendees

Group Photo of Hyderabad Seminar Attendees

Seminar Support Staff - Nupur and Shaivi

Seminar Support Staff - Nupur and Shaivi

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – SQL Server Performance Optimizations Seminar – Grand Success – Colombo, Sri Lanka – Oct 4 – 5, 2010

I have been on world tour on SQL Server Performance Optimizations Seminar. The latest seminar was conducted in Colombo, Sri Lanka on Oct 4 – Oct 5. I had previously written about this event over SQLAuthority News – SQL Server Seminar at Colombo Full. This event was oversubscribed and we could not accommodate the last few nominations due to the restrictions of the place. We had total of 35 attendees and the event offered lots of fun. The attendees were a perfect combination – all had few years of experience and many of them were responsible for performance for their server. There were few attendees who were just there to know if SQL Server can deliver performance and what are its tricks.

Colombo Beach

Colombo Beach

Warm Welcome

The event was very much welcomed and I received a lot of good feedback. In this event, only few slides were presented – the rule of thumb I had was that I will show only one slide per hour and remaining time I will spend with attendees with real world scenarios and examples. During the two day sessions, we went through many examples, nearly 30 different performance tuning tricks, and tools and understood how performance is often misunderstood with scalability.

SQL Server Seminar Group Photo

SQL Server Seminar Group Photo

What really caught my attention is that many of the attendees thought creating Indexes is the way to improve performance. I had fun time to bust the myth of indexes, and later on, we learned the real trick of how we can improve the performance using indexes.

SQL Horoscope

As attendees were really interested in their current scenario, we did something very interesting during the training. I provided them a couple of queries on the very first day. On the second day, they brought the query results back to me. Based on these query results, we tried to play game where I told them what their server’s bottleneck is and what is it used as. The game went for nearly 10 minutes and we had fun time talking to attendees regarding their server.

Pinal presenting SQL Server Seminar

Pinal presenting SQL Server Seminar

Real World Scenario

The real world is often quite different from the theoretical world. The issues which we learn in classroom are not the same when we deal with the live production server. I focused on real world scenario and asked attendees to bring their valuable queries and configurations. Together, we discussed about their server and tried to help them out with the next logical steps to improve their server. This was a learning experience for me as well. Every server is new and every server has its own tale to share.

Pinal presenting SQL Server Seminar

Pinal presenting SQL Server Seminar

Excellent Participants

No matter how good sessions are, without proper interactive conversations and questions, any session can become boring. As I said earlier, the attendees were very much interested in the subject itself and they really wanted to learn the tricks. Well, I wanted to share my tricks too. It was perfect match – which lead to overall great feedback of the event. I received many emails after the session, I would like to share one here.

Pinal presenting SQL Server Seminar

Pinal presenting SQL Server Seminar

Feedback

Here are some of the feedbacks received on my training in Sri Lanka.

“Many thanks for the email – your seminar was one of the best sessions I attended in my life. You made it so attractive and kept us at the edge of the seat every minute.”

Pinal presenting SQL Server Seminar

Pinal presenting SQL Server Seminar

“A BIG THANKS to your training, we all are at office and just experienced the first great benefit of your training right now. There was a major issue for almost a month which we discussed with you yesterday where the Java program and the SP tried to access the same table and Java program get stuck. Based on your training we have done some changes to Index & where clause and the problem is sorted out now. Juts now, we have completed. No more table lock and the Java program works fine. Once again thank you very much.”

Well, what else I can say, I am overwhelmed with this interest.

Pinal presenting SQL Server Seminar

Pinal presenting SQL Server Seminar

Next Seminar

My next seminar will be conducted in Hyderabad on Nov 27 to Nov 28 (Saturday, Sunday). The location of the seminar is at Best Western Amrutha Castle, 5-9-16, Opp. Secretriat, Saifabad, Khairatabad, Hyderabad. The fee for attending the seminar is Rs. 5,000. If you specify the code ‘SQLAuthority.com,‘ you will quality for 20% discount. Send email to indiainfo@solidq.com and pinal ‘at’ sqlauthority.com to register for the seminar.

You can click here to read more details about seminar.

Reference: Pinal Dave (http://blog.SQLAuthority.com)