Transcript SQL Server & High Availability - E

By George Squillace
New Horizons of Michigan
v2
MCT, MCSE, MCDBA,
MCITP – Database Administration,
MCITP – Enterprise Messaging,
MCITP – Windows Server 2008 Enterprise Administrator,
MCTS – Vista Deployment,
CompTIA A+, Network+
Definition:
What is Business Intelligence?

From Wikipedia:
Business intelligence (BI) is
a business management term
which refers to applications and technologies
which are used to gather, provide access to, and analyze data and
information about company operations.
Business intelligence systems can help companies have a more
comprehensive knowledge of the factors affecting their business,
such as metrics on sales, production, internal operations, and
they can help companies to make better business decisions.
Three Categories
of Business Intelligence Functionality
in Microsoft SQL Server 2005 & 2008
 Reporting Services (SSRS)
 Analysis Services(SSAS)
 Integration Services (SSIS)
Other Microsoft BI Facts of Interest
 The BI capabilities
within Microsoft SQL Server
are included in the purchase price
 The BI capabilities are created within
Business Intelligence Design Studio
(“BIDS”) which is Visual Studio
capable of BI-specific projects.
Types of Business Intelligence
Projects available within BIDS
SQL Server Reporting Services (SSRS)
Overview

Requires Internet Information Services (IIS) (only
supported on Windows)
Users connect to RS through a browser and obtain
one or more report(s)
 Supported Report Formats are:







Webpage/HTML
Acrobat (pdf)
Excel
Text file
XML
Others
SQL Server Integration Services (SSIS)
Overview

The Bulk of SSIS Is Used for “ETL”, which is:
 Extract, copy data from a source
 Transform, (optional), alter the data in some way
 Load, send the data to a destination



Very common requirement for database administrators
to perform ETL
To and from other SQL Servers and Excel, Access, text
files, and many other database platforms
SQL 2000 Server had a component, Data Transformation
Services (DTS), that performed ETL functions, but SSIS
and DTS are very different
SQL Server Analysis Services (SSAS)
Overview – Part 1




The Point: Better Decision Making and Forecasting
Usually involves a specially designed (often humongous)
database, populated through SSIS to perform advanced data
analysis and data mining.
Involves creation of a Data Mart, or even larger, a Data
Warehouse
Other relevant terms:
 OLAP (Online Analytical Processing), as opposed to OLTP (Online







Transaction Processing
Dimension
Measure
Cube
Data Mining
Decision Support
Star Schema & Snowflake Schema
Slice & Dice, ie, Males, Married, w/Children under 13, w/College
Degree, > 40 years old, Paid by VISA in 2007, ordered Product X…
Analysis Services Overview – Part 2



Non-trivial to design, populate, and implement
Involves the use of a client tool, such as Excel and its
Pivot Table capabilities
Involves learning additional query languages such as:
 Multi-Dimensional Expressions (MDX)
 Data Mining Extensions (DMX)
 XML for Analysis (XMLA)

Again, this component is included with the purchase of
Microsoft SQL Server but costs $10,000 or more for
other platforms like Oracle
OLAP Sales Cube (only three dimensions)
Reference: http://www.research.ibm.com/journal/sj/414/colos2.jpg
OLAP Cube,
Example 2, Sliced & Diced across three different dimensions
“Zeit” = time, “Sicht” = view
Reference: http://www.mybiq.com/products/olap-cube-views/image_preview
Pivot Table (Client Tool)
http://www.mosha.com/msolap/images/XAML_PivotTable.png
Design Courses v.
Implementation Courses:

Implementation Courses
 Hands-on oriented
 Procedural
 Students depart having had success completing a related
series of steps

Design Courses
 Asked during class, “What questions do I need answers
to?”
 What decisions must I make?
 Conceptual, minimally or rarely procedural
 Student participation and involvement (when
experienced) is a big value to other students
2791A Implementing and Maintaining
Microsoft® SQL Server 2005
Analysis Services (SQL 2008, Course 6234A)
•Describe how SQL Server Analysis Services can be used to implement analytical
solutions.
•Create multidimensional analysis solutions with SQL Server Analysis Services.
•Implement dimensions in an Analysis Services solution
•Implement measures and measure groups in an Analysis Services solution.
•Query a multidimensional Analysis Services solution.
•Customize an Analysis Services cube.
•Deploy and Secure an Analysis Services database.
•Maintain a multidimensional Analysis Services solution.
•Implement a Data Mining solution.
2792A Implementing and Maintaining
Microsoft® SQL Server 2005 Integration
Services (SQL 2008, Course 6235A)
First, what was the name of that game…
Per Hasbro…”Find out in this fun-filled game of
crazy mechanical gadgets and chain reactions!”
Go to the last slide for the answer…
(hit the END key)
Slide 11
2792A Implementing and Maintaining
Microsoft® SQL Server 2005
Integration Services
•Describe SQL Server Integration Services and its tools.
•Create an Integration Services package.
•Implement control flow in an Integration Services package.
•Implement data flow in an Integration Services package.
•Implement logging in an Integration Services package.
•Debug and implement error handling in an Integration Services package.
•Implement checkpoints and transactions in an Integration Services package.
•Deploy an Integration Services package.
•Manage and secure an Integration Services package.
Slide 10
2793A Implementing and Maintaining
Microsoft® SQL Server 2005
Reporting Services (SQL 2008, Course 6236A)
Describe SQL Server Reporting Services and its components.
 Create a Reporting Services report.
 Enhance a Reporting Services report.
 Create and manipulate data sets.
 Use report models to implement reporting for business users.
 Configure report publishing and execution settings.
 Implement subscriptions for reports.
 Administer Reporting Services.
 Implement custom Reporting Services applications.

2794A
Designing a Business Intelligence Solution
for the Enterprise
Using Microsoft® SQL Server 2005 (2 days)
•Capture the business and technical requirements for a
Business Intelligence solution architecture.
•Describe the major stages and requirements of a Business
Intelligence project lifecycle.
•Determine Business Intelligence development requirements
and implement a Business Intelligence development
project.
•Assess and design a Business Intelligence infrastructure.
•Describe and plan Business Intelligence operations and their
management.
2795A Designing an ETL Solution Architecture
Using Microsoft® SQL Server 2005
Integration Services (2 days)
•Plan data transfer and staging solutions for an ETL operation.
•Plan an SSIS Solution.
•Design and implement data flows.
•Incorporate logging, error handling, and reliability into a package.
•Optimize an SSIS solution.
•Deploy and operate an SSIS solution.
2796A Designing an Analysis Solution Architecture
Using Microsoft® SQL Server 2005 Analysis Services
(2 days)
•Capture the business and technical requirements for an analysis solution
•Design and implement a logical Online Analytical Processing (OLAP) solution
architecture.
•Design physical storage for a multidimensional solution.
•Create calculated members and named sets.
•Implement Key Performance Indicators (KPIs), actions, and stored procedures.
•Design the infrastructure for an OLAP solution.
•Deploy and secure an Analysis Services solution in a production environment.
•Monitor and optimize an Analysis Services solution.
•Implement a data mining solution.
2797A Designing a Reporting Solution
Architecture Using Microsoft SQL Server 2005
Reporting Services (2 days)
•Design a reporting solution.
•Develop a reporting solution.
•Collaborate with application developers to complete reporting
and analytical interfaces.
•Deploy and manage a Reporting Services solution.
•Design security for a Reporting Services solution.
•Optimize report execution performance.
2778A
Querying Microsoft SQL Server 2008
Database (3 days)
•Get started with databases and Transact-SQL.
•Perform basic queries.
•Group and summarize data.
•Join data from multiple tables.
•Work with subqueries.
•Modify data.
•Query metadata, XML, and full-text indexes.
•Use programming objects for data retrieval.
•Use advanced querying techniques.
Other (General) Resources

Microsoft
 www.microsoft.com/sql
 www.microsoft.com/learning
 www.microsoft.com/technet

www.e-Squillace.com







/tech/
/tech/techdiagrams
/tech/techreference
/tech/techreference/sql
/tech/screenshotgallery
List of my database books
User Groups
 West Michigan SQL ServerUser Group (WMSSUG)
 Detroit Area SQL ServerUser Group, www.sqlsig.org
What Are Your Action Items?
What do I need to learn?
 What do I need to read?
 What do I need to attend?
 What questions do I need answered?

SSIS reminds
me of this…
(Type “15” and hit “ENTER” to Return)