Tagged Questions

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

learn more… | top users | synonyms

1
vote
1answer
83 views

How to compare data between staging and operational tables?

I just want to ask how to compare two database tables, staging vs. operational tables. After we did the transformation and convert it to correct datatype like convert flag column (Y/N) nvarchar to ...
1
vote
0answers
46 views

How can I load csv data into mongodb and choose the types I'd like to have created for each column?

I'm looking for a way to load csv data in mongodb and specify the types that I'd like to have created for each column? I've been using mongoimport but I find it quite painful because there doesn't ...
-1
votes
2answers
24 views

how to identify why lookup failed in SSIS

i have created an ETL package and having a lookup to match userid in db. i checked in dataviewer what data is going as input to my lookup. the data is correct and the table i used in lookup to match ...
0
votes
0answers
42 views

Selectively load data using Oracle 11g r2 External Table Preprocessor

I am using the Preprocessor feature in Oracle 11g r2 to load several data files in a table. However, the raw files seem to have certain rows that shouldn't be imported with the rest. For example: ...
1
vote
2answers
292 views

What is a good solution for reliably transferring data between SQL Server and PostgreSQL?

In analyzing some of our business data, we're realizing that we need to join data across disparate data sources. For example, our application data is warehoused in Postgres (ported from MongoDB via ...
1
vote
1answer
125 views

Clustered Index with nightly ETL process

We have a nightly ETL process that involves data from many different sources being flattened, and loaded into a completely denormalized table on SQL Server 2008 for reporting. The clustered index on ...
1
vote
3answers
217 views

Looking for ideas on maintaining data warehouse in close to real-time - ETL tools, etc

I have done a fair number of traditional data warehouse implementations where the loads were done in a batch-oriented manner, i.e. the data is refreshed nightly or, at most, every few hours. I now ...
1
vote
1answer
48 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
0answers
530 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
35 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 ...
4
votes
1answer
2k 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
70 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 ...
1
vote
0answers
43 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
262 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. ...
2
votes
4answers
253 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 ...
2
votes
2answers
132 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 ...
4
votes
4answers
1k 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 ...
3
votes
1answer
87 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 ...
4
votes
1answer
3k 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
2answers
781 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 ...
7
votes
3answers
104 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 ...
0
votes
1answer
374 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 ...
1
vote
1answer
94 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
65 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 ...
1
vote
0answers
100 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
470 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 ...
1
vote
2answers
2k 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 ...
0
votes
1answer
270 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 ...
2
votes
4answers
5k 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 ...
4
votes
1answer
213 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
4k 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
2answers
3k 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 ...
1
vote
2answers
5k 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
2k 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
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 ...
2
votes
1answer
846 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
317 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" - ...
1
vote
0answers
157 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
0answers
460 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 ...
14
votes
3answers
7k 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
423 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 ...
4
votes
4answers
1k 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
3answers
7k 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 ...
6
votes
5answers
338 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
3answers
1k 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, ...
12
votes
3answers
13k 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? ...
0
votes
1answer
167 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.
0
votes
1answer
203 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? // ...
2
votes
2answers
527 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
1k 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 ...