POWER QUERY DATA EXPLORERHARVESTING XML DATA

JULIE SMITH AND ROB VOLK

• DATA SERVICES CONSULTANT AT INNOVATIVE ARCHITECTS IN ATLANTA, GA• ONE HALF OF THE DATACHIX WITH AUDREY HAMMONDS• SQL SERVER MVP• ROB VOLK – NEEDS NO INTRODUCTION, SINCE HE’S NOT GONNA DO MUCH

• EXCEPT COMPLAIN• AND XML STUFF

• WHICH HE’S COME TO FIND OUT IS A DEATH SENTENCE FOR SESSION SUBMISSIONS

JULIE SMITH AND ROB VOLK

• ROB VOLK IS A DATABASE ADMINISTRATOR FROM THE ATLANTA, GEORGIA AREA, HAVING WORKED IN THE WIRELESS TELECOM, UTILITY BILLING, AND CREDIT CARD PROCESSING INDUSTRIES FOR THE PAST 10 YEARS.  HE STARTED USING SQL SERVER WITH VERSION 6.5 AND IS VERY GLAD TO HAVE UPGRADED SINCE THEN. HE HAS PRESENTED AT SEVERAL SQL SATURDAYS AND USER GROUPS. IN HIS SPARE TIME HE ACCOMPLISHES MORE THAN HE DOES ON THE JOB, JUST DON'T TELL HIS BOSS.

TONIGHT’S AGENDA

• STORY OF HOW ANNOYED ROB WAS WITH PLANNING SQL SATURDAYS• HAS TO BE A BETTER WAY

• ONLINE PLANNING TOOLS—ASANA• 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 DATA EXPLORER POWER QUERY

• RELEASED JULY 2013• EXCEL ADD-IN FOR 2010 OR 2013• SELF SERVICE ETL!!

POWER BI SUITE

• POWERPIVOT – IN MEMORY EMBEDDED DATA MODELS• POWER VIEW – PRESENTATION READY VISUALIZATIONS• POWER QUERY

• ALL IN EXCEL !!!!

HISTORY

• M LANGUAGE FOR BUSINESS ANALYSTS—XAML• 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=235475&CLCID=0X409 TABLE. PAGE 122

JULIE WHEN SHE FIRST MET DATA EXPLORER…

IT CAN HARVEST THE FOLLOWING:• “OTHER”

• SHAREPOINT LIST, • WINDOWS AZURE MARKETPLACE, • ANY HADOOP FILE (HDFS), • HDINSIGHT, • ACTIVE DIRECTORY, • FACEBOOK.

• RDBMS: SQL SERVER, AZURE SQL SERVER, ACCESS (OOPS), ORACLE, DB2, MYSQL, POSTGRESS, TERADATA

• FILE: • EXCEL, CSV, TXT, 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

RESOURCES• HTTP://

OFFICE.MICROSOFT.COM/EN-US/EXCEL-HELP/LEARN-ABOUT-DATA-EXPLORER-FORMULAS-HA104003958.ASPX FORMULA CHEAT SHEET

• GREAT POST BY DATAPIG HTTP://DATAPIGTECHNOLOGIES.COM/BLOG/INDEX.PHP/COOL-THINGS-YOU-CAN-DO-WITH-DATA-EXPLORER/

• ON M LANGUAGE: HTTP://BLOGS.MSDN.COM/B/MODELCITIZEN/ARCHIVE/2010/09/22/UPDATE-ON-SQL-SERVER-MODELING-CTP-REPOSITORY-MODELING-SERVICES-QUOT-QUADRANT-QUOT-AND-QUOT-M-QUOT.ASPX

• M LANGUAGE SPECS FROM MICROSOFT: HTTP://MSDN.MICROSOFT.COM/EN-US/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