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
One of the most challenging part of Data Warehousing is to find the right way of extracting external data. Recently I have got a question about Google Analytics:
Google analytics data, is there any idea how to consume it in BW ?
For the first sight, I was thinking, "well it's challenging", because I went through the different standard tools for connecting data to BW and I didn't find an obvious solution:
- SAP Service API ? No, Google is not an SAP source system
- DB connect ? No, Google is not a DB system like Oracle or MSSQL
- File ? Maybe yes, if I can manage the automatic download to csv...
- Web Service ? Maybe yes, if I can manage the automatic download and conversion, but it's similar to the File interface problem...
This challenge gave me motivation to investigate how to automatize the loading of Google Analytics data into SAP BW and to share my results in this post series. In this post I want to show you my first step in solving my problem: I have defined my goal in a dataflow and prepared a working prototype. I find it really important to start with a very simple solution that works fine and from that to go deeper in the problem.
Prototype in Excel
The easiest thing we can do now to define our expected data that we want to analyze in a simple Excel table format. After loading the data into our system, we want to analyze the Number of the Sessions and Users that our website could generate on a specific date.
Source Structure: Google Analytics Sample Data
| Date |
YearMonth |
Year |
NumberOfSessions |
NumberOfUsers |
| 2015.01.01 |
201501 |
2015 |
1658 |
1412 |
| 2015.01.02 |
201501 |
2015 |
1880 |
1500 |
| 2015.01.03 |
201501 |
2015 |
1700 |
1605 |
| ... |
... |
... |
... |
... |
Data Flow in 5 minutes: Data Flow Wizard
If you work with an SAP BW version 7.3 or upwards then you can use the Data Flow Wizard for Fast Prototyping. It took me only 5 minutes to generate a simple dataflow so, it's a real Fast Prototyping tool. You only need to prepare your interface data in Excel format and to go through the steps of the wizard. How to use the Data Flow Wizard ? You can watch my video post here:
What's next ?
In this post I defined my goal and expressed it in a data flow format. I think for complex problems it's really important to define our goal first because we can easily get lost and this approach helps me keep focused on my goal. Did I answer how to get Google Analytics data now ? No, but I have defined my problem in a format of a structured use case. In the coming post I am going to show you the other part of the story: the opportunities of the Google API. Stay tuned!