Hello
I am new into BO. I have to design a universe of an OLTP schema (relational database). I want to know what’s the difference between designing universe of a OLTP and datawarehouse. I dont know but I guess are no Fact and dimension tables as in data warehouse. how do you identify the fact and dimension tables and what procedure do you follow? I know to design a universe from a datawarehouse. Just was wondering whether different approach is followed for OLTP. I have zero idea about this as have never done it before. Please provide guidelines as to how should I proceed about it and necessary steps.
Thanks in advance.
Rahul.
OLTP systems are designed for optimal performance regarding transaction processing and not reporting. Therefore OLTP systems are highly normalized.
In building a universe against an OLTP database you will face problems such as:
no dedicated conformed/shared dimension tables
multiple grains across tables holding facts
loops/multiple paths between tables
bad/uncleansed data as it is fed via life system(s)
not being able to create additional indexes to improve report performance
not being able to use an ETL process to precalculate certain complex measures (think of “Cash Position” for accounting, etc.)
Having said that, this is also where Business Objects shines, because it allows end-users to go against live data (the OLTP system) and avoid fan/chasm traps etc.
Developing a universe against an OLTP database will take more time (compared to building a universe against a well designed data mart), etc.
In addition:
Try a search on BOB (Designer forum), suggested keywords: OLTP
or look at Shop@BOB for Ralph Kimball’s books.
You also need to be good friends with your dba as there may be occasions that will necessesitate a new table, possibly in a separate schema on the same database (Oracle) or in a different database on the same server (SQL Server). This may not all ways be necessary, but I would definitely recommend a calendar table if your OLTP database doesn’t have one already. The plus side is that there should be a data diagram and someone who has in depth knowledge about the system.