User:Modevs/sandbox

Design Philosophy for Business Intelligence Targeted Databases

A business intelligence (BI) database is a database that is oriented toward maximizing reporting and analytic value of data by aggregating and summarizing important pieces of existing data in such a way that large reporting queries can run more efficiently. Because of this requirement, the design methods used for BI targeted databases differ from operational databases in that a BI database is designed to derive business information from existing data, which can be used to report on or gather meaning by aggregating existing data sources.

While operational databases are focused around data-input queries to accurately and in a normalized fashion manage data, business intelligence databases are focused on data-output, at the expense of normalization creating redundant derived data. Since data often will not be updated, this is a preferable and the requirements for BI databases are different than for example data entry requirements, denormalizing allows for faster reporting queries. Due to these differences, databases with these intentions are referred to as data warehouses.

Comparison of goals

The following table compares the goals of operational and BI-oriented databases.

Operational	Business Intelligence
Minimize data redundancy (normalization)	Optimize data to run quickly with a wide range of queries using mass amounts of data; de-normalized to provide quick retrieval of data.
Requires sub-second response time	Response time is important however as queries generally will take seconds, minutes or even hours optimization is aimed towards minimizing the impact of these large queries
Minimal derived data, derived data is typically generated on-demand	Large amounts of derived data to speed up report-based queries
Historical data often sacrificed for speed	Large amounts of historical data purposely kept to generate temporal reports

Data Sources

BI databases can derive data from a wide array of source. Examples that BI databases might aggregate existing data from include sources such as Point of sale applications, human resource applications, and survey results. BI database tasks can be performed both from multiple operational databases across organizational departments, as well as from external vendors and public sources at the same time. Data is generally aggregated using extract, transform and load tools.

Logical Design

Typically BI targeted databases will need to sustain massive amounts of dimensional data with ever-increasing amounts of historical data, making the storage of data a daunting task. The relational database management system used and physical storage may differ, however generally the logical design retains a few notable characteristics. Because of the specific goals of BI databases and decision support systems they provide data to, queries require complex operations such as filtering, join and aggregation. To efficiently support these operations database schemas such as the star schema using facts and fact tables, and the snowflake schema (a more normalized star) are often used to speed up queries, at the expense of initial or ongoing load time in managing a database.

Notably, to speed data retrieval rates data warehouses data often store data multiple copies of data in its most granular foam and in summarized forms called aggregates. The intention of this is to speed up read times at the expense of normalization.

The decision on what type of database schema to use relies heavily on data aggregation and summarization requirements.

Backup and Recovery

Since BI databases are derived from existing data, it is often possible to recreate them from source data should they fail. However due to the amount of time it may take to recreate a BI database, and the likely need to utilize Extract, transform, load tools in order to recreate data, disaster recovery is still a considerable issue.