This might be a very obvious thing but I cannot seem to find how I can do it.
I want to create a new data warehouse in SQL Server (hence, a new database). My main (fact) table already exists as an Oracle table on another server. My ideal scenario would be to have a remote view in my new database pointing to that table on the remote server. I could then build relationships and dimensions around this table easily.
I can't find a way to create such a view, only local ones. Is it possible?
How about my methodology, would you recommend building a separate program that copies data from the base table in Oracle to a SQL Server table instead of having a view?
If you tried something like this, how did you solve this issue?