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.

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.

Evolve from spreadsheet analysis into innovative Business Analytics at SAPPHIRE NOW

Not long ago, most small and medium-sized companies did data analysis with spreadsheets as more sophisticated business intelligence systems were not affordable. When you are using spreadsheets to get the information you need, when you need it, you are in a daily battle and on top of that, the data is almost always historical, so you are planning for tomorrow based on a picture from the past, not the present.In the present days data grows exponentially in terms of volume, variety of sources and the velocity at which it’s generated – which means that manual spreadsheet analysis can´t give you what you need fast enough. When decisions need to be made in a timely manner due to the fast pace of business you have little margin for error.

However, recent innovations in analytics are making business insight accessible, real-time, predictive, visual, mobile, social and affordable, take the example of The Carbon Disclosure Project. If you want to know more and you happen to be at SAPPHIRE NOW Madrid this year, a visit to the dedicated Analytics Campus is the ideal place to start. Here you have SAP experts can show you how analytics can drive faster, better informed decision-making at every level of your business. You will see how analytical tools designed for non-technical users allow you to intuitively uncover trends, patterns, warnings, opportunities within your data, so you can predict and adapt to changing conditions ahead of your competitors. For example, you will be able to ask the same questions Bill McDermott asked at SAPPHIRE NOW Orlando, "How will we grow? How will we innovate more powerfully, more competitively? How will we reinvent our business models and our business networks?

This is a great opportunity to find out more about the latest developments in analytics, get face-to-face advice from the experts, and swap experiences and best practices with your peers that you can bring back to create repeatable successes in your own business and help you answer these questions.

If you are not at SHAPPHIRE NOW, don´t worry, Clariba is more than happy to meet you and demonstrate the power of the SAP Business Analytics portfolio. As a Business Intelligence (BI) expert, we have the capabilities to consolidate data from your spreadsheets or any database so you can get a single source of the truth to draw BI insight from with SAP BusinessObjects BI stack, such as reports and dashboards. Contact us at info@clariba.com to schedule a live demo. Also, if you are not at SAPPHIRE NOW but would like to know what has been happening, go to SAPPHIRE NOW LIVE, or if you are  interested in business analytics you can check the SAPPHIRE NOW Analytics Blog.

Developing an Advanced Auditing Strategy Using Data Services

How often do you ask yourself: why isn't there anything similar to the audit universe that comes as a default feature in the SAP Business intelligence platform 4.0 for auditing ETL? How useful it would be to have an audit table in order to save useful information regarding Data Services ran jobs? All of us know that you can audit some things via DSCM (Data Services Management Console), but can you do it at the row level? In order to solve all of these questions, this article explains a workaround for auditing the ETL at the row level and make your life easier when errors occur, when you need an analysis or if you are just tracking data.

The idea is quite simple and the main part of it consists of a table (Load_Audit_Table). This table contains a number of columns that include a unique identifier called “Load_Key” and some other columns containing:

• the name of the user who ran the job
• the name of the job ran
• the starting date and time
• the ending date and time
• a Boolean field to indentify if the job finished in error or not
• the error message
• the log file location description.

Of course you can add more columns depending on your needs.

The other important part of the solution is a column named “Load_Key” and that column should appear in all the tables we use in the ETL (ODS tables, FACT tables, and so on).

The purpose of this solution is to give us the possibility not just to easily identify an error but also to audit at the row level. This means we can also analyze or track data which has been loaded in a certain date during the whole process because all the rows in all the tables that had loaded on this date will have the same “Load_key”.  Have a look at the example below.

Every time the ETL runs, a new load Key in generated automatically and is loaded into the Load_Audit_Table and in all the other system tables if there are no errors.

Now that we understand how it works and the value of this implementation, we need to explain how to develop this solution in terms of objects, codes, etc.

In the image below you find the job structure developed to implement the auditing method:

As you can see in the image the job is ALWAYS composed by two workflows, one at the beginning called “Audit_start” and a second one at the end of the job called “Audit_End”.

The job that we want to audit is located in the middle, in our example we are auditing the workflow that loads the dimension tables. Moreover, the main workflow should be surrounded by the “Try” and “Catch” blocks in order to get the error information at the end of the job in case of failure.

Now let see what is inside of each one of these blocks in order to understand how the auditing system works.

In the image below we are showing what is inside the first block, “Audit_Start”.

In this step we generate the unique identifier for the audit table. It is very simple: in the plain code block we add a SQL function which extracts the last key from the Audit table plus 1.

Sql(‘Datawharehouse’,’select max(load_key) +1 from audit_table)

Then, inside the data flow, we generate a new row in order to add the new key in the audit table.

The next block is the Try, but this block cannot be modified because it is only a block that gives a flag to record error information. The only thing we need to do is add it into the job and create the links.

After the main job, the Catch block is the following. In this case, Catch is editable - by opening the block with a double-click we can add objects into it.

In the image below you can see what is inside the Catch block.

First of all, we have the Audit_End workflow, and you might ask why? The reason is because when an error occurs, the job finishes in the Catch block. This means that if we had not got the Audit_end block inside the Catch block, every time there was an error, we would have been missing important information.

Then a plain code block comes; inside it there is a code that provides us all the error information and the code that allows us to update the new row created for the new load_key.

sql('DataWarehouse','update load_audit set is_error='1', error_num=' || error_number() || ', error_desc='' 

|| error_message() || ''where load_Key='|| $G_Job_Load_key);

Finally, the last step is adding the “Audit_End” block which provides us with the date and time when the job is finished. To do this we write a SQL code that adds this information to the Audit_table.

sql('DataWarehouse','update load_audit set job_end_date=sysdate where load_Key='|| $G_Job_Load_key);

Now you have implemented the audit method developed by Clariba consultants! In addition, you can use this auditing table to shoot an email when an error occurs. You can set it up by reading the column “Is_Error” and if the record is “1” send an email to the BO administrator with the error information saved in the same table.

We hope this will be of use to you. If you have any questions or observations, please leave a comment below.