Читать книгу Applied Microsoft Business Intelligence - Sarka Dejan - Страница 3

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.