| Microsoft SQL Server 2014 Business Intelligence Development: Beginners Guide |
Page 1 of 2 Author: Reza Rad This book aims to explore Microsoft’s Business Intelligence (BI) tools and is targeted at beginners. This sounds like a contradiction. The book is aimed at: “...BI professionals (consultants, architects, and developers) who want to become familiar with Microsoft BI tools”. So, belying its title, it is not a book for the complete beginner, some knowledge of databases and architecture, is required to get the most from the book. Below is a chapter-by-chapter exploration of the topics covered. Chapter 1 Data Warehouse Design The book opens with a look at the initial steps in creating a BI system, designing the data warehouse, which is optimized for reporting and analysis, and is used to help with decision making. The chapter continues with a look at the architecture and components of a BI system. Data from various source systems is extracted, transformed and loaded (ETL) into the data warehouse. A common ETL tool is SQL Server Integration Services (SSIS). From the data warehouse, another structure is created, using SQL Server Analysis Services (SSAS), which is either file-based (OLAP cube) or memory-based (tabular model). Next, data visualization is discussed, this is what the user sees, and such tools include SQL Server Reporting Services (SSRS), Excel, and Power View. Master Data Management (MDM) maintains the definitive version of reference data, accessible via the Master Data Services (DMS). Finally, the Data Quality Services (DQS) component is used to maintain and improve data quality. Next, the chapter looks at building a data warehouse, emphasising its denormalized flattened tables, which improve report query performance. The section then discusses dimensional modelling, defining some salient terms: fact/measure (business numbers/amounts), dimension (descriptions), attributes (columns), fact table (facts with links to dimensions), and grain (level of fact table detail). The star and snowflake schemas are explained, the latter has a degree of normalization in the dimension tables. The star schema is preferred for performance reasons. Next, a small data warehouse is designed, based on sales information for a chain of supermarkets. Various business questions (e.g. what reports are needed) help decide the scope of the work, together with the level of data to store (the grain). Next, the various steps of Kimball’s best practices are followed: choose business process, identify grain, design dimensions, design facts. Some optimizations are discussed (e.g. replacing strings, using integer surrogate keys in the fact table). The chapter ends with a look at different types of facts (transactional, snapshot, accumulating), and types of dimension (outriggers, role playing, degenerate). Finally, slowly changing dimensions are explained (0: no change accepted, 1: overwrite old value, 2: add a new record to preserve history). This chapter provides a useful overview of what BI is, its major components, how they fit together, and provides a helpful walkthrough of an example data warehouse. It’s noted that data warehouse design is a critical step. The chapter is generally easy to read, with useful explanations, helpful diagrams, inter-chapter references, and website links. The book requires some previous knowledge of databases systems (OLAP in particular). These traits apply to the whole of the book.
Chapter 2 SQL Server Analysis Services Multidimensional Cube Development This chapter extends the previous one by creating a cube from the data warehouse. OLAP provides faster reports by reshaping data into cube structures. Next, the SQL Server Analysis Engine (SSAS) is discussed, it is used to create cubes, hierarchies, dimensions, measures, and queries (using the Multidimensional eXpression (MDX) language). The chapter continues with practical exercises, including:
In each case, a step-by-step walkthrough is provided, with useful instructions and screenshots. This chapter provides a useful practical overview of OLAP cubes, their creation and amendment of various components. The author acknowledges there is much more to learn. Some sections are not suitable for the complete beginner. Additionally, some sections have bad grammar, and some nonsense sentences: “So the data will be too important in order to load data from the data warehouse into the SSAS cube with a processing step because it will keep data updated and more accurate.”
Chapter 3 Tabular Model Development of SQL Server Analysis Services This is SSAS’s other modelling technology, here data is loaded into an in-memory engine (xVelocity). Data Analysis eXpression (DAX) is the language that queries SSAS tabular. The chapter opens with a brief overview of SSAS tabular, including its faster loading (compared to the multidimensional model), and power-user access via Excel PowerPivot. The chapter continues with practical exercises, including:
This chapter provides a useful practical introduction to SSAS tabular. As well as helpful step-by-step walkthroughs, there’s a useful comparison of tabular versus multidimensional SSAS, the primary advantages of tabular is performance and ease of use, whereas multidimensional is a more mature product having greater functionality.
Chapter 4 ETL with Integration Services This chapter discusses SQL Server Integration Services (SSIS), used to populate a data warehouse. The chapter opens with an overview of ETL, and how it links to MDS (keeping the master data consistent), and DQS (ensuring the data is clean). The use of staging tables to ease resource contention is discussed. Next, the high-level architecture of SSIS is discussed, and some of its many components briefly described (e.g. copy file). The chapter continues with practical exercises, including:
As before step-by-step walkthroughs provide useful instructions and screenshots. Overall, the chapter provides a useful practical introduction to Microsoft’s ETL tool. |
|||
| Last Updated ( Wednesday, 06 April 2016 ) |