Database Architecture

The database architecture is the set of specifications, rules, and processes that dictate how data is stored in a database and how data is accessed by components of a system. It includes data types, relationships, and naming conventions.

The database architecture describes the organization of all database objects and how they work together. It affects integrity, reliability, scalability, and

performance. The database architecture involves anything that defines the nature of the data, the structure of the data, or how the data flows.


Database Architecture Sample Diagram:

Operational Data Source:

An operational data source is a database repository you can use to store and persist runtime data. Your application can read and write data to and from this data source throughout the life of the application. This is unlike the metadata data source.

Staging Area:

The Data Warehouse Staging Area is temporary location where data from source systems is copied. A staging area is mainly required in a Data Warehousing Architecture for timing reasons. In short, all required data must be available before data can be integrated into the Data Warehouse.

ETL (Extract, Transform and Load):

ETL is short for extract, transform, load, three database functions that are combined into one tool to pull data out of one database and place it into another database.

Extract is the process of reading data from a database.

Transform is the process of converting the extracted data from its previous form into the form it needs to be in so that it can be placed into another database. Transformation occurs by using rules or lookup tables or by combining the data with other data.

Load is the process of writing the data into the target database.

EDW (Enterprise Data Warehouse):

The Enterprise Data Warehouse (EDW) is a service offered by the Data Services Department of the Information Services and Technology Division.  EDW consolidates data from multiple sources in support of campus wide decision making and related information needs such as reporting, analysis, and planning.

The vision for the EDW is to provide information that is secure, accurate, timely, consistent, integrated, appropriately detailed, well-organized, and easy to obtain so that people throughout the campus -- staff, faculty, researchers, and executive-level administrators -- will be better able to assess their needs, set priorities, understand the impact of change, and fulfill their programmatic responsibilities more efficiently.

Operational Data Store:

An operational data store (ODS) is a type of database that's often used as an interim logical area for a data warehouse.

The general purpose of an ODS is to integrate data from disparate source systems in a single structure, using data integration technologies like data virtualization, data federation, or extract, transform and load. This will allow operational access to the data for operational reporting, master data or reference data management.

Atomic Level Data Warehouse:

Atomic data are data elements that represent the lowest level of detail. For example, in a daily sales report, the individual items sold would be atomic data, while rollups such as invoice and summary totals from invoices are aggregate data.

Data Mart:

A data mart is the access layer of the data warehouse environment that is used to get data out to the users. The data mart is a subset of the data warehouse that is usually oriented to a specific business line or team. In some deployments, each department or business unit is considered the owner of its data mart including all the hardware,software and data. This enables each department to use, manipulate and develop their data.

Reporting (Using SQL Server Reporting Services)

SQL Server Reporting Services provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports for your organization, as well as programming features that enable you to extend and customize your reporting functionality.


Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. Reporting Services includes a complete set of tools for you to create, manage, and deliver reports, and APIs that enable developers to integrate or extend data and report processing in custom applications. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components.


With Reporting Services, you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources. Reports can include rich data visualization, including charts, maps, and sparklines. You can publish reports, schedule report processing, or access reports on-demand. You can select from a variety of viewing formats, export reports to other applications such as Microsoft Excel, and subscribe to published reports. The reports that you create can be viewed over a Web-based connection or as part of a Microsoft Windows application or SharePoint site. You can also create data alerts on reports published to a SharePoint site and receive email messages when report data changes.

Analysis (Using SQL Server Analysis Services)
Analysis Services (SSAS) provides online analytical processing (OLAP) and data mining functionality for business intelligence solutions. Before designing a business intelligence solution using Analysis Services, you should familiarize yourself with the OLAP and data mining concepts required for a successful solution.


Analysis Services combines the best aspects of traditional OLAP-based analysis and relational-based reporting by enabling developers to define a single data model, called a Unified Dimensional Model (UDM) over one or more physical data sources. All end user queries from OLAP, reporting, and custom BI applications access the data in the underlying data sources through the UDM, which provides a single business view of this relational data. 


Analysis Services provides a rich set of data mining algorithms to enable business users to mine their data looking for specific patterns and trends. These data mining algorithms can be used to analyze data through a UDM or directly from a physical data store.