You may have used SSRS in the past or you know what SSIS is, but BI is much more than just the
tools. Until you come full circle on all aspects and best practices of BI
you’ll struggle in your BI projects. This is why we created the BI Boot Camp.
It provides the training of 4 days in 2 days. Rushabh Mehta, MVP SQL
Server and an independent consultant with Solid Quality Mentors, designed this
Boot Camp with 11 labs where you can learn all aspects of BI and the Best
Practices. If you are doing web sites with SharePoint and MOSS you will be
pleased by this boot camp because SharePoint is often the front-end for most BI
solutions.
Business Intelligence is one of the topmost IT priorities and is increasingly
pervasive within organizations large and small. Business Intelligence touches
many aspects of our daily lives, from enhancing our online and retail shopping
experience to the healthcare we receive. BI also impacts the quality of
decisions that business leaders make. For businesses, BI is no longer a luxury,
but a necessity to survive in an increasingly competitive marketplace.
This 2 day boot camp has been designed to give you a head start in Business
Intelligence practice. You will acquire fundamental knowledge of Business
Intelligence and dimensional modeling along with understanding on how to use
the various Microsoft technologies to implement a BI solution. These
understanding of these technologies will be refined by the hands-on labs
through which you will build a complete BI solution from ground up by the end
of day 2. You are not required to have deep knowledge of BI or any of the
Microsoft SQL Server 2005 and 2008 technologies that we will use to implement a BI
solution. However, a basic understanding of BI and a good foundation of
relational database theories and T-SQL is essential.
As we go through each module outlined, we will also spend a brief amount of
time discussing best practices.
Introduction to Business Intelligence
We will provide a brief introduction to Business Intelligence and the need and
value of Business Intelligence in an organization. We will also discuss the
various components of a Business Intelligence solution and introduce the
various technologies that make Business Intelligence possible.
Preparing Data for Analytics
In this topic we will cover Dimensional Modeling concepts including Fact and
Dimension tables and how to implement a dimensional model within your
organization. We will also address the needs for implementing dimensional
models and talk about Data Warehousing and Data Marts. We will also briefly
discuss processes for loading data into these dimensional tables.
Introduction to ETL Process and Techniques
This module will provide an insight into ETL processes. We will also discuss
overall process planning, data discovery process, data cleansing, reference
tables and staging requirements.
Integration Services Basics – Implementing Workflows in SQL Server 2008
Integration Services
Starting with an Introduction to SSIS fundamentals and the productive
development tools for visually creating and debugging Integration Services
packages, we will quickly dive down into the control flow element of SSIS where
we will look at the different features that enable us to create workflows that
form the basic framework of our ETL processes. Here you will learn about tasks,
containers, variables and constraints. You will also learn about managing
connections through the connection manager.
Advanced Features in Microsoft SQL Server Integration Services
Building upon the control flow features, we will look at some advanced features
such as SSIS Expressions, Event Handlers and also a few important package
properties that you will find useful in the development of dynamic packages.
Processing Data using Microsoft SQL Server Integration Services
One of the most exciting features of Integration Services is the Data Flow
component. This is a high performance data transformation engine that can be
used to implement complex transformation routines to data. The data flow
component allows you to efficiently pull data from multiple data sources and
load transformed data to multiple destinations in a variety of different
formats. The transformation components allow you to efficiently transform this
data to desired shape and error handling capabilities of the data flow can help
isolate bad data for additional processing. We will look at how to implement
data flows to transform our data for loading our data marts.
Introduction to Microsoft SQL Server Analysis Services
Here, we will introduce key concepts of Analysis Services and the Unified
Dimensional Model (UDM) and introduce the design, development and management
tools for Analysis Services. We will also see how to build our first cube,
deploy it and analyze information in this cube. As we walk through the demos,
we will look at Data Sources and Data Source Views and their features and also
review the cube wizard. We will also discuss deployment and processing of the
Analysis Services project.
Customizing the Cube, Dimensions and Measure Groups
We will look at various properties of the cube, dimensions and measure groups
and understand how these properties impact analysis and user experience. We
will also cover dimensional hierarchies and member properties of attributes. We
will also cover the concepts of role playing dimensions and the different types
of relationships between cube dimensions and measure groups.
Advanced Analysis Services Features
This topic will look at some advanced features in Analysis Services that extend
the value of the information by using Calculations, defining KPI’s and adding
Actions.
Reporting against the UDM Using SQL Server 2008 Reporting Services
This topic is an introduction to Reporting Services. We will see how to create
reports against the UDM. We will also look at creating parameters as well as
other features of Reporting services to format these reports and make them
dynamic. We will also learn to deploy these reports.
Advanced Reporting against the UDM
This topic will cover some other key technologies that can be used by end-users
to report against this data. We will see how to expose the UDM to end users for
self service reporting using Report Builder, use pivot tables in Excel to
report against the UDM and also highlight some features in Excel 2007 that make
data analysis easier for the end user. Finally we will briefly discuss other
technologies for reporting against the UDM including ProClarity, Excel Services
and MOSS, Business Scorecard Manager and Performance Point Server.
Labs
The labs will be built on Adventure Works LT which is a scaled back version of
Microsoft’s Adventure Works sample database. Using this relational database as
our transactional source, we will design a data mart model for analysis, create
ETL packages that will transform this data and load the data mart, design a
Unified Data Model to expose this data for multi-dimensional analysis and
finally report against this OLAP data store in a variety of formats. The labs
are designed to give you a sense of how to apply the different Microsoft
technologies to build complete BI solution. This course or labs are not
intended to be a comprehensive education in implementing and using these
technologies, because there are many facets of these technologies that the time
constraint of this course does not permit us to go into. However, you will walk
away with a very good idea on the basics of BI and these technologies that
enable BI.
