Transcript TechNet Event Session

TNQ400-13
How To Use Office 2000
Features In Data Warehousing
Michael L. Flakus
Senior Consultant
BEST Consulting
Session Prerequisites

This session assumes that you
understand the fundamentals of:



Basic knowledge of Office 2000
Basic knowledge of
Microsoft® SQL Server™ 7.0
This is a level 200 session
What You Will Learn Today



Using Excel 2000 PivotTable and
PivotChart to access
Multi-Dimensional data
Using PivotTable and Chart Office Web
Components (OWC) to access MultiDimensional data
Overview of Microsoft SQL Server
OLAP services and functionality
Definition Of Terms






OLTP – On-Line Transaction Processing
OLAP – On-Line Analytical Processing
OWC – Office Web Components
OLAP Cube
OLAP .cub file
MDX – Multi-Dimensional Extensions
Data Warehouse Framework
Data Warehouse/Data Mart Design
Dir
Data
Data Marts or
Transform/
Cleansing
Data
Warehouse
Operational
Sources
Schema
Using
Information
Managing
Building
Transform
Schedule
Repl
End-User Tools
InfoPublish
OLAP
Repository (Persistent Shared Meta -Data)
Data Warehouse Management
Data Flow
Meta -Data Flow
Source: Microsoft SQL Server 7.0 Data Warehousing Framework
http://www.microsoft.com/sql/bizsol/datawareframe.htm
Software And Components

Office 2000



Excel Microsoft Query



Excel PivotTable and PivotChart
Office Web Components (OWC) PivotTable
and Chart
Used to create or open connection to data source
An optional Microsoft Office component
Internet Explorer (IE) 4.01 or greater (OWC do
not work with Netscape Navigator)
Software Installation Tip

Include Microsoft Query in standard
Office 2000 installation for users who
will be creating or opening database
connections
OLAP Data Source Options

OLAP Service Provider


Microsoft OLAP Provider
Third-party OLAP Provider (conform
to OLE-DB for OLAP standard and be
Office compatible)
OLE DB for OLAP Web Site:
http://www.microsoft.com/data/oledb/olap/default.htm

Offline Cube files (.cub)
About Offline Cube Files

Offline cubes files (.cub) from
OLAP data







Can be created using Excel
Include as subset or all data
Allow users to continue working with
data when disconnected from network
Can be updated when reconnected
Generally, provide slower performance
Do not include calculated measures
May not be supported by third-party
OLAP providers
Creating A Data Source


To connect to OLAP data, you
create a data source using
Microsoft Query
Microsoft Query is invoked either;


While using the PivotTable and
PivotChart wizard
Or, directly from the Excel 2000 menu
PivotTable Interaction
With OLAP Data Sources
Excel
2000
Client
MDX
Query
Internet
Explorer
Client
Data Set
Results
MDX
Query
OLE DB for OLAP Provider
OLAP Data
Source
Data Set
Results
Today’s Demo Scenarios



Northwind sales representatives need
to analyze company sales data
The Northwind IT department needs to
broadly, easily, and securely provide
access to the OLAP database
Overview of the process used to
create an OLAP database
Demo 1


Scenario – Northwind sales
representatives need to analyze
company sales data
Solution – Provide interactive tools
that allow users to view and explore
OLAP data
PivotTable Options –
OLAP



Note “Save data
with table layout”
is not available
Enabling
“Refresh on
open” is
suggested
Disable “Save
password”
PivotTable Options –
Non-OLAP

For best performance,
leave these two Excel
PivotTable Options to
default values as
follows:



“Save table with data
layout” enabled
“Refresh on open”
disabled
Consider disabling
“Save password”
®
Windows NT
Integrated Security


OLAP uses only Windows NT
integrated security
Offline .cub files do not have
this protection
Best Practices



OLAP Data sources provide best
performance for large or frequently
refreshed data sets
Selecting and linking data in an
OLAP data Source is not required
Offline Cube files do not use
Windows NT Security
Pop Quiz

What is Microsoft Query used for?
A: Connecting to an OLAP data cube

Why do OLAP data sources generally
provide better performance with large
or frequently accessed data sets?
A: They use a client / server architecture
and return only the selected data
OWC PivotTable
And Chart
Office Web Components

Office Web Components (OWC) –
OWC are included in Office 2000 and behave
similarly to other office applications, but
operate in an internet environment
These components include:
 The Spreadsheet Component

The PivotTable Component

The Chart Component

The Data Source Component
Demo 2


Scenario – Interest in Northwind’s
OLAP data has grown within the
company; The IT department needs
to broadly, easily, and securely
provide access to the OLAP database
Solution – Use the Web to distribute
PivotTable and Chart views of
OLAP data
OWC And Office 2000






No technical dependency
Office 2000 does not need to be
installed before deploying OWC
Can coexist with Office 95 and 97
OWC components are licensed with
Office 2000
Compatible with Office
Can be deployed before Office 2000
OWC Component Install



Create an Office 2000 Install Image
using setup.exe /a or the ORK that
installs the OWC
Set Codebase Attribute to
MSOWC.CAB to call the Web installer
control
For more information see Chapter 12
of Programming Microsoft Office 2000
Web Components by Dave Stearns
(MS Press, 1999)
Pop Quiz

How are Office Web Components
Licensed?
A: With Office 2000

Which components are used to
access OLAP data?
A: PivotTable and Chart

Do the component require Office 2000
to be installed on the workstation?
A: No, they can be installed independently
Data Warehouse/Data
Building Mart Design
Dir
Data
Data Marts or
Transform/
Cleansing
Data
Warehouse
Information
Managing
Overview Of Microsoft SQL Server
OLAP Services And Functionality
Operational
Sources
Schema
Transform
Schedule
Repl
Using
End-User Tools
InfoPublish
OLAP
Repository (Persistent Shared Meta -Data)
Data Warehouse Management
Data Flow
Meta -Data Flow
Northwind OLTP Schema
Northwind Star Schema
OLAP Sales Cube
OLAP Process
Cube
Building





Design
Extract
Transform
Load
Process
Query
Tools



OLAP
Server
OLTP
Sources

Classic
Reports
PivotTable
PivotChart
Security
Demo 3


Scenario – Produce OLAP multi-dimensional
database from OLTP data source
Solution – Provide tools to enable the
creation of multi-dimensional databases
OLAP Database Backup
And Recovery


The entire database can be
backed up with standard file
server backup routines
OLAP Add-in Package allows the
backing up and restoring of
individual OLAP database
Best Practices


Clearly communicate to users the
structure, origin, and content of the
source data
Learn techniques to improve build and
viewing performance

OLAP Services: Performance Implications
of the Architecture
http://msdn.microsoft.com/library/backgrnd/html/olapperf.htm
Pop Quiz

What does OLAP represent?
A: Online Analytical Processing

What happens in the back room of the
process?
A: Data is extracted, transformed, and
loaded into a multi-dimensional schema

What happens in the front room of the
process?
A: Data is presented in a secure manner to
the end-user for analysis
Why OLAP Services And
Office 2000?
Flexible
storage
architecture


Intelligent
aggregations

Ease-ofmgmt

PivotTable
Service




Supports MOLAP, ROLAP, and HOLAP equally well
Application requirements determine storage
Significantly smaller databases for same performance
Faster initial and incremental load times
Lower TCO
Broader accessibility of data warehousing
Client-side cache - improves performance
Client/server architecture for Excel PivotTables
Questions And Answers
For More Information

Refer to the TechNet website at


Microsoft® Official Curriculum


MOC website: on
www.microsoft.com/train_cert/
Visit our Technology Center on SQL
Server


www.microsoft.com/technet/
www.microsoft.com/TechNet/SQL
IT Professionals User Groups

www.microsoft.com/technet/usergroup/default.asp
Suggested Reading



Programming Microsoft Office 2000 Web
Components by Dave Stearns, (Aug 1999),
Microsoft Press; ISBN: 073560794X
Microsoft OLAP Unleashed
by Tim Peterson, Jim Pinkelman, Bob Pfeiff,
(Nov 1999) Sams; ISBN: 0672316714
Microsoft OLAP Solutions by Erik Thomsen,
George Spofford, Dick Chase, (Jul 1999)
John Wiley and Sons; ISBN: 0471332585
Suggested Reading




The Data Warehouse Toolkit by Ralph Kimball,
(Feb 1996), John Wiley and Sons, ISBN 0471153370
The Data Warehouse Lifecycle Toolkit by Ralph
Kimball, Laura Reeves, Margy Ross, Warren
Thornwaite, (Aug 1998), John Wiley and Sons,
ISBN 0471255475
Data Warehouse Design Solutions by Christopher
Adamson, Michael Venerable, ( Jul 1999) John
Wiley and Sons; ISBN: 047125195X
The Data Webhouse Toolkit by Ralph Kimball,
(Jan 2000), John Wiley and Sons, ISBN 0471376809
Session Credits



Author: Michael L. Flakus
Producer/Editor: Mike McWilliams
Thanks to Our Microsoft Technical Field
personnel who reviewed this session:


Joel Bubeck
Mark Steel
TNQ400-13