Can you replicate Stacker using Magic ETL

Mr_Miyagi

Want to use Stacker, but don't know MySQL? Does the Stacker process take longer than expected and generates millions of rows? Ever wondered if there was another alternative to Stacker? Well, now there is by utilizing Magic ETL!

 

One day, I was developing a dataset with Magic ETL and one of the requirements was to add a column that identified where a particular quantity value came from. I had multiple input datasets that were being transformed and each primary key was a different data type; this is when I had an Aha moment.

 

I had a different dataset that was utilizing Stacker; this particular dataset had an average turnaround time of 4 hours and would generate 20+ million rows. I constantly changed the form dataset and sometimes would miss a syntax, thus rendering the Stacker to not process successfully. I started to break down the stacker plugin query and try to understand how it functioned. After analyzing the query and how it interacted with the form dataset, I was able to figure out which transformation blocks were required to replicate the Stacker.

Mr_Miyagi

To replicate the Stacker, you’ll need to use the following transformation blocks in this particular order:
1. Select Columns
2. Set Column Type
3. Add Constants
4. Append Rows

That’s it!

These 4 transformation blocks are all that’s needed to replicate the Stacker into a working concept. You’ll also still be able to add any unique dimensions that pertain to a specific dataset. The following conditions are vital for the concept to work properly:

Naming Convention (Select Columns):

Any primary keys and/or measures being aligned need to have the same naming convention and are case sensitive.
eg. Column “Material” will not align with column “material”, column “Video Likes” will not align with column “Post Likes” if intended; instead, name the columns to “Likes”.

 

Data Type (Set Column Types):

Any primary keys and/or measures being aligned need to be the same data type.

 

Formatting:

For any Date values being aligned, make sure that they are all in the same format. This transformation can be done at the input dataset or before the “Add Constants” step.
eg. mm/dd/yyyy will not stack properly with a date column formatted as mm/yyyy

Source Column (Add Constants):

The source column plays an important role as it identifies the individual datasets being stacked. This column can be named to anything you wish.

 

Please see the attached documentation as it goes over this concept in great detail with a use case.

Replicate Stacker Using Magic ETL.docx