Comparing Microsoft and Teradata BI Environments
A Data warehouse is a repository of integrated data and information,
extracted from diverse sources and made available for queries and
analysis used to create business intelligence that supports business
analysis activities and decision-making.
Data warehousing is not intended for current "live"
data, although 'virtual' or 'point-to-point' data warehouses can
access operational data. A 'real' data warehouse is generally preferred
to a virtual DW because stored data has been validated and is set
up to provide reliable results to common types of queries used in
A core capability of Business Intelligence is being able to efficiently
extract information from data. Enterprise data is typically distributed
across multiple transactional systems [Finance, Sales, HR etc.]
and must be aggregatd before analytical processes can be run on
it. An efficient BI system has:
- Rapid movement of data from source systems to analytical system
- Easy auditing of data
- Minimum number of copies of the data – reduces audit
- Rapid analytical queries [2-3 seconds]
Users presented with an ‘analytical view’ of data
– rather than a relational view as found in the database,
the use can work with data dimensions and measures.
To achieve these aims, Microsoft and Teradata developed solutions
using different strategies.
Microsofts approach was to extract data from the central relational
data warehouses to multi-dimensional data marts. Multi-dimensional
data means that users automatically get a hierarchical, dimensional
and measured view of the data.
This additional step in the analytical process adds additional
time before the data is available to the user, and adds another
layer of auditing.
This approach also requires more disk space.
These items are not major issues today, as disk space and CPU
cycles are relatively cheap, auditing can be automated and Microsofts
proactive caching will compensate for the delays in organising the
data, bringing real-time analysis ever closer.
Rather than extract data to multi-dimensional data marts, Teradata
uses a mix of parallel hardware and innovative software, to provide
a solution for both small and very large data sets.
This allows the BI structure to be very simple. The data only
moves once, minimising any delays. This also negates the need for
an additional copy of data, simplifying auditing.
To effectively hide the complexity of the relational store, Teradata
uses a logical layer between the user and the EDW or EDS data structure
to translate the relational views of the data into analytical views.
Comparing Microsoft vs. Teradata
The approach that is best for you depends upon:
- The size of your enterprise data volume - extremely large sets
of data favour the Teradata approach.
- An average enterprise data volume – with an average BI
requirement, both Microsoft and Teradata solutions fit requirements.
Traditionally, Teradata has focused on only the largest companies,
whereas Microsoft’s call cry has, for years, been “BI
for the masses”.
Whilst Teradata solution fits all, and in certain cases is the
only feasible solution, Teradata’s systems are too expensive
for other than large enterprises. Microsoft offers a more cost-effective
solution for the majority of customers.
The reason relational systems have poor analytical performance
is in the way that most RDBMS engine designers store their data
structures on disk - it doesn’t lie with the relational model
On comparable hardware, analytical access to multi-dimensional
data is considerably faster than the same access to data stored
in mainstream relational engines.
In a Teradata environment, data is extracted and cleaned, then
placed in a central store, known as an Enterprise Data Store or
Enterprise Data Warehouse (EDW). It is held as a relational structure
and all the analytical queries are run directly against the data
in the EDW.
In a Microsoft environment, data is placed in a central store
or data warehouse which is also typically structured as relational
tables. Subsets of the data are then moved from the warehouse into
data marts, restructured as multi-dimensional data, and it is against
these data marts that queries are run.
Why Teradat Works Best
Teradata resolved issues concerning being able to run fast analytical
queries against a relational structure by using a mix of parallel
hardware and innovative software, providing a solution that scales
to truly massive data sets.
This provides for a very simple BI structure. The data only moves
once, delays are minimised and only two copies of the data are held
[source systems and EDW], simplifying auditing.
Teradata hides the complexity of the relational store, by using
a logical layer between the user and the EDW or EDS data structure.
This layer translates the relational views of the data into analytical
views so the users never have to see the relational structure.
Next: Database Development
Back To Top
The World's Leading Guide To BI Strategy, Program & Technology
Database Index | Relational
Model | Object Model | Other
Models | DBMS | Contextual
Types | Microsoft SQL | Oracle
OODBMS | Data Warehouse | Teradata
ADW | Data Mart | MS
vs Teradata | Development Platforms
| ODBC | JDBC | SMP
| MPP | SN