I have a huge production database. I need to summarize the data from production database to the Analysis Database.
How can I populate the fact data efficiently ?

Thanks.

The typical way is to create your dimension tables and use one column with
the identity property. Create a stage fact table with all your natural keys
then your final fact table with links to the identity values in the
dimensions. You typically do not want null values in your fact table so you
can create an unknown entity in your dim tables as needed. You also want to
place your measures (numerics/aggregates) in your fact table. This is where
knowing your granularity is extremely important.

HTH

--
Ray Higdon MCSE, MCDBA, CCNA
---
"Jini" <anonymous (AT) discussions (DOT) microsoft.com> wrote

For the theory of dimensional modelling etc. get The Data Warehouse Lifecycle Toolkit by Ralph Kimball. The SQL Server Resource Kit has also got a good section on Extract Transform and Load (ETL) including more practical examples using SQL, as well as stuff about partitioning which can improve efficiency

I'd generally start with the warehouse schema first, and then consider the ETL once I know what the target looks like. For the ETL itself I'd probably use DTS - its pretty good at pumping data. For efficiency staging the data and using stored procs to transform it, definitely helps, if you've got the room

HTH,
Phil.

The lifecycle toolkit covers many project mgmt items as well, for pure data
mart modeling you might want the "data warehouse toolkit" minus the
lifecycle.

--
Ray Higdon MCSE, MCDBA, CCNA
---
"Phil Austin" <anonymous (AT) discussions (DOT) microsoft.com> wrote

a good section on Extract Transform and Load (ETL) including more practical
examples using SQL, as well as stuff about partitioning which can improve
efficiency.
use DTS - its pretty good at pumping data. For efficiency staging the data
and using stored procs to transform it, definitely helps, if you've got the
room.