Recently in our company we started to use Oracle data integrator for our Data warehousing requirements. There in Oracle Warehouse builder are dimensions, cubes but i couldn't find their matches in Oracle Data Integrator. Could you help me to implement dimensions, cubes in ODI, please? Thanks in advance.
Specifically what functionality do you need to work around ?
If you want to build Dimensions , start with IKM for SCD's , Fact tables are just tables with dimension FK's and measures , no specific KM needed other than your loading strategy (Incremental, Full etc).
The only functionality I can see missing is :
Defining Hierarchies and Dimensional Hierarchies as database objects in the oracle DB.
One-click to move from ROLAP to MOLAP and actually load cubes rather than tables - however there is an Oracle OLAP KM I believe (not used it).
Let me know what you think is missing?
Thank you very much for reply. I want to implement dimensions and facts in ODI as i used to do it in OWB, i meant i don't know how can i make fact table use dimensions' dimension keys as FKs in fact tables. This is just one of the problems that i came across. By the way i'm new to odi and there might be some workarounds to overcome this situation.
Thanks in advance
I'll try and answer your questions as best I can.
I havent used OWB in ages, but the principles will be the same for assigning the Dimension FK's.
When loading your Fact tables in ODI, you will have the tables containing your fact records in the source pane (obviously) from which you map the Fact table measures to. You then need to either :
Use the lookup operator to lookup your dimension FK , you typically join on the natural key / business key and possibly a date if your dimension is SCD Type 2 etc.
Quick example of lookups here : https://blogs.oracle.com/dataintegration/entry/whats_new_in_odi_11g_-_part_3_1
They are similar to OWB I think.
The more old-school way is to simple drag the Dimension tables onto the source pane, join to them as appropriate (column and outer join etc). Then map the Fact table FK to the Dimension table PK in the target fact table mapping. Thats my prefered method, I've never really taken to Lookups much.
What ever you do keep an eye on the SQL generated by simulting your interface, I much prefer the ODI way of loading an I$ table with the source joins, then integrating the I$ data to the fact table compared with OWB's nested select on select on select ;-)
If you want to use the E$ tables to trap fact rows you dont have a dimension value for, create a constraint on your fact table to the Dimension and enable Flow control in the fact interface, this will hive off any Facts without a dimension, alternatively you can load the fact anyway and use a -1 or similar value to specify an 'unknown'. Up to you for that bit.
For time dimensions thats easy, I've answered that one before !
Its not wizard based but its very flexible!
Hope this helps, Once you crack the learning curve its a very powerful tool, quick to use and can make life very easy for you.