Transcript Data Explorer - Steel City SQL

POWER QUERY AND OLD
SCHOOL
HARVESTING XML DATA
JULIE SMITH
• DATA SERVICES CONSULTANT AT INNOVATIVE ARCHITECTS IN ATLANTA, GA
• ONE HALF OF THE DATACHIX.COM WITH AUDREY HAMMONDS
• SQL SERVER MVP
• @JULIECHIX
• ALWAYS FORGET TO UPLOAD MY MATERIALS
• NOT TODAY! HTTP://SDRV.MS/LTBCXF
ROB VOLK
• ROB VOLK IS A DATABASE ADMINISTRATOR FROM THE ATLANTA, GEORGIA AREA
• BLOGS AT WEBLOGS.SQLTEAM.COM/ROBV
• @SQL_R
• FORMER MVP
TODAY’S AGENDA
• STORY OF HOW ANNOYED ROB WAS WITH PLANNING SQL SATURDAYS
•
HAS TO BE A BETTER WAY
•
FORAGING FOR DATA—ON SPEAKERS, VENUES, STRANGE COINCIDENCES
•
POWER QUERY –FIRST CAME OUT AS DATA EXPLORER
•
CURL
•
QUERYING XML WITH SQL (TRANSACT SQL TO BE EXACT)
INTRODUCING
POWER QUERY
• RELEASED JULY 2013
• EXCEL ADD-IN FOR 2010 OR 2013
• SELF SERVICE ETL!!
POWER BI SUITE
• POWERPIVOT – IN MEMORY EMBEDDED DATA MODELS
• POWERMAP
• POWER VIEW – PRESENTATION READY VISUALIZATIONS
• POWER QUERY
• ALL IN EXCEL !!!!
POWER BI
• OFFICE 365
• DATA MANAGEMENT GATEWAY
• COLLABORATE WITH OFFICE 365 SHAREPOINT
• HTTP://OFFICE.MICROSOFT.COM/EN-US/OFFICE365-SHAREPOINT-ONLINE-ENTERPRISEHELP/POWER-BI-ADMIN-CENTER-HELP-HA104078330.ASPX
HISTORY
• M LANGUAGE FOR BUSINESS ANALYSTS
• PROJECT OSLO
• DOMAIN SPECIFIC LANGUAGE
• M HAS MORPHED INTO THE POWER QUERY FOR EXCEL FORMULA LANGUAGE
• DOWNLOAD A PDF HERE: HTTP://GO.MICROSOFT.COM/FWLINK/?LINKID=320633
JULIE WHEN SHE FIRST MET POWER QUERY…
IT CAN HARVEST THE FOLLOWING:
• “OTHER”
• SHAREPOINT LIST,
• WINDOWS AZURE MARKETPLACE,
• ANY HADOOP FILE (HDFS),
• HDINSIGHT,
• ACTIVE DIRECTORY,
• FACEBOOK.
• EXCHANGE ** NEW
• RDBMS: SQL SERVER, AZURE SQL SERVER, ACCESS (OOPS), ORACLE, DB2, MYSQL, POSTGRESS,
TERADATA
• FILE:
• EXCEL, CSV, TXT, , XML, JSON, FOLDER (METADATA)
• FROM THE INTARWEBZ!
• WRITE AN M OR POWER QUERY FORMULA LANGUAGE QUERY YOURSELF FROM SCRATCH
SOURCES
• REGULAR SOURCES—MOST DATABASE PLATFORMS INCLUDING HADOOP
• FILES—ALL TYPES
• ODATA
• WEB SITE TABLES!
• ACTIVE DIRECTORY!
FUNCTIONS
• TRANSFORMATION!
• MERGE (JOIN ON KEY)
• APPEND—COMBINE SOURCES TO NEW DESTINATION
CREATE YOUR OWN FUNCTION
CREATE YOUR OWN FUNCTION
Goes To symbol
RESOURCES
• HTTP://OFFICE.MICROSOFT.COM/EN-US/EXCEL-HELP/LEARN-ABOUT-DATA-EXPLORERFORMULAS-HA104003958.ASPX FORMULA CHEAT SHEET
• GREAT POST BY DATAPIG HTTP://DATAPIGTECHNOLOGIES.COM/BLOG/INDEX.PHP/COOLTHINGS-YOU-CAN-DO-WITH-DATA-EXPLORER/
• ON M LANGUAGE:
HTTP://BLOGS.MSDN.COM/B/MODELCITIZEN/ARCHIVE/2010/09/22/UPDATE-ON-SQLSERVER-MODELING-CTP-REPOSITORY-MODELING-SERVICES-QUOT-QUADRANT-QUOT-ANDQUOT-M-QUOT.ASPX
• M LANGUAGE SPECS FROM MICROSOFT: HTTP://MSDN.MICROSOFT.COM/ENUS/LIBRARY/DD285271.ASPX
• HTTP://BLOGS.MSDN.COM/B/MLANGUAGE/
• DOWNLOADABLE 200 PAGE DOC ON FORMULA LANGUAGE (M)
HTTP://GO.MICROSOFT.COM/FWLINK/?LINKID=235474&CLCID=0X409
RESOURCES FROM ROB (MORE RECENT)
• https://social.technet.microsoft.com/Forums/en-US/0f9fec02-4469-4c56-92ec-00d46b26b3fc/howto-automateparameterizeloop-power-query?forum=powerquery
• http://www.mattmasson.com/2014/04/defining-configurable-settings-for-your-queries/
• https://office.microsoft.com/en-us/excel-help/learn-about-data-explorer-formulasHA104003958.aspx
• https://office.microsoft.com/en-us/excel-help/power-query-formula-categoriesHA104122363.aspx?CTT=5&origin=HA104003958
http://thatmsftbiguy.com/powerqueryexcel/