http://forum.kimballgroup.com/etl-and-data-quality-f9/-t1.htm Wed, 01 Sep 2010 08:10:27 GMT 10 http://kimballgroup.com/images/KGlogoBasic.gif http://forum.kimballgroup.com/etl-and-data-quality-f9/-t1.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/scd-type-2-update-crte_tsmp-and-updt_tsmp-t709.htm sachinh4u Data Loaded as below (CMPNY_ID & EFF_DT are PK) CMPNY_ID EFF_DT CMPNY_NM STAT_C UPDT_TSMP CRTE_TSMP 2 2008-11-07 ABC D 2010-08-26-00.17.34.189507 2010-06-30-01.32.08.000000 34 2009-11-15 PQR D 2010-08-13-07.05.54.563109 2010-07-25-01.55.17.000000 993 2010-02-28 MNO A 2010-08-12-09.03.51.000000 2010-08-12-09.03.51.000000 993 2010-08-25 MNO1 A 2010-08-25-02.02.27.000000 2010-08-25-02.02.27.000000 2664 2009-10-07 XYZ A 2010-08-12-09.03.51.000000 2010-08-12-09.03.51.000000 2664 2010-08-25 ... ETL and Data Quality Wed, 01 Sep 2010 08:10:27 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/scd-type-2-update-crte_tsmp-and-updt_tsmp-t709.htm#2997 http://forum.kimballgroup.com/etl-and-data-quality-f9/scd-type-2-update-crte_tsmp-and-updt_tsmp-t709.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-for-accumulating-snapshot-t691.htm spaul
Does anyone know if the Kimball ETL book or class offers some in-depth help with the Accumulating Snapshot fact tables or is the focus on transaction-based fact tables? ]]> ETL and Data Quality Tue, 24 Aug 2010 20:12:54 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-for-accumulating-snapshot-t691.htm#2906 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-for-accumulating-snapshot-t691.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/null-values-best-practice-digest-t707.htm bmoraillon Hi all, i read all the post and design tips about null values management. Can you tell m if the following statements are right or wrong for you : Dimension attributes null values management : For string dimension attributes, use : "Not available" : if they're are not in the production system "Not applicable" : if there is no sense For date dimension attributes, use : null Fact foreign keys null values management : link to one of this 3 special rows ... ETL and Data Quality Mon, 30 Aug 2010 15:17:02 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/null-values-best-practice-digest-t707.htm#2982 http://forum.kimballgroup.com/etl-and-data-quality-f9/null-values-best-practice-digest-t707.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-as-a-source-for-some-attributes-of-a-dimension-t693.htm bmoraillon Hi all, is it allowed to programmatically add value on a row of a dimension by the etl process ? For instance : Source contains only books : NK | NAME 142 | invisible Etl source the DW dimension 'Product' and programmatically add a category : book so 'Product' looks like : SK | NK | NAME | CATEGORY ... 1 | 142 | invisible | book or do i have to add the category in my source table system ? Many thanks Best Regards ETL and Data Quality Wed, 25 Aug 2010 11:42:12 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-as-a-source-for-some-attributes-of-a-dimension-t693.htm#2912 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-as-a-source-for-some-attributes-of-a-dimension-t693.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/handling-unknown-dimension-atrributes-that-are-type-2-changes-t696.htm meb97me In the data warehouse we're building, we've got an Employee dimension that has a self referencing attribute called ReportsTo which stores the surrogate key of their line manager who is also in the DW (in most cases). This attribute is a type 2 change as we want to capture historical heirachical changes in the company, however some people aren't always assigned a valid manager (its a really crappy system that i wont bore you with) but suffice to say they can store invalid managers rather than ... ETL and Data Quality Wed, 25 Aug 2010 14:58:56 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/handling-unknown-dimension-atrributes-that-are-type-2-changes-t696.htm#2915 http://forum.kimballgroup.com/etl-and-data-quality-f9/handling-unknown-dimension-atrributes-that-are-type-2-changes-t696.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/best-praticte-for-inserting-fact-and-linking-to-dimensions-t690.htm bmoraillon is it allowed to have null values in fact table for some dimensions foreign keys ?

Exemple :

DateDimFk DimFk1 DimFk2 50 (coming from business system 1)
DateDimFk null DimFk2 50 (coming from business system 2)

or do i have to create a special dimension data row (for instance "?") to link these facts ?

Many thanks in advance.
Best Regards.]]> ETL and Data Quality Tue, 24 Aug 2010 15:26:53 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/best-praticte-for-inserting-fact-and-linking-to-dimensions-t690.htm#2900 http://forum.kimballgroup.com/etl-and-data-quality-f9/best-praticte-for-inserting-fact-and-linking-to-dimensions-t690.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/techniques-for-data-transfer-t685.htm DBADS I know flat/csv files extraction can be an option but it doesn't seem to be an automated way...
Is there another way that is more automated?]]> ETL and Data Quality Sun, 22 Aug 2010 14:42:34 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/techniques-for-data-transfer-t685.htm#2878 http://forum.kimballgroup.com/etl-and-data-quality-f9/techniques-for-data-transfer-t685.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/how-to-create-fill-in-rows-for-date-ranges-that-have-gaps-t629.htm cjportil I need some help building some SQL that will create "fill in" rows for date ranges that have gaps. The data I'm working with looks something like this: Student_ID EFF_DATE EXP_DATE 100 1/1/2009 5/1/2009 100 8/1/2009 12/31/2009 101 12/1/2009 4/1/2010 101 7/1/2010 9/1/2010 101 11/1/2010 12/1/2010 102 1/1/2010 6/1/2010 In this example students ... ETL and Data Quality Wed, 14 Jul 2010 21:58:22 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/how-to-create-fill-in-rows-for-date-ranges-that-have-gaps-t629.htm#2583 http://forum.kimballgroup.com/etl-and-data-quality-f9/how-to-create-fill-in-rows-for-date-ranges-that-have-gaps-t629.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/anyone-use-talend-for-a-production-dimensional-model-build-t683.htm robber I'm looking at Talend as they seem to be on a roll with the recent inclusion of Data Quality and Master Data Management components into their suite. Wondering what experiences anyone has had building a standard dimensionally modelled Datamart or Data Warehouse? I really like the code generating, meta data driven approach, it is what I'm used to. They have recently released an ELT component which is also a key component in my opinion. Any real life feedback out there? ETL and Data Quality Thu, 19 Aug 2010 15:40:46 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/anyone-use-talend-for-a-production-dimensional-model-build-t683.htm#2851 http://forum.kimballgroup.com/etl-and-data-quality-f9/anyone-use-talend-for-a-production-dimensional-model-build-t683.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/change-data-capture-t675.htm ATran Hi, We are trying to chose a way to capture change from the source system (we have to support MSSQL and Oracle). - i have problem understanding the difference between the two method : Using Audit Column and Timed Extract. Plus, if we choose this technique, we need a way to capture the deleted row. Relying on trigger is not a very good option for us, because we don't want to add trigger in every table. Maybe, having a staging table with only the source id for a given table could do the ... ETL and Data Quality Tue, 17 Aug 2010 15:02:00 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/change-data-capture-t675.htm#2810 http://forum.kimballgroup.com/etl-and-data-quality-f9/change-data-capture-t675.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/informatica-fact-tables-t673.htm pradeeshkumar Which table should be loaded first. Either Dimension table or fact table? ETL and Data Quality Tue, 17 Aug 2010 04:25:08 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/informatica-fact-tables-t673.htm#2808 http://forum.kimballgroup.com/etl-and-data-quality-f9/informatica-fact-tables-t673.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/fk-on-facttables-and-performance-t655.htm nicolad76 Hi, I would like to ask a question about constarints in DW tables. I have read that it is not a good practice to put constraints in DW tables since they overload the server with "useless" data consistency checks. Reading this forum I could notice that the good practice is to define as FK in the fact tables. FK introduce constraints, right? Meaning each insert will have to have all FK validated. When my ETL saves/modifies about 10M rows in one specific fact table, how to FK affect ... ETL and Data Quality Thu, 05 Aug 2010 19:59:59 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/fk-on-facttables-and-performance-t655.htm#2733 http://forum.kimballgroup.com/etl-and-data-quality-f9/fk-on-facttables-and-performance-t655.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/datawarehousing-design-questions-t648.htm RobinvB Hello, We are designing a new datawarehouse based upon a new service platform. My role in the team is not technical; I do not design packages nor do I decide how data is stored. The people in the team that do have a technical role come from a different, not technical background. After reviewing their work I came upon a couple of decisions made that I doubt as being the ideal solution. When discussing these issues with them, I failed to explain to them why (if at all) it might be better to ... ETL and Data Quality Fri, 30 Jul 2010 07:43:09 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/datawarehousing-design-questions-t648.htm#2688 http://forum.kimballgroup.com/etl-and-data-quality-f9/datawarehousing-design-questions-t648.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-to-handle-deleted-reference-data-t630.htm robertfowler Hello all, I have a situation that I am unsure how to handle and would appreciate some input. My operational system allows me to add, edit and remove customers. My source operational system also constrains me from adding a cusomter with the same name and the same short name - these need to be unique on the online and the source database has the constraints to enforce this. Also current system uses operational keys as primary for the dimensions. The process that I am having a problem ... ETL and Data Quality Thu, 15 Jul 2010 06:40:33 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-to-handle-deleted-reference-data-t630.htm#2588 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-to-handle-deleted-reference-data-t630.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-for-reference-lookup-data-t616.htm acobb I am currently providing input for an ETL architecture and wanted some advice on the best practice for loading reference data. The source system currently runs continuously and logs keys information about actions being taken against the source system. The logs for the system currently record ID's for the data that is being acted upon to indicate that the data has changed. My main question is if the source system should log all of the information on the object that changed, only log the data ... ETL and Data Quality Fri, 09 Jul 2010 15:57:27 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-for-reference-lookup-data-t616.htm#2521 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-for-reference-lookup-data-t616.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/how-to-load-a-slowly-changing-dimension-type-2-with-one-sql-merge-statement-in-oracle-t11.htm ubethke
You can access the solution at http://www.business-intelligence-quotient.com/?p=66]]> ETL and Data Quality Tue, 03 Feb 2009 15:26:28 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/how-to-load-a-slowly-changing-dimension-type-2-with-one-sql-merge-statement-in-oracle-t11.htm#16 http://forum.kimballgroup.com/etl-and-data-quality-f9/how-to-load-a-slowly-changing-dimension-type-2-with-one-sql-merge-statement-in-oracle-t11.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/index-t581.htm dellsters
Also in the fact table, do you create one index with all the foreign keys or each individually?]]> ETL and Data Quality Thu, 17 Jun 2010 15:12:22 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/index-t581.htm#2381 http://forum.kimballgroup.com/etl-and-data-quality-f9/index-t581.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/require-comprehensive-test-cases-for-scd-type2-t584.htm abhijit.singh2 Hi, I have written the following code in Oracle for SCD Type2. SCD2 table name is employee_tgt and source table is employee_src. It maintains history of column emp_name. The primary key is emp_id and the surrogate key is emp_sk. The load is assumed to run daily. I wish to use it as template for any dimension of Type2. However, I require someone to provide me with a comprehensive test data set so that I be sure that the code is correct. Code: CREATE SEQUENCE seq_employee_tgt MINVALUE 1 ... ETL and Data Quality Mon, 21 Jun 2010 09:37:08 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/require-comprehensive-test-cases-for-scd-type2-t584.htm#2394 http://forum.kimballgroup.com/etl-and-data-quality-f9/require-comprehensive-test-cases-for-scd-type2-t584.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/ssis-scd-component-t576.htm fdarrigo We are using (or attempting to use) the Kimball Method SSIS Slowly Changing Dimension Component for SSIS from Todd McDermid (http://kimballscd.codeplex.com/). We have run into a few issues and would appreciate some advice if anyone has used this SSIS component. First, although, the existing dimension input columns for “SCD2 Current Record”, “SCD2 Effective Date”, and “SCD2 Expiry Date” are assigned on the first tab, when running the job against or test staging table and dimension table, we ... ETL and Data Quality Fri, 11 Jun 2010 17:42:26 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/ssis-scd-component-t576.htm#2347 http://forum.kimballgroup.com/etl-and-data-quality-f9/ssis-scd-component-t576.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/how-to-consolidate-numerous-etl-processes-t545.htm c Hi, Within the department there are numerous users whose ETL processes are slightly identical to one another (e.g. an extra variable). This not only puts a burden on the server load, but also create problems where a certain measure is not identical across the department. Therefore we're venturing down a new path to consolidate all ETL processes into one, which will eliminate the repetitive processes but most importantly achieve a single source of truth. We have 10 data sources and an ETL ... ETL and Data Quality Thu, 20 May 2010 13:26:37 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/how-to-consolidate-numerous-etl-processes-t545.htm#2203 http://forum.kimballgroup.com/etl-and-data-quality-f9/how-to-consolidate-numerous-etl-processes-t545.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-from-250-disparate-customer-host-systems-t548.htm bg4 My task is to produce an ETL process that pulls data daily from our customer systems (which can be most any database/OS). The data will be loaded into our central system, an analysis will be run and the aggregate data solution sent back to all customers. Any recommendations for tools to achieve this scenario? I'd like to be able to connect to each customer's host (via whatever protocol) and execute the ETL from my central location. I will be able to install a client on the customer's system, ... ETL and Data Quality Thu, 20 May 2010 17:32:47 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-from-250-disparate-customer-host-systems-t548.htm#2205 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-from-250-disparate-customer-host-systems-t548.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-and-cleaning-randomly-updated-timestamped-data-from-a-3rd-party-t541.htm ianschenck Hi, I have a datasource which is a 3rd party api which gives us basically a ton of atom feeds. I'm not sure if this is a problem in ETL or a problem with my design, but I'm leaning towards solving it by loading/cleaning. Anyhow... We get data on videos from a variety of 3rd party sites detailing how many times they've been viewed (among other things, but let's just focus on views). It's a rather simple dw design; there is a date dimension and a time-of-day dimension (time-of-day analysis ... ETL and Data Quality Sat, 15 May 2010 21:10:24 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-and-cleaning-randomly-updated-timestamped-data-from-a-3rd-party-t541.htm#2184 http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-and-cleaning-randomly-updated-timestamped-data-from-a-3rd-party-t541.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/updating-fact-table-best-practice-t520.htm marric Hi, Ssystem used : Windows 2003 srv SQL Server 2005 STD for the OLTP SQL Server 2005 Ent for the DW with - SSIS for the ETL - SSAS for the cube ASP.NET control to display the OLAP for the user I know theres is not "one size fits all" solution but I would like to have input about the best pratice when dealing with fact table update. We are using a OLTP database to manage paramedic events. Those events are entered in the OLTP database and they can be changed later for ... ETL and Data Quality Wed, 05 May 2010 18:31:30 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/updating-fact-table-best-practice-t520.htm#2110 http://forum.kimballgroup.com/etl-and-data-quality-f9/updating-fact-table-best-practice-t520.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/use-of-edw-technologies-etl-and-reporting-tools-to-create-client-reports-t517.htm dansawyer Good afternoon, We are a smaller business creating a 'software as a service' application for clients. We have been using a transaction grain level EDW as a query tool for our in house users. Concurrently customers have asked for special 'reconciliation' level reports. It would seem feasible to use the EDW as the basis for these custom reports. However an internal concern has arisen as to the 'validity' of the EDW data. The business driver is, based on the in place logic to build and verify ... ETL and Data Quality Sun, 02 May 2010 20:00:39 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/use-of-edw-technologies-etl-and-reporting-tools-to-create-client-reports-t517.htm#2101 http://forum.kimballgroup.com/etl-and-data-quality-f9/use-of-edw-technologies-etl-and-reporting-tools-to-create-client-reports-t517.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-auditing-standards-and-how-to-implement-them-t492.htm Mj1978 Any Tool specific examples will be appreciated: e.g Datastage and Informatica

Thanks
MJ]]> ETL and Data Quality Tue, 13 Apr 2010 16:50:15 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-auditing-standards-and-how-to-implement-them-t492.htm#2024 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-auditing-standards-and-how-to-implement-them-t492.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-replacement-suggestions-needed-t507.htm Traci Marcero Greetings, I’ve just begun the process of researching a fitting ETL tool that will allow for a simplistic user friendly source extraction to staging configuration for minimal licensing costs. We are in the works of decommissioning a very old version of DataMirror (4.3) that pumps data from our i5 system into a SQL Server 2000 data warehouse. The reason for the decommission is that we no longer hold licensing for the DataMirror product, thus we are not supported on it. Additionally, management ... ETL and Data Quality Wed, 21 Apr 2010 21:02:19 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-replacement-suggestions-needed-t507.htm#2065 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-replacement-suggestions-needed-t507.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/assign-default-value-for-a-dimension-t486.htm MLaforge Hi, We have the following case: For the product code in the source data, lookup the product natural key in the product dimension and assign the surrogate key returned by the lookup to the fact. If the lookup fails, assign the unknown product whose natural key is "Unknown". The unknown product is entered initially in the dimension and should never change afterwards, we use negative SID for default entries (-1 for "unknown", -2 for "not applicable" and -3 for "not ... ETL and Data Quality Wed, 07 Apr 2010 20:36:52 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/assign-default-value-for-a-dimension-t486.htm#1990 http://forum.kimballgroup.com/etl-and-data-quality-f9/assign-default-value-for-a-dimension-t486.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/extracting-data-when-there-is-no-timestamp-in-the-source-system-t446.htm DWH
Thanks]]> ETL and Data Quality Sat, 20 Feb 2010 15:00:42 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/extracting-data-when-there-is-no-timestamp-in-the-source-system-t446.htm#1803 http://forum.kimballgroup.com/etl-and-data-quality-f9/extracting-data-when-there-is-no-timestamp-in-the-source-system-t446.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/populating-bridge-tables-technics-t476.htm mar78 Hi, I'm trying to figure out which are the best practises concerning the populating and maintaining bridge tables using ETL tools. I have a transaction fact tables, and each row of it involves 1 or more rows of Departement dimension table. I guess i obviously have to load the bridge before loading the fact table, could this be done in the same data flow ? From above all the possible combinations, how could we determine on the fly, the unique sets for populating the bridge instances ... ETL and Data Quality Tue, 30 Mar 2010 06:58:20 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/populating-bridge-tables-technics-t476.htm#1938 http://forum.kimballgroup.com/etl-and-data-quality-f9/populating-bridge-tables-technics-t476.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/nulls-in-source-lead-to-duplicates-in-fact-table-t469.htm mselway Hi, my historic source data has inconsistencies. I have a product with a 1:m to product_detail which has a 1:m to invoice which has a 1:m to invoice detail. Invoice_detail is the grain of my fact table, but some of the historic data has nulls in the link from product_detail to invoice and invoice to invoice_detail. My load process assigns a -1 (unknown member) to where these nulls occur, but this results in duplicate values in the invoice_detail attribute which, being the grain of the fact ... ETL and Data Quality Fri, 19 Mar 2010 09:39:05 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/nulls-in-source-lead-to-duplicates-in-fact-table-t469.htm#1899 http://forum.kimballgroup.com/etl-and-data-quality-f9/nulls-in-source-lead-to-duplicates-in-fact-table-t469.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/initial-stage-tables-from-multiple-sources-t447.htm AaronLS I was somewhat confused while reading this: The DWH ETL Toolkit, On page 116, Chapter 4, Cleaning and Conforming, Assumptions, in the first bullet "...customer information from various sources initially can be staged in a table [. . .] whose configuration is the same regardless of the data source. Such a structure could be used for incoming data from various data sources deposited and queued for ETL work." If I understand this correctly, what is being described is a scenario where ... ETL and Data Quality Wed, 24 Feb 2010 23:06:26 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/initial-stage-tables-from-multiple-sources-t447.htm#1804 http://forum.kimballgroup.com/etl-and-data-quality-f9/initial-stage-tables-from-multiple-sources-t447.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/audit-dimension-for-a-dimension-t445.htm kskistad The books describe how to create a useful audit dimension for the fact table rows, but what about the dimension table rows? Why isn't that mentioned in the books? Isn't the data quality and lineage also important for dimensions? Would you create a Audit dimension outrigger with a foreign key in the associated dimension table? Would this be the same Audit dimension as the fact table audit dimension, so all data is in one table? Or would you simply add audit fields to each of your dimension ... ETL and Data Quality Thu, 18 Feb 2010 23:04:15 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/audit-dimension-for-a-dimension-t445.htm#1800 http://forum.kimballgroup.com/etl-and-data-quality-f9/audit-dimension-for-a-dimension-t445.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/fact-dimension-reload-design-t431.htm surfinwind Hello fellow DW/BI practitioners! I am in process of designing ETL for an enterprise-wide DW. One of the ideal functionality is to have a parameter driven capability to re-load historical fact transactions along with dimensions. What ETL metadata needs to be present within facts/dimensions in order to design an automated process for such re-loads? ... assuming dimensions are standard type 2. Here is what my thoughts are: Both dimension/facts need to have columns for source system effective ... ETL and Data Quality Thu, 11 Feb 2010 16:38:54 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/fact-dimension-reload-design-t431.htm#1749 http://forum.kimballgroup.com/etl-and-data-quality-f9/fact-dimension-reload-design-t431.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/real-time-data-aggregation-t425.htm asridhar7 i would like get some inputs, best practices around populating Periodic aggregation and Accumulated Aggregation tables from Real Time Data Source (PowerExchange) ETL and Data Quality Tue, 09 Feb 2010 16:01:33 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/real-time-data-aggregation-t425.htm#1732 http://forum.kimballgroup.com/etl-and-data-quality-f9/real-time-data-aggregation-t425.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-architecture-t422.htm tropically Hi My company is currently planning on building a datawarehouse. We are an oracle shop and are considering the approach for data migration from client servers to the datamart. Thoughts we have 1) Asynchronous CDC . I believe oracle now is replacing this with Golden Gate. 2) Export / Import 3) Db Links 4) Oracle data pump. (Similar to export / import) Questions Anyone have any other option. Not sure if ETL tools are used for this. My thoughts have been that the ETL tools are used ... ETL and Data Quality Fri, 05 Feb 2010 16:04:39 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-architecture-t422.htm#1723 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-architecture-t422.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/detecting-changes-t411.htm SCAI_Andre Hi, I have a question regarding the pros and cons of the several approaches of detecting changes in dimensional data. The ETL Toolkit mentions several techniques, but I'm not so much interested in the technical side of the issue (log scraping vs. daily dumps) but the logical view on the data: Image an operational system storing the customer data in a table called t_customer. This table has a "last_changed_timestamp" column, set by a trigger. I "could" extract all ... ETL and Data Quality Mon, 25 Jan 2010 10:27:00 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/detecting-changes-t411.htm#1686 http://forum.kimballgroup.com/etl-and-data-quality-f9/detecting-changes-t411.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/database-kdb-t322.htm bi_at_nj
If so, do u use informatica with it ?


- Thanks,
bi_at_nj]]> ETL and Data Quality Sat, 31 Oct 2009 06:14:08 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/database-kdb-t322.htm#1337 http://forum.kimballgroup.com/etl-and-data-quality-f9/database-kdb-t322.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/handling-late-changes-to-type-2-attributes-t414.htm jkaszynski Hi. I'm trying to find a good way to handle late changes to Type 2 attributes. Please note that I am not asking about early-arriving facts that could be handled with an inferred dimension member. Rather, I am interested in the situation where the dimension member already exists, and you find out about a change to a Type 2 attribute after the fact. As an example of what I mean, in my client's data warehouse, each customer is associated with (covered by) a particular salesperson. Over time, ... ETL and Data Quality Tue, 26 Jan 2010 15:03:44 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/handling-late-changes-to-type-2-attributes-t414.htm#1695 http://forum.kimballgroup.com/etl-and-data-quality-f9/handling-late-changes-to-type-2-attributes-t414.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/one-fact-table-having-records-at-different-granularity-level-t409.htm jjagadish Hello, My question is just validation for an approach that we followed for one of our client requirements. Based on the requirement the FACT table should be constrcuted by the records from 2 Source table A and B. The structure of the table is as follows.. Table A PK1 PK2 PK3 TOT_AM Table B PK1 PK2 PK3PK4 AMOUNT Table A is the Parent table for Table B and maintains a One to Many relatioship. Due to the reporting requirement we got from the client it was decided that records ... ETL and Data Quality Thu, 21 Jan 2010 11:52:23 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/one-fact-table-having-records-at-different-granularity-level-t409.htm#1666 http://forum.kimballgroup.com/etl-and-data-quality-f9/one-fact-table-having-records-at-different-granularity-level-t409.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/fact-table-loads-t320.htm bi_at_nj Here is a scenario: * Fact table contains 100 million records * Monthly Load of 10million is done at the end of the month to the same fact table In this scenario, how do you handle the indexes in fact table at the time of load? If indexes are made unusable before load, then the rebuild index is time consuming after the load. So, what strategy do you follow in such scenarios? How do we ensure that the data is made available to the users in the shortest possible time. (Assume ... ETL and Data Quality Sat, 31 Oct 2009 04:54:03 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/fact-table-loads-t320.htm#1335 http://forum.kimballgroup.com/etl-and-data-quality-f9/fact-table-loads-t320.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/updating-periodic-snapshot-fact-tables-t401.htm jimbo1580 What are the best practices for maintaining periodic snapshot fact tables if the atomic data that created them is modified? We have a couple periodic snapshot tables that capture new inventory and new revenue at the end of every month. Sometimes the business users may correct data at a later time or back date inventory or revenue weeks later. Should I be recalculating the periodic snapshot for the affected periods? How would I know what periods to recalculate for? What if the revenue ... ETL and Data Quality Wed, 13 Jan 2010 15:19:49 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/updating-periodic-snapshot-fact-tables-t401.htm#1632 http://forum.kimballgroup.com/etl-and-data-quality-f9/updating-periodic-snapshot-fact-tables-t401.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-question-for-loading-a-fact-table-t399.htm kenny I have a Fact table with the following facts which is supposed to be loaded daily Case_Statistics_Fact ================ CaseId (FK) Time_Id(FK) EsitmatedCost EstimatedStartdate ActualCost ActualStartDate Here is the scenario when a case is added it is added with the EstimatedCost and EstimatedStartdate lets say (Jan 1,2010) after a week or x no of days lets (Jan 7,2010) the actualcost and actualstartdate is updated in the source system The daily ETL will pickup the Jan 1,2010 ... ETL and Data Quality Mon, 11 Jan 2010 16:32:56 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-question-for-loading-a-fact-table-t399.htm#1626 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-question-for-loading-a-fact-table-t399.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/part-of-fact-information-arives-later-t400.htm hennie7863 For a customer i have the following challenge: When fact data arrives it will be loaded into the fact table. But it's possible that some data of a specific fact is not yet available but it will arrive later (late arriving (PART OF) fact). I was thinking how to ETL this. May be someone could help me out. I can think of two options: dirty update of datawarehouse tables(hmmm) or if i know that certain recordinformation comes later, keep track of them. When the information is arrived load the complete ... ETL and Data Quality Mon, 11 Jan 2010 19:55:19 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/part-of-fact-information-arives-later-t400.htm#1627 http://forum.kimballgroup.com/etl-and-data-quality-f9/part-of-fact-information-arives-later-t400.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/how-to-implement-filebased-filtering-screens-in-perl-t390.htm mugen_kanosei I am having to recode my etl for performance reasons and am trying to figure out how to reimplement some features that were easy when it was database staged vs file staged. The original steps involved querying the source and loading it into a staging table in the database. From there I have a table that holds all the sql screens for checking errors that perl iterates over and runs the sql. Complex screens were easy because the lookup tables and dimensions were all in the database and the SQL ... ETL and Data Quality Tue, 05 Jan 2010 07:20:26 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/how-to-implement-filebased-filtering-screens-in-perl-t390.htm#1605 http://forum.kimballgroup.com/etl-and-data-quality-f9/how-to-implement-filebased-filtering-screens-in-perl-t390.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/scd-type2-etl-design-t331.htm KK_ETL Hi, We are trying to build a new data warehouse. Planning to capture the data as SCD Type2 in the Data Warehouse. However, the source system doesn't has any date fields for extraction. Let me explain the scenario: Table Name : DWS_CUST Scenario 1 Columns : Customer No (PK) Cust_Name Cust_address1 Cust_address2 Cust_address3 Cust_address4 Start_date End_date Data 1 XYZ 34 ... ETL and Data Quality Thu, 05 Nov 2009 17:02:46 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/scd-type2-etl-design-t331.htm#1369 http://forum.kimballgroup.com/etl-and-data-quality-f9/scd-type2-etl-design-t331.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/about-business-intelligance-in-data-warehousing-t361.htm Anonymous Business Intelligence (BI) refers to skills, processes, technologies, applications and practices used to support decision making. BI technologies provide historical, current, and predictive views of business operations. Common functions of Business Intelligence technologies are reporting, OLAP, analytics, data mining, business performance management, benchmarking, text mining, and predictive analytics. Business Intelligence often aims to support better business decision-making.[1] Thus ... ETL and Data Quality Fri, 04 Dec 2009 10:42:57 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/about-business-intelligance-in-data-warehousing-t361.htm#1476 http://forum.kimballgroup.com/etl-and-data-quality-f9/about-business-intelligance-in-data-warehousing-t361.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/hand-coded-etl-revisited-t35.htm Nigel Nichols
I read Gary Nissen's article, 'Is Hand-Coded ETL the Way to Go?', with interest.

Given that this was written nearly six years ago, I wonder whether the position has changed i.e. whather ETL tools are now more strongly recommended ober hand-coding.

Nigel]]> ETL and Data Quality Fri, 13 Feb 2009 15:41:10 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/hand-coded-etl-revisited-t35.htm#143 http://forum.kimballgroup.com/etl-and-data-quality-f9/hand-coded-etl-revisited-t35.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/business-intelligence-tool-t345.htm Jaswanth How to work on repository ETL and Data Quality Mon, 16 Nov 2009 05:13:22 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/business-intelligence-tool-t345.htm#1404 http://forum.kimballgroup.com/etl-and-data-quality-f9/business-intelligence-tool-t345.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-data-without-key-t338.htm hennie7863 For a customer of mine i'm loading messages in a datawarehouse. The messages don't have an id(?!). With this message i want to load some dimensions and the fact. Are there best/Good practices of doing this? Currently i'm thinking of giving these messages a self generated key. Load the data and compare afterwards if the load went ok. So the dimensions are using this key and the fact. I'm not very happy with this solution. So i hope that someone gives me a better solution. O and we're talking ... ETL and Data Quality Thu, 12 Nov 2009 12:56:49 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-data-without-key-t338.htm#1384 http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-data-without-key-t338.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-informatica-32bit-vs-64bit-t321.htm bi_at_nj
If so what kind of problems are you facing in the 64 bit version.

Is there anything you like the most in the 64bit version?

- Thanks in advance,
bi_at_nj]]> ETL and Data Quality Sat, 31 Oct 2009 04:55:58 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-informatica-32bit-vs-64bit-t321.htm#1336 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-informatica-32bit-vs-64bit-t321.htm