title: Best way to Load Data in data warehouse
body: <p>We are building a new near realtime(refreshed every 10 minutes) hospital data warehouse from scratch. Data lies on an oracle database and we are planning to use SSIS packages to extract data from Oracle and load in the SQL Server database. I am very new with DWHousing and need suggestions on building the DWH.</p>
  <p>Questions :</p>
  <ol>
  <li>When extracting the data from Oracle should I import the complete tables or should I get only selected columns which I want?
  For example : If I have person,address,facility_hist tables should I join them in the extracting package and only get the selected columns? or should I extract all three tables separately in staging area and then combine them and load them in SQL Server.</li>
  </ol>
  <p>2)Indexes should be created on the target tables or while extracting the data?</p>
  <ol start="3">
  <li><p>I am planning to do the initial load in batches by using the create_dt_tm for the records and load the data in months. Once everything is loaded, we are planning to use the updt_dt_tm and load any updates done in last 10 minutes.</p>
  </li>
  <li><p>Is connecting to the database a good idea to get the data every 10 minutes?</p>
  </li>
  </ol>
  <p>Image shows DWH ETL SSIS Package I am planning to use (instead of flat file I'll be connecting it to the oracle DB)</p>
  <p><a href="https://i.stack.imgur.com/bdIXW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bdIXW.png" alt="enter image description here" /></a></p>