CODEWITHSUNDEEP
sqlsql-updatedata-warehouse
Theant6118
Theant6118

Reputation: 79

In SQL, how do I update data to the data warehouse as a daily activity?

I'm trying to figure out how to create a strategy to UPDATE both the dimension and fact tables of a data warehouse, as a daily activity.

Essentially, on the date the when the data is inserted into the operational DB, I want it to then be populated into the Dimension tables and hence Fact table.

To do this I presume I would use the SYSDATE() as a part of a WHERE condition, but I don't know how to set the date portion of SYSDATE() to equal the day the operational data is taken. Would it be 'today'? I don't know.

I can't even give a cogent example because i don't really know what I'm doing here.

I've seen somewhere that if I only use the date, that the update will occur at midnight?

I don't really know where to go next. Can anyone help?

Upvotes: 1

Views: 2993

Answers (1)

Matt
Matt

Reputation: 14341

@Theant6118 sounds like you have a lot of research and reverse engineering to do. There is no set rule of when or what time of day to do and update. If someone says midnight that could be depending on when all of the ETL (extract transform load) jobs for your operational data db complete and could depend on what date your ETL processes uses to filter what it does and dos not extract from your transnational data sources.

Next it kind of sounds like you are also wondering what datetime function to use to figure out current system time and that is somewhat dependent on the database engine you are using.

As far as timing. If your operatoinal DB is the data source for your datawarehouse (which is pretty standard) whenever that completes you can then process the cubes/tabular models or ETL for your datawarehouse independent of what the date of the transnational information you are synching is.

  1. definitely update this question with what technology is being used. e.g. sql-server, oralce, etc.

  2. Do some discovery on if sql-server SQL agent jobs when the execute what they do and their dependencies. I am not positive what the equivalent is on other platforms. And update this question.

  3. Query data! test your hypothesis to see what information is contained within the operational data store and what is in the data warehouse.

  4. Build your game plane and start to execute.

Continuous Learn Learn Learn about ETL, the specific technologies being used, and the current configuration!

Upvotes: 2

Related Questions