Transcript 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
 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
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
Business Intelligence for Law Firms. Period.
© 2013 Iridium Technology LLC
References
• Google “Excel 2013 BI features”
Become Microsoft Office Certified
Business Intelligence for Law Firms. Period.
© 2013 Iridium Technology LLC
Cool Features – Mini Demos
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
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
(Digression) Turning Off GETPIVOTDATA()
• GETPIVOTDATA() function is
 Awkward
 Misleading
Mini-Demo
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
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
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
Trendlines
• Another cool feature that not enough people use
(2010/2013)
• Improved toolset (tool pane) in 2013
Mini-Demo
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)
Let’s Talk about Cubes
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
Business Intelligence for Law Firms. Period.
© 2013 Iridium Technology LLC
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
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
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
Business Intelligence for Law Firms. Period.
© 2013 Iridium Technology LLC
Oh, and one more thing…
Oh, and one more thing…
“Desktop Dashboards”
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.
Business Intelligence for Law Firms. Period.
© 2013 Iridium Technology LLC
Grab Bag Topics
Business Intelligence for Law Firms. Period.
© 2013 Iridium Technology LLC
Pet Peeve #1: Poor Cube Navigation in Excel
Excel 2010
SQL Server Mgmt Studio
Excel 2013 (Partially Fixed)
Pet Peeve #2: ‘Not all items showing’ error
when filtering over 10,000 rows of data
• Not fixed in Excel 2013
• Workaround exists
(filtering)
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
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
Got Kids? Office 365 Home
• The Jones home:
 (2) Business laptops
 (1) Personal Windows laptop
 (2) MacBooks for the boys
Calculated Measures and
Dimensions
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!
Creating a Calculated Measure
• The function is located in OLAP Tools:
Creating a Calculated Measure (cont.)
• Point-click-and-drag – No MDX Knowledge Required!
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
PivotTable Tips, Tricks, and
Techniques
Expand All + Collapse All
• New function in Excel 2013
• Very useful when building out a PivotTable
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
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)
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
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.
Part 2?
• Internal Data Model
• PowerPivot
• PowerView (including MDX Designer)
• Dashboarding with PowerView
Business Intelligence for Law Firms. Period.
© 2013 Iridium Technology LLC