Transcript One BI - Capita FHE

Andrew Fryer
Technical Evangelist
Microsoft UK Ltd
Mission Critical
BI
Behind every cloud is another cloud
The cloud infrastructure is made
available to the general public
or a
large industry group
and is
owned by an
organization selling
cloud services.
The cloud infrastructure is
operated
solely for an
organization.
It may be managed by the
organization or a third party
and
may exist on premise or
off premise.
The cloud infrastructure is a
composition of
two or more clouds
(private, community, or public)
that remain unique entities but
are bound together by
standardized or proprietary
technology that enables
data and application
portability
(e.g., cloud bursting for loadbalancing between clouds).
SQL Azure
SQL Server 2012
Azure Data Sync
Azure Reporting Services
Appliances
Azure Data Market
Azure Storage
© 2011, Enterprise Strategy Group, Inc.
All Rights Reserved.
PROTEC
T
DATA
CONTRO
L
ACCESS
ENSURE
COMPLIANCE
Protect data-at-rest
Data/Key separation
Use strong authentication
Monitor all activity
Transparent Data
Encryption
Crypto
Enhancements
Extensible Key
Managements
Kerberos authentication
enhancements
SQL
Server
Audit
Change Data
Capture
Detect non-compliant
configurations
Policy-Based
Management
Industry Certification
Common Criteria
Certification (EAL4+)
User-Defined Server
Roles
Audit
Resilience
Default Schema for
Groups
Audit in all
SKUs
User-Defined
Audit
Contained Database
Authentication
Audit
Filtering
T-SQL
Stack Info
Mission Critical
BI
Cloud
AlwaysOn protects multiple databases at once
Always On provides read only copies of databases
BI
Mission Critical
Cloud
Lies damn lies and statistics
Microsoft BI components
Business User Experience
Business Collaboration Platform
Data Infrastructure & BI Platform
Flexible:
Familiar:
Rigid:
Powerful:
Near real time:
Hard:
Secure:
Control:
Can model anything
Enterprise scale
Fine grain control
Lots of expertise
15 minute refresh
Process & versions
hard to change
MDX isn’t easy
Easy to Use:
users get it
Mashups:
make your own
Agile:
Deploy in a click or 2
Collaboration:
Between IW & IT
Chaos:
IT can’t manage it
Refresh:
Could be a day out
No Downsides
Easy to Use:
Collaboration:
Powerful:
Mashups:
Secure:
Cool:
Agile:
Control:
users get it
make your own
Deploy in a click or 2
Between IW & IT
Fine grain control
Process & versions
Enterprise scale
End user analysis
17
18
BIN 202, BINHOL271
Crescent only works from Tabular
BISM for now
Crescent and alerting depend on
SharePoint
Silverlight based
21
BIN 302, 311
Architecture
Usage
Unified Dimensional Model
Report Model
PowerPivot
BI Semantic Model
Reporting
3rd Party apps
Excel
PowerPivot
SharePoint
BI Semantic Model
Data model
Multi-dimensional
Tabular
MDX
DAX
Business logic & queries
Data access
Databases
LOB Applications
ROLAP
MOLAP
Files
VertiPaq
OData Feeds
Direct Query
Cloud Services
Tools
BISM
Reporting Services
Data Feeds
Data Sets
Align to the Azure Data Market
Still in BIDS
Still in Excel
The case for the data warehouse
Still need to combine and refine source data
Improve consistency and accuracy
Capture History
Near real time BI
Data Quality Services
Reference Data
Services
SSIS Data Flow
Values/Rules
Source +
Mapping
Data correction Destination
Component
New Records
Corrections & Suggestions
Reference Data
Definition
Correct Records
SSIS Package
Invalid Records
New MDS Interface
MDS Excel add-in
Crawlers
api
Index server
Interfaces
Discover
Barcelona
Inventory
Barcelona
Cleanse,
match
DQS
Data Sources
Acquire
SSIS
Curate
MDS
Match,
de-duplicate
DQS
Publish
SSIS
IS
DQS
MDS
Barcelona
Columnstore
Uses VertiPaq compression
Row store:
…
Column store:
C1
C2
C3
C4
C5
C6
Columnstore Performance
Performance Illustration
32-logical processors 256GB RAM,
1Tb database with 1.4 billion rows
Total
CPU
time
Elapsed
time
Columnstore
31.0
1.10
No columnstore
502
501
Speedup
16X
455X
Column store whitepaper
select
w_city,
w_state,
d_year,
SUM(cs_sales_price) as cs_sales_price
from warehouse, catalog_sales, date_dim
where w_warehouse_sk = cs_warehouse_sk
and cs_sold_date_sk = d_date_sk
and w_state in ('SD','OH')
and d_year in (2001,2002,2003)
group by w_city, w_state, d_year
order by d_year, w_state, w_city;