2017springitpf-excel in extreme ways
TRANSCRIPT
![Page 1: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/1.jpg)
ExcelInExtremeWaysIntegratingExcelintoyourdevelopmentcycle
![Page 2: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/2.jpg)
Introduction
• AndresFradkin• DataAnalyst• F&SIT
![Page 3: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/3.jpg)
WhatisExtremeExceltoyou?
![Page 4: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/4.jpg)
WhyshouldweuseExcel
• Everyonehasit,andmanypeoplealreadyfeelcomfortablewithit• Licensingisalreadyinplace
• NewtoolsinExcelmakeitextremelyuseful• Oneversionofthetruth
![Page 5: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/5.jpg)
Onebigreason
• YourSubjectMatterExperts(SMEs)haveusedittooneextentoranother• Typicallytwolevelsofexperience• CasualUsers• Knowbasiccontrols
• PowerUsers• Tinkerwitheverythingbutmaynothavearealunderstandingofthepowerattheirfingertips
![Page 6: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/6.jpg)
ExcelturnedExtreme
• In2010MSdevelopedPowerPivotforExcelwhichbroughtpowerfulCubestructurestoanewin-memoryTabularDataModel• Thesepowertoolshaveexisteddormantineveryversionofexcel,waitingforyoutodiscoverthem• TheseDataModelswerealsoimplementedattheserverlevel
![Page 7: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/7.jpg)
![Page 8: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/8.jpg)
NowavailableinPowerPivot
•Managesyourrelationships• Nomoreneedforvlookups
• Createportableandreproduciblecalculatedcolumns,measures,KPI’s,andHierarchies• RefreshingDatafrommultipledatasourcesandmergingthemtogether
![Page 9: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/9.jpg)
NewlanguagesforExcel
• DAX(DataAnalysisExpressions)createdforPowerPivot• similartoexcelformulalanguage
![Page 10: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/10.jpg)
![Page 11: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/11.jpg)
Newlanguagescontinued…
• Awholenewadd-inwascreatedforETL(ExtractTransform&Loading)data calledPowerQuery• “M”languagecreatedtomanageit
![Page 12: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/12.jpg)
![Page 13: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/13.jpg)
Excelasdevelopment
•WhenaTabularDataModeliscreatedinExcelPowerPivot,itisidenticaltoaDataModeldevelopedontheserver• NowitcanbeimportedwholesaleorberecreatedintheserverenvironmentstartingwithSQLServer2014• Allauthorizeduserswillbeabletoaccessthesamedataandviews
![Page 14: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/14.jpg)
![Page 15: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/15.jpg)
![Page 16: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/16.jpg)
TheServerDataModel
• DataintheDataModelontheserverisautomaticallyrefreshable• TheserverDataModelcanbeaccessedfrommanyotherprograms• Excel• Tableau• PowerBI• DataZen
![Page 17: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/17.jpg)
AnalysisandDataseparate
• YourwholedatasetnolongerneedstoliveinyourExcelWorkbooknorshouldit• Allofyourmeasures,KPIs,Hierarchies,andcalculatedcolumnscanbemanagedinsidetheserverdatamodel• ThinExcelworkbookscanconnectyourModelandgiveyouanewwaytoexploredata
![Page 18: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/18.jpg)
![Page 19: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/19.jpg)
ExcelandTableauDemo
![Page 20: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/20.jpg)
NextSteps
• SQLServer2016• ManytoManyrelationshipsbuiltin• SimplerScriptinginSSMS(nomoreXMLA)• IntegratedRstoredprocedures
• SQLserver2017(versionNext)• PowerQuery built-in• PowerBIincludedwithSSRSonpremises
![Page 21: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/21.jpg)
GettingStarted
• EnablePowerPivot(Downloadfor2010)• EnablePowerQuery (Downloadfor2013+2010)• DownloadODACcomponentsforOracle• DownloadDAXstudio(optionalbutveryuseful)• CheckoutDAXformatterandDAXPatternswebsites
![Page 22: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/22.jpg)
Questions?
![Page 23: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/23.jpg)
References
• http://www.daxpatterns.com/• http://www.daxformatter.com/• https://powerpivotpro.com/?nabm=0• http://www.sqlbi.com/• http://excelhero.com/blog/• https://sites.google.com/site/e90e50charts/
![Page 24: 2017SpringITPF-Excel In Extreme Ways](https://reader031.vdocuments.us/reader031/viewer/2022012018/61686930d394e9041f6f67cf/html5/thumbnails/24.jpg)
• ThankYou!• Enjoytherestoftheconference!