Wednesday, May 6, 2015

Seamless iTunes and ODI Integration

Who said two powerhouses can’t work together in perfect harmony? Here we have Apple Inc. and Oracle -- two of the largest in their respective industries -- which can now interface with each other seamlessly. If you are a business that uploads your products to iTunes wouldn’t it be nice to be able to view reports on sales daily/weekly/monthly?That is where Apple Connect and ODI12c come in. Apple Connect gives users a way to download reports to show anything from daily to yearly stats on sales -- we are even given the option to pull pre-sales reports. Apple Connect does a very good job of documenting the different options for reporting in a .pdf document that can be found on Apples website 
Why ODI12c?
Well let’s say that you wanted to take these reports and load them to a target area so you could do reporting on the sales numbers. Without a tool to schedule, a mechanism to download, and another tool to then unzip, and then another tool to load the data to a target environment, you would be looking at a customized mess. What if you only had to worry about dragging and dropping a couple of objects into a gui interface and drawing a line from one to the other? Then you could use ODI12c built-in scheduling -- virtually nothing custom involved.
Below is the Task Hierarchy from a Knowledge Module that I created to make this all happen with one click of the mouse:
The steps are:
Download the file -- this uses built in ODI tools to call an API for iTunes Connect. There are additional options for download so I added flexibility to this step to make it user friendly to change.
Unzip File -- this step uses built-in ODI tools to call gzip.
The next steps comes packaged with ODI...These steps create and drop work tables and load the unzipped file to the target location in this case it is an Oracle table.
The next step is Archive Zip File -- since we already have it zipped we simple move this file to an archive folder.
The next step is Delete Source File -- since we don’t want to have this process run with old data we want to delete the file that we unzipped and are using to loading the target.
Here is what the whole process looks like in the mapping area of ODI. Again all the steps listed above are happening in what you see in the diagram above, from unzip to delete source file.
What happens, though, if you need to reload a file and you don’t want to download and unzip to happen? Simply create an additional Physical Mapping Design that uses a built-in file to target integration; this sounds duplicative but it’s not. Then when you run the mapping you can select the Physical Mapping Design you want to run.
In a few weeks, my next post will cover additional case studies which use different Physical Mapping Designs.