Hi guys,
Did you ever think about how you can have a sample data to start build your ETL in meantime you don’t have access to the database?
Imagine the following scenario,
You’re a Europe(an) database administrator and you have received new requirements to build a new report. You found that the required data is located at China and your connection is actually strictly limited due security restrictions on both countries.
The integration will be something as I illustrate on the below figure.
Ok so far so good. The goal is to design a integration between China (database X) and Europe (database Y) but at the moment you only have access to database X and Z. For security reasons, China (database X) does not allow you to sent you entire database dump file, they want to know what fields you require on your new reports and they’re open to sent you sample data. How can they achieve that?
Well, there’re two ways to achieve data: the right and hard way or the fast way. The right way could be to starting ask for a database dump file which has been denied in the beginning. Bureaucracy takes time, there’re a lot of constraints to consider of related to data security.
The easy way is, ask them to send you sample data as a excel file and then how to import data starts to be your problem. You’re fine with that because there’s a tool that helps you on that task.
- Identify column headers you want to extract and delete the others.
- Create a SQL table where your column headers will be your table fields.
- Start deltasqltool, select excel file and press generate to create your SQL statements.
- Run the statement on your MSSQL and voilá. You got already a sample database based China’s data.
- Build the ETL based on your sample data.
Later you will decide how to deal with your data and where to place the ETL. For example, you can place the ETL on the staging area or you can place on your data warehouse. It depends of your needs.
Please feel free to comment or ask for help if you got any problems handling with this tool.
Application: deltasqltool – createinsertsfromcsv
Download: http://deltasql.sourceforge.net/deltasql/tools.php
Hope you find it useful,
RMC