User:Modevs/sandbox
Design methods for business intelligence 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.
Business intelligence oriented databases differ from operational databases in that a BI database is designed to derive business information from existing data[1], 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[2].
Comparison of goals
The following table compares the goals of operational and BI-oriented databases.[3]
| Operations | 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 |