Excel 2.0marcel westra
Agenda
• Introduction
• The new Excel
• Three steps to create insights
• Detailed explanation of the new Excel functionality
• What’s next
• Questions
Official• Ir. Ing. M. R. Westra
• Sr. TSP Business Intelligence
Introduction
Real14 years at Microsoft
Advisor for data analytics projects for
Philips, ING, Rabo, RDW, Achmea, Heineken and more
Microsoft is changing
from Windows and Office sales machine to Subscription and Usage
• Adoption of non Microsoft technology
• Participation in open Source (Hadoop)
• Cloud First Mobile first
The mission of Microsoft
Azure
Azure for research
The new Excel (short demo)
Power map
http://www.youtube.com/results?search_query=power+map
raw data visualizationinformation
Data
martData
mart
clean
sin
g d
e-d
up
lica
tio
n
Data
sci
en
ces
data sets
get and
shape
store and
calculate
show
find and share
raw data visualizationinformation
Data
martData
mart
clean
sin
g d
e-d
up
lica
tio
n
Data
sci
en
ces
data sets
get and
shape
store and
calculate
show
Power view
Power mapPower pivotPower query
find and share
Power Query
How it works
Step 1: Get Data
• Define Connection to a data source• Database
• Internet page
• File
• Hadoop
• Odata (www.odata.org)
Step 2: Shape Data
• Remove Columns
• Filter
• De-Duplicate
• Replace Values
• Split Columns
• Change type
• Unpivot
• Merge
• Standard deviation \ square and power…
Step 3: Load Data
• Initial load
• Append
• Reload
demo
Get Shape Data
• Excel look and feel
• What you see is what you get
• Rich set of standard data functions
• Generate reusable scripts
Power Pivot
Power Pivot Engine
• (unlimited)In-Memory Database in Excel on desktop
• Column compression
• Data Analysis Expressions (DAX)
desktop
SQL ServerAnalysis Service
Server
Power Pivot
• Build relation on the loaded data
• Add Calculations (and KPIs)
Step 4 build relations
Step 5: add DAX Calculations
DAX Syntax Specification for PowerPivot:http://msdn.microsoft.com/en-us/library/ee634217.aspx
DAX function reference:http://msdn.microsoft.com/en-us/library/ee634396.aspx
demo
Visualizations
New visualizations in Excel
• (Time) Slicers
• Power View
• Power Map
• New things will come
Pivot Table
Power View
Power Map
demo
raw data visualizationinformation
Data
martData
mart
clean
sin
g d
e-d
up
lica
tio
n
Data
sci
en
ces
data sets
get and
shape
store and
calculate
show
Power view
Power mapPower pivotPower query
find and share
Share
• Power Query
• Power Pivot
• Power View
raw data visualizationinformation
Data
martData
mart
clean
sin
g d
e-d
up
lica
tio
n
Data
sci
en
ces
data sets
get and
shape
store and
calculate
show
Power view
Power mapPower pivotPower query
find and share
Q&A, natural language
Sketch Insight
Sand Dance
http://research.microsoft.com/en-us/projects/sanddance/
References
Getting started with power query and power map
Connect Excel to Hadoop with Power Query
Getting Started with Power Query - Part 1
Other links
http://www.microsoft.com/en-us/powerbi/default.aspx
http://www.powerpivotblog.nl/
http://www.bol.com/nl/p/praktijkboek-powerpivot-in-excel/9200000010047027/
Bing (or Google )