Datawarehouse concepts

[rawindar]

Hi all,

       I have several queries here

      1.Datawarehouse contains historical data.If any updations occur in data,how can we update these changes to existed data in datawarehouse.Do we need to write any code.Is the process of applying changes will different to database(oracle..etc) to database(teradata etc).

      2.genrally Datawarehouse divides into datamarts.How this will be happen.Is datamart exists in Datawarehouse or will it exists in some other database.Do we need to apply any other process (eg:ETL) to create Datamarts.

      3.I have read a statement "While we are loading data into datawarehouse we select data which is relevent and useful to business users".How can we identify this ,Why dont we select whole data.may it contains duplicates.Except this what are the other reasons.

       4.What are the differences between ETL and ELT.Which is better one.

Thanks & Regards

Rawindar

[LinusH]

Is these questions just for curiosity, or for a real life project?

If the later, I get a bit concerned. I don't think you can decide on such fundamental issues based on answers on a forum... You need the competence within the project.

1. This is usually done by ETL Logic, and is supported in DI Studio by transformation like the SCD Type 2 Loader.
2. A design issue. Normally the data marts can reside in the same database, but often on different schemas (if an external RDBMS is used). It will "happen" by designing and creating ETLjobs to populate them from the detail layer of the warehouse.
3. Yo need a business requirement process within the project - defining and documenting them. Then a mapping is done to source system. Why not "all data" - because I think that a warehouse should differ from other systems in that all data in the warehouse should be identified, defined and data quality checked. This is usually not possible when getting all data. By getting all source data, you can achieve an Operational Data Store (not a data warehouse).
4. If you don't have huge data volumes, don't bother. ELT means that most of the transformation process can take place within the target database (opposed to in some staging areas outside the target DBMS). In some cases you can gain better performance.

Good luck

Linus

[bentleyj1]

This is not the proper forum for this question.  SAS Support Communities are not intended for answering questions on a take-home test.