Are there any (dis)advantages in building a universe on fully normalized tables instead of building dimensional model/tables and then universe on top of them?

[Former Member]

Hello,

I’m hoping someone can help me with understanding advantages and disadvantages if we want to build a universe on top of a fully normalized tables, compared to using a dimensional model with star schemas.

I’ve read some discussions here that say that it is possible to create a universe on top of normalized tables. Then, can we avoid building of dimensional tables (a data mart), and just use normalized tables? I would say that it is easier to use star schema dimensions and facts tables to build a universe, but our end users might ask “why do we have to go through building a dimensional data mart, if we can have same reports with hierarchies and drill-down functionality based on a universe built on top of our already existing normalized tables?”

Can you point me to some established best practices regarding using normalized tables to build a universe? Any documents with some examples for this?

Any expected difficulties during design/development phase of our universe, related to using normalized tables?

Any expected performance degradation if we use normalized tables compared to using dimensional tables?

Can I build universe more easily if I transform (modify) our normalized model (by using alias tables and views) to look like snowflake model?

I’m using BOE XI 3.1, tables are in Oracle 11.2.

Thank you

[Former Member · Accepted solution]

Few Disadvantages that I usually face when building universe on Normalized tables,

1. Performance - Read operations have to suffer because indexing strategies do not go well with table joins

2. Derived Tables - Due to complex Queries/ Logic, most of the time, end-up creating derived tables, which doesn't use back-end table indexes and slows down the report runtime.

3. Normalized table/ Transaction tables may not always have proper cardinality and often results in Cartesian products

4. Normalized tables may not have tight referential integrity and may have to join more than one column and join on varchar, etc whereas, good Dimensional model datawarehouse will have proper keys/ integer joins and not always necessary to join on multiple fields

5. Often deal with Fan and Chasm Traps

6. Dealing with Database fields with nulls, blanks, date in numeric format, etc.,

7. No Facts, Dimensions separated and most of the time they are in same table

and More...

If performance is not a matter and building Datawarehouse is a big deal, then I will start building Universe on normalized tables by having the database diagram as reference for Joins, contexts, etc

Note: After dealing with universes based on normalized tables for few years (by creating views, complex sql queries data loading to tables and unv on these tables, derived tables), I ended up creating star-schema dimensional model (couple of months extra ETL work), users/ developers felt lot better when they have to create standard/ ad-hoc reports and they are super fast compared to previous universes.