Managing ETL dependencies with BusinessObjects Data Services (Part 1)

Are you satisfied with the way you currently manage the dependencies in your ETL? Dependencies between jobs (or parts of jobs) are an important aspect of the ETL management. It pertains to questions like: Do you want to execute job B if job A failed? Imagine that you have a job C with sub-job 1 (usual runtime: 3 hours) and sub-job 2 (usual runtime: 2 minutes). If sub-job 1 was successful and sub-job 2 failed, can you gracefully restart job C without the sub-job 1 being restarted again?

As soon as you have more than 1 simple job, you have to manage your dependencies. In this article (part 1 of a series of articles about ETL Dependencies Management) I’ll first list some of the characteristics I’m looking for in an ideal dependency management system. I will then have a look at some of the possibilities offered by SAP Data Services 4. In part 2 (my next post), I will propose the architecture of a possible dependency management system. In part 3, I will go into the details of the implementation in Data Services. I’ll finish with part 4 by telling you about how the implementation went, and if some improvements are possible.

The ideal dependency management system

In this post I will use the word “process” to design a series of ETL operations that have a meaning together. Example: extract a source table, create a dimension, or update a fact table. The objective here is to manage the dependencies between the processes: updating a fact table should probably only be allowed if updating the corresponding dimensions was successful.

A dependency management system should ideally have at least the following characteristics:

• Run a process only if its prerequisites ran correctly
• After a failure, offer the option to re-run all the processes or only the processes which failed
• Trace the outcome of each process (ran successfully, failed, did not run)
• Run dependent processes dynamically (rather than statically, i.e. based on date/time)

The possibilities

Let’s enumerate some of the possibilities offered by Data Services, with their respective pros and cons.

1) One job with all processes inside. This is very easy to implement, dynamic in terms of run times, but it doesn’t allow for concurrent runs. Most importantly, it means that failures have to be managed so that the failure of one process does not stop the whole job.

2) One process per job, with jobs scheduled at specific times. This is very easy to implement, allows concurrent runs, but is not dynamic enough. If the process durations increase with the months/years, jobs may overlap.

3) One main job calling other jobs (for example with execution commands or Web Services).

4) One process per job, all the jobs being scheduled at specific times, but checking in a control table if the pre-requisites ran fine. Otherwise they just sleep for some time before checking again.

5) Use the BOE Scheduler to manage jobs based on events (how-to is well described on the SCN). I’ve not tested it yet, but I like this approach.

By default, the first two possibilities only manage the “flow” side of the dependency management (after A, do B). But they do not manage the conditional side of the dependency management (do B only if A was successful). In both cases, a control table updated by SQL scripts would allow the ETL to check if the prerequisite processes have been run correctly.

What I don’t really like in the solutions 2 to 5 is the fact that it’s difficult to have an overview of what’s going on. You cannot really navigate within the whole ETL easily. The solution 1 gives you this overview, but at the cost of having a potentially huge job (without the possibility of processes running concurrently).

Also note that the solutions with multiple jobs will need to manage the initialization of the global variables.

What I miss in all these solutions is an optimal re-start of the ETL. If 10 of my 50 processes failed, and I want to restart these 10 only, do I really have to start them manually?

In my next blog post I’ll propose an architecture that addresses this optimal restart.

Until then, please let me know your thoughts about how you manage your ETL dependencies. Any of the 5 solutions mentioned before? A mix? Something else? And how well does it work for you.

Do you know what drives your business?

Knowing your business and making smarter decision in order to keep up with the everyday risks involved in running your business is critical in order to survive in today’s global economy. Making effective decisions requires information. This information must be accurate and updated, and in the right level of detail that you need to be able to move forward at optimal speed. Key business analytics is what allows you to draw information from the data you collect in the different parts of your business. When you understand exactly what is driving your business, where new opportunities come from, and where mistakes were made, you can be proactive to maximize existing revenues and reveal areas for expansion. Better decisions can be made when you have more visibility into vital insight coming from your own company.

SAP Business Intelligence (BI) Solutions provides a window into your company. A dashboard for example is a single, reliable, and real-time overview of your company.  It offers you quick insight, in visual appealing formats that are easy to understand. You also have "What-If" tests that let you measure the business impact of a particular change. This can also be made available on mobile devices, so you can make informed decisions on-the-move. When you have information you can trust, you are able to act rapidly and stay ahead of the game.

With years of expertise in BI, Clariba has helped several companies to draw insight from their data. For example, Vodafone Turkey´s Marketing department sought our help to provide the Customer Value Management Team with a dynamic and user-friendly visualization and analysis tool for marketing campaigns. With the central dashboard we delivered, the marketing team was able to analyze existing campaigns and design outlines for new ones based on key success factors.

You can learn more about SAP BI solutions here, and you can also watch SAP BI Solutions videos on YouTube. Want to unlock this information on what drives your business forward? Contact us on info@clariba or leave a comment below, and discover how SAP BI Solutions can help you achieve it.

Tips for installing SAP BusinessObjects BI4 in Spanish

The Spanish installation of SAP BusinessObjects BI4 is said to frequently crash, and even when it does not, something is still wrong in the server side so SAP BusinessObjects installation administration console does not work as it should and certain formulas used in the front-end side malfunction. This article explains some tricks that can be applied when installing SAP BusinessObjects in Spanish so customers can successfully migrate to the new SAP BO BI4. Folders Security Configuration

When configuring the security for a folder in the SAP BusinessObjects administration console, an issue appears. This can be easily reproduced by installing the software in Spanish, proceeding to enter in CMC and start configuring security. The following error can be seen when entering in Folders Top Level Folder Security:

"A server error occurred during security batch commit: Request 0 of type 44 failed with server error : Plugin Manager error: Unable to locate the requested plugin CrystalEnterprise.ScopeBatch on the server. (FWB 00006) "

There is no other solution rather than to change the Regional Settings of the server from Spanish (Spain) to English (United Kingdom), then this issue is solved.

UserResponse Formula Use

An issue occurs in WebIntelligence when Preferred Viewing Locale is Spanish and Users refresh reports Prompts: A numeric prompt is interpreted as a text with a wrong scientific format when the =UserResponse() formula is used.

The issue can be reproduced taking one object from the official SAP “STS Southeast Demo” Universe, retrieving one object with a prompt and applying the UserResponse() formula as indicated below

In the example stated above the UserResponse() formula should be evaluated to “201010” instead of “2.0101e5”. This can cause serious issues as it is a change Vs. previous software versions because this could be included in many formulas and filters, and could cause a migration project with reports using this frequently used formula to be dramatically extended.

A workaround for this is, for the time being, to keep the Preferred Viewing Locale in English for every user who refreshes information.

So, summarizing, the pieces of advice proposed for a successful installation in Spanish language are:

• Keep your server with Regional Settings of the server in English (United Kingdom)
• Configure your BI LaunchPad users to keep the Preferred Viewing Locale in English
• Keep the BI LaunchPad users Product Locale property setting to Spanish (Spain)

As a result, users will visualise the whole front-end in Spanish language with no errors.

This solution has been tested in latest 4.0 SP4 which includes FeaturePack 3.

The benefit of these workarounds is to provide the market of Spanish language customers with the ability to start migrating to the new SAP BI4 platform with success, and enjoy the product in Spanish language with no bugs.  Hope this helps. If you wish to leave your comment or opinion, please feel free to do so below!

Clariba Obtains the SAP Partner Center of Expertise Certification

Certificate PCoE

SAP service and support audit confirms that Clariba can support its customers in accordance with SAP’s current technical and organizational standards, thereby receiving the Partner Center of Expertise (PCoE) Certification.

On the 30th of May Clariba employees received the amazing news – They had passed the final audit and SAP was granting Clariba Support Center with the PCoE certification.

This was the finishing line of a process that started in 2011 and took a lot of effort from Clariba Support Center members, going from the installation of SAP Solution Manager (the system used to provide support), through certifying support center staff, creating all marketing materials and finally undergoing a 6-hour audit to evaluate if all requirements were in place.

“It’s been a very long and complex process, since it was our first experience with SAP native applications. Installing and configuring SAP Solution Manager has been a big challenge, but now it  allows us to provide full SAP Enterprise Support to our customers. We use SAP Solution Manager to monitor our customer installations remotely as well as connect them with the resourceful SAP back-end infrastructure. In addition, SAP Solution Manager provides our support customers with a Service Desk available 24x7, to create and track their incidents and to ensure a fast and efficient service for very critical issues at any time” says Carolina Delgado, support manager at Clariba.

The Partner Center of Expertise (PCoE) certification affirms that the defined procedures, guidelines and certified support team members are available to provide qualified, timely and reliable support services for SAP BI Solutions.

“As a requirement for achieving the PCoE certification, all our support consultants have the Support Associate - Incident Management in SAP BusinessObjects certification and as a commitment to high quality and professionalism, we require all our consultants to be certified in the SAP BusinessObjects suite. Both accreditations ensure that our support staff has the required knowledge to efficiently address and resolve any technical issue that may arise. In addition, they can draw from a wide knowledge base that comes from many years of experience on SAP BusinessObjects installation and development as well as on a continuous interaction with the SAP community" mentions Carolina.

PCoE Process

With this in place, Clariba Support Center can offer VAR delivered support to its clients, providing the knowledge and support tools for SAP BusinessObjects licenses through a single point of contact ─ a familiar, agile and available partner, ensuring that a defined and known point of contact is always available to customers in the event that any problems arise in their SAP BusinessObjects systems.

Carolina points out that the Key benefit is the proximity with customers and their environments. “In most cases we are maintaining BI systems which have been deployed by Clariba itself. This gives us the advantage of better understanding the customer's environment, so that we can provide a more accurate and tailored service.”

As a SAP Partner Center of Expertise (PCoE), Clariba can offer their clients

a fully qualified staff according to SAP quality standards;

real-life SAP BusinessObjects implementation, training and support experience;

a relationship with the client organization built on trust and past successes;detailed knowledge of the organization’s history, systems and business processes when the BI system has been deployed by Clariba;

staff trained for rapid issue resolution and focused on customer satisfaction;

very strong cooperation and relationship with SAP experts worldwide and an integrated support platform that ensures the best quality of service.

For more information on Clariba´s Support Center offerings visit http://www.clariba.com/bi-services/support.php

BI and Social Media - A Powerful Combination (Part 1: Google Analytics)

If you take a look at the latest Business Intelligence (BI) trends you will see a great deal of mentions revolving around the “Social Media” topic. There are a lot of interesting ideas out there and it seems to be certain that this will become even more prominent in the near future since it continues growing and it is becoming an intrinsic part of our society. This also means that the information related to Social Media becomes more valuable with each passing day, becoming the new “gold mine” for Business Intelligence consultants when handled correctly. No wonder there are a lot of us that want to start digging in!

In this blog post, I will share the research I recently made with the objective to determine the possible alternatives to extract and use information from Google Analytics to develop SAP BusinessObjects reports and dashboards. An important note is that I did not consider SAP BusinessObjects Data Integrator’s Unstructured Data and Text Analysis capabilities on purpose, in order to see what other options were out there.

Extracting data from Google Analytics

The first thing you must know is that in December 2011, Google released the Google Analytics Core Reporting API (in replacement of its old Data Export API) which allows us to extract data from its application. Although it is not rare to see the old API still being used, if you are starting a fresh development and have the opportunity to choose, then go with the new version of course. Considering these interfaces, I found four different strategies that stood above the rest.

Programmed Google Docs spreadsheet: It is a Google Docs spreadsheet available free of charge, which contains embedded Google Apps Script code to connect to the Google Analytics Data Export API. Once authenticated, it allows the user to define the dimensions, metrics and filters filling in the fields of the document with the desired values. Subsequently, this spreadsheet can be exported as a .CSV file and use it as a data source to be reported with several tools of SAP BusinessObjects such as Web Intelligence or Data Integrator, for example. Credits go to Mikael Thuneberg who developed this. 1

GA Data Grabber: It offers a 7-day trial version, the cost to purchase the Google  Analytics module is 299USD per year (at the present date). It consists of an Excel file containing Macros and Visual Basic code to run queries with any of the dimensions, metrics or filters available in the Google Analytics Core Reporting API. This is one of the best options I found so far, since it has a user interface that is very easy to use, good flexibility to select metrics, dimensions and filters, besides having a fairly comprehensive structure that facilitates the understanding of the type of information that can be obtained from the API. By having the data available in an Excel file there are more SAP BusinessObjects tools that can be involved in addition to Web Intelligence and Data Integrator, such as LiveOffice that allows direct integration with Xcelsius dashboards. 2

Reporting Utility of Google Analytics: This functionality is already included in the Google Analytics application. It is a relatively manual process, which requires the user to have some prior knowledge of the dimensions and metrics that can be obtained. However, custom reports can be created with specific information that can be exported to .CSV files so they can be incorporated into the reporting capabilities of SAP BusinessObjects.

Custom Application Development: If third party tools are not the desired option, then the best strategy to use is to develop a custom application in any common programming language, such as PHP, Ruby, Python, Java and JavaScript. The Google Analytics Core Reporting API lets you connect with these type of applications. In spite of representing the option that requires more effort to be implemented, it is also the one that permits to have a perfectly tailored, automatable and free solution. The data can be potentially stored in text files or custom databases from where any SAP BusinessObjects tools can draw information.

Official Documentation always does the trick

I did not want to get into too much technical detail because they are very susceptible to change through time, and it is always best practice to look at the official documentation published by Google. In this sense, I will leave you with a few links that will be very useful to deepen the technical knowledge necessary to develop an integrated solution with Google Analytics.

• Google Analytics Core Reporting API Overview:

https://developers.google.com/analytics/devguides/reporting/core/v3/

• Dimensions & Metrics Reference (to understand what information can be retrieved):

https://developers.google.com/analytics/devguides/reporting/core/dimsmets

• Google Analytics Core Reporting & Data Export API Change Log:

https://developers.google.com/analytics/community/export_changelog

• Google Analytics Core Reporting API Client Libraries:

https://developers.google.com/analytics/devguides/reporting/core/v3/gdataLibraries

• Google APIs Console:

https://code.google.com/apis/console

Final Thought

As with all things that become “the latest trend” and  develop at a hectic pace, a lot of changes are expected to happen, meaning that sooner rather than later there will be more and better options available. So my final thought is to keep an inquisitive eye for all that is moving around Social Media and its most popular networks, because this is the future that is already happening.

If you have any questions or anything to add to help improve this post, please feel free to leave your comments.

References

1 For more info: http://www.automateanalytics.com/2010/04/google-analytics-data-to-google-docs.html

2 For more info: http://www.gadatagrabbertool.com/