Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 11 Nov 2002 @ 17:10:40 GMT


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


Subj:   Re: 3NF vs Dimensional modelling
 
From:   John Grace

Robert, Neil, et.al.,

Each time I read one of your persuasive arguments for or against, dimensional modeling or 3rd Normal Form, or exactly what classification a snowflake takes, I am a convert to your religion.

(Ok, Ok, I guess, I am not evangelical. I am here for the purely "social" aspects of the forum.)

To me, a good data warehouse is going to change over time. Expect this. Making a data model, of any variety, perfect, is unrealistic and, perhaps, a pointless endeavor.

On to Analysis Server:

I don't necessarily think SQL is a problem. It will evolve, of course, but drastic "enhancements" to SQL is not the solution to these issues.

Robert, I am intrigued by your success with Analysis Services. We too, have had some success working with Analysis Server on large data warehouses, including Teradata.

Analysis Server pluses. Ease of use. Support for MOLAP, HOLAP, ROLAP. Creates and loads own ROLAP structure (cool), MDX.

Analysis Server minuses. Limited (even simplistic) SQL generator capabilities. Processing large (even small) MOLAP cubes is not quick. Creates and loads own ROLAP structure (doesn't work in real world). The expectation that all measures exist in a single fact table. NT.

What we are able to do is swap out Microsoft's SQL Generator and replace it with one that has a greater aggregate awareness and 3NF capability, essentially masking the DM complexity, without complex views, from Analysis Services. Let Analysis Services process the MDX and post processing the results to create the "extensions" or capabilities that SQL can't support.

We have even created a single, physical Analysis Server cube that can dynamically switch from DM to DM or platform to platform (Teradata, Oracle, DB2). For example, one of our clients has a large snowflake DW, the detail data is not available in this model. The detail data in stored on another system in a 3NF form. We are able to "present" a logical, DM to Analysis Services that it understands. As MDX queries enter the system, we are able to "grab" the outgoing SQL, re-write it, and route the "new" SQL to the appropriate system. 90% of the queries are routed to the Dimensional Model and 10%, those looking for more detail, are routed to the 3NF system. We have mixed up DM and 3NF data models, choosing the best of both, and delivering results to the business user in real-time.

New Web Services are coming on-line to provide navigation, integration, and aggregation. You choose the OLAP or reporting tool you like and plug it into a Web Service running at an IP address. I can use Microsoft Access, you can use Crystal, your buddy can use Brio, and we all get the same results for the same question.

The ability to dynamically mix and match data models and database platforms, in real-time is here. I think the folks that originally developed Teradata had it right, two (or more) inexpensive processors are better that one big expensive processor. I make the same suggestion for data, two (or many) data sources are better that one big data source.

I am, as always, interested in your thoughts and comments.

John Grace
Geppetto's Workshop



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023