1 / 41

Data Analysis Nirvana: Excel 2013 Business Intelligence Features

Data Analysis Nirvana: Excel 2013 Business Intelligence Features. Tom Jones – President, Iridium Technology LLC. Introduction – Iridium Technology. “Business Intelligence for Law Firms. Period.” Founded in 2009 8 -member team 100% focused on legal BI

ursa
Download Presentation

Data Analysis Nirvana: Excel 2013 Business Intelligence Features

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Analysis Nirvana: Excel 2013 Business Intelligence Features Tom Jones – President, Iridium Technology LLC

  2. Introduction – Iridium Technology • “Business Intelligence for Law Firms. Period.” • Founded in 2009 • 8-member team 100% focused on legal BI • BI solutions for ADERANT and ELITE clients • Tuning of existing BI solutions • Custom dashboards/reports • 35 clients in all regions • Client firm size 85 – 2300 timekeepers

  3. Agenda • Part 1 • Mini-Demos: slicers, timelines, tables, Quick Explore, etc. • Ad-hoc querying of cubes • Publishing your results through dashboards • “Grab Bag” topics • Calculated member and dimensions ** • Pivot table tips and tricks • Part 2 (?) • Internal Data Model • PowerPivot • PowerView (including MDX Designer) • Dashboarding with PowerView

  4. References • Google “Excel 2013 BI features”

  5. Become Microsoft Office Certified

  6. Cool Features – Mini Demos

  7. Quick Analysis • Quick Analysis tool lets you convert your data into a chart or table in two steps or less. You can preview the data and make your choice in one click. • You can instantly create different types of charts, including line and column charts, or add miniature graphs (called spark lines). • You can also apply a table style, create PivotTables, quickly insert totals, and apply conditional formatting. • Everything performed by Quick Analysis can be done manually Mini-Demo

  8. Tables (2010/2013) • My favorite feature that not enough people use • A Table is not a Pivot Table • Features • Total Row • Formulas applied to columns • Styles • Data Validation • Conditional Formatting • Tab to insert • (Table Tools, Convert to Range to remove table) Mini-Demo

  9. (Digression) Turning Off GETPIVOTDATA() • GETPIVOTDATA() function is • Awkward • Misleading Mini-Demo

  10. Slicers • Previously slicers were only available for PivotTable data • Now slicers can also filter data in Excel tables, query tables, and other data tables • Slicers show the current filter so you’ll know exactly what data you’re looking at Mini-Demo

  11. Timeline • A timeline makes it simpler to compare your PivotTable or PivotChart data over different time periods • It's more visual and intuitive than the traditional filter • There must be a DATE format column Mini-Demo

  12. Charts Made Easier • Formatting, picking and previewing changes to chart elements are quicker and easier • With Chart Recommendations, Excel will recommend the most suitable charts with for data with previews Mini-Demo

  13. Trendlines • Another cool feature that not enough people use (2010/2013) • Improved toolset (tool pane) in 2013 Mini-Demo

  14. Quick Explore: Drill into PivotTable Data • Quick Explore feature lets you drill into your Online Analytical Processing (OLAP) cube or Data Model-based PivotTable hierarchy to analyze data details on different levels. • I admit it… • This is the one new feature that I didn’t do much for me Mini-Demo (Next Time)

  15. Let’s Talk about Cubes

  16. No Cubes? Then you Don’t Have BI • What is a cube? • Data storage optimized for querying and reporting • Microsoft SQL Server component “SSAS” • Topic specific: GL, Salary, Profitability, etc. • What can it do for me? • Ad-hoc query capabilities • Data source for dashboards • Data source for firm-wide reporting

  17. Ad-hoc Querying with SSAS Cubes • Excel is Iridium’s preferred query tool for working with SSAS Cubes • Excel 2013 offers tighter integration with SQL Server • We strongly encourage our clients to use Excel 2013 Demo: Ad-hoc querying in Excel

  18. Training: “QOTD” Program • QOTD is part of Iridium’s training curriculum • 23 lessons to get our clients to “expert” level in analysis • Focused on working with pivot tables connected to a cube

  19. Cubes and Dashboards • Cubes are a great source for dashboards • 2 second average screen refresh • You have a choice of tools: • Excel Services / SharePoint • SSRS Demo: Cube-based Dashboards

  20. Oh, and one more thing…

  21. Oh, and one more thing… “Desktop Dashboards”

  22. What About Excel Services? • Excel is a great mockup tool and great for personal analysis, but what next? • Excel Services is a server technology included in SharePoint 2010 and SharePoint 2007. This shared service enables users to load, calculate, and display Excel 2010 workbooks on Microsoft Office SharePoint Server 2010. • Using Excel Services, users can reuse and share Excel workbooks on Microsoft Office SharePoint Server 2010 portals and dashboards. The entire workbook or just parts of it (such as just a single sheet, chart or table) can be shared. • End-users can view live, interactive workbooks using only a web browser.

  23. Grab Bag Topics

  24. Pet Peeve #1: Poor Cube Navigation in Excel Excel 2010 SQL Server Mgmt Studio Excel 2013 (Partially Fixed)

  25. Pet Peeve #2: ‘Not all items showing’ error when filtering over 10,000 rows of data • Not fixed in Excel 2013 • Workaround exists (filtering)

  26. Pet Peeve #3: Lack of Cascading Filters • You filter on FY 2010… • So why are 2005 FY periods offered? • Not fixed in Excel 2013 • “Workaround” exists

  27. Pet Peeve #4: Slow Filtering Large Dimensions • With large dimensions, “Keep Only Selected Items” is very slow • Fixed in Excel 2013! • This one feature is worth upgrading for if you are a power user

  28. Got Kids? Office 365 Home • The Jones home: • (2) Business laptops • (1) Personal Windows laptop • (2) MacBooks for the boys

  29. Calculated Measures and Dimensions

  30. Calculated Measures and Dimensions • New in Excel 2013 • Allows ad-hoc Measure and Dimension creation • Calculations inside PivotTable – no more references to data in a PivotTable that must be modified after every change in the Report!

  31. Creating a Calculated Measure • The function is located in OLAP Tools:

  32. Creating a Calculated Measure (cont.) • Point-click-and-drag – No MDX Knowledge Required!

  33. Creating a Calculated Dimension • Much the same as creating a Calculated Measure • However, more complicated – hierarchies, individual member manipulation • But still click-and-drag! • Huge step forward from Excel 2010

  34. PivotTable Tips, Tricks, and Techniques

  35. Expand All + Collapse All • New function in Excel 2013 • Very useful when building out a PivotTable

  36. Deferring Layout • “Don’t send me data until I finish telling you what data I need!” • Lessens the amount of data transferred • No more waiting on data to be returned after every little change to the PivotTable

  37. Refresh on Open and Actions on Refresh • Refresh on Open: Get updated data from the data source whenever the workbook is opened • Great for Excel-based Reports • Actions on Refresh • Preserve Formatting • Auto-fit Column Widths (no more cutoffs)

  38. Manual Sorting • Not all data fits into Excel’s sorting Paradigm • Sort small data sets manually to present the data in the most useful way

  39. Filter Early • Filter early to cut down on server load, data transfer times, and wait times. • Filter to a subset of the data you’ll eventually need when building the Report • Same benefits plus you can build out the PivotTable faster • Once the PivotTable is complete, remove any unnecessary Report Filters and retrieve the data once.

  40. Part 2? • Internal Data Model • PowerPivot • PowerView (including MDX Designer) • Dashboarding with PowerView

More Related