Microsoft Business Intelligence Big Data Data || Sall Sall Data Data || !ll !ll D Data ata
P Peter eter Myers My ers Bitwise Solutions Pty Ltd
v6 11Mar2014
Presenter Introduction Peter Myers BI Expert, Bitwise Solutions BBus, SQL Server Ser ver MCSE, MCT, MCT, SQL Ser S erver ver MVP (since 2007 Experience! in !esi"nin", !evelopin" #n! $#int#inin" Microso%t Microso%t !#t#&#se #n! #pplic#tion solutions, since '7 )ocuses on e!uc#tion #n! $entorin" B#se! in Mel&ourne, *ustr#li# peter+$ers-&itwisesolutions+co$+#u .ttp/www+lin1e!in+co$inpeters$ .ttp/www +lin1e!in+co$inpeters$ers ers
Presenter Introduction Peter Myers BI Expert, Bitwise Solutions BBus, SQL Server Ser ver MCSE, MCT, MCT, SQL Ser S erver ver MVP (since 2007 Experience! in !esi"nin", !evelopin" #n! $#int#inin" Microso%t Microso%t !#t#&#se #n! #pplic#tion solutions, since '7 )ocuses on e!uc#tion #n! $entorin" B#se! in Mel&ourne, *ustr#li# peter+$ers-&itwisesolutions+co$+#u .ttp/www+lin1e!in+co$inpeters$ .ttp/www +lin1e!in+co$inpeters$ers ers
Seinar !i •
•
•
•
Provi!e IT !ecision $#1ers, IT pro%ession#ls #n! !evelopers wit. t.e in%or$#tion t.e nee! to co$$ence pro!ucin" st#te3 o%3t.e3#rt, inte"r#te! Business Intelli"ence (BI solutions & usin" Microso%t BI Intro!uce t.e next "ener#tion o% Microso%t sel%3service #n#ltics wit. Power BI 4escri&e .ow SQL Server c#n !eliver BI solutions Intro!uce &i" !#t# #n! !escri&e .ow to i$ple$ent &i" !#t# solutions
Seinar "utline •
•
•
•
•
•
•
Intro!uction 4e%inin" Business Intelli"ence Intro!ucin" Microso%t Business Intelli"ence Sel%3service *n#ltics wit. Power BI %or 5%%ice 68 En!3to3en! Corpor#te BI wit. SQL Server Bi" 4#t# Solutions wit. 9in!ows *:ure ;4Insi".t Conclusion, Q<*
Logistics •
•
Ple#se silence $o&ile p.ones Se$in#r .ours –
•
•
Me#l &re#1s
=estroo$s )ee!&#c1
Presentation Download •
T.is present#tion c#n &e !ownlo#!e! in P4) %ro$/ –
.ttp/www+&itwisesolutions+co$+#u!ownlo#!s20'>02 Microso%tBI+p!%
Defining Business Intelligence •
•
To!#, BI is "ener#ll # well3un!erstoo! ter$ *ccor!in" to ?#rtner, BI is !e%ine! #s/ * &ro#! c#te"or o% #pplic#tions #n! tec.nolo"ies %or "#t.erin", storin", #n#l:in", s.#rin" #n! provi!in" #ccess to !#t# to .elp enterprise users $#1e &etter &usiness !ecisions
•
•
5r put $ore si$pl@ It tr#ns%or$s !#t# into 1nowle!"e It enco$p#sses # &ro#! spectru$ o% tec.nolo"ies #n! usu#ll reAuires s1ille! pro%ession#ls to i$ple$ent #n! $#n#"e
Defining Business Intelligence •
BI is use! & !ecision $#1ers to/ –
–
–
•
n!erst#n! t.e .e#lt. o% t.e or"#ni:#tion Coll#&or#te on # s.#re! view o% !#t#, &usiness #n! present#tion lo"ic =e!uce t.e ti$e to !ecision
Its "o#l is o%ten to/ –
–
I$p#ct t.e &otto$ line & $e#surin" speci%ic oper#tions En.#nce co$petitive #!v#nt#"e BI is no lon"er # luxur #%%or!e! & # l#r"er co$p#nies it is now consi!ere! #n essenti#l p#rt o% t.e IT port%olio
Defining Business Intelligence •
BI i$ple$ent#tions c#n &e !escri&e! in ter$s o%/ –
–
–
Corpor#te BI Sel%3service BI (SSBI M#n#"e! BI
Defining Business Intelligence #or$orate BI •
•
•
•
IT Pros !eliver solutions to inte"r#te #n! tr#ns%or$ &usiness oper#tion#l !#t# to $#1e it use%ul #n! relev#nt to &usiness users T.e co$$onl !esi"n, i$ple$ent #n! $#int#in enterprise !#t# w#re.ouses (E49, !#t# $o!els #n! inte"r#te! reportin" #n! #n#ltics T.ese solutions reAuire si"ni%ic#nt ti$e, expertise #n! $one It is usu#ll un!erstoo! t.#t t.ese solutions !o not #!!ress #ll &usiness nee!s
Defining Business Intelligence #or$orate BI
3: 1: Data 2: 4: ": #: %: ': The $taging Manual Clients data sources warehouse need may use cleansing areas warehouse (arious access access can may isbe may periodically simpliy data tools to mirrored/replicated mirrored/rep manages data be sources tore&uired &uer &uery thedata populated data directly y licated the to or warehouse cleanse data analy!ing to rom warehouse reduce dirty data population and contention data sources repor ting reporting
Data %are&ouse
Data Sources
Data Marts Staging !rea
Client Access
Manual #leansing
#lient !ccess
Defining Business Intelligence #or$orate BI '#ontinued( •
To!#, o!#, IT $# !eliver #n! support suppor t # $#n#"e!, sel%3service sel%3ser vice BI coll#&or#tion environ$ent to e$power #n#lsts to cre#te custo$ BI solutions
Defining Business Intelligence Self)service BI •
T.e "o#l o% SSBI is to e$power #n#lsts so t.#t t.e c#n !esi"n, custo$i:e #n! $#int#in t.eir own BI solutions –
–
9it.out SSBI, t.e #re ot.erwise %orce! to rel entirel upon !#t# #n! resources $#!e #v#il#&le & IT In re#lit, t.ou"., it is pro&#&l .#ppenin" in #n un$#n#"e! w#, w.et.er IT #llow it or not
Defining Business Intelligence Self)service BI '#ontinued( •
SSBI is not # co$plete solution nor # repl#ce$ent %or corpor#te BI –
–
–
–
•
IT Pros still nee! to !eliver corpor#te BI So, SSBI is # co$&in#tion o% corpor#te BI #n! extensions to e$power #n#lsts to $ore %ull exploit it It is not #&out #n#lsts wor1in" in isol#tion =#t.er, it is #&out #n#lsts wor1in" $ore closel wit. IT Pros to s.#re so$e o% t.e BI wor1lo#!
se! #n! $#n#"e! #ppropri#tel, it usu#ll proves itsel% to &e inv#lu#&le to t.e or"#ni:#tion
Defining Business Intelligence Self)service BI* !dvantages •
•
•
T.e or"#ni:#tion &eco$es $ore #"ile, #n! &ene%its %ro$ t.e #&ilit to "#t.er #n! #n#l:e !#t# $ore Auic1l IT Pros c#n re$#ine! %ocuse! on corpor#te BI *n#lsts c#n/ –
–
–
–
–
*ccess #n! wor1 wit. corpor#te !#t# wit.out reli#nce on IT Cre#te #! .oc solutions Cre#te person#l #n! te#$3centric solutions B#se !ecisions on !#t# inste#! o% intuition M#1e %#ster #n! $ore #ccur#te !ecisions
Defining Business Intelligence Self)service BI* Disadvantages •
SSBI c#n &e expensive #n! ti$e consu$in" to i$ple$ent #n! $#int#in –
–
•
*n#lsts will reAuire tr#inin", speci#li:e! tools #n! !#t# #ccess Met#!#t# !iction#ries s.oul! &e $#int#ine! to provi!e consistent #n! reli#&le #ccess to !#t#
SSBI Aueries c#n i$p#ct on corpor#te sste$ per%or$#nce #n! #v#il#&ilit
Defining Business Intelligence Self)service BI* Disadvantages '#ontinued( •
SSBI solutions c#n result in !uplic#tion o% e%%ort, !#t# #n! lo"ic –
•
Co$$onl re%erre! to #s D$ultiple versions o% t.e trut. or Dspre#!$#rt
SSBI solutions $# not &e 1nown to IT, !ocu$ente!, reviewe!, #pprove!, &#c1e! up, or #!eAu#tel secure! –
Fnowle!"e o%, #n! expertise to $#int#in, SSBI solutions coul! &e lost w.en #n #n#lst le#ves t.e or"#ni:#tion
Defining Business Intelligence Managed BI •
•
M#n#"e! BI is #&out $utu#l &ene%it %or IT Pros #n! #n#lsts, #n! IT "overn#nce T.e "o#l o% cle#r IT "overn#nce is ensure t.#t t.e invest$ent in IT "ener#tes &usiness v#lue #n! $iti"#tes ris1
Defining Business Intelligence Managed BI '#ontinued( •
)or SSBI, t.is $e#ns ensurin" responsi&le BI & $#n#"in" wit. oversi".t to/ –
–
–
–
–
–
=eview, #pprove #n! #u!it solutions Ensure trustwort. !#t# is !elivere! in # co$pli#nt, responsive #n! secure w# Ensure !#t#, $et#!#t# #n! lo"ic re$#in #v#il#&le #n! current, #n! is &#c1e! up ;#ve visi&ilit into .ow !#t# is use! t.rou".out t.e or"#ni:#tion Ensure #ppropri#te #ccess per$issions #re en%orce! Incorpor#te or up"r#!e SSBI solutions into corpor#te BI solutions, i% #ppropri#te
Defining Business Intelligence Suary •
IT Pros i$ple$ent corpor#te BI, #n! solutions !o not usu#ll !eliver #ll user in%or$#tion reAuire$ents –
–
•
•
IT Pros c#n re$#in %ocuse! on !eliverin" enterprise reAuire$ents, #n! c#n !eliver #n! support # $#n#"e! BI environ$ent * p#rtners.ip &etween IT #n! #n#lsts encour#"es #n! supports SSBI
SSBI is not # repl#ce$ent %or, &ut #n extension o%, corpor#te BI * "oo! "overn#nce process will incre#se t.e #!option o% BI #n! $iti"#te ris1
Introducing Microsoft BI Strategy and +ision •
To i$prove or"#ni:#tions & provi!in" &usiness insi".ts to #ll e$ploees, le#!in" to &etter, %#ster, $ore relev#nt !ecisions –
–
–
–
Microso%t .#s # lon"3ter$ co$$it$ent to !eliverin" # co$plete #n! inte"r#te! BI o%%erin" SQL Server .#s le! innov#tion in t.e BI sp#ce %or $ore t.#n # !ec#!e T.ere is wi!espre#! !eliver o% intelli"ence t.rou". 5%%ice T.e pl#t%or$s #re enterprise3"r#!e #n! #%%or!#&le
Introducing Microsoft BI .usiness +ser ,-perience
.usiness Collaboration Platorm
)normation Platorm
*amiliar +ser ,-perience Sel%3Service *ccess #n! Insi".t 4#t# Explor#tion #n! *n#lsis Pre!ictive *n#lsis 4#t# Visu#li:#tion Contextu#l Visu#li:#tion )ntegrated Content and Collaboration T.in Clients 4#s.&o#r!s BI Se#rc. Content M#n#"e$ent
)normation Platorm 4#t# 9#re.ousin" Inte"r#tion Services M#ster 4#t# 4#t# Qu#lit *n#lsis Services 4#t# Mo!elin" #n! 4#t# Minin" =eportin" Services •
Introducing Microsoft BI ,-cel •
5ver t.e p#st !ec#!e, t.e core spre#!s.eet c#p#&ilities .#ve &een en.#nce! to en#&le #n#lsts to #n#l:e, co$$unic#te, #n! $#n#"e in%or$#tion –
*!!3ins provi!e ric. #n! inte"r#te! BI c#p#&ilities/ •
•
•
•
Power Quer G !#t# #cAuisition #n! prep#r#tion Power Pivot G !#t# $o!elin" Power View G present#tion3re#!, #n! inter#ctive reports Power M#p G 64 "eosp#ti#l visu#li:#tion
Introducing Microsoft BI ,-cel* #o$lete and Powerful SSBI .ool *ccess
•
•
Power View Power M#p
S.#re
Cle#n
Visu#li:e
M#s.3up
Explore
/esources •
Microso%t Business Intelli"ence we& site –
•
Microso%t 5%%ice Excel we& site –
–
•
.ttp/o%%ice+$icroso%t+co$excel Tr # %ree one $ont. tri#l to!#
Microso%t Power BI %or 5%%ice 68 we& site –
•
.ttp/www+$icroso%t+co$&i
.ttp/www+power&i+co$
MS4H Blo"/ Power BI –
.ttp/&lo"s+$s!n+co$&power&i
SL Server
Big Data
Module "utline •
4#t# Mo!elin" –
•
4#t# *cAuisition –
•
Power Quer
4#t# Visu#li:#tion –
–
–
•
Power Pivot
Power Pivot =eportin" Power View Power M#p
S.#rin" #n! Coll#&or#tion
Data Modeling wit& Power Pivot
Data !cuisition wit& Power uery
Power Pivot /e$orting
Data +isualiation wit& Power +iew
Data +isualiation wit& Power Ma$
Power BI Så and #olla3oration
Data Modeling wit& Power Pivot •
Excel 20'6 inclu!es t.e wor1&oo1 !#t# $o!el to $#s.3up #n! #n#l:e !#t# –
–
–
•
*llows i$portin" t#&les o% !#t# %ro$ !i%%erent !#t# sources Cre#tin" rel#tions.ips &etween t#&les Cre#tin" si$ple c#lcul#te! %iel!s (#""re"#tion o% sin"le colu$ns
T.e wor1&oo1 !#t# $o!el is !elivere! wit. # client3si!e version o% *n#lsis Services, 1nown #s t.e xVelocit In3Me$or *n#ltics En"ine
Data Modeling wit& Power Pivot •
T.e Power Pivot #!!3in %or Excel provi!es # ri&&on t#& #n! # !evelop$ent win!ow to cre#te $ore sop.istic#te! !#t# $o!els –
–
•
5%%ice Pro%ession#l Plus #n! 5%%ice 68 ProPlus e!itions onl T.e #!!3in is !is#&le! & !e%#ult
4#t# c#n &e source! %ro$/ –
–
–
–
Cop #n! p#ste Extern#l !#t# sources Excel lin1e! t#&les Power Quer
Data Modeling wit& Power Pivot Delivera3les •
•
*n intuitive Auer3#&le resource t.#t serves &usiness user experiences Inte"r#tes !#t# %ro$ # v#riet o% !#t# sources, inclu!in"/ –
–
•
Tr#!ition#l !#t# source, suc. #s rel#tion#l !#t#&#ses Hon3tr#!ition#l sources, suc. #s !#t# %ee!s, text %iles #n! spre#!s.eets
4elivers #cceler#te! #ccess to potenti#l extre$el l#r"e !#t# volu$es G well &eon! t.e row li$its o% #n Excel wor1s.eet
Data Modeling wit& Power Pivot Delivera3les '#ontinued( •
I$proves #ccess to, #n! us#&ilit o%, t.e !#t# $o!el –
•
•
Enc#psul#tes &usiness rules wit. c#lcul#tions #n! 1e per%or$#nce in!ic#tors (FPIs Supports # ric. v#riet o% client tools, inclu!in" t.ose to !eliver/ –
•
)rien!l n#$es, .ier#rc.ies, etc+
Inter#ctivit, !#t# visu#li:#tion, reports, scorec#r!s, !#s.&o#r!s, #n! custo$ experiences
=epresents # Dsin"le version o% t.e trut.
Data Modeling wit& Power Pivot Develo$ent Met&odology '+ 2+ 6+ >+ 8+ + 7+ +
Cre#te # wor1&oo1 #n! open t.e Power Pivot 9in!ow I$port !#t# to cre#te t#&les 4e%ine rel#tions.ips &etween t#&les En.#nce t.e !#t# $o!el !esi"n wit. .ier#rc.ies, c#lcul#te! colu$ns, c#lcul#te! %iel!s #n! FPIs Prep#re t.e !#t# $o!el %or Power View Pro!uce reports &#se! on t.e !#t# $o!el 5ption#l Pu&lis. t.e wor1&oo1 to S.#rePointPower BI Con%i"ure #uto$#tic !#t# re%res.
Data Modeling wit& Power Pivot "$$ortunities •
•
•
Pro!uce # Dsin"le version o% t.e trut. !#t# $o!el Store volu$es o% !#t# in excess o% wor1s.eet li$its (',0>,87 rows #n! wit. .i". co$pression ('0x on #ver#"e E#sil #n! e%%icientl inte"r#te !#t# %ro$ !i%%erent !#t# sources/ –
–
•
•
Corpor#te, loc#l or extern#l Eli$in#te t.e use o% t.e VL55FP %unction
Source !#t# %ro$ !i%%erent !#t# %or$#ts G rel#tion#l, $ulti!i$ension#l, !#t# %ee!, %iles E#sil re%res. !#t#, w.en reAuire!
Data Modeling wit& Power Pivot "$$ortunities '#ontinued( •
•
En.#nce t.e !#t# $o!el wit. .ier#rc.ies Enc#psul#te &usiness rules wit. c#lcul#tions –
–
•
Pro!uce !i%%icult Excel lo"ic in t.e !#t# $o!el wit. 4*J Pro!uce ti$e intelli"ence %or$ul#
En#&le consistent per%or$#nce $onitorin" wit. FPIs
Data Modeling wit& Power Pivot /esources •
Power Pivot we& site –
•
Boo1/ DMicroso%t Excel 20'6/ Buil!in" 4#t# Mo!els wit. PowerPivot –
–
•
.ttp/www+$icroso%t+co$en3us&ipowerpivot+#spx Pu&lis.er/ Microso%t Press *ut.ors/ *l&erto )err#ri #n! M#rco =usso
4*J =esource Center –
.ttp/soci#l+tec.net+$icroso%t+co$wi1icontents#rticles'0+!#x3resource3 center+#spx
Data Modeling wit& Power Pivot /esources '#ontinued( •
9.itep#per/ 4*J in t.e BI T#&ul#r Mo!el –
–
•
Inclu!es # s#$ple Excel wor1&oo1 .ttp/www+$icroso%t+co$!ownlo#!en!et#ils+#spxKi!2872
Boo1/ D4*J )or$ul#s %or PowerPivot/ T.e Excel Pros ?ui!e to M#sterin" 4*J –
–
Pu&lis.er/ ;ol M#croN Boo1s *ut.or/ =o& Collie
Data Modeling wit& Power Pivot
Data !cuisition wit& Power uery
Power Pivot /e$orting
Data +isualiation wit& Power +iew
Data +isualiation wit& Power Ma$
Power BI Så and #olla3oration
Data !cuisition wit& Power uery •
•
Power Quer is # new #!!3in %or Excel to !iscover, tr#ns%or$ #n! consu$e !#t# *llows !e%inin" Aueries w.ic. run # seAuence o% steps to i$port #n! res.#pe !#t# %ro$ one or $ore !#t# sources –
Quer steps #re !e%ine! & usin" Power Quer )or$ul# L#n"u#"e •
•
Si$ple Auer step lo"ic !oes not reAuire writin" %or$ul#s *!v#nce! Auer step lo"ic c#n &e written to lever#"e t.e %ull power o% t.e l#n"u#"e
Data !cuisition wit& Power uery •
•
•
Quer results c#n &e lo#!e! into #n Excel t#&le or t.e wor1&oo1 !#t# $o!el Power Queries c#n &e pu&lis.e! to t.e Power BI 4#t# C#t#lo"ue (clou! Supports # l#r"e collection o% !#t# source tpes/ –
–
–
–
9e& (;TML t#&le )ile 4#t#&#se 5t.er sources
Data !cuisition wit& Power uery Data Source .y$es
Data !cuisition wit& Power uery "$$ortunities •
=epl#ce le"#c Excel !#t# retriev#l lo"ic wit. Power Quer Aueries –
–
–
–
–
•
Lever#"e new !#t# source tpes/ –
•
Cre#te re%res.#&le Aueries to source, %ilter #n! s.#pe !#t# Eli$in#te t.e reAuire$ent to $#nu#ll prep#re #n! lo#! !#t# Eli$in#te co$plex tr#ns%or$#tion#l lo"ic in wor1s.eets #n! $#cros Inte"r#te Auer results %ro$ !i%%erent !#t# sources Lo#! !#t# !irectl into wor1&oo1 !#t# $o!els ;4Insi".t, *:ure stor#"e, *ctive 4irector, Exc.#n"e, )#ce&oo1, etc+
Pu&lis. li&r#ries o% Aueries %or !iscover #n! reuse
Data !cuisition wit& Power uery /esources •
Microso%t 4ownlo#! Center –
–
•
Tec.E! Hort. *$eric# 20'6 –
–
•
Microso%t Power Quer %or Excel .ttp/www+$icroso%t+co$en3us!ownlo#!!et#ils+#spxKi!667 4BI3B228/ DMicroso%t D4#t# Explorer %or Excel/ 4iscover, Co$&ine, #n! =e%ine our 4#t# & )#is#l Mo.#$oo! .ttp/c.#nnel+$s!n+co$EventsTec.E!Hort.*$eric#20'64BI3B228
Power Quer %or Excel )or$ul# L#n"u#"e Speci%ic#tion –
.ttp/"o+$icroso%t+co$%wlin1KLin1I462066
Data !cuisition wit& Power uery /esources '#ontinued( •
Power BI Blo" –
–
Blo" entr 06 M#r 20'>/ DMicroso%t *!!s Connectivit %or S*P Business5&ects to Power BI %or 5%%ice 68 .ttp/&lo"s+$s!n+co$&power&i#rc.ive20'>0606$icroso%t3#!!s3 connectivit3%or3s#p3&usinesso&ects3to3power3&i3%or3o%%ice368+#spx
Data Modeling wit& Power Pivot
Data !cuisition wit& Power uery
Power Pivot /e$orting
Data +isualiation wit& Power +iew
Data +isualiation wit& Power Ma$
Power BI Så and #olla3oration
Power Pivot /e$orting •
Excel reportin" &#se! on t.e Power Pivot !#t# $o!el c#n &e #c.ieve! & usin" t.e DT.is9or1&oo14#t#Mo!el wor1&oo1 connection/ –
–
–
–
–
PivotT#&les #n! PivotC.#rts CBE %unctions *pps %or 5%%ice Power View Covere! in l#ter topics Power M#p
Power Pivot /e$orting Pivot.a3les •
T.e PivotT#&le )iel!s exposes t.e resources o% t.e !#t# $o!el/ –
•
)our !rop :ones #re use! to con%i"ure t.e l#out o% t.e PivotT#&le –
–
–
•
C#lcul#te! %iel!s, FPIs, #n! .ier#rc.ies G "roupe! & t#&le
)ilters Colu$ns #n! =ows V#lues G c#lcul#te! %iel!s onl
Slicers #n! ti$elines c#n &e #!!e! to en#&le inter#ctive %ilterin"
Power Pivot /e$orting Pivot#&arts •
•
•
•
PivotC.#rts c#n &e &#se! on # PivotT#&le C#n &e pl#ce! on t.e s#$e or sep#r#te wor1s.eet #s t.e PivotT#&le Con%i"ur#tion is si$il#r to t.e PivotT#&le Two !rop :ones c.#n"e n#$e #n! &e.#vior/ –
–
Colu$n L#&els &eco$es Le"en! )iel!s =ow L#&els &eco$es *xis )iel!s
Power Pivot /e$orting #B, 5unctions •
Excel inclu!es seven CBE %unctions t.#t c#n retrieve !#t# $o!el $e$&ers #n! v#lues/ –
–
–
–
–
–
–
C+.,M,M., returns # $e$&er %ro$ # cu&e !i$ension C+.,0+, returns # v#lue %ro$ t.e cu&e, #n! c#n &e %iltere! usin" report %ilters #n! slicers C+.,$,T returns # set o% $e$&ers C+.,$,TC+T returns t.e count o% $e$&ers in # set C+.,5,DM,M., returns t.e nt. $e$&er in # set C+.,M,M.,PP,T6 returns # $e$&er propert C+.,5P)M,M., returns # FPI propert
Power Pivot /e$orting !$$s for "ffice •
•
•
•
•
•
*pps %or 5%%ice #re new in 5%%ice 20'6 *llow visu#li:in" !#t# %ro$ Excel t#&les or wor1s.eet r#n"es T.e c#rr # li".t %ootprint #n! use we& st#n!#r!s3&#se! tec.nolo"ies suc. #s ;TML8, O#v#Script, CSS6, JML, #n! =EST *PI C#n &e e$&e! into 5%%ice !ocu$ents #n! use # &rowser control in # secure #pp runti$e environ$ent *pps #re !iscover#&le #n! #re !ownlo#!e! %ro$ t.e 5%%ice Store So$e #pps #re %ree ☺
Power Pivot /e$orting •
I% t.e wor1&oo1 will &e ren!ere! & Excel Services, onl use Excel client %e#tures t.#t #re supporte! –
–
4oes not support $#cro3en#&le! wor1&oo1s Supporte! %e#tures/ • • • • •
C.#rts E$&e!!e! i$#"es Con!ition#l %or$#ttin" Slicers Ti$elines
• • • •
Sp#r1lines ;perlin1s 5utlinin" *pps %or 5%%ice
Power Pivot /e$orting "$$ortunities •
•
•
•
Pu&lis. wor1&oo1s to s.#re wor1&oo1 !#t# $o!els #n! #ssoci#te! reports Pro!uce inter#ctive report l#outs, inclu!in" !#s.&o#r!s =e%#ctor le"#c wor1&oo1 report l#outs wit. CBE %unctions to sur%#ce Power Pivot !#t# Exten! !#t# visu#li:#tion c#p#&ilities wit. *pps %or 5%%ice
Power Pivot /e$orting /esources •
Excel Cu&e )unctions wi1i site –
•
Boo1/ DMicroso%t SQL Server 200 M4J Step & Step –
–
•
Pu&lis.er/ Microso%t Press *ut.or/ C+ =#n Cl#
5verview o% *pps %or 5%%ice –
•
.ttp/www+excelcu&e%unctions+co$
.ttp/$s!n+$icroso%t+co$en3usli&r#ro%%ice22002+#spx
Boo1/ DExcel 4#s.&o#r!s #n! =eports –
–
Pu&lis.er/ 9ile *ut.or/ Mic.#el *lex#n!er
Data Modeling wit& Power Pivot
Data !cuisition wit& Power uery
Power Pivot /e$orting
Data +isualiation wit& Power +iew
Data +isualiation wit& Power Ma$
Power BI Så and #olla3oration
Data +isualiation wit& Power +iew •
•
•
Power View is # report #ut.orin" experience in Excel =eAuires # t#&ul#r !#t# $o!el, inclu!in" # Power Pivot !#t# $o!el =eport s.eets c#n &e #!!e! to t.e wor1&oo1 #n! con%i"ure to pro!uce inter#ctive !#t# explor#tion, visu#li:#tion, #n! present#tion experiences –
–
–
;i".l visu#l !esi"n experience =ic. $et#3!riven inter#ctivit Present#tion3re#! #t #ll ti$es
Data +isualiation wit& Power +iew ,-a$le
Data +isualiation wit& Power +iew Data Model Pre$aration •
T.e !#t# $o!el c#n &e prep#re! to %ull exploit t.e Power View experience &/ –
–
–
–
–
•
4e%inin" %rien!l n#$es 4e%inin" snon$s (%or Power Q<* *!!in" !escriptions Settin" colu$n #n! c#lcul#te! %iel! !#t# %or$#ts Settin" colu$n c#te"ori:#tions
I$#"es c#n &e en#&le! &/ –
–
Storin" i$#"e !#t#, or =e%erencin" extern#l i$#"es (not supporte! in Power BI sites
Data +isualiation wit& Power +iew Data Model Pre$aration '#ontinued( •
T.e *!v#nce! ri&&on t#& #llows/ –
–
Con%i"urin" t#&le !e%#ult %iel! sets Con%i"urin" t#&le &e.#vior/ •
•
•
•
=ow i!enti%ier (li1e pri$#r 1e Feep uniAue rows 4e%#ult l#&el #n! i$#"e
D*uto$#tic c#lcul#te! %iel! &e.#vior c#n &e en#&le! –
–
*pplies to non3.i!!en nu$eric colu$ns 4e%#ult #""re"#tion c#n &e set wit. t.e Su$$#ri:e B propert
Data +isualiation wit& Power +iew /e$ort Design •
Supporte! visu#li:#tion tpes/ –
–
–
–
–
–
•
T#&les (!e%#ult M#trices C.#rts (&#r, colu$n, line, pie, #n! sc#tter C#r!s Tiles M#ps (reAuires internet connectivit
)ilterin" c#n &e #c.ieve! in t.e %ilter p#ne (&#c1"roun!, or %or e#c. visu#li:#tion –
Slicers #n! tiles c#n #lso &e use! to %ilter
Data +isualiation wit& Power +iew "$$ortunities •
Con%i"ure Power Pivot !#t# $o!els wit. reportin" properties to exploit Power View %e#tures –
•
•
I$#"es, !e%#ult %iel! sets, D#uto$#tic c#lcul#te! %iel!s, etc+
Cre#te inter#ctive #n! visu#ll i$pressive Power View reports &#se! on Power Pivot !#t# $o!els E!uc#te users on/ –
–
;ow to inter#ct wit. Power View reports ;ow to cre#te t.eir own Power View reports, even %or #! .oc (one3 o%% #n#lsis
Data Modeling wit& Power Pivot
Data !cuisition wit& Power Power uery uer y
Power Pivot /e$orting
Data +isualiation wit& Power +iew
Data +isualiation wit& Power Ma$
Power BI Så and #olla3oration
Data +isualiation +isualiation wit& Power Power Ma$ •
•
•
Power M#p is # new n ew #!!3in %or Excel to inter#ctivel visu#li:e sp#ti#l !#t# in 64 =eAuires =eAuires # t#&ul#r !#t# $o!el, inclu!in" in clu!in" # Power Pivot !#t# $o!el *ni$#te! tours c#n &e cre#te! #n! pl#e! in t.e Excel client or exporte! to MP> vi!eo –
St#tic i$#"es c#n #lso &e copie! to t.e clip&o#r!
Data +isualiation wit& Power Ma$ Develo$ent Met&odology '+ Cre#te # wor1&oo1 #n! !evelop # Power Pivot !#t# $o!el, or connect to #n extern#l t#&ul#r !#t#&#se 2+ Cre#te tours, consistin" o% # seAuence o% scenes 6+ Con%i"ure t.e $#p #n! $#p l#out %or e#c. scene >+ Intro!uce l#er(s to visu#li:e !#t# superi$pose! on e#c. scene 8+ *nnot#te t.e $#p wit. c.#rts, text&oxes #n! le"en!s + Con%i"ure tr#nsition properties &etween t.e scenes 5ption#l 7+ Pro!uce #n! s.#re #n MP> vi!eo o% t.e tour
Data +isualiation wit& Power Ma$ "$$ortunities •
•
Present inter#ctive tours %ro$ t.e !es1top Pro!uce #n! s.#re tours o% "eosp#ti#l vi!eos –
Vi!eos c#n &e/ •
•
•
E$&e!!e! into PowerPoint present#tions plo#!e! to ouTu&e *!!e! to S.#rePoint !ocu$ent li&r#ries
Data +isualiation wit& Power Ma$ /esources •
Microso%t 4ownlo#! Center –
–
Power M#p Preview %or Excel 20'6 .ttp/www+$icroso%t+co$en3us!ownlo#!!et#ils+#spxKi!668
Data Modeling wit& Power Pivot
Data !cuisition wit& Power uery
Power Pivot /e$orting
Data +isualiation wit& Power +iew
Data +isualiation wit& Power Ma$
Power BI Så and #olla3oration
Så and #olla3oration •
•
Power BI solutions #re #ut.ore! & usin" t.e Excel client It is usu#ll in#ppropri#te to s.#re t.e solutions & %orw#r!in" t.e wor1&oo1 to ot.er users –
–
–
–
–
–
=eAuires t.#t t.e user .#s Excel inst#lle!, #n! possi&l t.e #!!3ins T.e user c#n view !#t# $o!el !#t#, #n! $o!i% $et#!#t# Current !#t# $# not &e #v#il#&le #s !#t# re%res. $# not wor1 %ro$ t.eir loc#tion, or & usin" t.eir cre!enti#ls 9or1&oo1 versions #re !i%%icult to control Per$issions c#n no lon"er &e centr#ll $#n#"e! IT c#nnot $onitor us#"e
Så and #olla3oration •
•
It is pre%er#&le to s.#re Power BI solutions in $#n#"e! w#s Microso%t provi!e t.ree $#n#"e! #ltern#tives/ –
–
–
S.#rePoint 20'6 (on3pre$ises G Enterprise E!ition 5%%ice 68 (clou! G ProPlus #n! E6 E!itions Power BI %or 5%%ice 68 (clou!
Så and #olla3oration Power BI for "ffice 67 •
T.ese services exten! t.e sel%3service BI c#p#&ilities in Excel & $#1in" t.e$ #v#il#&le in # coll#&or#tive online environ$ent/ –
–
–
–
Power BI Sites Power BI Q<* Quer #n! 4#t# M#n#"e$ent Power BI 9in!ows Mo&ile BI *pp
Så and #olla3oration Power BI Sites •
•
•
Power BI sites #pplic#tion custo$i:es # S.#rePoint 5nline site %or Power BI purposes #n! #ctivities *v#il#&le wit. # su&scription to Power BI %or 5%%ice 68 5nl supports Excel wor1&oo1s, w.ic., w.en ren!ere! to t.e &rowser, #re viewe! wit. t.e Excel 9e& *pp –
•
Power View reports c#n &e option#ll !ispl#e! & usin" ;TML8
Supports wor1&oo1s up to 280MB in si:e
Så and #olla3oration Power BI 8! •
•
•
•
Q<* en#&les # &ro#! #u!ience o% users to e#sil !iscover #n! explore # wor1&oo1 !#t# $o!el & usin" n#tur#l l#n"u#"e Su&$itte! Auestions in En"lis. #re respon!e! to wit. !#t# visu#li:#tions #n! reports T.e responses #re inter#ctive, #n! so c#n &e $o!i%ie! to %urt.er re%ine t.e !#t# explor#tion reAuire$ents 4elivers #uto3co$plete, #n! %e#ture! #n! #uto3 su""est Auestions to pro$pt #n! "ui!e t.e process
Så and #olla3oration uery and Data Manageent •
•
•
Business users c#n s.#re #n! $#n#"e Aueries, #n! c#n #n#l:e t.e us#"e o% t.eir s.#re! !#t# & usin" t.e M#n#"e 4#t# port#l Queries #re s.#re! to t.e 4#t# C#t#lo"ue w.ic. inclu!es Auer $et#!#t# #n! !#t# %or t.e purpose o% preview T.e M#n#"e 4#t# port#l en#&les/ –
–
Viewin" us#"e #n#ltics o% s.#re! Aueries (se#rc.e! or i$porte! Viewin" #n! $#n#"in" !#t# sources use! & Power Quer Aueries, #nnot#tin" t.e$ wit. in%or$#tion to i$prove t.eir !iscover
Så and #olla3oration uery and Data Manageent
Så and #olla3oration %indows Mo3ile BI !$$ •
•
•
Provi!es live $o&ile #ccess to t.e i$port#nt &usiness in%or$#tion store! in # users 5%%ice 68 #ccount T.e #pp en#&les viewin" #n! inter#ctin" wit. Excel #n! Power View content =eAuires 9in!ows or 9in!ows =T t#&let or PC, inclu!in" Sur%#ce –
•
Supporte! processors/ x, x>, *=M
Currentl, t.e #pp is not supporte! on/ –
iP#!, Mo&ile p.ones or *n!roi! !evices
Så and #olla3oration Power BI* Licensing and !vaila3ility "verview #loud 3ased
Su3scri$tion odel
Priced $er user $er ont&
!vaila3le 5e3 2014
7
Så and #olla3oration Power BI* 9ow to Buy
.wo o$tions* Microsoft "nline Su3scri$tion Progra
Purc.#se on PowerBI+co$
Microsoft +olue Licensing
=e#c. out to our Microso%t p#rtner %or $ore in%or$#tion
Så and #olla3oration Power BI* 9ow to Buy '#ontinued( Power BI wit& "67 ProPlus MOSP Syndication
Power BI BI
Power BI !dd)on
'S&arePoint "nline Plan 2(
'"ffice 67 , or ,4(
EA Open
Power BI BI !dd)on
Så and #olla3oration "$$ortunities •
•
•
Provision Power Power BI sites to $#int#in # secure, wi!el3#ccessi&le #n! centr#li:e! loc#tion loc#tion %or !iscoverin" #n! s.#rin" Dsin"le Dsin"le version o% t.e trut. !#t#, lo"ic #n! reports Pu&lis. Power Quer Aueries %or !iscover #n! !#t# consu$ption En#&le !#t# re%res. o% !#t# $o!els, even to on3pre$ises !#t# sources
Så and #olla3oration "$$ortunities '#ontinued( •
se n#tur#l l#n"u#"e Auer to en#&le intuitive report reAuests –
–
•
•
•
Pro$ote %e#ture! %e#ture! Auestions #s st#rtin" st#r tin" points %or !#t# explor#tion =e!uce t.e nu$&er o% reports to cre#te
4eliver Power Power View reports repor ts to #n ;TML83c#p#ti&le $o&ile !evice, wit.out # !epen!enc on Silverli".t St# connecte! to %#vorite reports repor ts wit. t.e 9in!ows $o&ile BI #pp En#&le IT $onitorin" o% #ccess #n! resource us#"e
Så and #olla3oration /esources •
Microso%t Power BI %or 5%%ice 68 we& site –
–
•
.ttp/www+power&i+co$ Si"n up %or t.e %ree 60 !# tri#l to preview t.e Power BI service
MS4H Blo"/ Power BI –
.ttp/&lo"s+$s!n+co$&power&i
Power BI Suary ,$owering sers wit& Self)service in ,-cel 4iscover Se#rc. #n! co$&ine pu&lic #n! intern#l !#t# wit. Power 7uery
*n#l:e
Visu#li:e
Mo!el < #n#l:e '00s o% $illions o% rows li".tnin" %#st wit. Power Pi(ot Explore #n! visu#li:e !#t# in new w#s wit. Power 0iew #n! Power Map
Power BI Suary #onnecting and #olla3orating in "ffice 67 S.#re
S.#re Aueries < wor1&oo1s #n! re%res. %ro$ on3pre$ises !#t# wit. .) $ites
)in! 7 <
Mo&ile
)in! #nswers wit. se#rc.3&#se! !#t# visu#li:#tion in 78
St# connecte! %ro$ #nw.ere wit. ;TML8 #n! t.e Power .) mobile app
Power BI Suary /esources •
Power BI 4e$o Contest –
–
–
.ttp/&lo"s+$s!n+co$&power&i#rc.ive20'6'0'"et3re#!3"et3set3%or3 t.e3power3&i3!e$o3contest+#spx 5ver 80 i$#"in#tive solutions pro!uce! wit. re#l !#t# & usin" Power BI )in#lists/ .ttps/www+%#ce&oo1+co$$icroso%t&i#ppR''2'60767>8
Power BI
Big Data
SL Server BI •
SQL Server #lso c#n pl# # $#or role in !eliverin" BI
SL Server BI •
St#n!#r!, Business Intelli"ence #n! Enterprise e!itions provi!e BI c#p#&ilities to !eliver l#r"er sc#le BI solutions –
–
4#t# w#re.ousin" Enterprise In%or$#tion M#n#"e$ent (EIM/ •
•
•
–
–
–
•
Extr#ct, Tr#ns%or$ #n! Lo#! (ETL wit. Inte"r#tion Services M#ster 4#t# M#n#"e$ent wit. M#ster 4#t# Services 4#t# Qu#lit *ssur#nce wit. 4#t# Qu#lit Services
4#t# $o!elin" ($ulti!i$ension#l #n! t#&ul#r 4#t# $inin" =eportin"
SQL Server 20'> is !ue %or rele#se in t.e co$in" $ont.s
SL Server SL Server 2014 Investents In)eory tec&nologies In)Meory "L.P •
8G20x per%or$#nce "#in %or 5LTP inte"r#te! into SQL Server
In)Meory Data %are&ouse •
•
8G28x per%or$#nce "#in #n! .i". !#t# co$pression p!#t#&le #n! clustere!
SSD Buffer Pool ,-tension •
>G'0x =*M #n! up to 6x per%or$#nce "#in tr#nsp#rentl %or #pps
,n&anced &ig& availa3ility !lways"n en&anceents •
Bac;u$ to !ure •
Incre#se! #v#il#&ilit #n! i$prove! $#n#"e#&ilit o% #ctive secon!#ries
"nline data3ase o$erations •
:ew &y3rid scenarios
Incre#se! #v#il#&ilit %or in!ex #n! p#rtition $#inten#nce
E#s to i$ple$ent #n! cost e%%ective !is#ster recover solution to 9in!ows *:ure Stor#"e
"t&er investents Better toget&er wit& %indows Server •
•
9ig& availa3ility to !ure +irtual Mac&ines •
E#s to i$ple$ent #n! cost e%%ective .i". #v#il#&ilit solution wit. 9in!ows *:ure Virtu#l M#c.ines
De$loy to !ure •
4eplo$ent wi:#r! to $i"r#te !#t#&#se
•
•
9in!ows Server 20'2 =e)S support 5nline resi:in" V;4J Microso%t ;per3V replic# 9in!ows DBlue support
,-tending Power +iew •
En#&le Power View on existin" #n#ltic $o!els #n! support new $ulti!i$ension#l $o!els
SL Server Parallel Data %are&ouse 'PD%( Insig&ts "n !ny Data of !ny Sie
PD% and PolyBase ,na3ling Integrated uery !cross 9adoo$ and /elational Data SQL
PolB#se
=esult set
•
•
•
•
;#!oop !#t#
=el#tion#l !#t#
Sin"le Auer %or rel#tion#l #n! ;#!oop !#t# Process !#t# in pl#ce se existin" s1ills wit. si$ple T3SQL co$$#n! )uture exp#nsion to ot.er !#t# sources
SL Server BI /esources •
Microso%t SQL Server we& site –
–
•
Microso%t SQL Server 20'> we& site –
–
•
.ttp/www+$icroso%t+co$sAlserver Ev#lu#te SQL Server %or %ree %or 0 !#s .ttp/www+$icroso%t+co$sAlserversAl3server320'>+#spx 4ownlo#! CTP2 %ree tri#l to!#
Microso%t P#r#llel 4#t# 9#re.ouse (P49 we& site –
.ttp/www+$icroso%t+co$sAlserversolutions3tec.nolo"ies!#t#3 w#re.ousin"p!w+#spx
Power BI
SL Server
Module "utline •
•
•
•
Intro!ucin" Bi" 4#t# Intro!ucin" ;#!oop Intro!ucin" 9in!ows *:ure ;4Insi".t 4e$onstr#tions
Introducing Big Data storage> searc&> så> analysis> and visualiation=? @ %i;i$edia
Introducing Big Data •
Bi" !#t# solutions !e#l wit. co$plexities o%/
V5LME (Si:e
V*=IET (Structure
VEL5CIT (Spee!
Introducing Big Data Pet#&tes
Ter#&tes
?i"#&tes
Me"#&tes
Data #o$le-ity* +ariety and +elocity
Introducing Big Data ,na3ling /es$onses to :ew uestions
9.#ts t.e soci#l senti$ent o% $ pro!uctK
;ow !o I &etter pre!ict %uture outco$esK
;ow !o I opti$i:e $ services &#se! on p#tterns o% we#t.er, tr#%%ic, etc+K
Introducing 9adoo$ •
•
*p#c.e ;#!oop is %or &i" !#t# It is # set o% open source proects t.#t tr#ns%or$ co$$o!it .#r!w#re into # service t.#t c#n/ –
–
•
Store pet#&tes o% !#t# reli#&l Execute .u"e !istri&ute! co$put#tions
Fe #ttri&utes/ –
–
–
–
–
5pen source ;i".l sc#l#&le =uns on co$$o!it .#r!w#re =e!un!#nt #n! reli#&le (no !#t# loss B#tc. processin" centric G usin" # DM#p3=e!uce processin" p#r#!i"$
Introducing 9adoo$ 9ow it %or;s
Introducing 9adoo$ 9ow it %or;s RUNTIME
Server
Server
Server
Server
Introducing %indows !ure 9DInsig&t •
;4Insi".t is Microso%ts ;#!oop3&#se! service t.#t en#&les &i" !#t# solutions in t.e clou! –
–
•
*v#il#&le #s # 9in!ows *:ure service ;4Insi".t Server is #v#il#&le to inst#ll on3pre$ises onl %or t.e purpose o% !evelop$ent #n! testin"
E$powers or"#ni:#tions wit. new insi".ts on previousl untouc.e! unstructure! !#t#, w.ile connectin" to t.e $ost wi!el use! BI tools on t.e pl#net
Big Data Suary •
•
Bi" !#t# re%ers to !#t# sets so l#r"e #n!or co$plex t.#t t.e &eco$e #w1w#r! to wor1 wit. in convention#l w#s ;#!oop c#n store pet#&tes o% !#t# reli#&l #n! execute .u"e !istri&ute! co$put#tions –
•
•
Bi" !#t# Auer results o%ten involve si"ni%ic#nt l#tenc
Power BI inclu!es #ut.orin" #!!3ins to Auer, #n#l:e #n! visu#li:e !#t# source! %ro$ 9in!ows *:ure ;4Insi".t SQL Server c#n connect to, Auer, #n! consu$e &i" !#t# results G &i" !#t# is ust #not.er !#t# sourceN
* Microso%t c#se stu! !escri&es .ow Flout pro!uce! # $ulti!i$ension#l BI Se$#ntic Mo!el (cu&e &#se! on t.eir open3source ;ive !#t# w#re.ouse sste$
Big Data /esources •
Microso%t Bi" 4#t# we& site –
•
9in!ows *:ure ;4Insi".t we& site –
•
.ttp/www+win!ows#:ure+co$en3us!ocu$ent#tionservices.!insi".t
;ortonwor1s tutori#ls –
–
•
.ttp/www+$icroso%t+co$&i"!#t#
.ttp/.ortonwor1s+co$tutori#ls Hu$erous tutori#ls #re #v#il#&le to le#rn #&out &i" !#t# & usin" t.e ;ortonwor1s S#n!&ox
Flout c#se stu! –
.ttp/www+$icroso%t+co$sAlserverenuspro!uct3in%oc#se3stu!ies1lout+#spx
Suary
Presentation Download •
T.is present#tion c#n &e !ownlo#!e! in P4) %ro$/ –
.ttp/www+&itwisesolutions+co$+#u!ownlo#!s20'>02 Microso%tBI+p!%
