Tip of the day - Table and index size in Oracle

Ever wanted to find out how big are the tables in your data warehouse or in your ETL storage area? Here is a quick tip.

You can get the size of each table belonging to a specific user with the following code:

select sum(bytes)/1048576 Size_MB, segment_name Table_name

from user_extents

where segment_name in (

     select table_name from all_tables

     where owner = 'OWNER_NAME_HERE')

group by segment_name

order by 1 desc;

In order to get the size of the indexes with the corresponding table names (useful when the indexes have system-generated names), we need another query:

select sum(u.bytes)/1048576 Size_MB, u.segment_name index_name, i.table_name

from user_extents u

join all_ind_columns i

     on u.segment_name = i.index_name

     and i.column_position = 1

where i.index_owner = 'OWNER_NAME_HERE'

group by u.segment_name, i.table_name

order by 1 desc;

If you have any doubts or suggestions, leave a comment below.

Export to Text automation in Web Intelligence

Users typically need their Web Intelligence (WebI) data tables exported automatically into text files in order to use them across other SAP BusinessObjects BI modules. Unfortunately SAP BusinessObjects, including the newest SAP BI 4 release, does not include a direct option to automate the export of content of a WebI document tab to text format. In order to cover this gap and achieve the Export to Text feature for WebI we designed a fully automated process which is shown in this article.

The problem

Users want to automatically export raw data tables from WebI to TXT file, but none of the existing scheduling format options – PDF, XLS, CSV – are satisfying, because:

• A PDF brings an static document that cannot be re-used directly
• An XLS or XLSX has the limitation of 65535 or 1 million rows respectively
• CSV does not export tables, it just exports the Query content

Users of old releases could use the old Desktop Intelligence (DeskI) module as an alternative, but unfortunately it has been discontinued in the new SAP BusinessObjects BI4 release.

The consequences

Users see WebI as an “limited” module in terms of sharing options and export size. Moreover, customers will not migrate to the new SAP BI4, especially those who heavily do Query & Analysis an export the result table to txt using DeskI. The future does not look very promising because:

• Even if a manual Export to TXT is available since SAP BI4 FP3, automation for it is not currently available and SAP does not have a release date for this feature
• DeskI alternative is not possible in SAP BI4. Even if a DeskI add-on is planned for coming versions, the future of its scheduling function is uncertain and corporations should not allow DeskI to be part of their BI Roadmap.

The solution

The following method describes a way to schedule a WebI report with Export to text functionality and it involves the use of the following items:

1. A 1st WebI document with the table to be exported
2. A Web Service that is pointing to that document table as a source
3. A 2nd WebI document with just one query that sits on the Web Service created. No tables nor charts are needed here
4. A vbs script that adapts the output from this 2nd WebI document

Detailed steps to follow for every item are:

1. The 1st webI document contains all the development needed (Queries, objects, variables, filters) and a table with the final data you would like to export
2. This 1st WebI document must be edited with WebI Rich Client. Select the table you want to export -> Right Click -> Publish Block -> Create Web Service
3. The 2nd WebI document which contains the Web Service based query can be scheduled to run with the following options:

• CSV type
• Double quote text qualifier, tab column delimiter
• Export to a server folder (e.g. D:)
• Name it with txt extension (e.g. Results1.txt)

See below a snapshot with the schedule configuration detail:

Configuration of the schedule in WebI for a txt export

This example is applied to only 1 table to export, but multiple tables per document could be exported by ticking the “Generate separate CSV per Data Provider” option.

Once run with Success, the result of this schedule will be a text file (Results1.txt) with the content delimited by tabs but with a small defect: the so-called text qualifier (double quotes) appears everywhere.

In order to remove this annoying text qualifier (double quotes) a program can be scheduled. You can use your free style but if you copy and paste the following txt into a file called “QuoteRemoval.vbs” it will do the job:

set objRe = new RegExp

objRE.Pattern = """"

objRE.Global  = True

strFileName = "D:Results1.txt"

set objFS = CreateObject("Scripting.FileSystemObject")

set objTS = objFS.OpenTextFile(strFileName)

strFileContents = objTS.ReadAll

objTS.Close

strNewContents = objRE.replace(strFileContents,"")

set objWS = objFS.CreateTextFile("D:Results2.txt")

objWS.Write StrNewContents

objWS.close

The result of this executed script will be a perfectly formatted Results2.txt file

Last but not the least, you can build a system of events that triggers the different items sequentially, or embed these items in an object package that can be scheduled as a whole.

Applicability & Benefits

This method enhances the sharing options for the SAP BusinessObjects platform, allowing an unlimited amount of raw data to exit the platform through WebI automatically, and be re-used in Big Data modules like HANA, Visual Intelligence, Explorer or simply for individual consumption.

Seeing even further, this turns WebI into a real ETL (Extraction, Transformation and Load) tool providing integration capabilities to the end users.

Summarizing, this method:

• Allows a better integration of SAP BusinessObjects with the corporate BI processes improving efficiency and effectiveness
• Facilitates companies to opt for a migration to SAP BI4 release, with all the benefits that the newest platform brings

If you have questions about this method, or if you want to share your experience or tips, please feel free to leave a comment.

BI and Social Media – A Powerful Combination (Part 3: Sentiment Analysis)

In previous posts we have covered the role thatGoogle Analytics and Facebookplay in BI projects focused on Social Media Analysis. Therefore, it was only a matter of time before we covered Twitter - the most popular micro-blogging network you can find on the web. Besides that, it will not be rare to find analysts and reviews that consider Twitter the social network that can potentially deliver the highest amount of meaningful information to analyze.At this point, I guess everyone has a general idea of what Twitter is and what it delivers, so the objective of this article will be to make an overview of a Sentiment Analysis showcase that we built extracting data from Twitter with SAP BusinessObjects Tools. Then, in future articles we will cover each phase of the development in more detail. Generally speaking, we consider Sentiment Analysis as the process of identifying, extracting and measuring data from a subjective information source, such as customer surveys, opinion polls, or tweets as in our case.

Data Extraction

As in any BI project, the first step is to define the data that you need, and how to get it. Using SAP BusinessObjects tools, the best way to do this is to develop an Adapter for Data Integrator using the SDK that this tool includes in its installation folders (check this article from SAP SDN that proved to be very helpful).

However, to do the demo as quickly as possible, we used another approach:

• We developed a Java program that made use of Twitter’s getSearch API to extract tweets and place them in text files Note that for demo purposes this is more than enough, but for a broader project the flat files are not a satisfactory solution.

• With Data Integrator, we configured an ETL flow to extract the data from the files and store them in database tables to accumulate enough tweets to make the demo meaningful.

Also consider that in this phase it is very important to get comfortable with Twitter’s API and the different parameters that it uses so you can take advantage of it as much as possible.

Data Parsing and Sentiment Analysis

Once we were able to place the tweets in text files and customize the extraction parameters as we desire, then we could actually analyse the tweets to start delivering insight from them. To do so, we followed these steps:

• Get the raw tweets that we stored in the database before and perform a parsing process with Data Integrator, to get rid of the JSON format that Twitter API uses, enabling us to manipulate the tweets as text strings.

• Use the feature of Text Analysis that Data Integrator includes to perform the “Sentiment Analysis” process and classify the tweets in one of the different sentiment categories that we used. For the demo purposes that we had there is a SAP Blueprint called Text Data Processing Data Quality that contains Data Integrator jobs with a Voice of Customer implementation that already contains a set of extraction rules implemented for the English language. Therefore, you can make use of this blueprint and its rules to develop the Sentiment Analysis phase.

• Build a universe on top of the tables with the analyzed data in order to make it available for reporting with any of the SAP BusinessObjects tools that take an universe as data source, e.g. Xcelsius, WebIntelligence, Explorer, etc. In this step, we also made a use of an universe that came included in the same Text Data Processing Data Quality blueprint that we used for the point above.

Data Visualization

Finally comes the eye-catching part: present all the hard work you have done. To show the users how flexible this solution can be, we decided to present the data with Explorer and some Exploration Views built on top of its Information Spaces. However, as said before, if you build an universe on top of the tables that resulted from the Text Analysis process then you will have a great number of possibilities and tools to play with, in order to bring forth the presentation you want according to your requirements and objectives.

In future articles, we will cover each one of these sections in further detail. However, with this general layout we hope you get a good idea of what you need to do to make your Sentiment Analysis demo happen!

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

Turn data into actionable insight with BI

Making Better Data-Driven Decisions

Do you wish you had a clearer view on the performance of your company and feel you lack key information to guide your decisions? All the Data you gather in different departments is just piling up, isolated and useless? Taking your organization through the current fragile economy is already challenging enough to do it without visibility of what happens in your organization. In order to solve issues and take advantage of strengths you need to turn data into actionable insight. SAP business intelligence software solutions give you the visibility you need to make important business decisions based on key data and facts, not guess-work. They allow you to draw information from data, rather than just storing it for the sake of it.

Interactive dashboards and rich visualizations help you monitor your business performance at a glance, and the real-time insights allow you to adjust aspects of your business before they become a real problem.

Reporting allows you to access and transform corporate data into highly formatted and automatic reports, while interactive reports let you answer ad hoc questions and interact with data, building your own queries.

Analysis solutions help you determine trends from historical data and make better forecasts.

With data explorations tools you can find immediate answers to business questions in a search-engine manner.

With BI application design tools, your IT department will be able to create BI applications for specific audiences.

It´s not necessarily a matter of implementing each and everyone of the solutions. Depending on your particular needs and user types, you could select the more adequate tool. Take a look at the SAP Business Intelligence Solutions Comparison Matrix to understand a bit more about each product.

Take the example of Vodafone Turkey, they used Excel to manage their several marketing campaigns in the past, but this process was not only susceptible to human error, but also time-consuming. They needed a functional solution to serve multiple users and help them understand campaigns and act according to their results.

They implemented a central dashboard, a highly visual solution that could accommodate a large number of campaigns and variety of KPIs for both new and recurring campaigns. The Campaign Analytics Solution allows the team to analyze existing campaigns and design outlines for new ones based on key success factors. The dashboard also helps the team to understand the net take rate for each campaign compared to the targeted subscribers. And more significantly, marketers can now easily and definitively follow the revenue generated by each campaign.

If you wish to know how SAP Business Intelligence Solutions can help solve your company´s specific needs, contact us on info@clariba.com or leave a comment below.

Data Quality - the basis for good BI

Usually companies learn about the importance of data quality management in the worst possible way – by dealing with the issues generated by the lack of it, and addressing data errors, data movement, and unstructured data after many costly problems. If your data is lacking in quality, everything you learn from it is useless, as information cannot be trusted. Without accurate customer and performance insight you will never be able to see what areas of your business need to improve. Data Quality Management solutions allow you to integrate, transform, improve and deliver trusted data that supports critical business processes and enables sound decisions. As you expand into new markets or develop new products this will become even more important, as the more data you gather, the easier it is for problems to start occurring.

With SAP Data Services you can enjoy a single solution that encompasses data integration, data quality, data profiling, and text analysis. This will allow you to deliver trusted data that supports critical business processes and enables sound decisions.

To give you an example of the importance of data management, Vodafone Netherlands sought the help of Clariba to implement key reports within a maintainable BI solution while automating report generation and distribution and also to develop a dashboard with key indicators for management. However, the first phase of this project focused on ensuring that trusted data was provided from the current databases to the BI solution. Complex queries were streamlined and redundant data sources consolidated. Subsequently BusinessObjects universes were developed for the central data warehouse and the CDR data mart. Only when the relevant data sources were available, with good quality data, the Clariba team went on to develop the reports and dashboard.

Learn how SAP Analytics Solutions can help your company with its data quality management, making quality your goal. Contact us on info@clariba.com or leave a comment below.

Successful Change with SAP Business Intelligence

Deliveting Business Transformation

The pace of change, specially due to the rapid deployment of new technology is growing at an incredible pace. For businesses to remain competitive, they need to keep up with these changes almost constantly, it can come from an expansion, restructuring, merger and acquisition, regulatory compliance and more.SAP Business Intelligence Solutions unveils key concepts and processes that are vital to the planning and execution of successful change strategies.  Being aware of happens in your business can optimize organizational change and smooth and speed the transition periods.

Take the example of Doha Bank, when they had to adapt to a new mandate from the Central Bank of Qatar to report key balance-sheet figures on a monthly basis. The timeline was tight and the requirements complex, with the pressure of punitive fines for the non-compliant.

The internal financial reporting was previously done manually, and data required for the monthly reports was spread across several sources. After an intensive process of data cleansing and consolidation, the Bank went for a SAP Business Intelligence solution called Web Intelligence, which allowed for the reports to be produced automatically, in a timely and error-free manner.

Although we used the example of a big organization, these solutions can also be adopted by the SME. SMEs use their speed of action as competitive advantage to remain in the game with the big players, and they have to constantly adapt to change, be it in the market, in their organization or in their business model.

Find out how your SAP Business Intelligence Solutions can deliver business transformation. Contact us on info@clariba.com, o leave a comment below.

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.

Boost Business Performance at SAPPHIRE NOW

Your business is a living thing, made up of interdependent systems that must work together to carry out crucial processes. Its an arrangement of moving components that operate and interact to deliver results.  Therefore it is vital that all the departments functions flawlessly so that your business can grow.

For this to work out, the first step is to have the right technologies in place to automate repetitive business processes, reduce human error, support communication and collaboration, and monitor performance.

Benefits can be felt in all hierarchy levels.  Top level executives will be able to examine the overall health and direction of the company. Department leaders such as the CFO will e able to tap into analytics and performance management tools in order to get visibility into performance, support compliance and reduce risk. HR can streamline the administration tasks and focus on aligning talent to business goals. Sales and Marketing can understand what customers want and deliver products and promotions accordingly. Operations can be optimized by improving planning and stock replenishment to function more profitably.

Do you want to know more about what SAP solutions could do for your company? If you are at SAPPHIRE NOW don´t miss the opportunity to network with your peers in similar industries and find out how they've used SAP solutions to solve common business challenges in this last day of the event. Visit the interactive campus to get to know all the latest innovations from across the SAP portfolio. Learn more about top solutions from SAP HANA and see why this is the fastest growing product in SAP, business analytics, mobile solutions, the cloud, to database and technology.

If you don´t have the chance to be at SAPPHIRE NOW Madrid this year, contact us at info@clariba.com and we will be more than happy to go through the latest SAP innovations with you, ans share some of our successes, for example on the SAP HANA proof of concept we are developing for a major Telco player in MENA, or the implementation of a BI mobility solutions called MyBI Mobile, which enables you to bring all your SAP Business Intelligence content to mobile devices (including Xcelsius on iPad). You can also visit the SAPPHIRE NOW LIVE website to watch live presentations and panels happening on this last day of SAPPHIRE NOW, and also view the replays of what happened on the previous days.

Cloud computing - the SME favorite at SAPPHIRE NOW

Responding quickly to change is not just a competitive advantage anymore, its a survival requirement. A very common assumption is that small businesses are often more agile than large enterprises. However, small companies can find themselves stuck in the middle of long, resource-consuming IT projects when they try to put new solution s in place to support their growth. When you don´t have a vast specialized IT team for implementation or unlimited hardware and software budgets, every purchase has to be carefully considered. Although Software-as-a-Service (SaaS) can be benefiting to companies of all sizes, the advantages for the small business is particularly appealing. It comes as no surprise that cloud computing adoption among SMEs is booming. SaaS is available on a subscription basis, and it allows enterprise-level applications to be deployed quickly and without  the need for hardware capital or on-premise licenses, and because of the concept of the Cloud, you can tap into economies of scale. The best part is that you can scale up or down to tend to your needs and upgrades and patches don't have to be done by your IT staff, which can now focus on vital IT business strategy. SAP’s OnDemand applications can be delivered in as little as 48 hours, so you can have it up and running in weeks.

At SAPPHIRE NOW’s Cloud Campus you can discover SAP’s cloud solutions, no matter what aspect of your business you are looking to attend to. SAP’s cloud solutions enable you to be competitive even when it comes to bigger corporations, as you will have access to the same technologies they do, and will get the insight you need to make fast, information-based decisions to take you to the head of the game.

At Clariba we understand the need that companies have for rapid solutions to their business problems, and we have been working with pre-packaged solutions as well as on demand, as this makes top notch BI solutions easily accessible, no matter the size of your cpmpany. If you cannot come to SAPPHIRE NOW Madrid this year, contact us at info@clariba.com for more information on how we can help your company become more competitive. You can also check the SAPPHIRE NOW Cloud Blog for news and updates on this topic. You can also follow the Industry blog and the Services blog for more specific news.

Find out the benefits of Enterprise Mobility at SAPPHIRE NOW

Old news: we all know mobile devices nowadays mean a lot more than just a device for making calls. People are using their smart phones and tablets as a way to research, buy, sell, connect with people, well interact in a series of ways in their personal life. This phenomenon is now starting to move to the workplace too. In fact, mobiles are  becoming the preferred way of consuming business information regardless if they are outside or inside the office. Not only companies are seeing a boost in productivity, but they are also realizing that mobile innovation is helping companies streamline business processes due to up-to-the-minute, highly detailed information that enables decisive action in situations where this was impossible previously. Workers can make fact-based decisions, avoiding problems or seizing opportunities in the moment, in ways that traditional desktop applications simply can’t support.

All is well... well... there´s a challenge: to deliver a secure, joined-up mobile strategy that can make “anytime, anywhere” a reality for your business processes. This must also take into account the variety of mobile devices and operating systems in concurrent use, and most of all, ensure company data is secure at all times.

At SAPPHIRE NOW’s dedicated Mobile Campus you can find everything you need to start from SAP´s business-grade apps for management, sales, service, transaction and much more on the go. You will also see SAP’s device management solution, development platform and mobile strategy so that you get an idea of how the whole picture would look. But it´s not about seeing, you will also be able to test these technologies hands-on. This is a great opportunity to get to grips with all the possibilities that mobile solutions can bring to your business: increase productive, improve service, accelerate processes and bring greater flexibility to employees and customers, such as the Standard Bank South Africa has done by unwiring their customers.

If your not at SAPPHIRE NOW this week, you can contact Clariba at info@clariba.com to meet with one of our consultants to learn more about enterprise mobility solutions, such as the one we are developing for Arab Business Machines, which will deliver mobile BI on their iPads for insights no matter where employees are. You can also follow the SAPPHIRE NOW Mobile Blog to follow what is happening at the event and get more information around Enterprise Mobility.