It is a common, though tricky, thing to host some, but not all of your analytical data that is to be fed into the cubeware of your trust in the nearby, type-consistent and easy-to-maintain SQL Server instance.
This methodology has also the nice side-effect that it will not give the .. say Oracle-minded ... datawarehouse people much to reason about such they will not get distracted from their ... um ... daily slee.., ah, work was the political correct term.
The idea will however bite you the first time you notice that you cannot mix dimensions and facts between two seperate Oracle/SQL Server data views. So you will read diverse blog entries trying to mix the two data sources within a single view.
Oracle will be your primary data source. "Add table" in the view allows you weel to choose a registered SQL Server as a secondary data source, but the table chooser stays empty!
If you read above-mentioned thread to its end, there will be the definitive hint, which is "Add Named Query" and .... here you are ... nearly.
Be aware that this will operate on some "Remote view" feature of the Oracle ODBC driver. For example, Oracle tends to lower-case only the key columns, which can fool you when switching an existing dimension from a seperate data view to the now merged view.
I would not recommend trying it the other way round (Oracle secondary, SQL Server primary data source) unless your primary data source will become bigger than the secondary one ... ok, we still hope that ... since it is to expect that using this kind of federation for practical volumes of fact data will not be funny.
Abonnieren
Kommentare zum Post (Atom)
Keine Kommentare:
Kommentar veröffentlichen