Tagged Questions

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

learn more… | top users | synonyms

0
votes
0answers
50 views

Word for particular ETL procedure [closed]

I am wondering who can answer this question, or if an answer even exists. Basically, I am wondering what the technical WORD is ( if one exists) for a procedure/process that occurs during a Sql Server ...
0
votes
0answers
26 views

Tables with same schema in DB for different sources. Bad pattern?

The main purpose of the system being developed is to collect data from different sources, process and standardize it, and then add it to the DB. After information from all sources is collected it ...
-1
votes
3answers
85 views

How to load multiple values in cell from Excel to SQL Server many to many table [closed]

I have values in excel like this. StudentNumber| Subjects 1| english, science, maths 2| maths, biology 3| history, zoology I have Student table in database ...
1
vote
1answer
91 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
61 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
48 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
327 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
135 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
236 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
50 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
547 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
36 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
72 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
275 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
263 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
138 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
91 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
796 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
106 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
387 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
96 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
101 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
490 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
273 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
6k 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
218 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 ...
2
votes
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
900 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
322 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
161 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
486 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
450 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
8k 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
344 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
170 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.