Microsoft SQL Server 2014 business intelligence development beginner's guide : get to grips with Microsoft Business Intelligence and data warehousing technologies using this practical guide

350 Pages·2014·8.137 MB·English

by  Rad, Reza

Microsoft SQL Server 2014
Business Intelligence
Development Beginner's Guide
Get to grips with Microsoft Business Intelligence and data
warehousing technologies using this practical guide
Reza Rad
BIRMINGHAM - MUMBAI
Microsoft SQL Server 2014 Business Intelligence
Development Beginner's Guide
Copyright © 2014 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system,
or transmitted in any form or by any means, without the prior written permission of the
publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the
information presented. However, the information contained in this book is sold without
warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers
and distributors will be held liable for any damages caused or alleged to be caused directly
or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
First published: May 2014
Production Reference: 2220514
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-84968-888-8
www.packtpub.com
Cover Image by Artie Ng ([email protected])
Credits
Author Project Coordinator
Reza Rad Amey Sawant
Reviewers Proofreaders
John Heaton Simran Bhogal
Goh Yong Hwee Maria Gould
Raunak T. Jhawar Ameesha Green
Francesco Quaratino Paul Hindle
James Serra
Indexer
Priya Subramani
Acquisition Editor
James Jones
Graphics
Valentina Dsilva
Content Development Editor
Madhuja Chaudhari Disha Haria
Yuvraj Mannari
Technical Editors Abhinash Sahu
Pragnesh Bilimoria
Taabish Khan
Production Coordinator
Pooja Nair Komal Ramchandani
Copy Editors Cover Work
Sayanee Mukherjee Komal Ramchandani
Aditya Nair
Alfida Paiva
About the Author
Reza Rad has more than 10 years of experience in databases and software applications.
Most of his work experience is in data warehousing and business intelligence. He has
worked with Microsoft technologies from SQL Server 7 and Visual Basic 6 to the latest
versions of SQL Server and .NET. He has a Bachelor's degree in Computer Engineering.
Reza is a DW/BI architect and consultant. He has worked with large enterprises around
the world and delivered high-quality data warehousing and BI solutions for them. He has
worked with industries in different sectors, such as Health, Finance, Logistics, Sales,
Order Management, Manufacturing, Telecommunication, and so on.
Reza has been awarded the Microsoft Most Valuable Professional in SQL Server from 2011
to 2014. He is an international speaker in SQL Server, BI, and DW conferences around the
world. He speaks in SQL PASS Summits, Microsoft TechEd, SQL Saturdays, SQL Server user
groups, and code camps.
Reza has written books on SQL Server and databases. One of his most recent books is SQL
Server 2012 Integration Services: An Expert Cookbook, Packt Publishing.
Reza is a big fan of data-related technologies, his expertise is in EIM, ETL, Data governance, big
data, data warehousing, BI, data visualization, Master Data Management, OLAP, and so on. He
is the author of Webcast series on the RADACAD website (http://www.radacad.com). He
writes articles and blog posts on his technical blog (http://www.rad.pasfu.com); he writes
about most of the Microsoft technologies, such as SSIS, MDS, DQS, SSAS, Power BI, HDInsight,
and so on. His blog contains the latest information on his presentations and publications.
Reza is a Mentor and a Microsoft Certified Trainer. He has been in the professional training
business for many years. He conducts extensive 400 handed-level training for many enterprises
around the world via both remote and in-person training. He is an active member of MSDN
forums, especially those on SQL Server and Power BI. He is also the leader of the New Zealand
Data Warehousing and Business Intelligence user group, which is a registered chapter of PASS.
About the Reviewers
John Heaton graduated top of his class with a Diploma in Information Technology from
Technikon Witwatersrand in South Africa (equivalent to a Bachelor's degree in Computer
Science). He has worked for more than 10 years with Oracle Corporation and has held
various positions, including that of a Practice Manager. He had been co-running the North
Business Intelligence and Warehouse Consulting practice, delivering business intelligence
solutions to Fortune 500 clients. During this time, he steadily added business skills and
business training to his technical background.
In 2005, John decided to leave Oracle and become a founding member in a small business
named iSeerix. This allowed him to focus on strategic partnerships with clients to design
and build Business Intelligence and data warehouse solutions.
John's strengths include the ability to communicate the benefits of introducing a Business
Intelligence solution to a client's architecture. He has gradually become a trusted advisor
to his clients. His philosophy is based on responsibility and mutual respect. He relies on
the unique abilities of individuals to ensure success in different areas and strives to foster
a team environment of creativity and achievement.
Today, John specializes as a Solution / Technical Architect assisting customers in designing large
complex data warehouses. Through the years, he has worked in numerous industries with
differing technologies. This broad experience base allows him to bring a unique perspective
and understanding when designing and developing a data warehouse. The strong business
background, coupled with technical expertise, and his certification in Project Management
makes him a valued asset to any data warehouse project.
John has authored Business Intelligence Cookbook: A Project Lifecycle Approach Using
Oracle Technolog, Packt Publishing.
Goh Yong Hwee is a database specialist, systems engineer, developer, and trainer based
in Singapore. He is a Microsoft Certified Trainer and a Certified Novell Instructor. Some of
the courses that he is authorized to deliver and has delivered include Microsoft SQL Server
Business Intelligence, Microsoft Windows Server, Microsoft SharePoint, Microsoft Exchange
Server, and Suse Linux Enterprise Server. Throughout his training, he has consistently
maintained a Metrics that Matter score exceeding 8 out of 10. He has also been
instrumental in customizing and reviewing his training center's training for its clients.
When imparting knowledge, his objective has been to make technologies easy and simple
for everyone to learn. His no-frills approach to training has gained him recognition over the
years from both clients and employers, where his clinching of the Best Instructor Award, an
accolade conferred by his employer, bore testimonial. He has been in the training industry
for five years, and prior to that, he was associated with a Japanese MNC in Singapore
as a Systems Analyst, specializing in data warehousing on Microsoft SQL Server, RPG
programming on the IBM iSeries, and BPCS ERP. Over the years, he has chosen to focus his
work and specialization on Microsoft SQL Server and is currently in full-time employment
with a Fortune 500 company in Singapore, taking up the specialist, consultancy, developer,
and management roles.
Raunak T. Jhawar is a graduate in Computer Science from the University of Pune and has
more than five years of experience working as a software professional working with BI, data
visualization, and Hadoop.
Raunak is presently working with Aditi Technologies in Bangalore as a Technical Leader,
working with clients and consulting them for their BI and analytics engagements.
Francesco Quaratino has been enjoying working with SQL Server since 2000, as either a
Developer or an Administrator. He currently leads an ambitious BI project for Betgenius Ltd.
in London, where he lives with his family. The last certification he achieved was MCSE: 2012
Data Platform.
James Serra is an independent consultant with the title of Data Warehouse/Business
Intelligence Architect. He is a Microsoft SQL Server MVP with over 25 years of IT experience.
He started his career as a software developer, and then he was a DBA for 12 years. For the
last seven years, he has been working extensively with Business Intelligence using the SQL
Server BI stack. He has been a permanent employee, consultant, contractor, and owner of
his own business. All these experiences, along with continuous learning, have helped him to
develop many successful data warehouse and BI projects. He is a noted blogger and speaker,
having presented at the PASS summit and the PASS Business Analytics Conference.
James has earned the MSCE: SQL Server 2012 Business Intelligence, MSCE: SQL Server 2012
Data Platform, MCITP: SQL Server 2008 Business Intelligence Developer, MCITP: SQL Server
2008 Database Administrator, and MCITP: SQL Server 2008 Database certificates. He has
a Bachelor of Science degree in Computer Engineering from UNLV.
James resides in Houston, TX with his wife Mary and three children, Lauren, RaeAnn,
and James.
This book is dedicated to my wonderful wife Mary and my children
Lauren, RaeAnn, and James, and my parents Jim and Lorraine. Their love,
understanding, and support are what made this book possible. Now, if they
only understood the content!
www.PacktPub.com
Support files, eBooks, discount offers, and more
You might want to visit www.PacktPub.com for support files and downloads related to your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files
available? You can upgrade to the eBook version at www.PacktPub.com and as a print book
customer, you are entitled to a discount on the eBook copy. Get in touch with us at service@
packtpub.com for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range
of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
TM
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library.
Here, you can access, read and search across Packt's entire library of books.
Why subscribe?
‹ Fully searchable across every book published by Packt
‹ Copy and paste, print and bookmark content
‹ On demand and accessible via web browser
Free access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib
today and view nine entirely free books. Simply use your login credentials for immediate access.
Instant updates on new Packt books
Get notified! Find out when new books are published by following @PacktEnterprise on Twitter,
or the Packt Enterprise Facebook page.
Table of Contents
Preface 1
Chapter 1: Data Warehouse Design 7
Understanding Business Intelligence 7
The architecture and components of a BI system 9
The data warehouse 9
Extract Transform Load 10
Data model – BISM 10
Data visualization 12
Master Data Management 12
Data Quality Services 12
Building the data warehouse 13
Dimensional modeling 14
Fact or measure 15
Dimension 15
The Fact table 15
Grain 15
The star schema 16
An example of Internet sales 16
FactSales 18
The customer dimension 19
DimDate 20
DimProduct 22
DimStore 22
Types of facts 22
The Factless Fact table – The Bridge table 23
Types of dimensions 23
Slowly Changing Dimension 24
SCD type 0 24
SCD type 1 25
SCD type 2 25
Summary 26