|
Introduction
Business Intelligence (BI) solutions provide the data warehouse infrastructure that enables organizations to make better, faster decisions about their customers, partners, and operations by turning mountains of data into valuable business information that’s always at the fingertips of decision makers. SQL Server 2005 is a complete, integrated database / data warehouse / business intelligence platform
that provides the features, tools and functionality to design, build and manage entire enterprise BI systems.
This class teaches the student how to design, build and deploy practical business intelligence
solutions using the SQL Server 2005 DB/DW/BI platform. The student will learn how to use SQL Server 2005 to:
- Analyze: Use Analysis Services to gain an integrated view of your business data for reporting, OLAP cube analysis, dashboards, Key Performance Indicator (KPI) scorecards, and data mining. Learn how to deliver on rich and powerful analytics for your organization.
- Integrate: Use Integration Services to integrate data from OLTP operational and enterprise data
sources into a data warehouse. SSIS provides out-of-the-box enterprise-class scalability options with its advanced data-integration pipeline architecture, high-performance processing, and native support for 32-bit/64-bit platforms.
- Report: Use Reporting Services to deliver the business intelligence an enterprise needs to make better
business decisions. Leverage powerful design tools to author reports accommodating any reporting need. Empower every business user with interactive, personalized, and relevant reports or simply enable them to build their own reports. Enjoy central manageability of the enterprise reporting platform.
Course Objectives
The student will learn:
- Microsoft's Business Intelligence vision: BI for the Masses
- How to use Analysis Services to create a multi-dimensional OLAP cube
- How to use Integration Services to transfer data and perform ETL processing to populate a data warehouse
- How to use Reporting Services to deliver enterprise BI reporting
- How to setup a star & snowflake schemas data warehouse
- How to provide advanced analytic functionality including dashboard and multi-dimensional interactive reports
- How to design models for Report Builder, the end-user ad-hoc reporting tool
- How to browse data mining cubes from Excel 2007
Duration
4 days
Format
Lecture with projection screen and optional hands-on using student's own laptop
Audience
Business Intelligence architects, DBA-s and developers, IT & project managers
Prerequisites
Windows desktop navigation, SQL concepts (SQL Server, Sybase, Oracle, or DB2), Business Intelligence concepts, relational database concepts, dimensional modeling concepts
Course Materials
Course materials are yours to keep. You will be provided with the following software a few days after placing the registration order:
- BI TRIO 2005 on 21 CD-ROMs
Course Content
- Microsoft's Business Intelligence vision: pervasive BI
- SQL Server 2005 Business Intelligence components
- Design, development and management tools
- Data Warehousing and Data Mining processes
B. Analysis Services (SSAS)
- Data Warehousing concepts
- Star and snowflakes schemas
- Measures(fact table) and dimensions
- Storage and grouping based aggregations
- Joining fact table to dimension tables
- Data sources & data source views
- Measures & measure groups
- Dimensions
- Surrogate keys & business keys
- MDX calculations
- Key Performance Indicators (KPIs)
- Actions
- Perspectives
- Translations
- The Cube Wizard
- Advanced dimension features
- Parent-child dimensions
- Multi-level hierarchies
- Attribute relationships
- Slowly, rapidly & never changing dimensions
- Working with cubes & advanced cube features
- Calculations
- KPIs
- Actions
- Extending cube functionality
- Partitions & proactive caching
- Partitioning cubes for efficient storage
- Proactive caching
- Automating administration/processing
- Deploying cubes to SSAS servers
- Security on cubes
- Managing SSAS servers
- Building mining models
- Discovering patterns in data
- Integrating mining algorithms with business needs
- Architecting predictive models
- Applying the Data Mining Wizard
- Using Excel 2007 as mining structure browser
C. Integration Services (SSIS)
- Data warehouse ETL processing theory
- Objects & concepts: looping, control flow, data flow
- Using BIDS to create SSIS Project & Packages
- Working with control flow tasks
- Working with data flow transformations
- Sending data to multiple destinations
- Dimension table processing theory
- Using loops with control flow tasks
- Slowly Changing Dimension (SCD) wizard
- Fact table processing theory
- Using variables in control flow tasks
- Modular design basics
- Checkpoint/restart architecture
- Executing SQL statements
- Execution & scheduling
- Package scheduling
- Notifications, configurations & deployment
- Event handling & logging
- Sending emails
- Configurations & deployment
- Automatic execution notification
- Data cleansing basics
- Conditionally splitting data
- Merging data
- Fuzzy lookup and fuzzy grouping
D. Reporting Services (SSRS)
- Creation, management, delivery
- Report Designer
- Report Builder
- Designer vs. Builder
- Report Wizard
- Reporting portal options
- Headers & footers
- Paging & sorting
- Using subtotals and formulas
- OLAP reporting
- Components & features
- Creating reports
- Table reports
- Matrix reports
- OLAP cube reports
- Interactivity – Parameters, Filters, Drilldown, Drillthrough
- Dashboard reports
- Components & features
- Report models
- Creating a report model
- Creating reports
- Building a Chart Report
- Automating report execution
- Security
- Deployment
- Report execution log
- Subscriptions
- Creating snapshot reports
- Using report caching
- Rendering in Excel, XML, or PDF
|
