Comparing Microsoft and Teradata BI Environments

Data Warehouse

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 business.

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:

1. Rapid movement of data from source systems to analytical system
2. Easy auditing of data
3. Minimum number of copies of the data – reduces audit complexity
4. 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.

Microsoft Approach

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.

Teradata Approach

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:

1. The size of your enterprise data volume - extremely large sets of data favour the Teradata approach.
2. 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 itself.

On comparable hardware, analytical access to multi-dimensional data is considerably faster than the same access to data stored in mainstream relational engines.

Teradata

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.

Microsoft

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 Platforms

Back To Top