Transcript Data Explorer

POWER QUERY
DATA EXPLORER
HARVESTING 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-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