http://forum.kimballgroup.com/etl-and-data-quality-f9/-t1.htm Fri, 05 Feb 2010 16:04:39 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/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/quality-of-our-data-and-datawarehouse-t420.htm gtmikwen
Thanks for helping us on our last question.
We have looked at different open source offers for data integration. And we are now looking for data quality software able to complete the data integration software.

Are there any packages able to suit our needs? Different or same software makers are fine for us.

Thank you.]]> ETL and Data Quality Wed, 03 Feb 2010 15:25:19 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/quality-of-our-data-and-datawarehouse-t420.htm#1718 http://forum.kimballgroup.com/etl-and-data-quality-f9/quality-of-our-data-and-datawarehouse-t420.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/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/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/open-source-data-program-t393.htm gtmikwen
We are benchmarking a few data integration solutions now, finding most of these tools interesting.

We have been looking at open source solutions and are wondering which of the proprietary or open source solutions could be best for us to use. Knowing that our company is growing with an even more important client base.

Thank you.]]> ETL and Data Quality Wed, 06 Jan 2010 14:35:08 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/open-source-data-program-t393.htm#1612 http://forum.kimballgroup.com/etl-and-data-quality-f9/open-source-data-program-t393.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 melina386 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 http://forum.kimballgroup.com/etl-and-data-quality-f9/source-for-accumulating-snapshot-fact-table-t223.htm kbarrett I have a question about accumulating snapshots and I was hoping someone could shed some light on the subject. Does Kimball give any guidance anywhere (books, online, etc.) as to whether one should build the supporting transactional fact tables that relate to the accumulating snapshot before building the snapshot? We are looking at building a snapshot fact table for the entire insurance policy lifecycle (from initial submission to quote to binding/issuing the policy to first claim (if ... ETL and Data Quality Tue, 21 Jul 2009 16:23:57 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/source-for-accumulating-snapshot-fact-table-t223.htm#957 http://forum.kimballgroup.com/etl-and-data-quality-f9/source-for-accumulating-snapshot-fact-table-t223.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/open-source-etl-t115.htm AzeemFarooqui
Our client is keen on using java code to perform ETL. I don't feel this is a viable option and am looking into the option of using an open source ETL tool. Does any one have any useful information on open source ETL and the pros/cons of these against standard ETL tools?

I appreciate your help.

Regards
Azeem]]> ETL and Data Quality Tue, 21 Apr 2009 09:52:04 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/open-source-etl-t115.htm#509 http://forum.kimballgroup.com/etl-and-data-quality-f9/open-source-etl-t115.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/open-source-etl-vs-commercial-etl-t217.htm dellsters Anybody have experience with open source etl like Talend or Pentaho? They are becoming more popular, and I was wondering what advantages/ disadvantages of open source vs commercial etl tools. Any downsides of using open source for SOX or HIPAA? ETL and Data Quality Thu, 16 Jul 2009 04:56:32 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/open-source-etl-vs-commercial-etl-t217.htm#931 http://forum.kimballgroup.com/etl-and-data-quality-f9/open-source-etl-vs-commercial-etl-t217.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/techniques-for-updating-existing-fact-records-t275.htm johnpaulmurphy I wanted to get peoples opinion on what techniques work best when you have to do updates to Fact rows i.e. overwrite, adjust but keep audit etc...

Thanks.]]> ETL and Data Quality Wed, 16 Sep 2009 18:57:59 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/techniques-for-updating-existing-fact-records-t275.htm#1164 http://forum.kimballgroup.com/etl-and-data-quality-f9/techniques-for-updating-existing-fact-records-t275.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/maintaining-reference-tables-t282.htm ajviolette I'm looking for some feedback regarding what tools others are using to maintain data in Data Warehouse specific reference tables. These tables are typically used to provide cross reference or hierarchical definitions for the source data during ETL processing. My previous company developed web pages for maintaining these special purpose tables that were stored in the staging area of the Data Warehouse. My current company has been using Excel spreadsheets to store and maintain ... ETL and Data Quality Thu, 01 Oct 2009 20:32:32 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/maintaining-reference-tables-t282.htm#1211 http://forum.kimballgroup.com/etl-and-data-quality-f9/maintaining-reference-tables-t282.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/conforming-dimensions-standardising-de-duplicating-and-suvivorship-t278.htm johnryan Hi, I'm currently reading the DW ETL toolkit, which seems to have some excellent ideas. However, as it doesn't come with any downloads (e.g example SSIS packages) - I'm struggling to understand a few things. If anyone can answer the following it would be most appreciated: 1)2 sources for a dimenion interests me. Am I right in understanding that the issues here are essentially that we could have an attribute (e.g. customer location for the Customer Dim) in both data sources. Therefore, ... ETL and Data Quality Wed, 23 Sep 2009 15:02:19 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/conforming-dimensions-standardising-de-duplicating-and-suvivorship-t278.htm#1186 http://forum.kimballgroup.com/etl-and-data-quality-f9/conforming-dimensions-standardising-de-duplicating-and-suvivorship-t278.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/financail-calendarseed-data-t232.htm GBS74 I am tring to write pl/sql procedure to create financial calendar seed date. I have confusion about ... how to code to get financial week /period/start and end date for calendar if financial year and quarter start at first monday of April. any idea or script ?
regards]]> ETL and Data Quality Wed, 29 Jul 2009 21:29:42 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/financail-calendarseed-data-t232.htm#999 http://forum.kimballgroup.com/etl-and-data-quality-f9/financail-calendarseed-data-t232.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/business-logic-dwh-vs-source-system-t37.htm inglev Hello everyone, We are currently implementing a Data Warehouse (consolidating data from several source systems) and we had an argument on where the business logic should reside. Simplified example: The source contains the fields “amount”, a “discount” and a “total amount”. The “total amount” is supposed to be the “amount”*(1-“discount”), but for us, the DWH team, it is already available as a readily loadable fixed ... ETL and Data Quality Tue, 17 Feb 2009 08:07:12 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/business-logic-dwh-vs-source-system-t37.htm#154 http://forum.kimballgroup.com/etl-and-data-quality-f9/business-logic-dwh-vs-source-system-t37.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/does-it-belong-in-the-stage-tables-or-fact-tables-t270.htm kskistad If I have a fact table with a "counter" fact, for example a customer places an order, but then goes back and changes parts of that order any number of times, I want to store how many times the customer changed his/her order. The grain of the fact table is the orderID. The changes are captured at the source in a change history table, but that table only stores the previous 5 days changes. I see two ways to do this: create another table, a factless fact table, and capture the history ... ETL and Data Quality Thu, 10 Sep 2009 16:37:01 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/does-it-belong-in-the-stage-tables-or-fact-tables-t270.htm#1150 http://forum.kimballgroup.com/etl-and-data-quality-f9/does-it-belong-in-the-stage-tables-or-fact-tables-t270.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/transposing-from-columns-to-rows-t268.htm nxlefrancois I have data extracts from transaction system containing multiple measures (columns) all being of the same type of indicator, for which I would like to transform into multiple rows in my fact table. E.g. Extract contains (for each row): date location nbr_visitors_can_bc nbr_visitors_can_on nbr_visitors_can_qc ... (one for each of the 10 Canadian provinces) nbr_visitors_us_ma nbr_visitors_us_wi nbr_visitors_us_ok ... (one for each of the 50 US states) I'd like my FACT_VISITATION ... ETL and Data Quality Wed, 09 Sep 2009 18:25:46 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/transposing-from-columns-to-rows-t268.htm#1145 http://forum.kimballgroup.com/etl-and-data-quality-f9/transposing-from-columns-to-rows-t268.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-architecture-and-control-flow-t116.htm monsieur_arrie Hi Folks, I am new to the forums, they look like an interesting place to discuss BI issues and troubles. So, I am not new to BI, but we have done a lot of re-architecting of the etl systems with servers being moved, wan considerations etc. Currently, our source system extracts, compresses and ftps data to our etl area. File names are representative of the 'day' the data refers to. This allows data o be queued in case of ftp failure etc. My etl system is entirely file based. ... ETL and Data Quality Tue, 21 Apr 2009 15:32:52 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-architecture-and-control-flow-t116.htm#510 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-architecture-and-control-flow-t116.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-server-sizing-t266.htm sjain Hi, Could help me to suggest what should be the possible considerations for sizing an ETL Server? Like platform( UNIX, Linux, Windows), Volume of data(in GBs), type of source(relational, application, files), type of transformation/transforms(type of operartion such as SCD) ,Batch - window( Daily, weekly, monthly) sampling rate, commit size How it affect the sizing (directly or indirectly) Or is it depend upon the tool you are using like SAP BO Data Services, Informatica, Datastage Any ... ETL and Data Quality Mon, 07 Sep 2009 08:04:22 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-server-sizing-t266.htm#1141 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-server-sizing-t266.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-server-hardware-configuration-t255.htm Devendra Naik Example Server Type(Intel/Itaninum etc ) , Number of CPU/cores / Memory / Disk configuration and size .
We are planning to upgrade our env. and to start a new project, I would like]]> ETL and Data Quality Fri, 21 Aug 2009 15:12:17 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-server-hardware-configuration-t255.htm#1093 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-server-hardware-configuration-t255.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/incremetal-load-from-1-fact-to-another-fact-t241.htm vibhutidevatraj Hi i have a situation. There is a fact A and fact B which acts as a source for another fact C. The Fact A and B contains detail values which are loaded daily and Fact C contains aggregated values which is loaded weekly. Fact A and B contains Source_file_name, source_file_date information in them but fact C has no file informations. My questions are Is it a good practice to have multiple facts as source for another fact? If yes, then how the data sould be loaded incrementally in ... ETL and Data Quality Tue, 04 Aug 2009 09:13:58 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/incremetal-load-from-1-fact-to-another-fact-t241.htm#1025 http://forum.kimballgroup.com/etl-and-data-quality-f9/incremetal-load-from-1-fact-to-another-fact-t241.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/perfect-design-vs-time-to-implement-t237.htm DanColbert
I have a situation where the time I have to launch a business process is shorter than the time I need to get a new dimension designed and pushed. I'm thinking about implementing the process without that new dimension and adding it later.

What are the pitfalls I can expect if I choose this rout?

Thanks in advance!

Dan]]> ETL and Data Quality Fri, 31 Jul 2009 14:41:36 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/perfect-design-vs-time-to-implement-t237.htm#1009 http://forum.kimballgroup.com/etl-and-data-quality-f9/perfect-design-vs-time-to-implement-t237.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/using-3rd-party-sort-packages-in-etl-stream-t216.htm juz_b I was wondering if anyone can share your experience with using a 3rd party Sort package (CoSort, SyncSort etc) in your ETL Stream. I have been using Business Objects Data Integrator for the last 5 years and never had a chance to integrate a 3rd party Sort package into the ETL stream. My understanding is that it is always faster to process Flat file (source and target), compared to a database table. My dilemma is that by processing using a flat file, you lose the ability to query in the ... ETL and Data Quality Wed, 15 Jul 2009 21:13:47 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/using-3rd-party-sort-packages-in-etl-stream-t216.htm#926 http://forum.kimballgroup.com/etl-and-data-quality-f9/using-3rd-party-sort-packages-in-etl-stream-t216.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/audit-dimension-help-t198.htm mugen_kanosei Hello all. I'm at the stage now where im building the audit dimension for my warehouse. I am having a little trouble figuring out a few things though. How do you aggregate the data from the error event fact table into the audit dimension? Some records will have 5 screens fail, some 3, but they may not all be the same screens. How do you summarize all these into a few unique audit records? The best I have come up with so far is to write a select statement to make audit dimension columns for ... ETL and Data Quality Mon, 29 Jun 2009 23:43:58 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/audit-dimension-help-t198.htm#874 http://forum.kimballgroup.com/etl-and-data-quality-f9/audit-dimension-help-t198.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/staging-activities-t196.htm kskistad What are the common staging activities that the Kimball method recognizes? Traditionally I have used staging databases to 1) Decouple from the source-to-Data Mart for restartability 2) Consolidate multiple sources and source formats into a single homogeneous environment 3) Data cleansing, such as populate missing data, scrubbing fields, etc. But some articles I've seen talk about surrogate key handling and conforming within the staging database. Most ETL tools I've used will read ... ETL and Data Quality Mon, 29 Jun 2009 17:30:24 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/staging-activities-t196.htm#868 http://forum.kimballgroup.com/etl-and-data-quality-f9/staging-activities-t196.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/large-fact-table-and-maintaining-periodic-snapshot-practice-t182.htm buzzer75 I would like some opinions with my approach here. I am trying to replace an overkill lift and load ETL process that basically replicated the entire universe of dataset every period instead of doing just Delta Load. In this delta approach, I have a stage table with new fact rows and I merge it to the target base table to load delta. I also a current flag to the new and old records. For reporting purposes, the current picture is all we need and I use the flag. For weekly and monthly view if needed, ... ETL and Data Quality Fri, 19 Jun 2009 00:30:25 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/large-fact-table-and-maintaining-periodic-snapshot-practice-t182.htm#830 http://forum.kimballgroup.com/etl-and-data-quality-f9/large-fact-table-and-maintaining-periodic-snapshot-practice-t182.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/interval-time-sum-column-t173.htm Enrico Hello, In my data warehouse I have a fact table that joins with calendar table on BETWEEN clause ( calendar.date between myfact.startdate and myfact.enddate ). In calendar date I have a record for each day. I need to sum a fact table column in my query only one time for each fact table records. Now my fact table column is sum for each records of the query. select sum(myfact.value) from calendar, myfact where calendar.date between myfact.startdate and myfact.enddate How ... ETL and Data Quality Mon, 15 Jun 2009 11:10:36 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/interval-time-sum-column-t173.htm#801 http://forum.kimballgroup.com/etl-and-data-quality-f9/interval-time-sum-column-t173.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/updating-historic-transactions-and-snapshots-t176.htm cal.sneds Hi, I have a project where we will be building Transactional Fact Tables along with daily Periodic Snapshot Fact tables based on the same data. Now, I'll try to be as brief as possible, but this needs some explaining, this is the dilemma; The task seems straight forward, but the business is able to go back in time and update some of the transactional records and they want to reflect this in the DW, while keeping the old record along with the update as a new record and an audit ... ETL and Data Quality Tue, 16 Jun 2009 10:23:10 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/updating-historic-transactions-and-snapshots-t176.htm#810 http://forum.kimballgroup.com/etl-and-data-quality-f9/updating-historic-transactions-and-snapshots-t176.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/enormous-data-size-t143.htm jaiveeru This all started from my query 2 weeks back on solving data modeling problem in recursive hierarchical data table. I posted a query and got replies, almost instantly. All replies pointing to one solution. I did exactly as suggested i.e. I resolved the tree structure into a flat table so that there is no many to many items left. This was done in addition to resolving all one to many data other tables as well. I happily added all redundant fields into table and my fact table size now is ... ETL and Data Quality Thu, 14 May 2009 15:04:30 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/enormous-data-size-t143.htm#622 http://forum.kimballgroup.com/etl-and-data-quality-f9/enormous-data-size-t143.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-fact-table-t163.htm rpcasey001 In reading a previous thread, I understand that when a Dimension is changed by a SCD type 2, a new record is created for the new key and no update is needed on the previously existing facts. However, what happens in the loading of the fact table that lets the process know that it has to load a fact again? Does this happen since the combination of keys has changed and it is recognized as a new row? Should a fact table load check for existing rows, if so, how? -- RPC ETL and Data Quality Mon, 01 Jun 2009 16:03:54 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-fact-table-t163.htm#741 http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-fact-table-t163.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/stage-table-for-fact-data-t165.htm rpcasey001
--- RPC]]> ETL and Data Quality Tue, 02 Jun 2009 14:20:10 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/stage-table-for-fact-data-t165.htm#755 http://forum.kimballgroup.com/etl-and-data-quality-f9/stage-table-for-fact-data-t165.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-fact-table-t38.htm bakunian Hi, I have following tables FACT, A_DIM, B_DIM. How do I update relationship between a_key and b_key in the FACT table when new record arrives in TYPE2 a_dim dimension? Below is simple create scrip to illustrate what I mean. create table fact (a_key integer, b_key integer); create table a_dim (a_key integer, a_id integer, a_string varchar2(20)); create table b_dim (b_key integer, b_id integer, b_string varchar2(20)); insert into a_dim values (1, 100, 'value1'); insert into ... ETL and Data Quality Tue, 17 Feb 2009 20:56:00 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-fact-table-t38.htm#160 http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-fact-table-t38.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-data-aggregated-to-date-into-fact-table-t152.htm grahan007 I have following source table: Source Table ID App_Version Os_Version Date 9123305 2.5.2.60 Windows NT 5.1 2/15/2009 9123306 2.5.2.60 Windows NT 5.1 2/15/2009 9123307 2.5.2.60 Windows NT 5.1 2/15/2009 9123308 2.5.2.60 Windows NT 5.1 2/15/2009 9123309 2.5.2.60 Windows NT 6.0 2/15/2009 9123310 2.5.2.60 Windows NT 5.1 2/15/2009 9123311 2.5.2.60 Windows NT 5.1 2/15/2009 9123312 2.5.2.60 ... ETL and Data Quality Tue, 26 May 2009 13:12:06 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-data-aggregated-to-date-into-fact-table-t152.htm#706 http://forum.kimballgroup.com/etl-and-data-quality-f9/loading-data-aggregated-to-date-into-fact-table-t152.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/hand-coding-etl-questions-t141.htm mugen_kanosei I know the numerous pros of using an ETL tool, but due to circumstances outside my control I have to hand code the ETL. My questions are in regards to actual coding practices. I am currently loading a couple of dimensions using perl. So far the entire load is in one perl script that is set to run every night. I'm wanting to code something more like what the books suggest, metadata driven, batch scheduling, etc. But im unsure how this is getting handled in general. At first I thought metadata ... ETL and Data Quality Thu, 14 May 2009 00:40:10 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/hand-coding-etl-questions-t141.htm#610 http://forum.kimballgroup.com/etl-and-data-quality-f9/hand-coding-etl-questions-t141.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/sql-or-pl-sql-for-hand-coding-etl-t146.htm tropically Wanted to get a general idea, as to what others have used when hand coding ETL for loading data into data marts.
My thoughts : Straight inserts , updates, merges are faster, however can't capture errors. Pl/SQL is more flexible allowing to log errors if any.

Any thoughts would be appreciated.]]> ETL and Data Quality Mon, 18 May 2009 16:20:11 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/sql-or-pl-sql-for-hand-coding-etl-t146.htm#635 http://forum.kimballgroup.com/etl-and-data-quality-f9/sql-or-pl-sql-for-hand-coding-etl-t146.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/date-dimension-in-oracle-with-one-sql-statement-t52.htm ubethke CREATE TABLE d_date AS SELECT n AS Date_ID, TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date, TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD') AS Days, TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short, TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') AS Month_Num, TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') ... ETL and Data Quality Thu, 26 Feb 2009 17:54:07 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/date-dimension-in-oracle-with-one-sql-statement-t52.htm#245 http://forum.kimballgroup.com/etl-and-data-quality-f9/date-dimension-in-oracle-with-one-sql-statement-t52.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/fact-begin-and-end-dates-t139.htm tropically deleted ETL and Data Quality Wed, 13 May 2009 22:29:29 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/fact-begin-and-end-dates-t139.htm#607 http://forum.kimballgroup.com/etl-and-data-quality-f9/fact-begin-and-end-dates-t139.htm http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-load-dropping-indexes-and-constraints-t60.htm AzeemFarooqui
I am currently working on an ETL solution using BODI and SQL Server 2005. Our data warehouse is very small (no more than 5mb) currently and expected growth over the next year is not going to exceed 15mb.

Based on the above volume estimates does it make sense to drop existing indexes/constraints when performing the ETL load into the fact table?

I'd appreciate other peoples comments and views on this.

Regards
Azeem]]> ETL and Data Quality Tue, 03 Mar 2009 12:32:56 GMT http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-load-dropping-indexes-and-constraints-t60.htm#290 http://forum.kimballgroup.com/etl-and-data-quality-f9/etl-load-dropping-indexes-and-constraints-t60.htm