Last weekend Damu Venkatsesan and I made the short trip to Nashville for SQL Saturday #272. It’s the closest event to Atlanta this year (so far…) and we are trying to get to as many SQL events as possible to promote the Healthcare SQL VC.
The day started off on bad note with a dead battery. That couldn’t keep us away from the event, but did cause us to be a little later than we wanted. The location was easy to find and plenty of parking so no trouble once we got the car started.
Despite the late start we were able to catch a full day of sessions and they had a very good assortment of speakers. A few of the highlights for me were Allen White’s “Automate Your ETL Infrastructure with SSIS and PowerShell” and Joey D’Antoni’s “In-Memory Columnstore Indexes–Make Your Data Warehouse Fly”. I plan on implementing both of these into future projects. All of the sessions I attended were well done.
A few quick notes about Nashville:
- The space was almost perfect for an event like this, especially the room sizes. They didn’t give a crowded or empty feeling in any of the rooms I was in.
- Nice line up of sponsors. Pretty diverse mix too.
- Lots of Atlanta speakers were there. I’m sure we’ll see them again at #285.
- Big bonus for having great SQL Saturday swag – a pint glass. Nice!
The attendance seemed to be pretty good and everyone I spoke to enjoyed themselves. The organizers should be pretty happy with the outcome.
I recently wrapped up a project using some of the new(ish) Microsoft tools: SSAS Tabular, Power View and
PowerPivot Power Pivot. The project still fresh on my mind, so I wanted to document my experience and offer some praise and criticism.
SSAS Tabular/Power Pivot
I really like the Tabular Model and find it easy to work with. The speed and flexibility of the tools are a nice change from multidimensional. The security model, which could be a problem in multidimensional, is much simpler to implement and modify.
One complaint about the GUI is that it can sometimes be difficult to manage once you get into more complex measures that contain a large number of calculations. I don’t really have a suggestion for how to resolve this, but it would be helpful if it were a little cleaner. It can really get confusing when the development is shared between multiple users.
The ability to import a SSAS Tabular model directly into PowerPivot is needed. Power Pivot can be used as a source, but it does not work the other way around. This would be a great help, especially for business users that are may be trying to learn or want to gain more insight. Marco Russo has created a Connect request for it. Please vote for it if you agree.
I really like the tool’s ability to predict relationships and the Diagram view and think it is a great idea. The only problem is that it is often wrong. After a while you can usually predict when there will be an issue, but I’m not sure how much time it actually saves.
The error messages really need some work. Many of the messages I received did not seem to be related to the underlying issue. Several of these turned out to be just bugs, like duplicate measure names or issues with data source connections. None of them really gave any indication of the real problem. Luckily, most of the messages I encountered were resolved after a search. It would be nice to see a repository for these somewhere.
The business users like it and so do I, but there are a couple of items I would like to see added on.
There is a need to be able to use expressions in Power View. A very common request is to show the data for yesterday or today. There are workarounds, but there are limitations to this that limit interactivity.
More attention needs to be paid to look and feel including support for HTML5. There is currently not enough control over fonts and colors. This seems to be one of the first issues that users notice and in some companies this is critical.
The ability to export or save to another format is greatly needed. Business users want to be able to see the data in Excel – even after viewing it in Power View. The export to PowerPoint (Power Point now?) is nice, but they need access to the data itself.
The DAX syntax seems to be relatively easy to work with and is very similar to Excel functions. DAX does have limitations, especially when compared to MDX. I realize they are two different languages for different purposes, but a few new features would go a long way. There are workarounds for custom calendar support and many-to-many, but there is a need for those to be included natively. I would like to see this in the next version.
I would really like to see more functionality for DAX built into SSMS – similar to what is in DAX Studio. If you haven’t checked that out you probably should.
The biggest problem I found with DAX is the lack of examples, but this is beginning to change. In fact, there have been a couple of great documents released in the last few weeks. Alberto Ferrari released Understanding DAX Query Plans and a new article was released on Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services. If you are interested in DAX, both of these are well worth your time to read. That being said, I need to start posting more here.
In general, I think that this is a great start for Tabular. There is a lot here to work with already, but I look forward to seeing what happens next.
Yesterday, after much procrastination, I finally took the 70-452 exam. This exam is for MCITP: Business Intelligence Developer 2008 and expires at the end of the month. I wanted to go ahead and take it now to make the 2012 ( will there be 2014?) certification a little easier. The upgrade path from 2008 to 2012 is 3 exams instead of 5.
What did I use to prepare? I didn’t use anything specifically for 70-452, but I did use the materials I used for 70-448.
- MeasureUp 70-448 Practice Test
- 70-448 Self-Paced Training Kit
- My own experience
I couldn’t find any information on 2014 exams, so I will go ahead and start on the 2012 exams. I’d like to get those started as soon as possible.