• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 51
  • Last Modified:

Datawarehouse fact load

Hi,

I am currently building am data warehouse but I am finding that the reload of the fact table is taking longer than the business finds acceptable. I have been emptying out the fact table on every incremental load and repopulating it. So on each load, we start again from scratch. It worth noting that when a row needs to be changed in the source, a new row is added to the source. so type 2 SCD do not seem to be worth the effort.

Is there any way that we do not have to empty and reload the fact table?

Many thanks
0
Morpheus7
Asked:
Morpheus7
  • 2
1 Solution
 
Pushpakumara MahagamageVPCommented:
Use incremental load instead of full load.

read following URLs

http://radacad.com/ssis-incremental-load-with-datetime-columns

http://www.itprotoday.com/microsoft-sql-server/ssis-novices-guide-data-warehouses-flattening-while-staging-data

Have you try Database views to pull data to fact tables.
0
 
Morpheus7Author Commented:
Hi,

Many thanks for the reply. I use an incremental load for the Dimension tables and then clear out the fact table and reload it. But it is now taking too long to load as we need to refresh every 30 minutes or so.
0
 
Pushpakumara MahagamageVPCommented:
I have such reports, the users needs almost current status for their target in month end. So they refuse my D-1 reports, And I have 2 separate hardware for Transaction Server and Data warehouse.   Pulling data from Transaction tables during day time popups so many complication for the transaction process.

Then I configure transaction replication between OLTP and OLAP just only for required tables for data warehouse. and taking data from that secondary database  to fact tables. may be experts will not recommend the method but it solved my issue. you also can try.  

And you can identify long running query from SP_WhoIsActive and apply necessary fixes. Have you schedule index rebuild and statistic update. those can optimize sql performance.  
sp_WhoIsActive - https://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

and check whether any I/O Latch CPU utilization on the server and fix if any one is suffering.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now