SQL Saturday 2013

This saturday Jun-29 I attended the annual SQL Saturday event in South Florida this year on the campus of Nova University. It was a great way to freshen up some SQL and BI skills, network with regional industry professionals and some MVPs and learn about some new technology. It was a free event, with the corresponding array of sponsors present pitching their products and services, as well as a number of recruiters. My estimate is there were about 300-400 attendees. Of the five sessions I attended, I learned a lot in two, two others were ok and one was disappointing. Overall on a scale from 1 – 5, I’d rate the event a 4, somewhere in the middle of the usefulness spectrum. One can probably learn more about the technologies or products from webinars or other free resources on the web. But you can’t replace the networking aspect and directly talking with experts in their respective fields.

There were eleven parallel tracks making for a packed schedule. Below are my notes taken during the sessions I attended.

SQL Saturday Session Notes

• SQL Saturday
  1 day event 6/29/2013 at Nova University

• Resources
  • Books
    • BI in MSFT SharePoint 2013
      iBook for iPad
• Sessions
  • Keynote: Women in Technology
    Why are women so rare in tech? Myth or Reality.What are some of the stereotypes women face in technology and business?
    – speaking the lingo
    – hanging out with the guys for example after hours or on weekends
    – women second guess themselves more often
    – harassment is still fairly common in the workplace (especially in small companies)
  • Self-Service Visualization w PowerView
    Link:www.sqlant.comAnthony Sammartino, TekPartners BI Solutions
    • Self-service Tools
      IBM Cognos InsightMicrostrategy Visual InsightMicrosoft PowerViewTableau
    • PowerView
      For Server, long list of requirements. Incl. SilverLight 4 or 5 (HTML 5 rumored to be coming)
      For desktop, just Excel 2013
      Data Sources:
      – first just tabular models
      – just recently also multi-dimensional MDX models
    • Demo
      Link:mssalesdemos.comDemo around AdventureWorksFirst get to empty canvas with Sharepoint (see Anthony’s Blog for how to get there)Datasource .bism (BI Semantic Model)Dashboard of sales data with revenues and profits etcKPI measures, hierarchies, drill up & down, hyperlinks,

      Web with geo-coded display (Postal codes) via Bing map – very nice integration

      Scatter-Plot with moving bubbles (run-axis) – here running over time to see bubbles evolve.
      This was like the classic works of Asymco or Hans Rosling

      You can export to PowerPoint and send out .pptx; when played and connected to SharePoint, then interactive visuals!

      Can you export from PowerView to Excel? Not directly, but with a Connect tool from MSFT.

      Import from other datasources (Oracle or others) via SQL Server Analysis Services

  • PowerShell Working with XML and SMO
    Link:www.maxtblog.comMaximo Trinidad, MVP PowerShellNot very aware of surroundings; projector was not adjusted well and cut off left side of screen. Did not adjust to that. Did not use keyboard shortcuts, moved windows around incessantly etc.
    Couldn’t answer some questions; or at least not convincingly (Why use PS instead of .NET?)
    Got lost in error messages of his own examples;
    • Demo
      Importing sample XML file
      Accessing elements in the fileDiscovering members (Get-Member)
      Full access to .NET
    • Tools
      PowerShell Studio 2012 (Sapien)
      (Not very convincing demo, constant moving around of console windows etc.
      Would have been easier to just run things within one console window)
      Apps for iPad as lookup reference for cmdlets and for remote access.
    • Tips
      Use singlequote char ‘ to ignore special chars like $ (which is for variables)
      $xml | get-member | Out-Gridview -> moves results into a gridview window
      Use backquote char ` to continue cmd to the next lineUse Invoke-Sqlcmd method of $SQLQuery cmdlet?Use SMO (SQL Managed Objects) to access data from SQL Server in an object oriented style
      – this can auto-create .xml files and their corresponding .xsd definitions
  • SANless clustering
    Link:sios.comFusion IO and SIOS company
    • Technology
      Link:www.fusionio.com/Enterprise Flash cards up to N TB and very fast links to other servers with that card
      Gives you the power of LAN/SANs without the overhead.
      Customers like Apple (iTunes) and FaceBook run this technology
      Anytime you need fast performance, in particular with SQL dataCards work like an extra harddrive; you can format as RAID.IO turbine = read cache for regular IO from regular SAN or other sources.

      In case of a SQL cluster you would put a card in each physical server ($3900 / host)
      There is also a virtualized product if you are using VMs

      SIOS is the 2nd largest consumer of Flash RAM (after Apple for its mobile devices)

    • Problems
      Often server CPU is idle since data access is the bottleneckServer idleness is the enemy -> use Fusion IO to make data available much faster
      Get data to CPUs – Application Acceleration
      Server consolidation – more workload per server
  • Data Mining with SQL Server 2012
    Link:marktab.net/Mark Tabladillo
    • Resources
      Link:microsoft.com/sqlserverlabsPresentation Pro tool for highlighting mouse pointer
    • Teams
      3 teams who brainstormed an innovation and then pitched it in 3 minutes
      Which innovation, what technology used to build it
      Taught some techniques, such as voting for relative preferences
  • Agile BI with SQL 2012 and TFS 2012
    Mohamed Kabiruddin
    
    • Agile Manifesto
      People, process, otherMany small iterations ; frequent deliveriesKimball & Agile BI (Margy Ross, President Kimball Group)
    • Approach
      Traditional BI project approach:
      – 6 month cycle, very detailed with lots of data cleansing and ETL etc.Agile approach:
      – obtain set of features
      – put them on your backlog and prioritize them
      – build the first k features in an iteration
      – inspect and repeatSSIS Templates, handy transformations (Pivot, Unpivot, Script Component), avoid async transformations, use balanced data distributor, take advantage of deployment packages
    • Data Vis in SQL 2012
      PowerPivot for Excel. (Personal)
      PowerPivot for SharePoint (Team)
      Analysis Services Tabular
      Analysis Services Multidimensional (Corporate)
    • Demo with TFS 2012
      TFS also comes in a cloud version (less powerful, but free up to 5 users)
      Agile workflow with features, user stories and tasksBoards for both tasks and featuresPower Tools for custom workflow or work-item typesQuestion on modified boards -> answer not knownTFS 2013 new features:
      – Rooms (chat collaboratively with parts of the team)
      – Request feedback from users; sends email with link, feedback session, (didn’t work due to link security permissions not set correctly)
      • Agile Iterations
      • Branch & Merge
        Define splits from main branchView hierarchy of branchesTwo types of merges:
        – normal merge back to main branch
        – baseless merge (both branches do not have main as ancestor)
    • Demo of SSAS & Excel
      Created .bim file in SSASHere use dataset from data.gov about trade between countries
      Geoflow plug-in features of Excel 2013 (overlay data to 3D globe model)Use many-to-1 relationships between country and trade data
    • Balanced Data Distributor
      This component allows you to for example split a data flow of large file up across multiple nodes (say 1/3 of data for each of 3 nodes)
      Short demo of how to set this up