Understanding Microsoft Self-Service BI

1. Understanding Microsoft Self-Service BI Devin Knight dknight@pragmaticworks.com @knight_devin

2. About Devin • Will work for cupcakes • Popup books about maintaining a healthy relationship with your miniature donkeys. • And supporting cult being investigated by the government. • Blogs mainly consist of a collection of internet cat pictures Training Director Author of 5 books Runs JSSUG User Group Blogs at BIDN.com Twitter: @knight_devin DKnight@pragmaticworks.com

3. Different Levels of Business Intelligence Data Explorer

4. Which BI Do I Need? Problem I need to quickly prototype a solution to work out requirements for a larger project Best Solution Personal BI Data Explorer

5. Which BI Do I Need? Problem I need to address data quality problems with my data sources Best Solution Corporate BI Data Explorer

6. Which BI Do I Need? Problem I need one version of the truth with my data. Meaning everyone should base reports on the same data structure. Best Solution Corporate BI Data Explorer

7. Which BI Do I Need? Problem I need a more agile approach to developing BI. Best Solution Personal BI Data Explorer

8. Self-Service BI Roles Data Explorer

9. Self-Service BI Lifecycle

10. Tables • Tables • Hierarchies • Relationships DataPresentation DataExtraction DataModeling Power Map Power Map Power Map Power View Power View Excel Power Pivot Power Pivot Template Power Query Power Query Hadoop • Charts • Charts • Calculations Databases SharePoint Lists • Maps • Apps For Office • Key Performance Indicators Flat Files Internet • Exporting • 3-D Maps Reporting Services Active Directory Cloud Facebook

11. When Do I Engage IT? Access Read access is required for all necessary data sources Scalability When you need your solution to scale to a large user base Data Explorer

12. Power Pivot Why use Power Pivot? Why is Power Pivot so Fast? Who Should Use Power Pivot?

13. Different Flavors of Analysis Services LearningCurve Analysis Service Multidimensional Power Pivot Power Pivot For SharePoint Analysis Service Tabular Scalability

14. Why Use Power Pivot? Performance Uses In-Memory Technologies Providing Unmatched Analytical Performance Short Development Cycles Quick and Easy to Create and End to End Solution End User Capable Familiar Tools with Enhanced Capabilities 300 Million Excel Users Worldwide 10% of Excel Users Create PivotTables

15. Introduction to DAX What is DAX? Data Analysis Expression Language Query Language for Power Pivot Why DAX? Designed to Support a Larger User Base Simpler than Traditional Technical Languages to Learn Leverage Existing Knowledge of Excel Formulas Less of a Learning Curve for Analyst

16. Who Should Use Power Pivot? • Data Wrangler • Harmonize and Rationalize Data • Brings Together and Creates Meaning out of Data • Data Steward • Ensures data standards and cohesion • Develops policies and creates mechanisms to ensure policies are enforced • Power Analysts • Develops Dashboards and Interactive Reports • Uses Data Models Created by Data Wranglers • Collaborative Users • Add Perspective Based on Their Knowledge of the Business

17. Demos Importing data into Power Pivot Creating a Data Model in Power Pivot

18. Power Query Power Query Overview Why Use Power Query? Who Should Use Power Query?

19. Ways to Pull in Data Data Tab Power Pivot

20. Data Discovery with Power Query

21. Who Should Use Power Query? • Data Wrangler • Harmonize and Rationalize Data • Brings Together and Creates Meaning out of Data • Data Steward • Ensures data standards and cohesion • Develops policies and creates mechanisms to ensure policies are enforced • Power Analysts • Develops Dashboards and Interactive Reports • Uses Data Models Created by Data Wranglers • Collaborative Users • Add Perspective Based on Their Knowledge of the Business

22. Demos Discover -> Consume -> Visualize

23. Power View Power View Overview Why Use Power View? Who would use Power View?

24. Microsoft Reporting Options Excel PivotTables PowerPivot ReportingServices Main Purpose Ad-hoc Analytics and Reporting Who Uses It Power Users Requirements Excel 2010 or Higher Main Purpose Pixel Perfect Static Reporting Who Uses It Developers Requirements SQL Server Main Purpose Ad-hoc Reporting Who Uses It End Users Requirements Any Version of Excel PowerView PerformancePoint Main Purpose Data Exploration Who Uses It End Users Requirements SQL 2012 / SharePoint 2010 SP1 or Excel 2013 Main Purpose Dashboards Who Uses It Developers Requirements SharePoint 2007 or Higher

25. Flavors of Reporting Services

26. Who Should Use Power View? • Data Wrangler • Harmonize and Rationalize Data • Brings Together and Creates Meaning out of Data • Data Steward • Ensures data standards and cohesion • Develops policies and creates mechanisms to ensure policies are enforced • Power Analysts • Develops Dashboards and Interactive Reports • Uses Data Models Created by Data Wranglers • Collaborative Users • Add Perspective Based on Their Knowledge of the Business

27. Demos Creating Power View Report

28. Power Map Power Map Overview Linking Geographical Fields to Power Map properties Mapping Time Data Creating a Power Map Tour

29. Power Map Overview Power Map Features 3-D Data Visualization Tool for Excel 2013 Plots Geography Data Visually Analyze Data on a 3-D globe Using Bing Create Visual Tours to Share with Others

30. Animating Maps with Time Data Mapping Time Data First Half of Bringing Life into Maps Time Settings Data Point Persistence Instant Time Accumulation Persist the Last Easy Date Range Filter Capabilities Playback Speed Control

31. Creating a Power Map Tour Power Map Tours Second Half of Bringing Life into Maps Guided Tour of Important Elements you want to Highlight Creating Tour Scenes A Tour is a Collection of Scenes Scenes can be Named and have Different Effects Applied to them

32. Demos Power Map

33. Power BI Power Pivot Power Query Power View Power Map Natural Language Search Mobile BI

34. Thanks! Email: dknight@pragmaticworks.com Twitter: @knight_devin Blog: www.devinknightsql.com