How to build a data warehouse in SQL Server

"Guide" This article describes how to build a simple data warehouse in SQL Server and analyze related issues.

Basic concepts:

1. Cubes: Cubes are the primary object in online analytical processing (OLAP) and are a technology that allows fast access to data in a data warehouse. A cube is a collection of data, typically constructed from a subset of the Data Warehouse, and organized and summarized into a multidimensional structure defined by a set of dimensions and measures.

2. Dimensions: is a structural feature of a cube. They are organized hierarchies (levels) that describe the classification of data in the fact table. These classifications and levels describe a collection of similar members that the user will analyze based on these member collections.

3. Measure: In a cube, a measure is a set of values that are based on a column in the cube's fact table and are usually numbers. In addition, the measure is the central value of the cube being parsed. That is, measures are the numeric data that the end user focuses on when browsing the cube. The measure you select depends on the type of information requested by the end user. Some common measures include sales, cost, expenditures, and production count.

4. Metadata: The structural model of data and applications in different OLAP components. Metadata describes objects such as tables, data warehouses, and cubes in a data mart in an OLTP database, and also records which applications reference different block of records.

5. Level: A level is an element of a dimension hierarchy. Levels describe the hierarchical structure of data, from the highest (most summarized) level to the lowest (most detailed) level of data.

6. Data mining: Data mining allows you to define models that contain grouping and prediction rules to apply to data in a relational database or multidimensional OLAP dataset. These predictive models can then be used to automate complex data analysis to identify trends that help identify new opportunities and choose opportunities for winning.

7. Multidimensional OLAP (MOLAP): MOLAP storage mode enables the aggregation of partitions and copies of their source data to be stored on the Analysis server computer in a multidimensional structure. The MOLAP storage mode provides the potential for the fastest query response time, based on the percentage and design of partition aggregation. In summary, MOLAP is better suited to partitions in frequently used cubes and to the need for quick query responses.