Transcript BI Session v2

Business Intelligence
For the Common Man
Pete Zerger
Principal Program Manager, MVP
[email protected]
Cireson
Lee Berg
[email protected]
Concurrency
Pete Zerger
Lee Berg
@PZerger
@LeeAlanBerg
10-Time Microsoft MVP
Microsoft MVP
18 Years
Foods (and people) of the world
10 Years
Internet-of-Things
Agenda
• What is Business
Intelligence?
• Dataset and Personas
• BI Tools (free & commercial)
• Data Dimensions
• “Flattening” Relational Data
• Selecting the Right Data
Visualizations
• Optimizing for Scale
• Shortcuts to Advanced
Insights
• Next Steps
What is Business Intelligence?
Data
Mining
Structured
OLAP
raw data to
Data
ETL The process of analyzing
Data
Mart
provide
actionable information to the
OLTP
business for making decisions.
Unstructured
Data
Data
Warehouse
Semi-Structured
Data
What is Business Intelligence?
DELIVER RELEVANT & RELIABLE INFO
TO THE RIGHT PEOPLE
AT THE RIGHT TIME
What is Business Intelligence?
DELIVER RELEVANT & RELIABLE INFO
BETTER
TO THE RIGHT PEOPLE
DECISIONS
AT THE RIGHT TIME
FASTER
What is Business Intelligence?
Maturity Level
BI solution maturity stages
Wisdom
Knowledge
Information
Data
•
•
•
•
• Understand Causes
• Apply Patterns
Interpret
Understanding Relations
Apply Context
KPI, Dashboard Display
Utility (Usefulness)
• Improve Business Processes
• Make Business Decisions
• Gain Competitive Advantage
From Data to Decision
Today, we’ll streamline the 1st step &
get right down to business!
Aggregate
Data
Database, Data Mart, Data
Warehouse, ETL Tools,
Integration Tools
Present
Data
Reporting Tools,
Dashboards, Static Reports,
Mobile Reporting,
OLAP Cubes
Enrich
Data
Add Context to Create
Info, Statistics,
Benchmarks, Variance
to Plan or LY
Inform a
Decision
Decisions are
Fact-based and
Data-driven
Data Analytics Tools
Free Tools
Commercial Tools
Demo
The possibilities of free &
open source BI tools
Maximus Pharmaceuticals
Company Profile
Global conglomerate developing
groundbreaking new drugs and treatments
• Offices in the US, Europe, Asia
and Australia
• Managing drug trials in more than
2,000 clinics around the world
•
• Service desk processes 100’s of
thousands of incidents per year
Our Sample Dataset
ITSM Data for Global Enterprise
~1 million records. A big dataset.
We’ll discuss optimizing performance at scale
Users
(analysts, support
staff, end users, etc)
Other Cis
Work Items
(applications, services,
computers & other devices)
(incident, problem, service
request, change & release)
DisplayStrings
(display values to match GUIDs
in other tables)
Relationships
(reference table containing
relationship type, source &
target objects)
Dimension:
Data Dimensions
Status
Hardware
Incidents
Dimension:
Work Item
Facilities
Changes
Dimension:
Classification
Application
Problems
John
Dimension:
Assigned Analyst
Mar
Feb
Mary
Sam
Jan
Dimension:
Time
Scenario Objectives
technical
Our focus in this 120 minutes…
Desktop Support Technician
Alicia Thomber
focus
Deliver the right information to
answer the key questions…
Using the optimal data
visualizations…
Fully leveraging the dimensions
in our data…
With good performance at
scale…
Cesar Garcia
Service Desk Manager
Arturo Lopez
Chief Information Officer
business
“Flattening” Relational Data
Simplifying data for easy analysis
Relational data is often complex
Consolidating data into fewer tables can
• Make performance easier to manage
• Streamline process visualization and analysis
• Enable small scale ETL possible
Don’t confuse this targeted simplification process
with an enterprise data warehousing strategy!
Demo
Quick Dive into the
Sample Data
Choosing Data Visualizations
based on dataset and target audience
Well-crafted data visualizations help:
• make large data sets coherent
• uncover trends, realize insights, explore
sources, and tell stories
• transform and shape data
• provide insights to guide decisions
Which data visualizations are best will be influenced by the
data, the stakeholder and decision criteria
Demo
Advanced Data
Visualizations
Fast Dashboards for Big Datasets
Optimizing performance at scale
Query performance degrades at scale
There are several things you can do to help
• Server Resourcing (faster storage, more memory)
• Indexes (the right indexes)
• Statistic Creation & Update
• Tune the Engine
The Database Engine Tuning Advisor can help…even if you are
not a SQL expert. MS tutorial @ http://bit.ly/22vqzKX
Demo
SQL Indexing &
Server Tuning
Shortcuts
to advanced BI capabilities
Many enterprise management
solutions have databases with
complex schemas
Many BI solutions have
canned solutions for specific
products and scenarios
Demo
Content Packs for
Power BI
Custom BI Visualizations
Extending Power BI with Project SandDance
SandDance is a recent project to come
out of Microsoft Garage
• Start it up in your browser
• upload a data set (TXT, CSV or JSON)
• visualization options including 3D
scatterplot, maps, charts and histograms
Available in two
parallel versions:
• Stand-alone webbased UI
• Custom Power BI visual
Available (FREE) from http://www.sanddance.ms/
Demo
Extending Power BI
with Custom Visuals
Resources
Commercial & Open Source Business Intelligence Solutions
https://en.wikipedia.org/wiki/Business_intelligence
SCCM Dashboards for Power BI
https://github.com/Azure/azure-quickstart-templates/tree/master/101-acs-swarm
SQL Indexing Tutorial
http://www.tutorialspoint.com/sql/sql-indexes.htm
Tutorial: SQL Database Engine Tuning Advisor (3 lessons)
https://msdn.microsoft.com/en-us/library/ms166575.aspx
Project SandDance
https://www.sanddance.ms/
Resources
Performance best practices for SQL Server in Azure virtual machines
http://bit.ly/1qrJ0Uf
The difference between a clustered index and a non-clustered index
http://bit.ly/1MwSfZF
Next Steps
• Start small. Find a manageable scenario
and dataset in your org
• Start playing with a BI tool in your budget
• Use the tips and tutorials in this deck to
get comfortable with basic SQL tuning
• Try cloud-based solutions (like Power BI)
to short cut the journey to advanced BI
And Then …
Discuss:
Evaluations:
Ask your questions-real world answers!
Plenty of time to engage, share knowledge.
Please provide session feedback by clicking the EVAL button in the scheduler app (also
download slides). One lucky winner will receive a free ticket to the next MMS!