and Maintaining the Business Intelligence Environment,” you learn how to keep your business intelligence up and running once deployed. You will learn how to monitor your solution to ensure that it performs well and any changes you need to make to keep it running.
Finally, Chapter 18, “Scaling the Business Intelligence Environment,” covers how to handle performance issues by scaling the business intelligence tools. You learn how to scale up and scale out each of the tools.
Who Should Read This Book
This book is intended for business intelligence developers and architects, and those who are interested in learning more about the Microsoft business intelligence suite. If you need to create reports for your day-to-day operational work, design business-friendly analytics models for end users, or perform advanced analysis to make big business decisions, this book is for you.
It is assumed that you have some basic programming or SQL knowledge before picking up this book. You should understand query constructs and basic programming principles. You don't need experience with any of the business intelligence tools discussed here, but if you do have some experience, there is still quite a bit to learn!
If you are new to Microsoft's business intelligence tools, you would be best served by reading this book from start to finish. However, if you have some background with the business intelligence layout and need to learn about analysis versus reporting, you may want to look at just Part II or Part III, respectively. Finally, if you already have a business intelligence solution, but need to ensure that it is being managed properly, turn to the final section.
Tools You Will Need
This book is based on the SQL Server 2014 business intelligence tools, Excel 2013, and the November 2014 edition of the cloud-based software. All examples use the AdventureWorks 2012 databases and projects found on codeplex: http://msftdbprodsamples.codeplex.com/releases/view/55330.
What's on the Website
Some of the chapters within this book provide sample code for you to download and use. All information is found on Wiley's website: http://www.wiley.com/go/appliedmicrosoftbi.
Summary
Microsoft business intelligence tools provide a lot of power when it comes to your reporting and analysis needs. You must understand each of the tools to ensure you're harnessing that power properly. If so, you will help your organization and your own career move forward!
Part I
Overview of the Microsoft Business Intelligence Toolset
Chapter 1
Which Analysis and Reporting Tools Do You Need?
When embarking on a business intelligence (BI) project, you should consider several things. Should a centralized data warehouse be built or can the existing operational database act as the source for business intelligence? Once that hurdle has been leaped, the next question is: Should time be spent building a semantic model (cube) or again back to the original question: Can the existing operational database act as the source for business intelligence? Finally, once you've answered those questions, you need to decide how to deliver the data to end users. In other words, which reporting tool will be used? The focus throughout this book is on selecting, designing, and delivering a business intelligence solution based on the Microsoft business intelligence tools stack.
Regardless of the approach, you must make a decision concerning which tools to use to ultimately deliver the business intelligence solution. If a data warehouse is built, which Relational Database Management System (RDBMS) will store the data? Now that you have a data warehouse, is a cube or semantic model needed? If so, which type of model should you use: Power Pivot, tabular, or multidimensional? You then need to determine if the solution offers self-service reporting and/or operational reporting capabilities.
Selecting a SQL Server Database Engine
After all the politics have been hashed out, the first step in your business intelligence solution is identifying the data sources. In most scenarios, the solution will include a plethora of data sources, ranging from flat files to relational databases. After that, you must build an Extraction, Transformation, and Loading (ETL) system, which centralizes that data into a data warehouse. The data warehouse is typically housed on an RDBMS.
Building a Data Warehouse
A valid argument could be made against building a data warehouse. However, you should consider whether you prefer to report against a centralized, single-source pristine dataset or to report against multiple, disparate questionable data sources. In other words, are reports more effective leveraging data that is definitely accurate or possibly inaccurate? Another thing to consider is the responsiveness of the business intelligence solution without centralizing the data into a single repository. Often, organizations attempt to analyze data directly against source data and quickly realize that, even though simple, this approach is not efficient nor effective. Figure 1.1 shows a sample topology of this solution.
Figure 1.1 Reporting against disparate data sources
As a result, most organizations often decide to build a data warehouse. Figure 1.2 depicts a sample of a business intelligence solution that includes a data warehouse. Notice in this figure that instead of attempting to build reports against multiple data sources, a single source is used.
Figure 1.2 Business intelligence solution that includes ETL solution and data warehouse
Selecting an RDBMS
Once you've built a data warehouse, the next step is to select an RDBMS. The market for RDBMS systems has a wide range of choices. Selecting the correct system depends on several factors: number of users, disk space, data size, rate of growth, and frequency of data load to mention a few. Microsoft's RDBMS – SQL Server – includes several features that make it one of the more appealing systems available on the market. As of the writing of this book, SQL Server includes an in-memory Columnstore index which is designed specifically for data warehousing workloads. When included in the data warehouse design, you can achieve significant query performance and data compression. Another feature, Change Data Capture (CDC), assists in minimizing the amount of time required to load the data warehouse by providing mechanisms that detect inserts, updates, and deletes. These two features alone make SQL Server a viable Database Management System for hosting your data warehouse.
Selecting SQL Server Analysis Services
Now that a database engine is selected to host the data warehouse, the decision to build an analytical model or, in the case of a Microsoft Solution, semantic model must be made. With the latest release of SQL Server, semantic models have three choices from which you can select:
● Power Pivot
● Tabular
● Multidimensional
So not only must you decide how to build a semantic model, but also which model to use.
If the business intelligence solution requires very fast response times, ad-hoc capabilities, or predictive analytics, leveraging SQL Server Analysis Services (SSAS) is a great option. Whereas the aforementioned list is not inclusive of all factors that may drive the need for a semantic model, they definitely make a strong case in favor of it. SSAS offers a wide range of capabilities that assist in streamlining and reducing report requests, centralizing analytical formulas and key performance indicators, and – probably one of the more important robust capabilities – intuitively handling security