Any suggestions? Export (0) Print
Expand All

Data Marts

SQL Server 2000

In some data warehouse implementations, a data mart is a miniature data warehouse; in others, it is just one segment of the data warehouse. Data marts are often used to provide information to functional segments of the organization. Typical examples are data marts for the sales department, the inventory and shipping department, the finance department, upper level management, and so on. Data marts can also be used to segment data warehouse data to reflect a geographically compartmentalized business in which each region is relatively autonomous. For example, a large service organization may treat regional operating centers as individual business units, each with its own data mart that contributes to the master data warehouse.

Data marts are sometimes designed as complete individual data warehouses and contribute to the overall organization as a member of a distributed data warehouse. In other designs, data marts receive data from a master data warehouse through periodic updates, in which case the data mart functionality is often limited to presentation services for clients.

Regardless of the functionality provided by data marts, they must be designed as components of the master data warehouse so that data organization, format, and schemas are consistent throughout the data warehouse. Inconsistent table designs, update mechanisms, or dimension hierarchies can prevent data from being reused throughout the data warehouse, and they can result in inconsistent reports from the same data. For example, it is unlikely that summary reports produced from a finance department data mart that organizes the sales force by management reporting structure will agree with summary reports produced from a sales department data mart that organizes the same sales force by geographical region. It is not necessary to impose one view of data on all data marts to achieve consistency; it is usually possible to design consistent schemas and data formats that permit rich varieties of data views without sacrificing interoperability. For example, the use of a standard format and organization for time, customer, and product data does not preclude data marts from presenting information in the diverse perspectives of inventory, sales, or financial analysis.

Data marts should be designed from the perspective that they are components of the data warehouse regardless of their individual functionality or construction. This provides consistency and usability of information throughout the organization.

Microsoft® SQL Server™ 2000 tools used for a data mart may include any of the tools used for data warehouses depending on how the data mart is designed. If the data mart is created and maintained locally and participates in the organization's data warehouse as an independent contributor, its creation and maintenance will involve all the operations of a data warehouse. If the data mart is a local access point for data distributed from a central data warehouse, only a subset of the tools may be involved.

Distributing Data Warehouse Data to Data Marts

If data warehouse data is maintained in a central data warehouse, the data is prepared and loaded into the data warehouse at the central site and then distributed to local data marts.

SQL Server Agent and Data Transformation Services (DTS) can be used to schedule and perform data transfers, including filtering data appropriate to the data mart and updating the appropriate tables in the data mart. DTS packages can also be created and scheduled to update OLAP cubes in the data mart after new data is received from the central data warehouse.

Some data warehouse distribution scenarios may also use replication to coordinate and maintain data mart data.

See Also

DTS Overview

SQLServerAgent Service

Replication Overview

© 2016 Microsoft