Creating universe on transactional database

building semantic layer
1

Hi all,

Could you please let me know what are the advantages and disadvanatge for craeting universe on OLTP system ,my data load/refresh is about every 15 mintues.

what are the thing do i need to look in if i want create universe on OLTP/transactional DB.

Thanks

pavan.au143 (BOB member since 2011-12-29)

2

sashi v (BOB member since 2012-02-28)

3

Thanks for your reply,I already have universe from DWH.

If i want move from DWH to OLTP.

Will the design of the universe be same as the existing one?
2. If not - why not?

Thanks

pavan.au143 (BOB member since 2011-12-29)

4

It should be the same if there are no changes in relations b/w the tables.

But, DWH’s are created for a purpose…so usually the relations shouldn’t be the same…any way just double check on how to use the tables(if the same relations can be maintained after migration) and go ahead…

sashi v (BOB member since 2012-02-28)

5

Sashi,

I’d beg to differ on the design being the same.

The design will be considerably different - a transactional database is geared to data input while a relational database (DWH) is geared to data output.

Performance of the transactional database for the vast majority of queries will be slower than a well designed data warehouse - there will be far less indexing, the data will be spread across more tables, far more likely to encounter outer joins and so on.

Pavan,

I’d seriously question your motives for moving from a DWH to OLTP-based reporting. Why are you considering this?

6

My reply was confusing, Sry!

I didn’t mean that the design should be exactly the same. Design…is the way you join the tables in the designer based on their relations, if the tables are maintaining the same relations in both the databases then why should I change the join…?

but as I said, they shouldn’t be the same…DWH’s are made for a purpose…

No Offense! Just correct me if I am wrong ! :?

sashi v (BOB member since 2012-02-28)

7

Thank you shasi and mark for your reply…,

This is a proposal from from my client moving from DWH to OLTP …,They want me to prepare advantages and disadvantages and are there any new challenges…this is the reason i am expecting some information here…,

Thanks

pavan.au143 (BOB member since 2011-12-29)

8

I agree with Mark. Going form a DWH to OLTP is the wrong move. Performance will suffer greatly. Building universes will take much longer. Maintaining universes will take longer. The only reason I would ever build a universe on an OLTP is if there is no data warehouse with the needed data, and a data warehouse cannot be built, for some reason. The only purpose for a DWH is reporting. So use it. 8)

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

9

[quote:0638323ad3=“pavan.au143”]Thank you shasi and mark for your reply…,

This is a proposal from from my client moving from DWH to OLTP …,They want me to prepare advantages and disadvantages and are there any new challenges…this is the reason i am expecting some information here…,

Thanks
[/quote]

Advantages in moving from DWH to OLTP:
None that I’m aware of

Disadvantages in moving from DWH to OLTP (in addition to what Michael mentioned):
Performance of the OLTP system will suffer as report users will also now be using the same database
Potential row locking if not handled correctly
Queries will not be optimised for performance
All reporting will be “as at” reporting - a snapshot of how the system is then, no concept of history as there is in a DWH
Far less likely to have inner joins on integers
Far less likely to have all indexes that a DWH would have
Join paths will most likely be longer in the OLTP rather than using a standard star schema
Much harder to deploy aggregates and aggregate awareness/navigation functionality

10

See also this old post: Design Universe of an OLTP schema!

Andreas :de: (BOB member since 2002-06-20)