SQL Saturday

SQL Saturday has been running for a number of years around the world, I went to the first one ever held in Australia. It had a huge turnout with around 20 different events to choose from over the day. It had a fair few sponsors present as well as lots of Microsoft Valued Professionals (MVPs).

sqlsat296_web

Of the 20-ish sessions to choose from I went to these:

 

1st session: Kick-ass Ad-hoc Cube Browser build in SSRS 2014

I’ll freely admit I went pretty much for the session title alone. Creating MDX driven reports from SSRS has never as smooth as should have been so I did have some genuine technical interest though.

Grant Paisley showed his Reporting Services generic cube browser report. This was a nice way to have a single report that offered enough parameters you could create virtually unlimited reports from it without having to create new reports. A huge time saving win if you are working with SSRS and Analysis Services.

He has a site that hosts lots of free SSRS report templates you can can try online:  http://www.reportsurfer.com/ReportLibrary.htm

 

2nd Session: I have the data now what! (Troubleshooting problems using logs)

The most mundane sounding session but pretty interesting so bear with me on this one. The demo by Henry Rooney walked us through using LogParser http://technet.microsoft.com/en-au/scriptcenter/dd919274.aspx. This a free Microsoft tool that basically puts a SQL queryable interface on lots of different Windows & SQL event logs. Being able to use SQL to search for the text “Failure” across Application, Security, SQL Server & Cluster logs is a lot better than manually sifting through the Windows event logs for strange looking events around the time something broke. Even better – you can automate this and get it in a daily report.

 

Lunch was spent with 11 MVPs discussing all things SQL Server while eating the largest apple I’d even seen.  Questions were asked about Parallel Data Warehouse (PDW) as Matthew Winter was there (My notes on his presentation at Microsoft a few months ago:https://uxcpsim.wordpress.com/2013/12/19/parallel-data-warehouse-sql-servers-big-brother-presentation/ ).  Most discussion was a bit DBA-ish for me but it did strike a chord when there was mention of the difficulties of trying to track down performance issues on a shared SAN. I think a lot of people in the room may have had a little cry at some stage over that.

 

3rd Session: The Incredible Shrinking Execution Plan

Back in developer territory, Rob Farley suffered a gargantuan bout of demo-laptop-fail but managed to get back on course to show the evils of using table valued functions. Summary: these execute per row so are slow where-as in-line table value functions are set based and can be fully utilized by the query optimizer. See a related article here: http://blog.waynesheffield.com/wayne/archive/2012/02/comparing-inline-and-multistatement-table-valued-functions/

Mental note: if you are writing a view that has a begin and end statement you probably need to reconsider what you are doing.

 

4th session: Event notifications for the proactive DBA

Seeing as how I normally work in development databases with no DBA coverage to speak of, having another free tool to help figure out why the application has just stopped working is always good. Martin Catherall  walked us through using the built in SQL Server Event Notification mechanisms with Service Broker to enable asynchronous handling of events that are placed on a queue. Getting told when deadlocks happen and disk space runs out is valuable not just on production environments.

 

There was a lot of information to digest in just one day but a great opportunity to stay ahead of the game technically and do some quality networking. If you can’t make the regular user groups in the city during the week then this is a great way to get all that content all in one hit.

Telstra B.I. – A Full Service

http://www.sqlserver.org.au/events/ViewEvent.aspx?EventId=672

Do you know any BI/Information Management departments that are so confident in their service they have created 30 second demo videos of their offerings to show it off/market it? …Me neither but last night I got to see just that when I went to a presentation given by Telstra’s John Simon, head of express delivery in the grandly titled “Business Intelligence Centre of Excellence”. Here are a few notes I made:

  • 7 major data warehouses
  • 59 major reporting systems
  • Up to 2 mill queries per hour
  • 80tb, 3tb per month, 400 mill rows daily
  • Teradata with sass over the top solved performance issues
  • Cognos was ejected as query plans were not good
  • SQL 2012, share point 2013, power view & power pivot used for BI
  • Sharepoint themed to look like Windows 8 tiles to give modern look to BI portal
  • Disconnected tables in power view as of cu4
  • Dimensional modelling still important even though tabular is used by default for performance.
  • Multi dimensional cubes still used for larger financial reporting
  • T-inform is the offered BI portal clients can be brought onto
  • Offer conversion service from power pivot to ssas tabular
  • Client can upgrade to cube

It was a very interesting approach to market the BI centers Portal around the rest of the company and give a fully managed migration from Excel based end user reporting to potentially must faster and larger scale solutions. Having a sitewide Sharepoint license is an advantage in this scenario that few companies have so seeing what can be done with one was an impressive eye opener.

Parallel Data Warehouse (SQL Servers Big Brother) Presentation

Faster is better. That’s the rule that applies to pretty much everything: cars, broadband, bar service, data warehouses… I went along to Microsoft last week with this in mind as I knew already that their Parallel Data Warehouse is all about speed and they were keen to demo it.
A little bit late to the party it may be but 4 years after being for sale in other Western markets, Microsoft’s Parallel Data Warehouse (PDW) has entered the Australian market. It sits at the extreme end of the Microsoft Data Warehouse product range:
MSDWOfferings
Presented by ex Teradata employee Matthew Winter, it was a truly impressive fact fest that shows what can be achieved performance-wise when you let the vendors configure everything and then lock the box so no pesky client types can tinker in it.
  • Sold as an appliance  (pre-configured hardware & software all in 1 big box)
  • Contains control nodes, compute nodes & storage nodes (several nodes per rack)
  • Compute nodes are virtual machines with their own SQL Server instances
  • Up to 9 compute nodes per rack
  • Virtual machines run in Hyper-V
  • Hyper-V limited to 64 hosts, which is only thing that limits PDW size
  • Virtual machines watched by MS system center, 99.8% same performance as non virtual
  • Uses MPP – massively parallel processing, instead of current Symmetric Multi-Processing
  • Storage: JBOD – just a bunch of disks, not raid
  • Max storage: 6 Ptbytes
  • Cheap data drives, 70 disks, 32 per comp node plus hot swappable
  • Labelled SQL 2012 but actually 2014
  • Base unit is half rack
  • Full rack : 2 Tb processing per hour
  • Shell database records  meta data on a control node
  • Compute nodes do work
  • SQL execution plans are injected with special parallel instructions
  • Cluster column storage used
  • Don’t forget batch mode for column store
  • Won’t be batch mode if you only use auto stats. Collect your own stats
  • Polybase lets you write SQL against Hadoop and PDW, combining data
  • PDW 1000 * faster than Hadoop so good to load from Hadoop first
  • Migration is simple – schema needs distribution keys, nc indexes removed.
  • Identity columns not supported.
  • Price based on 32 core SQL license
  • Microsoft currently training 5 core partners

PDWAppliance

If your pockets are deep enough and need to process data at the kind of speed that is likely to set your hair on fire then PDW is for you. It is ultra appealing to have everything pre-configured and not have to worry about it.

There is only a handful of companies using it in Australia so far but that will change as there is a clear market for it, as demonstrated by the competitors already at the party – Oracle, Teradata, IBM etc…

Hands-On: The Fastest DBMS In The World

The wild world of IT consultancy is full of swings and roundabouts but maybe the best pro from a technology point of view is getting to get to work with a broad range of interesting technologies on the clients behalf. Recently I was on a client site when they got a team from Actian to do a POC on premises of Vectorwise – an very high performance DBMS that I had never encountered in the wild. The best was after they had set up the POC, we (people on the client site) were going to be able to play with it in order to compare it against other alternatives.

First it is important to understand what Vectorwise does well – it is not a database cure-all that does everything excellently. It does analytical style querying well, very, very well. The industry standard point of reference for database performance is the mundanely titled Transaction Processing Perforormance Council (TPC). They have a handful of different comparisons of different database workloads. The most relevent within the data warehouse/BI space is the TPC-H benchmark:

This benchmark illustrates decision support systems that examine large volumes of data, execute queries with a high degree of complexity, and give answers to critical business questions.

This basically gives us our data warehouse style world championship leaderboard of performance and cost. It looks like this:

TPC-H RESULT (2)

Got your attention yet? Thought so.

Cutting to the chase of our experiments on the client site, Vectorwise was running the heaviest queries the finance team relied on up to 100 x faster than the SQL Server being used already. This wasn’t entirely fair as Vectorwise sat on SSDs and had a bit more memory but those aren’t going to make SQL Server 100 x faster. Less intensive queries ran around 10 x faster than SQL Server, sometimes a little less. The Vectorwise consultants told us a little about the way the CPU cache is used exclusively for processing, which is what gives it it’s large performance advantage. Impressive stuff.

Related: Gartner Data Warehouse vendor magic quandrants

We found problems though, firstly run away queries, which the Vectorwise guys came in and promptly fixed through configuration changes.

The largest problem for the client was potentially user adoption as the variety of SQL used was a subset of ANSI SQL that lacks procedures, functions and data-types – the client site’s analysts (the people that would be writing the SQL) used T-SQL and Informix. Re-training a large team rarely seems like an inviting task.

The final problem was an operational one – the particular server we had set up for us was on a Linux server which would be great if there were any other Linux servers in the company or people skilled in Linux administration. There was a windows flavour available which would have eradicated this problem but was not forthcoming for mysterious management reasons.

It seems like a product that on the one hand provides jaw dropping query performance but only the other gives a slightly buggy, feature poor experience. If it can iron out these headaches before it’s competitors can provide the same performance then Vectorwise will be a lot more prominent in the DW space.

Power BI Presentation with Chris Webb

It’s always nice to have world class acts down here in sometimes sunny Melbourne, especially if you can have a chat with them after the gig. I missed out on that earlier in the year with Aerosmith but made up for it by going to see world renown B.I. specialist Chris Webb present on the future of Microsoft’s Power B.I.

He covered the following topics:

  • Power Pivot
  • Power Query
  • Power View
  • Power Map

I had taken a couple of clients along to the presentation and they were especially interested in Power View & Map  (not just the free pizza & beer MS put on)  as the nature of their business was geographically sensitive and the way postcodes could be looked up automatically into geographic co-ordinates to enable mapping of customers metrics impressed them. Power Map Video

Image

There were a fair oohs and aahs from the room as the camera flew around the 3-d objects being generated on top of Bing maps. Good stuff but not the bread and butter of your average data worker.

Chris recommended this video as a good Power BI demo – it’s actually pretty watch-able as the presenter keeps things moving quickly across all the products & manages to squeeze some classic pop & rock references in there.

Power Query stood out for me as very interesting if not 100% usable yet as it can search for data sets to download/purchase from within the UI. This is great in a demo but without a dazzling array of available data sets, only great in a demo. I found it hard to think of a lot of businesses that would find useful data sets on the open market that don’t have access to them already.

Power View as it has done for a while now, looked like a very visually pleasing & usable dashboarding solution especially now the mobile version has arrived. I know several clients that would kill to have their favourite charts/KPIs available on the go. Not everything quite works yet, some functionality is still outstanding but when complete (HTML 5 version) it will be the kind of thing that offers the people that sign cheques a lot more than an automatically emailed spreadsheet.

power-bi-for-office-365-tablet

As Chris is not a MS employee he doesn’t have to sell anything so he has some refreshingly honest view on the product.

As I’ve said here before, Microsoft is (very wisely) building on the familiarity and ubiquity of Office and in particular Excel, so while Power BI might lack some specific features, customers will want to use it because they already have Excel skills and don’t have to learn something completely new. Why pay a large amount of money for QlikView or Tableau licenses if you’re going to move to Office 365 anyway and everybody already knows Excel?…

There are a lot of “ifs” here though, and the current mess of Office SKUs and licensing could strangle it at birth… read more…

The necessity of having an Office 365 license for some of the cloud components was what Chris was referring to in that last comment. This will be a sticking point with larger enterprises that take years to get onto the latest version of software but less so with smaller companies that upgrade tactically and will see immediate benefits from the per user/per month licensing model of Office 365.

All in all a great non biased presentation of the new tools which wasn’t just tech demos but a dialogue about the usefulness & drawbacks of the product as well as it’s fairly impressive feature set.