Transcript SQL Server & High Availability - E

SQL Server 2005
Business Intelligence:
Courses & Background
By George Squillace
New Horizons of Michigan
MCT, MCSE, MCDBA
MCITP – Database Administration
MCITP – Enterprise Messaging
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
• Reporting Services
• Analysis Services
• Integrations Services (SSIS)
Other Microsoft BI Facts of Interest
• The BI capabilities within SQL Server
are included with the price of MSSQL
• The BI capabilities are configured
within “BIDS”, or Business
Intelligence Design Studio, which is a
subsection of Visual Studio included
with SQL.
Reporting Services (RS) 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:
•
•
•
•
•
•
.pdf
Excel
Webpage/HTML
Text file
XML
Others
SQL Server Integration Services (SSIS)
Overview
• The Bulk of SSIS Is Used for “ETL”
• Extract
• Transform
• Load
• 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
Analysis Services Overview –
Part 1
• The Point: Better Decision Making
• 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: Cube, Dimension, OLAP, Data
Mining, Decision Support, Star Schema, Snowflake
Schema
• Dimensions, ie, Males, Married, w/Children under 13,
w/College Degree, > 40 years old, Paid by VISA, ordered
Product X…
Analysis Services Overview –
Part 2
• Non-trivial to design and implement
• May involve the use of 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
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
•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.
Available ???
2792A Implementing and
Maintaining Microsoft® SQL
Server 2005 Integration Services
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.
• Available
Slide 10
2793A Implementing and
Maintaining Microsoft® SQL
Server 2005 Reporting Services
•
•
•
•
•
•
•
•
•
•
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.
Available ???
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.
• Available now
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.
• Available now
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.
• Available now
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.
• Available now
2778A Querying Microsoft SQL
Server 2005 (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.
• Available ???
Other (General) Resources
•
•
•
•
www.microsoft.com/sql
www.microsoft.com/learning
www.microsoft.com/technet
www.e-Squillace.com
•
•
•
•
/tech/
/tech/techdiagrams
/tech/techreference
/tech/techreference/sql
• User Groups
• West Michigan SQL ServerUser Group (WMSSUG),
[email protected]
• Detroit Area SQL ServerUser Group, www.sqlsig.org
What Are Your Action Items?
SSIS reminds me
of this…
(Type “11” and hit “ENTER” to Return)