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
Morpheus7Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSIS

From novice to tech pro — start learning today.