interoperability. independent consultant specialising in sql server analysis services and mdx: mvp
Post on 22-Dec-2015
219 views
TRANSCRIPT
Interoperability
Self-Service BI, Corporate BI and how they can complement
each otherChris Webb
Crossjoin Consulting [email protected]
• Independent consultant specialising in SQL Server Analysis Services and MDX: http://www.crossjoin.co.uk
• MVP for SQL Server BI• Blogger: http://cwebbbi.spaces.live.com• Co-author of “MDX Solutions” and “
Expert Cube Development with Analysis Services 2008”
Who am I?
• The Traditional BI story• That Guy in Finance• PowerPivot, what it is and what That Guy in
Finance will do with it• Who needs a data warehouse anyway?• Getting traditional BI and self-service BI to live
happily together
Agenda
A Traditional BI Stack
• Business suffering from Excel Hell:– No central repository for data– Some reporting done direct from transactional systems
• Contains dirty data• Reports from different systems don’t tally
– Some reporting hacked together with Access and Excel by frustrated users• Very labour-intensive and expensive• Error-prone and inconsistent• Data often not up-to-date
The Traditional BI Story, Part 1
• Boil-the-ocean project to create a data warehouse gets kicked off– Involves large team of internal resources and
expensive outside consultants– Takes several years to develop anything at all– Only includes small amount of the available data
in the first release– Users, still frustrated, carry on hacking reports in
Access and Excel
The Traditional BI Story, Part 2
• After much pain, effort and financial outlay, the data warehouse begins to prove useful– It contains clean, consistent and integrated data
from a wide variety of source systems – a single version of the truth
– Users begin to use the BI tools provided– But still frustration at the speed at which new data
can be added to the warehouse, and new reports developed
The Traditional BI Story, Part 3
• Inevitably, there’s always someone who insists on building his own BI solution
• Will never give up Excel – he loves the flexibility to do what he wants
• Sufficiently technical to create a solution that works well and embarrasses ‘official BI’
• Sufficiently senior to get the protection of management when IT gets upset
That Guy in Finance
• Excel is the #1 BI tool in every company• That Guy in Finance can make it sing, dance
and do things you never thought possible• Often, people only start to think about
replacing it when:– It can no longer handle the data volumes required– It becomes too slow to calculate a workbook
Excel: Weapon of Choice
Excel 2003
10
Excel 2007
11
Excel 2010 + PowerPivot
12
What is PowerPivot?• PowerPivot (previously known as Gemini) is
Microsoft’s entry into the self-service BI sector – Qlikview, Tableau, SiSense Prism and Spotfire are
competing tools• Allow power users to integrate data from
multiple sources and do analysis/reporting• Storing data in column-oriented databases
means:– Query performance is extremely fast– They can handle very large amounts of data
PowerPivot Client Architecture• PowerPivot comes as an Excel addin– Only works with Excel 2010 though...
• Behind the scenes it is actually a modified version of Analysis Services
• Main change is storage method– Not MOLAP, HOLAP or ROLAP– Vertipaq – an in-memory column-store database
• Gives much faster querying• Compression means large amounts of data can be
stored in memory
PowerPivot Client Workflow1. Switch from Excel to PowerPivot UI2. Connect to data sources3. Load ‘tables’ of data, filtering if necessary4. Define joins between tables loaded into PowerPivot5. Hide/Show, rename columns6. Define calculated columns7. Switch back to Excel and query data through pivot
tables/Excel cube functions8. Define calculated measures
Supported Data Sources• Relational databases– Entire tables– SQL queries
• Excel tables (plus paste from clipboard)• OData feeds• Analysis Services cubes or PowerPivot• Text files
• OData is “ODBC for the web”• Microsoft’s new format for exposing data as a
service• Based on REST, with ATOM or JSON payloads • Producers include:– SSRS 2008 R2 reports via data feed rendering– Project Dallas– ADO.Net Data Services– Sharepoint 2010 Lists– SQL Azure
OData
PowerPivot Server Architecture• PowerPivot models can be shared by publishing
workbooks to Sharepoint 2010– Can then be queried by any SSAS client tool– Excel Services is the obvious choice
• Requires Enterprise Edition!• PowerPivot server components:– PowerPivot System Service– SSAS 2008 R2 in Vertipaq mode– PowerPivot Web Service
19
PowerPivot Server Architecture
20
SharePoint Farm
WFE
App Servers
Content dBs
NLB
Producer
Data Sources
Excel Services
PowerPivot Mid-Tier
AS Engine
Browser
Consumer
PowerPivot Add-In
Excel
PowerPivot Gallery
21
Excel Services Report
22
• PowerPivot V1.0 is still missing:– Security – you can see everything or nothing– Advanced data modelling– Sophisticated data refresh
• But if you need this, maybe it’s time to move the solution to Traditional BI?
• More importantly, there is no easy way of turning a PowerPivot model into a SSAS cube – but this should come
What’s Missing in V1.0
One Version of the Truth?"Where is the single version of the truth in this architecture? I’ve just spent 4 years of my life trying to convince users to stop using Excel as a data store and here are Microsoft positively encouraging it. Hell will freeze over before this capability is used responsibly in most organisations”Mick Horne, EMC Consulting
24
• The short answer: yes• Self-service BI only makes it more important
to have:– Clean data– Conformed dimensions– Somewhere for people to get data that isn’t the
source system
Do We Still Need a Data Warehouse?
• The short answer: yes• Self-service BI is only going to appeal to power
users, not everyone• Will never be as reliable as a traditional BI
system – always a bit of a hack• Over-use of self-service BI leads to Excel hell on
a gigantic scale• Popular ‘user generated’ solutions should, in
time, be turned into ‘official’ BI solutions
Do We Still Need Traditional BI?
• The short answer: no• Excel will never go away, and with PowerPivot
the pain threshold has been raised• Cloud-based BI solutions are already on the
market and are increasingly popular• That Guy in Finance needs to be
accommodated, otherwise he’ll cause even more problems!
Can We Ignore Self-Service BI?
• Remember, traditional BI can be flexible• Data warehouse building and report writing are
often coupled together too tightly• Empowering users to create their own reports
can free up a lot of IT resources and increase user satisfaction
• Deploying tools like Report Builder or Analysis Services (with Excel) can address many of the complaints users have
Flexible Traditional BI
• PowerPivot reduces the risks of self-service BI– Lets That Guy in Finance do what he wants– But gives him the means to share solutions effectively– And gives IT the ability to monitor and regulate what’s
going on• Exposing corporate data via OData feeds will make
consuming it in PowerPivot easy– Reduces the demand for unofficial data dumps from
transactional systems – Third-party tools like Xtract for PowerPivot already allow
exposing SAP data in this way
Safer Self-Service BI
• So we need self-service and traditional BI• And we need to make sure the two work
together, rather than diverge• PowerPivot, Office 2010 and the SQL Server
2008 R2 BI stack make this possible
Happy Together
QUESTIONS?
• http://www.odata.org/• http://www.powerpivot.com/• http://blogs.msdn.com/powerpivot/• http://www.powerpivot-info.com/• http://powerpivotpro.com/• http://powerpivottwins.com/• http://powerpivotgeek.com• http://www.theobald-software.com/en/produ
cts/xtractpp.htm
Links