Load Google Analytics Data into SAP BW - Part III: Preparation of Extractor with Console Application


Download the Full Visual Studio Solution with the Source Code

In this post series, I show you the process of building a prototype for a Google Analytics Extractor to SAP Business Warehouse. You can replicate the source code by reading the posts. If you want to save time, you can also download the ready-to-use Visual Studio solution file with the source code. The posts explain my code in all the needed details.

Overview

In my previous post I have showed the usage of the Google Api Query Explorer and I chose the right parameters for my query after some testing. Now it's time to start implementing our extractor. In this post I will be preparing the skeleton of the solution that I feel as a very important step. In the past, I was always jumping directly to coding without knowing exactly how I want my solution works. In most of the cases it was a big failure since I can get lost in the details very fast. So in order to keep focused on my problem, I design first the skeleton of my program with empty methods expressing my intention about how I expect the solution should work. It's very good to start thinking only on high level because it will be a safety belt that we can grab when digging into the details. Start always with surprisingly simple things and solve more and more complex problems step by step! That worked for me so many times!

Create Console Application

Why C# ? Why not ABAP ?

Because I found a Client Library in C#, not in ABAP and this is the simplest thing for me by now to start with. Of course there are other popular languages you can choose and the solution can be adapted to other languages similarly.

Why Console Application ?

Because an extractor has not a real user interface, I want to use it for Automating my process. We can call console applications from a Process Chain in SAP BW with the help of OS Command process type. The only prerequisite that our SAP BW is installed on a Windows based server, in my case this is the situation so calling a console application from BW is not a problem at all.

In Visual Studio you can find a template project for Console Applications as you can see on the screenshot below. I just chose this template I created a solution called GoogleAnalyticsExtractor.


Get Google.Apis.Analytics.v3 through Nuget

How can we integrate the proposed Client Library for Google into our solution ? We can find it on Nuget. Fortunately Nuget is more and more popular for publishing and sharing libraries and moreover it helps to refresh your referenced libraries with a few click if a new version is published by the author. Go ahead, open Nuget and search Google Analytics and you will find the library that I have referenced: Google.Apis.Analytics.v3.

Google Analytics API has several dependencies, it will download some other packages from Nuget. As a result you will have the important dependencies referenced in your solution like the OAtuh library or the popular Json.NET, just to mention a few.

Prepare template methods for preparing the workflow

After these preparation steps the real work starts. Let's get started with the high level design. My design reflects my current knowledge about the problem domain of querying the Google API. So I accept that it might be not perfect and can be fine-tuned later when I know more about this. I summed up my design decision in the table below. As you can see I want my extractor does 3 main steps. First it initiates the communication to Google, then it queries my data and finally it converts the result to the expected CSV format and saves it somewhere.

# Workflow Step Method Signature
1 Initialize Analytics Client AnalyticsService InitializeAnalyticsClient()
2 Execute Query GaData ExecuteQuery(AnalyticsService analyticsclient)
3 Save Result to CSV void SaveResultToCSV(GaData data)

Responsibilities

Based on my table you can see that I separated the 3 methods by their responsibilities. I tried to design a method with a minimum responsibility possible that I can see by now. Maybe my solution can be refactored later, but it is enough at this stage. You can see also that I force a specific call order by using parameters for the methods to make it hard to call them in a random order.

  • 1: Initialize Analytics Client: This step is responsible for creating and initializing AnalyticsService, the Google Analytics Client. AnalyticsService is the part of the Client Library and can authenticate to Google Api and send query requests to Google and parse the results. In this method I am going to do all of the initialization configuration tasks that are necessary to access your data. The most important is the OAuth authorization process.
  • 2: Execute Query: Once we received a working AnalyticsService instance we can prepare our query request in this step and send it to Google. After a successful request we got our data in a form of GaData that is the container of our query result.
  • 3: Save Result to CSV Having our data in our hand, we can parse it to our expected format. This step is about converting GaData to CSV since we can upload CSV files to SAP BW easily.

Main method

The code snippet below shows the result of my preparation steps. All of the Console Applications have an entry method, called Main. Here I have defined my workflow using Google Api types. The code will be working after the implementation of the prepared methods.

Summary

Let me repeat myself: Start always with surprisingly simple things! The skeleton of the extractor is ready and I am happy with that because I can work in a structured way. I can keep focus on my problem during the implementation because I already have a prepared template that I need to fill with content. By now the content itself was not so important as the structure of our work. In the coming post we can concentrate on one of the implementation details, like the initialization of the Analytics Service. We are going to overcome the OAuth challenge by preparing our certificate and implementing the first method.

blog comments powered by Disqus