Questions tagged [etl]
'Extract, Transform and Load': Data load processing, normally in the context of data warehouse systems.
0
votes
0answers
14 views
ETL from non public database to a public one
I have a database that is in a private network. The database is not publicly accessible (you have to be on the local network to access it). We would like to use an ETL tool like FiveTran to export the ...
0
votes
2answers
37 views
Can Transactional Replication and triggers be used for near-realtime ETL [closed]
We want to have a data warehouse synced with our production OLTP database in near realtime. Is it possible to do this with Transactional Replication and triggers?
How we currently ETL
We use sql ...
2
votes
1answer
36 views
ETL, Production, Development — Should these be different servers? Instances?
You can say I've become an "accidental DBA" as people have left this company and I'm managing many aspects of the BI system now suddenly.
My predecessor left quite the mess regardless but I'm trying ...
0
votes
0answers
28 views
How to Represent a VARCHAR Column with XML Data in a Fact Table?
I have SQL Server 2016 environment and I have decided to design a source table with holds some sort of event data as a transactional fact table. The table has about a million rows, and generates about ...
25
votes
4answers
611 views
What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?
As part of our ETL process, we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded.
The comparison is ...
1
vote
1answer
52 views
When to Disable and Recreate Indexes in Data Warehouse ETL?
We are moving data from Staging Database into Kimball Data Warehouse DB.
I am hearing different ideas regarding when to disable and rebuild indexes.
Stage tables are truncated every day for each new ...
-2
votes
1answer
58 views
Datawarehouse Staging Tables: Null or Not Null
We are loading data from OLTP into Staging Tables with SSIS, then into Kimball data warehouse with ETL process.
We are inquiring about staging tables, if they should have null/not null constraints. ...
3
votes
2answers
87 views
Structured .TXT Data To A Proper DBMS
I'm presented with multiple .txt documents (each with its own set of columns) containing rows separated by some type of delimiter (which is different across the files). Each .txt file has different ...
0
votes
1answer
54 views
Query or ETL process to insert data into fact tables in SSMS
I'm working on creating a star schema for my project. I want to use it for reporting. I have used Google Play Store dataset from Kaggle.
The thing that I'm having trouble loading data into my fact ...
1
vote
0answers
33 views
Increase frequency of updates (ETL process) to Data Warehouse
Our data warehouse is updated only once every 24 hours (at night). The ETL process takes a little over an hour to complete. I am wondering how to increase the frequency of the ETL process - so running ...
7
votes
2answers
274 views
SQL Staging Tables: Primary Key Clustered or Heap
We are taking legacy flat txt files and inserting them into stage tables with SSIS.The question arose whether table should have primary clustered key index or not. This is direct flat file import with ...
0
votes
1answer
35 views
SSIS Installation on Dedicated Server
We are planning on installing SSIS 2016 on a new dedicated ETL server.
The packages will be called from SQL Server Agent (on a separate server), or from an application (again on a separate server).
...
0
votes
1answer
48 views
What's your data ETL methodology? [closed]
I think this is more like an open question: How do you do the ETL for your company?
We are using MySQL as our database management system and we have a production schema. Every month, when we received ...
2
votes
1answer
257 views
Efficiently bulk upsert unrelated rows
As part of a manual replication process between databases with different but related schemas, for each table, we identify new and modified records in the source database and feed them via a table ...
1
vote
1answer
76 views
How to copy selected table data from sql server to mysql on a daily basis? [closed]
I am having a retail pos application with MSSQL database. Now I need to transfer selected table data from SQL server to MYSQL database on a daily basis like job.
How to achive this?
0
votes
2answers
173 views
SSIS ETL or CDC - real time DWH reporting
I am currently working on SSIS package for continuous incremental data loads of the tables (40 tables now and expected to increase in near future) from OLTP system to a Reporting database residing on ...
1
vote
1answer
664 views
SQL Fastest Way to find Data Difference Between Two Tables
I am trying to locate fastest way to find difference between two tables. The first table is populated from text files daily, the second table is maintained in our database. We need to add data that ...
1
vote
1answer
72 views
reseed auto_increment to specific number?
Is it possible to set the identity column of a table to start from a specific value?
I'm converting an ETL stage from mysql to a sql server database where a staging database loads records with a ...
-2
votes
1answer
67 views
Can ETL access Data while Rebuild Index Operation is running
Can I get some help to understand below questions:
We have a ETL job running (Datawarehouse Server) and weekly index operations (Production Server)(Ola Hallengren script) , below questions are ...
1
vote
2answers
60 views
How do I run a stored procedure only on newest data loaded?
My database has a table where the data is loaded from an Excel upload. Then, a stored procedure is run on the data to validate it and insert it to another table.
Currently, my stored procedure will ...
2
votes
1answer
91 views
Handling variable fields in a CSV file with JSON?
Im trying to figure out the engineering to get a CSV file into PostgreSQL.
Requirements:
The CSV has X number of predetermined required fields
The CSV file has Y number of dynamic fields
Example:
...
2
votes
1answer
49 views
Is it bad practice to use a data-warehouse for providing reorganized non-BI data?
The Primary goal is the case-optimized data representation. The idea is to gather data from several databases, save the data in a data-warehouse and restructure/connect the data in data marts(similar ...
0
votes
1answer
275 views
SQL Bulk import vs SSIS in Simplicity
We are deciding between bulk insert or SSIS. We have a simple spreadsheet with 5 columns, customerid, cost, date, product name, etc. The spreadsheet is generally only around 200 rows every week.
All ...
2
votes
1answer
1k views
Is it impossible to force MariaDB users to SSL?
First of all, my english is really bad, so sorry for my english mistakes.
I have a MariaDB 10.2.15. I installed SSL, and its working fine, but I cant force use SSL to users.
my.cnf:
[mysqld]
ssl
ssl-...
2
votes
0answers
37 views
SSIS - dynamically merging a group of files
I'm trying to create an import package for our new mssql database and I'm having to merge a group of 20 files together to be fed into a table on the database. However not all of those 20 files will be ...
0
votes
1answer
113 views
Multiple Agent Jobs Running same SSIS Package
I have the following doubt, is there a possibility to have faild a Job when are running simultaneously as the following example:
4 SQL Agent Jobs running the same SSIS in different schedules, I've ...
0
votes
0answers
23 views
Partial Database Migration: Dealing with CRUD Queries
Overview
Notes:
We cannot create a linked server object, because of the security policies.
We are using MSSQL (T-SQL/Microsoft), and its SSIS tool. (I can add version(s), if they are needed)
...
1
vote
2answers
1k views
Character Conversion Issue Importing From CSV File
After doing a load of CSV files, there are various words that are "written" to the database incorrectly.
Some examples:
Diã¡ria should be Diária
Crã©dito should be Crédito
Ligaã§ãµes should be ...
2
votes
1answer
4k views
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' for SSIS
I have a SSIS package which is perform data migration between MySQL database and SQL server database. When I manually run it from visual studio or from SQL Server Management Studio, it ran ...
1
vote
0answers
75 views
SSISDB too large
I have client whose SSISDB is too large (71GB). I have tried to set RETENTION_WINDOW to 50 from 365 and ran the maintenance job, but it has made little difference. Can someone please suggest an ...
0
votes
0answers
66 views
Data warehousing (ETL): Scheduled data migration from MySQL to PostgreSQL
I've been assigned task of data warehousing for Reporting and data analysis. Let me first explain what we are going to do.
Step 1. Replicate production server MySQL database.
Step 2. Scheduled ETL: ...
0
votes
1answer
280 views
Load New Foreign Key in Facts Table, When Update Dimension with SCD Type 2
I'm working on the production of a Data Warehouse with SSIS, and today we create the first dimension with SCD of type 2. The fact table is linked in the DIM_CIPESSOA dimension through the idcipessoa ...
0
votes
0answers
180 views
Confusion in Informix when selecting dates with JOIN
I am trying to run a query in Informix, doing a join in a table of logs where we save the record of insertion of the rows.
Notice that the query below brings two results, with different insertion ...
4
votes
1answer
129 views
Single User Mode and Replication
If I put a replicated database in single user mode will the Log Agent count as the single user?
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
I'm trying to block users from ...
1
vote
1answer
152 views
Copying large amounts of data to another target tables
I'm migrating large amounts of data +40m of rows to a new version of the tables, my question would be more like, what could be the best way to improve the performance in terms of logging and execution ...
0
votes
1answer
264 views
Data Warehouse: Staging Area, DWH databases and ETL tool on one server? [closed]
I am thinking about a DWH server layout and I'd like to ask you if you can spot any downsides to this layout. The basic idea is to have Staging Area & Data Warehouse DBs and the ETL Tool for the ...
1
vote
2answers
84 views
Approaches for handling nvarchar sources to data warehouse
Situation is this...our data warehouse doesn't need nvarchar and is built using all non-unicode datatypes. But all new source systems are all coming in with unicode (utf8 Oracle or nvarchar in ...
-3
votes
1answer
301 views
I need to generate a GUID,help [closed]
I need help with an SSIS package, I need to generate a GUID code every time I register a row of data, can someone explain how it is done?
In the SISS package, the data is extracted from an excel file,...
0
votes
1answer
108 views
Restoring MSSQL back-ups to Oracle 12c
I currently have an Oracle 12c DW that has several data sources of varying types (MSSQL, Oracle, MS Access, PostgreSQL, etc.) being moved into it as part of our existing ETL process. We have FME (...
2
votes
1answer
36 views
How can I SELECT an 'instance' in PostgreSQL?
I am reading a paper called "Real Time Delta Extraction Based on Triggers to Support Data Warehousing" from the IEEE and they proposed the following code:
CREATE TRIGGER cdc_table ON db.table AS
...
1
vote
0answers
59 views
Data transformation
so, I'm facing this issue in storing my file in my database. I have an excel file that I need to load in database. The file looks like this:
The issue is that I need to store all this in columns: ...
1
vote
0answers
331 views
UPSERT or INCREMENTAL update of table but with DELETE
I have a following problem.
We have two systems, an OLTP system and our Data Warehouse, both on PostgreSQL. I need to sync one table from OLTP system with DWH, so they're identical. I am using ...
0
votes
2answers
816 views
SQL SSIS Closing Files already in use
Sometimes when running SSIS packages on flat files, I receive error: "The process cannot access the file because it is being used by another process".
Another person already has file opened.
Is ...
1
vote
3answers
102 views
SQL SSIS Internal ETL at Company
Business Departments at our Finance company want IT to import various spreadsheets (companyid, productid, balance, transactions) into the databases.
Lets say, the SQL physical server harddrive ...
2
votes
1answer
26 views
Existing data to new table, how to set up the PK & Identity
I am moving an Access app to SQL Server. I have confirmed the data in the existing PK is unique so I want to copy it into the new PK Identity field. The field is currently set as IDENTITY(1,1). I ...
1
vote
4answers
315 views
Does ELT data end up in an RDBMS?
I think I am confusing things.
In ETL, data conversion starts at Database A and ends up in Database B, both are relational databases. Maybe I have a 20-year-old system. I use ETL to get the data ...
0
votes
2answers
44 views
Is ELT appropriate for life critcal applications such as Medical Systems when converting to a new application? [closed]
My small experience with ELT and reading makes it look like it is mostly for development. Because the data is not cleaned up like in ETL that makes me think the new application will need to adapt its ...
0
votes
1answer
48 views
Handling delayed key info in real time data warehouse
Our data warehouse is currently loaded with a traditional daily ETL batch job, but we're looking to soon implement a few stars in real-time (fed by streaming Kafka messages, FWIW). So only a couple ...
1
vote
2answers
51 views
How Does SSIS Manage Connection Traffic?
I recently started using SSIS in my internship and I am still learning. We have a dedicated SSIS server that executes all of the ETL jobs and does not hold any other databases. We have many SQL ...
0
votes
0answers
274 views
How to extract and store errors from Pentaho PDI
I'm using Pentaho PDI 'Spoon' version 7.1 to build an ETL package.
The existing version of the package (built in SSIS) uses the built in variable @[System::ErrorDescription] to store an error ...
