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 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

About visualign
Visualize Data. Improve Performance.

Leave a Reply

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

WordPress.com Logo

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

Facebook photo

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

Connecting to %s

%d bloggers like this: