SQL Server MVP Deep Dives- P18

SQL Server MVP Deep Dives- P18: Each year Microsoft invites all the MVPs from every technology and country to Redmondfor an MVP Summit—all top secret—“don’t tweet what you see!” During theMVP Summit, each product team holds a series of presentations where they explaintheir technologies, share their vision, and listen to some honest feedback.
Nội dung trích xuất từ tài liệu:
SQL Server MVP Deep Dives- P18 634 CHAPTER 50 BI for the relational guy Terminology Certain basic terms, many of which are defined differently by different people, are used in business intelligence. I will submit my definitions, which are based on my background, to serve as a reference for this chapter. Data warehouse—Aa relational store of data that serves as a basis for reporting queries and/or a source for OLAP cubes. It consists of tables that support mul- tiple subject areas in the organization. It is often designed using dimensional modeling techniques and, in most Microsoft shops, is housed in SQL Server. This term is often interchanged with data mart, and in most cases data mart is more accurate. Data warehouse just sounds so much more substantial. I tend to use data warehouse even when referring to a data mart, because it tends to be used generically to cover both, and so that is the term used throughout this chapter. Data mart—Can be thought of as a mini data warehouse that is specific to a par- ticular subject area. Subject areas tend to align themselves with organizational departments, such as Sales, Finance, or Human Resources. Dimensional model—Represents business data in terms of dimensions and facts. Dimensional models are represented as star or snowflake schema. (The section “Dimensional modeling” later in this chapter will cover this topic in more detail.) Business intelligence (BI)—Generally, a process and infrastructure that facilitates decision-making based on business data. BI is too often thought of in technical terms; it is far more driven by business. Data mining—The process of discovering valuable trends in historical data that can provide insight into future events, based on a predetermined set of factors. A well-known example is on Amazon.com, where books are recommended to you based on your past buying patterns compared to those of other customers who have purchased the same books. ETL (extract, transform, and load)—The process of moving data from a source sys- tem, usually online transactional processing (OLTP), transforming it into the data schema represented in the data warehouse, and loading it into data ware- house tables. ETL will usually also initiate cube loading. The transformation stage can include various processes, such as converting codes, cleansing the data, and looking up surrogate keys from dimension tables. OLAP (online analytical processing)—A process that allows a user to quickly analyze data using common techniques known as slicing, dicing, and drillthrough. In the Microsoft world, OLAP is provided via Analysis Services cubes.Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Really, what is so different? 635 Really, what is so different? If you’ve had any exposure to operational reporting, you’ll already know many of the differences between reporting systems and traditional OLTP systems. Some of the these are shown in table 1. Table 1 OLTP versus reporting environment characteristics OLTP Reporting Queries Few rows (1–50) Many rows (millions, billions, or more) Indexes Few Many Query volume Medium to high Low Updates Small, frequent, dynamic Large, infrequent, scheduled The difference is even more fundamental. OLTP applications are designed based on a discreet set of specifications. Specific data is to be collected, and there are clear pat- terns about who will enter the data, at what point in the business process, and using what method. The first step to designing a business intelligence solution is to take sev- eral steps back to unde ...