Stephan Köppen

During our first project at one of our clients, we were facing this totally new technology called PDW with all it new ways of handling things and a lot to learn. So far we were used to loading with SSIS and we love the simplicity of this. Of course we didn’t want to loose this, but the performance of the regular component wasn’t as fast and loading via flat file (dwloader) is the preferred way of doing so.

Another thing we had to keep in mind was the amount of loads which will take in parallel. How can we make sure that the concurrent loads and queues don’t exceed the maximum limit?

So what are going to do? We had this powerful machine and wanted to get the most out of it but had the mentioned challenges to face. So after plenty of ideas, drafts and tests we finally came to the point that there is only one way. And that was BI.Loader.

 

What is BI.Loader

BI.Loader is Oraylis answer to simple and intuitive handling of PDW loads, which fits perfectly in the world of SSIS and data warehousing. It consists of 2 components – the SSIS Adapter and the Loadmanager.

The Loadmanager is in charge of the flat file loading towards the PDW. It scans directories for files and starts to load them if the tables are configured.  So even with just CSV writing in SSIS it is possible to automatically load into the PDW.

Since we have a lot packages depending on each other, better speaking the written facts, we needed something to make sure that the written file is written to the LZ and also successfully loaded into the appliance.

This is where the second component of BI.Loader comes in place. Our own SSIS destination, which communicates with the Loadmanager to verify that everything is done properly.

 

The PDW Destination

The Destination is a simple SSIS Adapter, which can be easily installed and used with either SSIS 2008 or 2012. It is based on a ADO Connection towards the PDW and connects also tot he Loadmanager.

When the database is configured for loading you can just select the table and type of load and start mapping your columns.

Within the mapping tab you have several options to match the columns. There is an auto match based on the input and output column names and also verifies the data type.  You can also click the columns in the mapping boxes on the left or write, or you can use the provided dropdown boxes in the middle of the dialogue.

To sum it all up the destination has plenty of other benefits:

  • SSIS 2008 and SSIS 2012 support
  • Writes CSV Files towards the Loadmanager
  • Verifies the underlying structure during loading an can adapt the CSV file according to the table structure
  • Handles synchronous and asynchronous loading (wait for Loadmanager response)

But there is one thing that we are really excited about. It loads into PDW and SMP so you can e.g. develop on SMP and deploy on PDW. Another great benefit is the easy migration towards PDW because no SSIS Adaptions are necessary.

 

The Loadmanager

The Loadmanager is the second component auf BI.Loader and is in charge of loading and managing the loads. With it’s easy configuration dialouges you decide what databases are loaded and how. It handles the whole process of table truncation or partitioned loading. The developer can focus on what’s important and leave the loading process to the Loadmanager.

The Loadmanager is a great and easy way to load either your PDW or your SMP Server. It’s own provided logging makes it possible to follow each step of the load process including the actual write of the file or the loading into PDW.

The Loadmanager in a couple of bullet points

  • Easy installation and setup
  • Simple and clean GUIs for configuration
  • Generic loading templates which support multiple platforms
  • Load handling support for Appending, Truncation, Merging, Partition replace, etc.
  • Monitoring Support to see exactly what is going on
  • Own database driven logging mechanism

Thanks to BI.Loader we have simple and easy way to load any kind of data to either PDW or SMP Servers. If you need any information or have questions regarding BI.Loader feel free to contact us.