Download - Presentatie ExcelExperts
![Page 1: Presentatie ExcelExperts](https://reader035.vdocuments.us/reader035/viewer/2022070601/5889684f1a28ab44758b4c51/html5/thumbnails/1.jpg)
1
From desktop to server based Power pivot.From Excel to SQL.
Henk Vlootman, Excel MVP
![Page 2: Presentatie ExcelExperts](https://reader035.vdocuments.us/reader035/viewer/2022070601/5889684f1a28ab44758b4c51/html5/thumbnails/2.jpg)
* Flavors of today: Power pivot in Excel and SQL
* Structure of a full SQL Power pivot model.* What is on today and what is not.* Preparations at desktop level:
What not to do in your Excel Power pivot model.
* Preparations at server side: Set up your model in SQL Server Data tools.
* Import in SQL Server Visual studio.* Time to demo.* My ego page.
Program
2
![Page 3: Presentatie ExcelExperts](https://reader035.vdocuments.us/reader035/viewer/2022070601/5889684f1a28ab44758b4c51/html5/thumbnails/3.jpg)
Flavors of today: Power pivotin Excel and SQL
Excel Powerpivot
Powerview / maps
Excel 2010Excel 2013Excel 2016Excel 365
Excel
Powerpivot
Powerview / maps
SQL Server Analyses Services(SSAS)
Excel 2010Excel 2013Excel 2016Excel 365
PowerQuery
Powerview
SharePoint
ExternalData
sources
3
Excel
Powerview / maps
![Page 4: Presentatie ExcelExperts](https://reader035.vdocuments.us/reader035/viewer/2022070601/5889684f1a28ab44758b4c51/html5/thumbnails/4.jpg)
Externaldata source
s
Structure of a full SQL Power pivot model
ModelDefinitio
n.XAML
Power pivotVisual Studio
Testingdashboar
dsExcel
Production
dashboards
Excel
Corporatedashboard
sPower view / maps
Deployment serverPurposes:* development* testing
Production serverPurposes:* corporate dashboards* production dashboards
Excel 2010Excel 2013Excel 2016Excel 365SharePoint
Excel 2010Excel 2013Excel 2016Excel 365
4
![Page 5: Presentatie ExcelExperts](https://reader035.vdocuments.us/reader035/viewer/2022070601/5889684f1a28ab44758b4c51/html5/thumbnails/5.jpg)
Externaldata source
s
What is on today and what is not.
Power pivotVisual Studio
Testingdashboar
dsExcel
Production
dashboards
Excel
Corporatedashboard
sPower view / maps
Deployment serverPurposes:* development* testing
5
Excel 2010Excel 2013Excel 2016Excel 365
![Page 6: Presentatie ExcelExperts](https://reader035.vdocuments.us/reader035/viewer/2022070601/5889684f1a28ab44758b4c51/html5/thumbnails/6.jpg)
Preparations on desktop side:What not to do in your Excel Power pivot model.* Don’t bother making dashboards in you’re Excel / Power pivot model. You’re
going to lose them anyway.* Don’t use tables to add to you’re model directly. Use import Excel files in
Power pivot instead.* Be aware of Power Query and direct import to Power pivot. I couldn’t make it
work (theoretical you can, but…).* If you use text files start with making Schema.ini files to be sure that you’re
import works fine. If you change to Visual Studio, you want them too.
6
![Page 7: Presentatie ExcelExperts](https://reader035.vdocuments.us/reader035/viewer/2022070601/5889684f1a28ab44758b4c51/html5/thumbnails/7.jpg)
Preparations at server side:Set up your model in SQL Data tools* You can directly import your Excel Power pivot model(must be .xlsx, VBA
code not allowed )into SSAS server, by using the Restore functionality. You don’t like that, because there is a general lack of ability to alter the model.
* So first import the model into SQL Server Data tools (SSDT)* Import you’re Excel Power pivot model into SSDT (can be .xlsm, VBA code
allowed ) and watch how you’re databases are imported.* Create, if necessary, a workspace database and you’re good to go for you’re
Visual Studio experience.
7
![Page 8: Presentatie ExcelExperts](https://reader035.vdocuments.us/reader035/viewer/2022070601/5889684f1a28ab44758b4c51/html5/thumbnails/8.jpg)
Import in Visual Studio.
* In Visual Studio You use Import from Tabular Server and select you’re project.
* You’re ready to go… amazing.
* But there is more…* You can add more Data sources…* And you can make testing dashboards in Excel…
* For me it is totally fun and * You have made you’re Excel Power pivot model corporate.
8
![Page 9: Presentatie ExcelExperts](https://reader035.vdocuments.us/reader035/viewer/2022070601/5889684f1a28ab44758b4c51/html5/thumbnails/9.jpg)
Time to demo…
9
![Page 10: Presentatie ExcelExperts](https://reader035.vdocuments.us/reader035/viewer/2022070601/5889684f1a28ab44758b4c51/html5/thumbnails/10.jpg)
My ego pageHenk Vlootman, Excel MVP
Vlootman Consultancy B.V.
[email protected] www.vlootman.nl++31 6 54 97 44 87
LinkedIn: https://www.linkedin.com/in/vlootman
MVP profile:http://mvp.microsoft.com/en-us/mvp/Henk%20Vlootman-5000207
10