slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Welcome PowerPoint Presentation
Download Presentation
Welcome

Loading in 2 Seconds...

play fullscreen
1 / 30

Welcome - PowerPoint PPT Presentation


  • 99 Views
  • Uploaded on

Welcome. Building And Deploying A Data Warehouse With SQL Server ™ 7.0 Speaker name Title Microsoft Corporation. Background On Data Warehouse Goals. Right Information Integrated from multiple sources Summarized from details Right Format Flexible analysis Flexible delivery Right Time

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Welcome' - loyal


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide3

Building And Deploying A Data Warehouse With SQL Server™ 7.0Speaker nameTitleMicrosoft Corporation

background on data warehouse goals
Background On Data Warehouse Goals
  • Right Information
    • Integrated from multiple sources
    • Summarized from details
  • Right Format
    • Flexible analysis
    • Flexible delivery
  • Right Time
    • Fast turnaround from business processes
    • Rapid query response
data warehouse process
Data Warehouse Process

Data characteristics

  • Targeted
  • Specialized (OLAP)
  • Raw detail
  • No/minimal history
  • Integrated
  • Scrubbed
  • History
  • Summaries

Source OLTP

systems

Data marts

Data

warehouse

  • Load
  • Index
  • Aggregation
  • Replication
  • Data set distribution
  • Access and analysis
  • Resource schedulingand distribution
  • Extract
  • Scrub
  • Transform
  • Design
  • Mapping

Meta data

System monitoring

Source: The Enterprise Group, Ltd.

microsoft sql server data warehousing strategy

Lower costs

Acquisition, deployment,

administration

Scalability

Individual decision maker

to enterprise

Integration

Microsoft BackOffice and Office

Access, metadata,

movement, management

Microsoft® SQL Server Data Warehousing Strategy
microsoft data warehousing framework

Building

Using

Data Warehouse/Data Mart Design

End-User Tools

Operational

Data

Data

Transform/

Cleansing

Data Marts

Information Dir

Managing

Repository (Persistent Shared Meta-Data)

Meta-Data Flow

Data Flow

Microsoft Data Warehousing Framework

Data Warehouse Management

products and components
Products And Components
  • Microsoft SQL Server 7.0
    • Data Transformation Services
    • Replication Services
    • Microsoft Repository
    • English Query
    • OLAP Services
  • Office 2000
    • Excel 2000 PivotTables
    • Web Components

Coveredtoday

data transformation services what is it
Data Transformation Services - What Is It?
  • A generalized facility for importing, exporting and transforming data between different and same data sources
data compatibility

MainframeDB2 MVS, VSAM,

CICS/IMS,

NCR Teradata

OLE DB/ODBC

Oracle

DTS

SQL/400

SQL Server™

DB2/NT

Data Compatibility
  • 100% OLE DB architecture (including ODBC)
  • Relational and non-relational sources
  • Text files and desktop sources
dts package
DTS Package
  • A self-contained definition of all the tasks to be performed as part of a transformation
  • Execute from GUI, scheduler, command line, or script
  • Contain multiple steps
  • Execute serially or in parallel
  • Provides simple “workflow”
  • Supports transactions across steps
  • Supports Visual Basic® Scripting, JScript™, Perl script
typical transformations
Typical Transformations
  • Data quality and validation
    • Missing values, scrubbing, exception handling
  • Data integration
    • Heterogeneous query, eliminating duplicates
  • Data transformation
    • Value mapping, merging/splitting columns
  • Data aggregation
  • All managed by central metadata with support for business rules and data lineage
sql 7 0 repository what is it

Repository

MTS

DTS

Form

VBA

SQL 7.0 Repository - What Is It?
  • Infrastructure for sharable and reusable metadata about applications, components, data…
  • Components
    • Information models
    • COM interfaces and SQL schema
    • Repository engine
    • Modelling and admin tools
data warehousing extensions
Data Warehousing Extensions
  • Provide new domains for data warehousing
    • DTS and OLAP model definitions
  • Implement common data warehousing infrastructure
    • Reducing the cost of data warehousing tools
    • Allowing ISVs to focus on features
  • Developed with 60 vendors in an open design process, specifications available on our Web site
  • http://www.microsoft.com/repository
english language query what is it
English Language Query - What Is It?
  • Translates English questions into SQL
  • Who wrote the most books?

select dbo.authors.au_fname, dbo.authors.au_lname, count(*) as q_count into #t000

from dbo.titleauthor, dbo.authors

where dbo.titleauthor.au_id=dbo.authors.au_id

group by dbo.titleauthor.au_id, dbo.authors.au_fname, dbo.authors.au_lname

select #t000.au_fname as "First Name", #t000.au_lname as "Last Name",

#t000.q_count as "count"

from #t000

where #t000.q_count=

(select max(t1.q_count)

from #t000 t1)

microsoft eq components
Microsoft EQ Components
  • Authoring tool (a.k.a., domain editor)
    • Creates English query domain
  • COM Automation Server
    • Accepts English, returns SQL commands, requests for clarification or answer
    • Runs on the client- or middle-tier
    • Can be used from any automation controller: Visual Basic, Visual C++®, Visual J++™, VBScript, JScript, ASP
entities relationships

Author

Entity

Relationship

Entity

Entities/Relationships

Who wrote the most books?

english query application

1. User submitsEnglish question

6. Answer displayed to user as HTML table

5.Answer retrieved from server as ADO recordset

2.Question submitted to English Query engine

3. SQL statement(s) returned in EQ response object

4. SQL submitted to server via ADO Conn.Execute

English Query Application

Internet Information Server

ASP application

Browser

SQL Serverdatabase

ADO

English

Query

olap services what is it
OLAP Services - What Is It?
  • New OLAP server for SQL Server
    • Powerful, easy-to-use analysis tools
  • Integration with
    • Windows NT®, SQL Server, Office, third-party products
    • Any OLE DB data source
  • Scalable
    • From desktop to enterprise
olap services provides

Intelligentaggregations

  • Significantly smaller databases for same performance
  • Faster initial and incremental load times

Flexible storagearchitecture

  • Supports MOLAP, ROLAP, and HOLAP equally well
  • Application requirements determine storage

Ease-of-mgmt

  • Lower TCO
  • Broader accessibility of data warehousing

PivotTable

Service

  • Client-side cache - improves performance
  • Client/server architecture for Excel PivotTables
  • Mobile/disconnected analysis support
OLAP Services Provides
flexible architecture
Flexible Architecture
  • SQL 7 OLAP Services supports MOLAP, ROLAP, and HOLAP
  • Users and applications see only cubes
aggregations
Aggregations
  • Precalculated aggregations increase query speed
  • But lead to data explosion
    • Example: OLAP Council Benchmark (APB-1)
      • 20 MB source database
      • Vendor 1: 2.1GB*
      • Vendor 2: 7.5GB
data explosion

Sales

Hardware

Software

Computerproducts

Boston

100

150

250

+

New York

250

100

350

Northeast

350

250

Hierarchy

Data cells: 4

Ratio: 2.25

Aggregation

Agg cells: 5

Data Explosion

600

Data explosion ratio depends on the number of dimensions, the levels of the hierarchies, and the parent-child ratios

how sql 7 olap services handles data explosion
How SQL 7 OLAP ServicesHandles Data Explosion
  • Aggregation wizard finds the “80-20” rule in the data
    • The 20 percent of all possible pre-aggregations that provide 80 percent of the performance gain
    • Analyses level counts for each dimensions and parent-child ratios for each level
pivottable service
PivotTable Service
  • An in-process desktop multidimensional component
    • Shares OLAP server code
    • Leverages OLAP server (where present)
    • Ships with
      • SQL 7, Excel 2000, and Visual Studio
  • Provides
    • In memory data- and query-caching
    • Multidimensional formula engine
    • Local cube persistence
data warehouse process1
Data Warehouse Process

Data characteristics

  • Targeted
  • Specialized (OLAP)
  • Raw detail
  • No/minimal history
  • Integrated
  • Scrubbed
  • History
  • Summaries

Source OLTP

systems

Data marts

Data

warehouse

  • Load
  • Index
  • Aggregation
  • Replication
  • Data set distribution
  • Access and analysis
  • Resource schedulingand distribution
  • Extract
  • Scrub
  • Transform
  • Design
  • Mapping

Meta data

System monitoring

Source: The Enterprise Group, Ltd.