'Extract, Transform and Load': Data load processing, normally in the context of data warehouse systems.

learn more… | top users | synonyms

2
votes
2answers
82 views

Best Practices for Testing Application Dashboard Data

We have a dashboard that contains metrics based on a 6 month reporting period. Users log in and see their metrics based on the current 6-month reporting period. Every 6 months, we load new metrics ...
1
vote
1answer
20 views

How to deal with multiple error columns in data flow

I'm writing a simple package to load data into tables and a I have multiple destinations within the same package. I'm attempting to first attempt batch inserts but want to fall back to row by row in ...
0
votes
1answer
260 views

How to perform ETL from RDBMS to Neo4j

I have a mature 50+ tables web application based on mySQL. In order to do some advanced data mining I want to use Neo4j and the goodnes of cypher. However I'm having a hard time migrating my data from ...
0
votes
0answers
25 views

ssis ado.net destination has failed to acquire the connection

I am Transferring data from sql server source db to sql server destination db (cross db). but some times getting error "ssis ado.net destination has failed to acquire the connection.The connection may ...
0
votes
0answers
28 views

Merge data using Integration Service

Please Consider this scenario: I have a table in my database. I want move this data in my OLAP database using SSIS.I can move all record from my table to OLAP database.The problem is I don't know how ...
2
votes
1answer
181 views

MySQL failing and very slow on importing a large file

I have a 47 GB MySQL dump of a single table: http://dumps.wikimedia.org/commonswiki/latest/commonswiki-latest-image.sql.gz I ultimately want it into PostgreSQL, but since I didn't figure out an easy ...
0
votes
2answers
58 views

General ETL principle - ETL from unknown number of tables? [closed]

AFAIK, ETL integrates data from a fixed number of sources/tables. Is it possible that ETL can integrate data from an unknown number of tables which might be added in the future, given that these ...
2
votes
4answers
177 views

SQL Server: Database integration and synchronization

I have n SQL Server databases with the same data schema. On the other hand i have a web application that needs to retrieve, for some entities, the union of the information stored into the n ...
4
votes
4answers
568 views

best ETL design to transfer transaction tables records into the data-warehouse

I have 2 type of tables to populate the data-warehouse with every day, lookup tables or configuration tables with few 100s records, and thats easy where i just truncate and refill the table. but for ...
1
vote
0answers
40 views

Data Integration [closed]

This is not exactly a DBA question but definitely a DB question and I think StackExchange DBA is my best bet for this question. Here I go : are there any frameworks ( free / open source preferred ) ...
4
votes
1answer
129 views

Large difference in disk space used by partitions

I have a partitioned table that contains about 155 million records total. It's partitioned by year and the partitions for both the current and previous years contain about 25 million records each. ...
0
votes
0answers
47 views

Inserting Records - How to get next incremented PK for each insert

I'm using spoon to look for Devices that have several attributes that define its location, and then create a Cabinet in that location if one doesn't exist. Is there an operation that lets me get a ...
4
votes
1answer
1k views

Importing a large column (8000 chars) from Excel using SSIS

I'm trying to set up a regular import of an excel spreadsheet that we get from a vendor. I'm using SQL 2008 R2 SSIS to import it into a table. The problem connection manager is an OLE DB connection ...
3
votes
1answer
72 views

Replace column values?

I recently got into SSIS and haven't been able to find what I am trying to achieve anywhere on the web. I am trying to take some rows as input, and then replace one specific column's value with ...
18
votes
7answers
4k views

Any Open Source / free ETL out there?

I was using Pentaho Data Integration even before Pentaho bought it and call it that. I have the last free version. I went on their website recently to see if they had released another version only to ...
3
votes
2answers
442 views

Is this a good strategy for importing a large amount of data and decomposing as an ETL?

I have a set of five tables (a highly decomposed schema for an ETL if I understand the nomenclature) that I'm going to load via bulk import, then run some inserts from those five tables into a SLEW of ...
0
votes
0answers
201 views

Using system variables in kettle.properties

I have a spoon job that I want to run from two different servers. One is UNIX and one is Windows. The database is on the Windows server for each configuration. Each configuration pulls data from ...
11
votes
3answers
5k views

Are regular VACUUM ANALYZE stil recommended under 9.1?

I'm using PostgreSQL 9.1 on Ubuntu. Are scheduled VACUUM ANALYZE still recommended, or is autovacuum enough to take care of all needs? If the answer is "it depends", then: I have a largish database ...
7
votes
3answers
91 views

Tablespace on ephemeral storage

For performance reasons in some scenarios, e.g. Amazon EC2, you have access to a faster and cheaper storage device, which loses all its data on reboots, so it is called "ephemeral". This question is ...
-1
votes
1answer
77 views

data warehouse construction from mdf and ldf file via etl

I have sample data warehouse stored in the form of mdf and ldf format. I want to use any ETL tool to extract data and then load datawarehouse on the specified database with username, password and ...
1
vote
1answer
67 views

Tips for catching changes in table data over time interval for export to Data Warehouse

I've recently been tasked with exporting data from one of our databases to a central Data Warehouse. The export is supposed to be incremental since a full load every 24 hours would be a massive waste ...
0
votes
0answers
17 views

What's the proper term for the diverse systems to which a DW connects?

The data warehouse I've built gets data from transactional databases, web services, flat files, and a few other bits and pieces. It also writes back to some of these, and to analysis tools. I'm ...
1
vote
0answers
59 views

Transfer data from DynamoDB to SQL server 2008?

I want to transfer data from amazon dynamo DB to SQL server 2008. What are the tools or methods which can be used to do this ?
3
votes
0answers
367 views

SSIS Recordset Destination error

I have an SSIS package targeted at SQL Server 2012. I have it deployed into the Stored Packages in Integration Services and then have a SQL Server Agent job which executes it. The first part of the ...
0
votes
2answers
903 views

Advice for ETL from Oracle to SQL Server?

I'm building a data mart in SQL Server which needs to extract each month from an Oracle database. Currently this is done by exporting to flat files, then running all the inserts/updates in SQL ...
2
votes
4answers
3k views

Quick way to validate two tables against each other

We're doing an ETL process. When all is said and done there are a bunch of tables that should be identical. What is the quickest way to verify that those tables (on two different servers) are in ...
0
votes
1answer
222 views

How do I debug an ETL process that is unable to commit any records?

I used a DB link to connect to another DB and delete a couple of records. Later I ran an Informatica Workflow (ETL tool) to load data into that database using a DB connection that connects directly to ...
1
vote
1answer
106 views

Expanding contractions/number ranges into separate records [closed]

I am planning the migration of a directory of image files plus a FileMaker-database containing the corresponding metadata into an Imagic IMS database (formerly known as ImageAccess). The vendor ...
4
votes
1answer
149 views

Is there a standard way to replace codes with values from a lookup table for reporting or analytics?

Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel ...
2
votes
3answers
2k views

Inserting rows into other table whilst preserving IDENTITY

I'm performing ETL logic on a SQL Server table. I'm going to be synchronizing data from one table to another. For all of the records that need to be added to the target table based on the source ...
5
votes
1answer
2k views

ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?

Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and ...
2
votes
0answers
278 views

How to do automated archiving for a SQL Server database based on interpretation of database metadata?

I am looking for a solution giving me automated archiving of SQL Server databases based on a smart interpretation of table relationships that is able to archive records from related tables minimizing ...
1
vote
2answers
2k views

Error on SELECT - Primary filegroup is full

A SQL Server 2008 R2 database view unions several transaction table. The query has been slow over the couple of months and the dev team is planning to recreate the query. I need to ETL the results ...
1
vote
2answers
954 views

Moving data from Oracle to SQL Server : Table size optimization

I am loading data using SSIS from Oracle to an SQL Server database. One table I moved had a size of 4 MB under Oracle but when loaded to SQL Server, the size grows to 7.96 MB! I analyzed the size ...
1
vote
0answers
130 views

ETL import more dimensions from only 1 column

I was requested at work to learn ETL. I have already created a database without using ETL with some dimensions and consolidations, etc, containing above 1 billion numerical values. Now I have to ...
2
votes
1answer
640 views

Does surrogate key assignment for a fact table require that the source data has natural keys?

Let's say I have a simple OLTP database with orders, products, and customers: And from it, I am building a data mart with an orders fact table, product dimension, customer dimension, and date ...
4
votes
1answer
284 views

Dodgy causes of ORA-12549?

I am dealing with a rather massive ETL processing which keeps throwing me the ORA-12549 error. Have been looking for problems in the database so far and nothing. The DBA says the database is "fine" - ...
8
votes
2answers
4k views

PostgreSQL for high volume transactions and for Data warehousing

Am quite new to PostgreSQL, I have never done a large deployment using it before. But, I have good experience in enterprise solutions and I want to try and apply some of what I learned using ...
3
votes
4answers
773 views

What options exist for copying data between servers on a regular basis?

My apologies if this has already been answered. I've searched SO and of course here on DBA, and am surprised to find no close matches. Specifically, I'm looking for a solution which survives schema ...
14
votes
2answers
5k views

What are the arguments in favor of using ELT process over ETL?

I realized that my company uses an ELT (extract-load-transform) process instead of using an ETL (extract-transform-load) process. What are the differences in the two approaches and in which situations ...
4
votes
1answer
291 views

Methodology & Architecture to Create ETL Process

What book or webpage can I read the architecture and methodology for creating ETL process? In other words, I'm looking for "how to do it" to create a ETL process when you have many source system to ...
6
votes
5answers
308 views

SSIS ETL Question/Option

In our current prod environment, when the ETL launches, it first checks an environmental variable at the OS level which tells it where the "config" database and table is for it to get the necessary ...
2
votes
2answers
735 views

ETL: extraction strategy for 200 source databases

What is the best extraction strategy for approximately 200 SQL Server 2005 source databases (same schema) for a daily load into a staging area in preparation for data warehouse cleansing, ...
11
votes
3answers
8k views

How do I run a large script with many inserts without running out of memory?

Question: I have a script with around 45 thousand insert from select statements. When I try and run it, I get an error message stating that I have run out of memory. How can I get this script to run? ...
8
votes
4answers
662 views

Is there a standard language/interface for programmatic ETL in SQL Server?

I'm currently in the process of creating ETLs for our data warehouse. We're using SSIS 2008, but we're running into issues, the biggest of which is the difficulty in re-using components. We have ...
0
votes
1answer
179 views

Using N-Tier in Business Intelligence

I have read that you also can apply N-Tier for Businesss Intelligence based on these criteria: *Presentation *Functional logic *Data What is your experience when you are using N-Tier? // ...
0
votes
1answer
141 views

Recommended Data profiling tool for BI developer

What recommended data profiling tool to retrieve a lot of information and enable to visulize the data into graphical statistics? The data is coming from the source database.
2
votes
2answers
350 views

how to insert multiple columns(around 40) into my sql database using java

i have a program to read the content of an excel file to my-sql db. i have 40 columns in excel and i created a table of 40 fields. and i want to import the data from excel into corresponding table ...
2
votes
3answers
636 views

Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system?

We are having an ETL process which inserts lots of data into tables. This database is set to Simple Recovery Model and the transaction log is growing a lot. I was thinking that would it help to set ...
1
vote
1answer
961 views

Loading multiValued fields into Solr via flat file, and possibly value position preservation in those fields

A hobby web project of mine uses data from many sources, some flat, some highly normalized. My ETL process boils all sources down to one logical table (which is soon to include some multi-valued ...