BI and Social Media – A Powerful Combination (Part 2: Facebook)

To continue with my Social Media series  (read the previous blog here BI and Social Media – A Powerful Combination Part 1: Google Analytics), today I would like to talk about the biggest social network of them all: Facebook. In this blog post, I will explain different alternatives I have recently researched to extract and use information from Facebook to perform social media analytics with SAP BusinessObjects’ report and dashboard tools. In terms of the amount of useful information we can extract to perform analytics, I personally think that Twitter can be as good or even better than Facebook, however, it has around 400 million less users. Facebook still stands as the social network with the most users around the world - 901million at this moment - making it a mandatory reference in terms of social media analytics.

Facebook

Before we start talking about technical details, the first thing you should understand is that Facebook is strongly focused on user experience, entertainment applications, content sharing, among others. Therefore, the user activity is more dispersed and variable as opposed to the real-time orderly fashion that Twitter gives us, which is so useful when building trends and chronological analysis. Hence, be sure of what you are looking for, stay focused on your key indicators and make sure you are searching for something that is significant and measurable.

Relevant Facebook APIs for Analytical purposes

The APIs (Application Programming Interface) that Facebook provides are largely directed at the development of applications for social networking and user entertainment. However, there are several APIs that can provide relevant information to establish Key Indicators that can later be used to run reports. As Facebook’s developer page1 states: “ We feel the best API solutions will be holistic cross API solutions.” Among the API’s that you will find most useful (labeled by Facebook as Marketing APIs), I can highlight the Graph API,  the Pages API, the Ads API and the Insights API. In any case, I encourage you to take a look at Facebook pages and guides for developers, it will be worth your time:

• Marketing Developer Program

• Marketing Developer Resources (with mentions of the APIs above)

• Facebook Marketing Solutions: http://www.facebook.com/marketing

Facebook APIs

Third party applications to extract data from Facebook

I only found a few third party applications to extract data from Facebook’s API that were comprehensive enough to ensure reliable access to data. Below are some alternatives designed for this requirement:

GA Data Grabber: This application has a module for the Facebook APIs, which costs 500USD a year. As in the case of Google Analytics, it has key benefits such as ease-of-use and flexibility to make queries. It may also be integrated with some tools from SAP BusinessObjects such as WebIntelligence, Data Integrator or Xcelsius dashboards through LiveOffice.2

Custom Application Development: It is the most popular option, as I already mentioned in my previous post about Google Analytics. The Facebook APIs admit access from common programming languages, allowing to record the results of the queries in text files that can be loaded into a database or incorporated directly into various tools of SAP BusinessObjects.

Implementation of a Web Spider: If the information requirements are more focused on the user’s interactions with your client’s Facebook webpage or any of its related Facebook applications, this method may provide complementary information to that which is available in the APIs. The information obtained by the web spider can be stored in files or database for further integration with SAP BusinessObjects tools. Typically, web spiders are developed in a common programming language, although there are some cases where you can buy an application developed by third parties, as the case of Mozenda.3

Final Thought

As I have mentioned in my previous post, in the area of Social Media new applications and trends are appearing at a hectic pace, a lot of changes are expected to happen, so it is just a matter of time until we have more and better options available. I encourage you to stay curious on Social Media analytics and its most popular networks, because right now this is a growing gold mine of information.

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

B-tree vs Bitmap indexes - Indexing Strategy for your Oracle Data Warehouse Part 1

Some time ago we’ve seen how to create and maintain Oracle materialized views in order to improve query performance. But while materialized views are a valuable part of our toolbox, they definitely shouldn’t be our first attempt at improving a query performance. In this post we’re going to talk about something you’ve already heard about and used, but we will take it to the next level: indexes.

Why should you use indexes? Because without them you have to perform a full read on each table. Just think about a phone book: it is indexed by name, so if I ask you to find all the phone numbers of people whose name is Larrouturou, you can do that in less than a minute. However if I ask you to find all the people who have a phone number starting with 66903, you won’t have any choice but reading the whole phone book. I hope you don’t have anything else planned for the next two months or so.

Searching without indexing

It’s the same thing with database tables: if you look for something in a non-indexed multi-million rows fact table, the corresponding query will take a lot of time (and the typical end user doesn’t like to sit 5 minutes in front of his computer waiting for a report). If you had used indexes, you could have found your result in less than 5 (or 1, or 0.1) seconds.

I’ll answer the following three questions: Which kind of indexes can we use? On which tables/fields shall we use them? What are the consequences in terms of time (query time, index build time) and storage?

Which Kind Of Indexes Can We Use?

Oracle has a lot of index types available (IOT, Cluster, etc.), but I’ll only speak about the three main ones used in data warehouses.

B-tree Indexes

B-tree indexes are mostly used on unique or near-unique columns. They keep a good performance during update/insert/delete operations, and therefore are well adapted to operational environments using third normal form schemas. But they are less frequent in data warehouses, where columns often have a low cardinality. Note that B-tree is the default index type – if you have created an index without specifying anything, then it’s a B-tree index.

Bitmap Indexes

Bitmap indexes are best used on low-cardinality columns, and can then offer significant savings in terms of space as well as very good query performance. They are most effective on queries that contain multiple conditions in the WHERE clause.

Note that bitmap indexes are particularly slow to update.

Bitmap Join Indexes

A bitmap join index is a bitmap index for the join between tables (2 or more). It stores the result of the joins, and therefore can offer great performances on pre-defined joins. It is specially adapted to star schema environments.

On Which Tables/Fields Shall We Use Which Indexes?

Can we put indexes everywhere? No. Indexes come with costs (creation time, update time, storage) and should be created only when necessary.

Remember also that the goal is to avoid full table reads – if the table is small, then the Oracle optimizer will decide to read the whole table anyway. So we don’t need to create indexes on small tables. I can already hear you asking: “What is a small table?” A million-row table definitely is not small. A 50-row table definitely is small. A 4532-row table? I´m not sure. Lets run some tests and find out.

Before deciding about where we shall use indexes, let’s analyze our typical star schema with one fact table and multiple dimensions.

Star schema with one fact table and multiple dimensions

Let’s start by looking at the cardinality column. We have one case of uniqueness: the primary keys of the dimension tables. In that case, you may want to use a b-tree index to enforce the uniqueness. However, if you consider that the ETL preparing the dimension tables already made sure that dimension keys are unique, you may skip this index (it’s all about your ETL and how much your trust it).

We then have a case of high cardinality: the measures in the fact table. One of the main questions to ask when deciding whether or not to apply an index is: “Is anyone going to search a specific value in this column?” In this example I´ve developed I assume that no one is interested in knowing which account has a value of  43453.12. So no need for an index here.

What about the attributes in the dimension? The answer is “it depends”. Are the users going to do searches on column X? Then you want an index. You’ll choose the type based on the cardinality: bitmap index for low cardinality, b-tree for high cardinality.

Concerning the dimension keys in the fact table, is anyone going to perform a search on them? Not directly (no filters by dimension keys!) but indirectly, yes. Every query which joins a fact table with one or more dimension tables looks for specific dimension keys in the fact table. We have got two options to handle that: putting a bitmap key on every column, or using bitmap join keys.

Further Inquiries...

Are indexes that effective? And what about the storage needed? And the time needed for constructing/ refreshing the indexes?

We will talk about that next week on the second part of my post.

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/

Join Clariba at the SAP Forum and SUG MENA Conference events in Doha on May 29 and Dubai on May 30

Clariba is pleased to be sponsoring SAP Forum and SUG MENA conference in Qatar and UAE in May 2012. SAP Forum (known as SAP World Tour in 2011) and the SUGMENA Conference will be jointly held by SAP MENA and SUGMENA (SAP User Group, MENA) in May 2012. This is a unique opportunity where attendees can enjoy unparalleled networking with peers from many industries, thought leadership keynotes and innovative demonstrations.

“Clariba is glad to be sponsoring the SAP Forum Qatar and UAE for the second year. We had a great event in 2011 and expect 2012 to be even better” says Marc Haberland, Managing director of Clariba. SUG MENA conference will be jointly held with SAP Forum on 2012 and Marc comments “we think this is a great addition to the event, as we get the chance to learn more about how SAP users are experiencing the solutions we offer”. Marc sees the event as the SAP highlight of the year in MENA “it´s the main SAP event in the region and a great occasion for business and IT professionals to network and to learn more about leading technologies like SAP BusinessObjects BI4, SAP HANA and many more up and coming innovative solutions”. On a final note, Marc says “don’t miss the chance to visit us in the Partner Exhibition zone where we will have our team showcasing our solutions. If you happen to be attending the event in Dubai, make sure you catch our customer case study presentation with ADCO on the Business Analytics track.”

As a trusted SAP Partner in Business Intelligence in the region and a specialist in SAP BusinessObjects technology, Clariba is on board of this incredible event as a sponsor. Members of the Clariba BI consultancy will be present at our booth in the exhibition area. They will be available to demonstrate our dashboard and reporting solutions for a variety of industries and business units and will be happy to guide you through the SAP business intelligence offerings. You will also have the chance to enter the drawing to win an iPad when you visit our booth.

Clariba will also be presenting at the Dubai event. Join the Business Analytics track (track 6), from 15:45 to 16:20 where you will have the chance to hear from Clariba, Jason Bath (Director of BA&T SAP MENA) and one of our customers, the Abu Dhabi Company for Onshore Oil Operations (ADCO), on how Clariba and SAP have helped them gain actionable financial insight into their business and achieve significant benefits.

In addition to meeting Clariba at our booth, the event also offers great benefits to professionals, such as C-level executives, IT decision makers and Business managers, whose career depends on the successful execution of business objectives. These are some of the activities participants can look forward to:

• Seeing the latest solutions underpinning SAP’s latest product strategy demonstrated on stage and at the SAP technology showcase.
• Hearing from some of the leading regional CIO’s on how they are driving SAP investments to deliver the competitive edge and productivity improvements required by their organisations.
• Witnessing the latest Ecosystem technologies in the SAP Forum and SUGMENA Conference Sponsors Exhibition.
• Networking with attendees from most SAP customers in your region as well with SAP Partners like Clariba.
• Listening to visionary keynote from Jim Snabe (co-CEO SAP) in Dubai and Steve Stathis Tzikakis (Senior Vice President, Head Premier Customer Network, SAP EMEA) in Doha and learn about the latest essential business technology trends and innovations.
• Discovering how today's open technologies and business networks can help create a business beyond boundaries.
• Realizing how investing in technology can deliver greater visibility for improved decision-making, operational excellence, flexibility and sustained growth.

The SAP Forum and SUG MENA conference in Qatar will be held on May 29 in the Grand Ballroom of the St Regis Doha, at Doha west bay from 8:30 a.m. to 5:00 p.m. Attendance at the SAP Forum is free of charge. REGISTER NOW to secure your place.

The SAP Forum and SUG MENA conference in Dubai will be held on May 30 in Sheikh Rashid Hall in the Dubai International Convention and Exhibition Centre - Dubai World Trade Center, from 8:30 a.m. to 5:00 p.m. Attendance at the SAP Forum is free of charge. REGISTER NOW to secure your place.

------------------------------------------------------------------------------------------------------------------------

Clariba delivers innovative, reliable and high-quality business intelligence (BI) solutions to customers worldwide. We are recognized and respected as one of the leading SAP Business Intelligence consultancies in EMEA. Our consultants, based in the Middle East and Europe are SAP BusinessObjects certified professionals and experts in the planning, installation, development and deployment of BI solutions. Clariba develops best practice BI solutions for dashboards, reporting and analysis, providing our customers with clarity and actionable insight to improve their business performance. Our customers in the Middle East are leaders in the telecommunication, education, manufacturing, and banking sectors. By working closely with business leaders and IT teams, Clariba turns vital data from ERP, CRM and other transactional systems into actionable insight for all levels of the organization. For more information on Clariba’s business intelligence solutions visit www.clariba.com or contact us at info@clariba.com.

Problem Uninstalling Data Services

I have faced a problem uninstalling Data Services  recently and I wanted to share the resolution, just in case you find the same problem. I was trying to upgrade a Data Services machine following SAP procedure (this is copying the configuration files uninstall and then install the new version – not very sophisticated as you can see). This was not as simple as I first thought.

Problems started after uninstalling the software, the new version refused to install stating that I should first uninstall the previous version. I uninstalled the software again… but Data Services is still there, so uninstalled again, but this time the process failed (makes sense as the software is already uninstalled), so I kept trying… reboot…uninstall… reboot…rename older path name… reboot…you see where this is going…

So, how did I finally solve this?

1. Start Registry Editor (type regedit in a command window or in the Execute dialog).
2. Take a backup of the current Registry content. To do this, with the top node of the registry (Computer) selected go to File -> Export and select a name for the backup file.
3. Delete the Key: HKEY_LOCAL_MACHINESOFTWAREBusiness ObjectsSuite 12.0EIM (Suite XX.X may vary).  NOTE: You may want to write down the key KEY_LOCAL_MACHINESOFTWAREBusiness ObjectsSuite 12.0EIMKeycode first as it contains the license code.
4. To remove the entry for the software in the Uninstall Window’s dialog, go to HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindows CurrentVersionUninstall and look for a KEY which property DisplayName is “BusinessObjects Data Services.
5. Finally delete the content of the installation directory (typically: C:Program FilesBusiness ObjectsBusiness Objects Data Services)

Now you can launch the installer and it should work.

Hope this may help you if in case you are experiencing the same issue. If you have any doubts or if you ever faced the same issue, leave a comment below.