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

learn more… | top users | synonyms

1
vote
0answers
10 views

Retrieve data from OLTP MySQL to SQL Server DWH using SSIS

I'm running a BI solution currently reading data from an OLTP SQL Server 2008 to a SQL Server 2012 data warehouse. The ETL phase is implemented with SSIS and runs twice a day. This phase relies on a ...
1
vote
1answer
1k views

Dimensional Modeling and ETL in Redshift

I have been researching Amazon's Redshift database as a possible future replacement for our data warehouse. My experience has always been in using dimensional modeling and Ralph Kimball's methods, so ...
3
votes
1answer
68 views

How do I load data from a single table to multiple tables & keep referential integrity

Using SQL Server 2008R2. I have a single table with a bunch of columns. I have built a new database with multiple tables to copy the data too. How do I copy the data, and still keep the ...
6
votes
3answers
104 views

Azure Data warehouse - User defined function issues

Anyone here had luck with creating and using UDFs on Azure data warehouse database? I am in the middle of migrating an on-prem warehouse from SQL Server 2014 to Azure datawarehouse and I ran into an ...
0
votes
1answer
22 views

What is the Difference between Data Transformation vs Transportation?

Is there a difference in meaning between "data transformation" and "data transportation" in ETL process and data warehousing? or both concepts can be used interchangeably in this context? Thanks
4
votes
2answers
108 views

Query “last” records in a table with non-sequential key fields

My goal is to clone a table from an Oracle database to a SQL Server database every midnight. I am using Talend Data Integration for this task. The problem is that this table has around 70 million ...
0
votes
0answers
24 views

Loading Data from url

I am trying to create a plan for data load and wanted to bounce off best practice ideas and learn about amazon RDS. I have multiple url sources of data and a RDS MYSQL instance to store them. I want ...
0
votes
1answer
28 views

What are the minimum requirements for running a dtsx package on a PC?

One of my clients has some very specific needs to get a spreadsheet run through some ETL processes I have created. I have developed an SSIS .dtsx package which imports an Excel spreadsheet into SQL ...
0
votes
1answer
32 views

data validation Datawarehouse ETL

Need a suggestion from Datawarehouse and ETL experts. I have a project which has bunch of fact and dimension table, and we wire the facts using surrogate keys to dimension (religiously following ...
0
votes
0answers
33 views

Do Queries on a MySQL Federated table lock the remote table?

We are trying to use Federated tables as an experiment where a remote server copies from the Federated tables to a local db with OLAP designed tables. My question is whether the SELECT queries on the ...
5
votes
2answers
8k 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 ...
2
votes
2answers
84 views

Best way to capture historical changes between levels in a dimensional hierarchy

What is the cleanest way to capture changes between levels in a dimensional hierarchy? I have the dimensional hierarchy [Area] > [Region] > [Location], where Area is the parent to Region and Region ...
2
votes
1answer
125 views

What is the best way to import this non-standard text file into SQL Server?

I have a text file that is generated daily. It is a fixed width format with one frustrating exception. The third column (NAME) has a variable width. The column is always exactly as long as the longest ...
1
vote
1answer
72 views

SQL Server : ETL Stage Index

We have a very large staging table (> 80 GB). From our source system we load invoice data in the staging table. From the staging we transform the data and load into DWH/Fact. Every day we delete the ...
0
votes
1answer
51 views

ssis surrogate key in parent child tables

I have two tables Person & Ownership having one to many relationship (each person have many ownerships). Primary Key is person_id of type GUID. I am developing SSIS package to load data from both ...
6
votes
1answer
129 views

How do you index alphanumeric foreign keys on large fact tables?

I have a field that is an alphanumeric field that ideally is a encrypted field for a non-unique identifier. It's used to relate other rather large fact tables in a many-to-many relationship. I do not ...
1
vote
1answer
24 views

decrypt data in column before load into destination

I have a table named city which contains two columns city_id (int) city_name (varbinary) -- encrypted column I want to extract data from this table, transform (decrypt the city_name) and load (...
0
votes
0answers
13 views

Isolation Level Impact on Selecting a Row Moving Between Two Tables

I have two tables (identical structure, one for current data and one for archive) being used as a source for a datamart staging table. I construct a SSIS data flow that runs SELECTs on both tables ...
4
votes
2answers
119 views

data warehouse automation tool: benefit or bane? [closed]

Today while investigating ETL and data warehouse solutions like Kettle, I came across a new concept (for me) called data warehouse automation tool. I also looked at the white paper here, which ...
1
vote
4answers
6k 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 Server....
6
votes
2answers
127 views

SQL server Database statistics degrading after execution

I have been experiencing timeouts while querying my SQL Server DB in particular one table. I tried updating the statistics and it fixed the performance issues for a couple of days. I started digging ...
1
vote
1answer
57 views

DB2 Database Conversion Workbench Issue

I am currently trying to convert a SQL Server database to DB2. I am using the Data Conversion Workbench (DCW) for IBM Data Studio. The issue I have is that DCW can not seem to correctly extract data ...
5
votes
1answer
68 views

Are there practical scenarios where inserts + deletes are faster than updates? (SQL Server)

I'm just wondering if there are common scenarios where an insert/ delete combination is faster than an update else insert function. Here is my specific example. I have to update a database with ...
1
vote
1answer
67 views

How to set SSIS flat file connection row delimiter to blank line?

I have a text file with the following format: DataColumn1 DataColumn2 DataColumn3 DataColumn1 DataColumn2 DataColumn3 DataColumn1 DataColumn2 DataColumn3 Each row with text in the file delimits a ...
0
votes
0answers
37 views

commonly used diagram for ETL

A the title indicates. Am i wondering what is the most commonly used diagram to explain an ETL process? In my case the process is rather linear, would a flowchart suffice? or is a Use-case also ...
0
votes
0answers
171 views

A “best practice” Postgresql function template for ETL processes

I use SQL Server in my day job, and have a personal project to build a star schema data warehouse in Postgres 9.4 on Ubuntu 14.04 to extend my knowledge (it'll be approximately 300GB uncompressed). I'...
0
votes
1answer
111 views

Extract data from Oracle DBF files

I'm completely new to Oracle and I was provided with what appears to be an Oracle database backup (170 DBF files and 3 CTL files). I'd like to extract the information using SSIS or Pentaho's Kettle ...
0
votes
0answers
121 views

How to import data from Sqlite into SQL Server 2012

I need to import data from sqlite into SQL Server so we can include it in our data warehouse. Based on previous system volume. Id expect there to be 50-100,000 rows after a couple of years. For ...
1
vote
0answers
34 views

SSIS Guarantee that all data will be moved to target when running data flows in parallel

My package has the following two tasks which run in parallel. DF - Move source data to target loads a copy of all source data to target via OLEDB Dest - Target and also loads a copy of source PK ...
1
vote
1answer
112 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
183 views

Running scheduled SSIS pkg with SQL Server 2012 Web Edition

I have a client with SQL Server 2012 Web Edition. I believe it has SQL Server Agent, but I see on the following Microsoft page that many integration services are not supported: https://msdn.microsoft....
1
vote
0answers
25 views

Logging within a procedure's transaction

I have an ETL process running inside a PostgreSQL 9.3 procedure on AWS RDS. The procedure normally takes about ten minutes to run, but I'm having a rare issue where it takes several hours and never ...
2
votes
2answers
751 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
20 views

SSIS - different versions of the project

I am designing the ETL process. I created a package that extracts the data from the source using stored procedures. The SQL query looks like this: EXEC BI.ProcedureName The procedure returns data ...
0
votes
0answers
69 views

Batch Processing in Talend open studio for data integration

I am using TOS for data integration to transfer a data between mysql and postgres. Its transferring data between small tables successfully. But a table has 3GB data. while trying to transfer data its ...
2
votes
3answers
29 views

Data Warehouse attribute that describes its timeframe?

I am writing a document about a Data Warehouse system, and would like to know if is there a single word (noun) that describes the DW attribute of how many time it contains? I mean, if a DW contains ...
0
votes
3answers
38 views

Can data be replicated from a slave MySQL back to a master without the slave containing all the master data?

We have a PHP/Symfony 2 application using MySql as the database. The database contains a large amount of data. The application is deployed to a separate server(s) and an empty/clean version of the ...
4
votes
3answers
104 views

is automated data movement via Excel considered ETL?

I am wondering if informal methods of automatically extracting, transforming, and loaded data constitute "ETL." For example, I code Excel VBA to extract from databases onto a spreadsheet, transform ...
1
vote
1answer
80 views

When to run ETL for dimensions

We have a suite of restaurant web applications that stores data in SQL Server 2012 on database server. We have now started to move data for reporting onto a separate data warehouse server. The data ...
2
votes
0answers
55 views

Hash values vs. column values in an incremental load [closed]

I have a situation where I need to do an incremental load on multiple files every month. The files have thousands-to-millions of rows, and between 5 and 50 columns. I've read articles about hashing ...
1
vote
0answers
175 views

Using SSIS vs writing your own ETL [closed]

I have two data sources. Data from Questionaires laying in a datatable in the data hosters database (queriable via REST in JSON) Data about clients from salesforce (also queriable via REST in JSON) ...
0
votes
0answers
9 views

Pentaho getfilename error

I am try to send 20 mail with attachments using pentahi DI, Jobs are like below Get File Names--->Add constants--->Mail But some mails are not send with below error, Thay say files are not ...
0
votes
0answers
16 views

Update source table from SSIS after Inserting in Destination [duplicate]

I am using SSIS 2012 from visual studio to extract all email information from source tables as follows: Data flow to take data from Notifications table in App1 Store that in SQL Destination. ...
0
votes
0answers
66 views

Tools to insert data faster in MySql database

I am trying to insert data from a flat file which is around 9 GB with about 15 million rows, I used SQl-server 2012 for that. I noticed data insert is much faster with sql server and it took around 30 ...
1
vote
1answer
51 views

What's the term that describes our data processes [closed]

We provide an online subscription product that allows our users to research and analyze financial data of their interested industries. As such, we have various tools for loading data from various ...
0
votes
1answer
672 views

Synchronise Oracle Tables to SQL Server

Our ERP is using an Oracle database while our BI system is based on SQL Server. We need to get the changed data in Oracle and copy it in our staging area. At the moment we get the changed rows by ...
2
votes
1answer
63 views

Introduce or not introduce SSIS to an ETL project [closed]

Given is an ETL process that works fine using traditional SQL. The process is well optimized making use of ALTER TABLE permissions and performing partition switching, index creation, partition ...
3
votes
1answer
144 views

Stored procedure deployment - push or pull into database? [closed]

Using stored procedures as part of an ETL process to populate tables in another database, which method would be considered a best practice (if any) in terms of deployment, maintenance, visibility and ...
1
vote
1answer
111 views

SSIS Transaction Scope

My question is in regards to the scope of a transaction when using SSIS transactions. If I have a sequence container and it is marked as "transaction required" and I have tasks in that sequence ...
1
vote
1answer
47 views

Setting the column width of a column in a table created by logparser

I'm trying to use LogParser for ETL. I am trying to import this file with the following command: "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT Field1 AS CountryCode, Field2 AS Zip ...