Archives of the TeradataForum

Message Posted: Fri, 06 Dec 2002 @ 13:13:23 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Anomy Anom,

Given current OLAP technology (granted, this can and certainly will change), the only functional way for Real Time OLAP to work (without adding a bunch of new data extraction/aggregation processes to move the data into a cube) is via a ROLAP architecture.

When your Teradata system is strong enough and your data model is small enough, you may only need to point the cube to the 'atomic' table and associated dimensional tables. However, in most cases, the performance of this approach isn't acceptable to a user accustomed to OLAP response times. In which case, the following solutions can be applied:

1) You build physical summarization tables of your 'atomic' table and let the OLAP tool navigate these tables at run-time. Oracle Express* does a great job of this out of the box. Microstrategy and Business Objects can do this, but the set up (in my opinion) is clumsy and inefficient relative to Oracle Express. The primary downside of this approach is that incremental updates have to be applied to each tables via application logic and the summary tables will be out of sync during the update process (unless you bar the users from accessing these tables during the update process to ensure data integrity.) The upside of this approach is that building your own tables allows you to determine the optimal summary strategy for your environment given performance requirements and batch window and disk space constraints.

*Note, with the advent of Oracle 11i (due last year and expected to be released sometime later this decade), Teradata users will no longer be able to use Oracle Express as a viable ROLAP solution because it is being merged into Oracle's RDBMS and will no longer be able to communicate with Teradata (which is too bad, it is a great OLAP engine and Oracle Sales Analyzer is a great front-end which is still about 3 years ahead of any other OLAP front-end in terms of functionality.)

2) You can allow the OLAP engine to build the physical summary tables for you; thus, theoretically, assuming your dimensional values remain consistent, you could incrementally update the base fact table then update the cube's respective partition and finally the OLAP engine would update the physical set of summary tables it created for you. As far as I know, Microsoft Analysis Services is the only OLAP engine that can do this; however, Microsoft didn't bother to specify primary indexes (or any indexes for that matter) for the physical summary tables it creates. Therefore, performance becomes a slight issue. Because the first columns of these Set tables are selected as the NUPI, you probably will blow out of perm space after the hours it takes to load the data. If you manage to actually load these tables, query performance will suffer from a data distribution which would make a healthy EKG reading look flat. In addition to the data distribution issue, Microsoft does not allow you to control the aggregation strategy. Their proprietary internal summarization algorithm is based on a combination of data cardinality, compression, and usage statistics. I imagine this would work fine if you want the most frequently requested queries to perform best; however, in our environment, we typically focus on making sure that the average response time of a query is consistent. This approach requires a more holistic understanding of the data and usage.

3) Finally, you could make use of Aggregate Join Indexes in Teradata. This is the best of both worlds. You can determine the optimal summarization strategy given your unique environmental constraints and requirements. Also, you don't have to worry about updating all of the AJIs via application logic. Teradata does it for you. Keep in mind, AJIs are relatively new and some issues can be improved; however, we have successfully built and tested a Teradata AJI ROLAP Architecture with success as a potential replacement for our current Oracle Express ROLAP architecture. This solution will work with any OLAP tool.

With Microsoft Analysis Services, though, you will need to find a way around the 'Single Logon' issue which currently exists with the MP- RAS version of Teradata (i.e., all queries from the MSAS cube will come into Teradata as a single user, thus concerning us about the regulation of spool space and the ability to track users in a production environment.) Outside of switching over to a Windows based version of Teradata, I'm open to any suggestions... We are currently investigating two alternatives.

While I personally haven't tested incrementally loading a tables with multiple AJIs associated with it, I'd imagine performance is 'good' as long as the updates are small (relative to the size of the base fact table.)

My 2 cents, hope this helps,

Claybourne Barrineau

	<Prev	Next>		<<First	<Prev	Next>	Last>>