1 / 12

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

cissy
Download Presentation

Power query Data Explorer

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Power query Data Explorer Harvesting xml data

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

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

  4. 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)

  5. Introducing data explorer Power Query • Released july 2013 • Excel add-in for 2010 or 2013 • Self service etl!!

  6. Power bi suite • Powerpivot – in memory embedded data models • Power view – presentation ready visualizations • Power query • All in excel !!!!

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

  8. Julie When She First Met Data Explorer…

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

  10. sources • Regular sources—most database platforms including hadoop • Files—all types • Odata • Web site tables! • Active directory!

  11. Functions • Transformation! • Merge (join on key) • Append—combine sources to new destination

  12. Resources • http://office.microsoft.com/en-us/excel-help/learn-about-data-explorer-formulas-HA104003958.aspx Formula cheat sheet • Great post by datapighttp://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

More Related