Business Intelligence (BI) is one of the hottest topics nowadays in the Information Technology field. Many companies and organizations intend to utilize a BI system to solve problems and help decision makers make decisions. This high demand for BI systems has raised the number of job openings in this field.
The following is Gartner's definition of Business Intelligence (http://www.gartner.com/it-glossary/business-intelligence-bi/):
Business Intelligence (BI) is an umbrella term that includes the applications, infrastructure and tools, and best practices that enable access and analysis of information to improve and optimize decisions and performance.
There are various reasons to have a BI system in place, but helping decision makers to make better decisions is one of the main purposes of BI. As an example, a director of a manufacturing company would like to understand the trend of sales in past months (or years) on specific products. This trend would be helpful for him to decide any changes in that product or to create some other editions of that product. A bank directory might like to use data mining solutions to distinguish suspicious or fraudulent transactions. A board of directors would be interested to see Key Performance Indicators (KPIs) of their business.
BI could help in all the scenarios mentioned here and many more. A BI system usually uses a data warehouse as a core tool. The data warehouse is an integrated dimensional data structure. Data from a variety of sources will be fed into the data warehouse and some data quality and governance would be applied on the data. The dimensional model of data warehousing is optimized for reporting and analysis, so data visualization tools can directly query against the data warehouse. Another layer of modeling might be added to the BI architecture, OnLine Analytical Processing (OLAP), or the tabular model. These models will improve data access in terms of speed and performance of queries. BI systems have one or more data visualization frontends that will be the GUI for the end user.
In this book, we will go through the BI architecture and explore the Microsoft technologies that can implement and deliver BI solutions.
Chapter 1, Data Warehouse Design, explains the first steps in thinking and designing a BI system. As the first steps, a developer needs to design the data warehouse (DW) and needs an understanding of the key concepts of the design and methodologies to create the data warehouse.
Chapter 2, SQL Server Analysis Services Multidimensional Cube Development, explains how an OLAP multidimensional structure is required to provide fast query response and aggregated data from a data warehouse. In this chapter, readers will learn what OLAP provides and how to design OLAP with Microsoft SSAS Multidimensional.
Chapter 3, Tabular Model Development of SQL Server Analysis Services, explains that SSAS Tabular is a new method of presenting data in Microsoft BI 2012 and is very useful in small BI systems and when a developer wants to create POC. In this chapter, the reader will learn about SSAS Tabular and how to use it in BI projects.
Chapter 4, ETL with Integration Services, describes how ETL is an operation of transferring and integrating data from source systems into the data warehouse. ETL needs to be done on a scheduled basis. In this chapter, readers learn how to think about ETL processes and use SSIS to perform data transfers.
Chapter 5, Master Data Management, guides readers on how to manage reference data. Master Data Management (MDM) is very critical in all integrated systems, especially in BI and data warehouse. In this chapter, the reader will learn how to use Master Data Services (MDS) to implement an MDM solution.
Chapter 6, Data Quality and Data Cleansing, explains that data quality is one of the biggest concerns of database systems. The data should be cleansed to be reliable through the data warehouse. In this chapter, readers will learn about data cleansing and how to use Data Quality Services (DQS), which is one of the new services of SQL Server 2012, to apply data cleansing on data warehouse.
Chapter 7, Data Mining – Descriptive Models in SSAS, provides a descriptive model on historical events. In this chapter, readers will understand data mining concepts and how to use data mining algorithms to understand the relationship between historical data, and how to analyze it using Microsoft technologies.
Chapter 8, Identifying Data Patterns – Predictive Models in SSAS, focuses on predicting future outcomes based on a pattern recognized in the existing data. In this chapter, readers will become familiar with algorithms that help in prediction, and how to use them and customize them with parameters. Users will also understand how to compare models together to find the best algorithm for the case.
Chapter 9, Reporting Services, explores Reporting Services, one of the key tools of the Microsoft BI toolset, which provides different types of reports with charts and grouping options. In this chapter, readers will learn when and how to use SSRS to create and design reports from data warehouses, SSAS Multidimensional, or SSAS Tabular.
Chapter 10, Dashboard Design, describes how dashboards are one of the most popular and useful methods of visualizing data. In this chapter, readers will learn when to use dashboards, how to visualize data with dashboards, and how to use PerformancePoint and Power View to create dashboards.
Chapter 11, Power BI, explains how predesigned reports and dashboards are good for business users, but power users require more flexibility. Power BI is a new self-service BI tool. In this chapter, you will learn about Power Query as a self-service ETL tool and Power Map as a 3D geospatial data visualization tool.
Chapter 12, Integrating Reports in Applications, begins with the premise that reports and dashboards are always required in custom applications. This chapter explains different ways to integrate SSRS reports and other dashboards into C# or VB.NET applications in web or Metro applications to provide reports on the application side for the users.
This book will explain the features of Microsoft SQL Server 2014 Enterprise Edition. However, you can also download and install MS SQL Server 2014 Evaluation Edition, which has the same functionalities but is free for the first 180 days, from the following link:
http://technet.microsoft.com/en-US/evalcenter/dn205290.aspx
There are many examples in this book and all of the examples use the following databases as a source:
AdventureWorks2012
AdventureWorksDW2012
AdventureWorksLT2012
You can download the database files from the following link:
http://msftdbprodsamples.codeplex.com/releases/view/55330
After downloading the database files, open SQL Server Management Studio and enter the following scripts to create databases from their data files:
CREATE DATABASE AdventureWorks2012 ON (FILENAME = '{drive}:\{file path}\AdventureWorks2012_Data.mdf') FOR ATTACH_REBUILD_LOG; CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = '<drive>:\<file path>\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG ; CREATE DATABASE AdventureWorksLT2012 ON (FILENAME = '<drive>:\<file path>\AdventureWorksLT2012_Data.mdf') FOR ATTACH_REBUILD_LOG ;
This book is very useful for BI professionals (consultants, architects, and developers) who want to become familiar with Microsoft BI tools. It will also be handy for BI program managers and directors who want to analyze and evaluate Microsoft tools for BI system implementation.
In this book, you will find several headings that appear frequently.
To give clear instructions on how to complete a procedure or task, we use:
Action 1
Action 2
Action 3
Instructions often need some extra explanation so that they make sense, so they are followed with:
This heading explains the working of tasks or instructions that you have just completed.
You will also find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "Expand the Chapter 02 SSAS Multidimensional database and then expand the dimensions."
A block of code is set as follows:
SELECT [<axis_specification>
[, <axis_specification>...]]
FROM [<cube_specification>]
[WHERE [<slicer_specification>]]New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "On the Select Destination Location screen, click on Next to accept the default destination."
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title through the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
For your convenience we have also provided a PDF that contains higher resolution color versions of the images used in this book. These can be extremely useful as you work through various stages of the project when working with materials or examining small detail changes as we tweak individual parameters. You can download the PDF from https://www.packtpub.com/sites/default/files/downloads/8888EN_ColoredImages.pdf.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website, or added to any list of existing errata, under the Errata section of that title.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.