Multi tool use

Clash Royale CLAN TAG

Best way to Load Data in data warehouse

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.

Questions :

1) 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.

2)Indexes should be created on the target tables or while extracting the data?

3) 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.

4) Is connecting to the database a good idea to get the data every 10 minutes?

Image shows DWH ETL SSIS Package I am planning to use (instead of flat file I'll be connecting it to the oracle DB)enter image description here

1 Answer
1

This is a very broad question and you haven't yet got into the deep complexities of
mapping source medical data to a business friendly star schema.

Where did the data warehouse requirement and the ten minute requirement come from? What's the more important driver for the business: having a latency of only ten minutes, or having consistent correct information? I guess the focus is operational (admittance etc.), not financial or HR?

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.

In an "ODS Layer" approach, there is a full replica of Oracle tables in your SQL Database (usually in a different SQL Server schema. Note "schema" in Oracle and SQL Server mean subtly different things)

An ODS is basically a copy of the source system. If you want 10 minute refreshes then you will need to do incremental (changes only) loads into your ODS. To do that you need to know what changed in the source since you last loaded your ODS. If there is a 'last changed' field in the source, this is easy. Otherwise it gets very difficult.

The ODS approach aligns with a 'batch load' pattern, when data is batched through in layers. An ODS allows you to test and troubleshoot at different layers.

The ODS approach is different to a 'stream' approach, which supports real time reporting, but contains isolated transformation rules all along the stream.

Indexes should be created on the target tables or while extracting the data?

Indexes should generally be disabled/dropped on tables before data loads then rebuilt afterwards

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.

I don't know what updt_dt_tm is but that sounds like a good idea

updt_dt_tm

Is connecting to the database a good idea to get the data every 10 minutes?

Yes. How else will you get the data out?

In summary, focus on getting a ten minute ODS replica working, based off incremental loads. Then your next step is to transform the data from the ODS to the star schema.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.