Emerging DB Technology – Columnar Database

Today’s Top Data-Management Challenge:

Businesses today are challenged by the ongoing explosion of data. Gartner is predicting data growth will exceed 650% over the next five years. Organizations capture, track, analyze and store everything from mass quantities of transactional, online and mobile data, to growing amounts of machine-generated data. In fact, machine-generated data, including sources ranging from web, telecom network and call-detail records, to data from online gaming, social networks, sensors, computer logs, satellites, financial transaction feeds and more, represents the fastest-growing category of Big Data. High volume web sites can generate billions of data entries every month.

As volumes expand into the tens of terabytes and even the petabyte range, IT departments are being pushed by end users to provide enhanced analytics and reporting against these ever increasing volumes of data. Managers need to be able to quickly understand this information, but, all too often, extracting useful intelligence can be like finding the proverbial ‘needle in the haystack.

How do columnar databases work?

The defining concept of a column-store is that the values of a table are stored contiguously by column. Thus the classic supplier table from supplier and parts database would be stored on disk or in memory something like:  S1S2S3S4S52010302030LondonParis Paris LondonAthensSmithJonesBlakeClarkAdams



This is in contrast to a traditional row-store which would store the data more like this:
S120LondonSmithS210Paris JonesS330Paris BlakeS420LondonClarkS530AthensAdams
From this simple concept flows all of the fundamental differences in performance, for better or worse, between a column-store and a row-store. For example, a column-store will excel at doing aggregations like totals and averages, but inserting a single row can be expensive, while the inverse holds true for row-stores. This should be apparent from the above diagram.

The Ubiquity of Thinking in Rows:

Organizing data in rows has been the standard approach for so long that it can seem like the only way to do it. An address list, a customer roster, and inventory information—you can just envision the neat row of fields and data going from left to right on your screen.

Databases such as Oracle, MS SQL Server, DB2 and MySQL are the best known row-based databases.
Row-based databases are ubiquitous because so many of our most important business systems are transactional.
Data Set Ex:  See the below data set contents of 20 columns X 50 Millions of Rows.

Example Data Set

Row-oriented databases are well suited for transactional environments, such as a call center where a customer’s entire record is required when their profile is retrieved and/or when fields are frequently updated.

Other examples include:
• Mail merging and customized emails
• Inventory transactions
• Billing and invoicing

Where row-based databases run into trouble is when they are used to handle analytic loads against large volumes of data, especially when user queries are dynamic and ad hoc.

To see why, let’s look at a database of sales transactions with 50-days of data and 1 million rows per day. Each row has 30 columns of data. So, this database has 30 columns and 50 million rows. Say you want to see how many toasters were sold for the third week of this period. A row-based database would return 7-million rows (1 million for each day of the third week) with 30 columns for each row—or 210-million data elements. That’s a lot of data elements to crunch to find out how many toasters were sold that week. As the data set increases in size, disk I/O becomes a substantial limiting factor since a row-oriented design forces the database to retrieve all column data for any query.

As we mentioned above, many companies try to solve this I/O problem by creating indices to optimize queries. This may work for routine reports (i.e. you always want to know how many toasters you sold for the third week of a reporting period) but there is a point of diminishing returns as load speed degrades since indices need to be recreated as data is added. In addition, users are severely limited in their ability to quickly do ad-hoc queries (i.e. how many toasters did we sell through our first Groupon offer? Should we do it again?) that can’t depend on indices to optimize results.


Pivoting Your Perspective: Columnar Technology

Column-oriented databases allow data to be stored column-by-column rather than row-by-row. This simple pivot in perspective—looking down rather than looking across—has profound implications for analytic speed. Column-oriented databases are better suited for analytics where, unlike transactions, only portions of each record are required. By grouping the data together this way, the database only needs to retrieve columns that are relevant to the query, greatly reducing the overall I/O.

Returning to the example in the section above, we see that a columnar database would not only eliminate
43 days of data, it would also eliminate 28 columns of data. Returning only the columns for toasters and units sold, the columnar database would return only 14 million data elements or 93% less data. By returning so much less data, columnar databases are much faster than row-based databases when analyzing large data sets. In addition, some columnar databases (such as Infobright®) compress data at high rates because each column stores a single data type (as opposed to rows that typically contain several data types), and allow compression to be optimized for each particular data type. Row-based databases have multiple data types and limitless range of values, thus making compression less efficient overall.

Thanks For Reading This Blog. View More:: BI Analytics

Business Objects Content Management planning

Hi BOOglers,

Let us resume with Administration track once again with Content management planning in Business Objects. Hope this is going to be more interesting and helpful for the Administrators to start up with.

Content management plan is a collection of procedures used to manage Business objects work flow in a collaborative and manageable environment. This ensures who needs access to what. While planning for Business Objects enterprise system appropriate content management planning is an important factor. Because of lack of expertise and time we always end up with BOE environment which is not structured properly, as a result BOE environment will become more difficult to manage and maintain.

Below are simple measures to consider in order plan for our BI content.

• Easy to understand the hierarchy and secure implementation
• Ensure users only accessing documents which they interested and authorized to.
• Efficient structure so users are able to search the info they need easily
• Easy access to information in the system will increase effectiveness of using the system.

Points to be consider for the Content management Planning

1. Creating a folder structure and organizing objects

As a first and foremost step we need to segregate the BI content according to users who is going to consume the information. This will enable us to decide the folder hierarchy of the system.

For example, for set of reports to Marketing department, we will manage them in the Marketing parent folder. Then we have a subfolder called Marketing- Americas or Marketing-Asia Pacific where reports can further be separated.

2. Organize users by creating a Group/User structure

Now we need to organize user group structure that will allow access to BI content. This will be similar to the folder structure, in our example we will be having a Marketing group each further categorized basd on the region.

3. Set access levels for folders and objects

Next we need to establish the security access levels for folders and objects contained in our group/user structure. This is extremely critical as we may risk in setting inappropriate security access levels for our users. Determining the needs of our users will help us establish who needs access to what folders and objects within the system. For example only users of the Marketinggroup can access the Marketing folder based on their functional roles.

Custom access levels created for each functional user group is depicted as below.

And finally this is how security is applied on Folder at Group level.

4. Creating corporate categories and assigning objects

The advantage of categories is that it will help the uses to search and access the reports that are appropriate to them. This can be configured according to uses requirement.

For example, the Marketing department user search for reports that are specific to vendor evaluation,

Even if the vendor related reports managed across different folders, we can create a separate category called Vendor management and group the corresponding reports in the Vendor management category. As a result the user not necessarily needs to go to every folder and search for the required report. They can simply access the category with which they authorized to access with.

In the below screen though the reports Marketing Dashboard and Sales Dashboard physically exist in Marketing and Sales folders respectively, still they can be accessed from Vendor  Management category.

The above are the initial steps that we can take to create a planned content management system with which we can end up with success.

I look forward to your inputs and feedback. Thanks for reading!

Read More  Business Objects Administration

AUTOMATION OF BW QUERIES USING WEB BROWSER

In the fiercely increasing competition amongst corporations it has become mandatory to make quick and sound crucial business decisions based on analysis of business critical data. This is the point where SAP BW comes into play. BW Queries are the core of the reporting and analysis functionality in SAP BW. They provide a flexible and intuitive platform for data analysis that can be developed using the SAP Business Explorer (BEx) Query Designer.


AUTOMATION OF BW QUERIES – OVERVIEW


The Automation of queries in SAP BW is require where the data from BW is extracted by reporting analyst using the BW queries and send to business frequently on a regular basis for their analysis for example monthly or weekly.


Queries using web browser can be configured to be run:
Only Once: We can schedule the BW queries to be run on a specific day or time to be refreshed and sent to the email id of the recipients.
Scheduled Basis: We can schedule the BW queries to be run on a regular basis i.e. every day, specific day of a week or monthly basis.


This document outlines a set of guidelines to automate a query for a given scenario.


PROCEDURE TO AUTOMATE THE QUERIES USING WEB BROWSER:


Step 1: Log in SAP BW using the User Name and Password. Enter the RRMX as transaction code into the transaction window and hit the Enter. New Excel window will open with the additional BEx (Business Explorer) tool bar.

Step 2: Open a query which you want to automate through BEx and refresh the same.

Step 3: Launch the query in web browser.

Tool Button –> Launch in Web Browser –> Query (Default View)

When you click on the Query (Default View) it will pop up the message box asking the user name and password.

While you enter the user name and password it will populate the query in web browser.

Step 4: Modify the output structure of the query according to your requirement using the Variable screen at the left side of the window

Applying filters


If you want to apply any filter on the query then you can do that using filter icon against the characteristics. (Ex. If you have to produce the report for particular business unit only then click on the filter icon in front of Master Cost Centers it will pop up another window. Select the Business unit as per your requirement and click on transfer at the bottom left.)

If you want to set the properties of any characteristic then right click on that it will pop up window and select the properties. It will pop up another properties window that will allow you to change the properties of the characteristics.

Step 5: Information Broadcasting: When you are done with the formatting of the query click on the Information Broadcasting and mention the email addresses of the recipients whom the report needs to be sent then click on Create new Setting with the Wizard.

Step 6: Select on the check box as Zip File, it will create the Zip file for the report then click on continue at the bottom.

Step 7: Enter the subject line for the report and enter the E-mail content in the content window and continue at the bottom.

Step 8: Insert the technical name and description for the automated report.

Step 9: Schedule the report: Select the Create New Scheduling and Periodic All and enter the date which you want to schedule the report for and the schedule as weekly or monthly. Mention the date and time on which you want the report to be sent in front of Next Start at and click on the transfer below.

If you want to execute the report at the time of creation then click on the Execute button at the bottom otherwise close that. Now the report is scheduled for the period you mentioned this will automatically refresh the query in the same format you created on the scheduled date and E-mail this report to the recipients with the report attached in Zip format.
MODIFICATION IN SCHEDULE OF AUTOMATED QUERIES:-
If you want to make changes in the schedule then select the query and click on the Schedule button at the bottom it will pop up the window just create the new schedule and delete the old as well and click on transfer.

The BEx Web query automation is a standalone, comfortable Web application for data analysis that the user can call up using an URL or as an iView in the Enterprise Portal. The Open a query which you want to automate through BEx and offers a wide range of functions for data analysis via various tab pages and the associated view-specific toolbars.
SAP query automation is an information modeling tool which can be used to analyze business data. To help analyze data in a more user friendly manner, SAP BW used to describe any kind of numeric information from a business process level. There are various ways to describe key figures in SAP BW.
Essentially, a query is a database research action with interesting additional functionality like currency scenarios, complex calculation options, and analysis functions. However, analyses born out of queries can be applied flexibly to a multitude of areas in a multidimensional dataset of an SAP BW Info Provider. This is enabled by combining analytical functionality with the provided drilldowns and filter options. Therefore, one query or few queries can often map an entire analytical application.

HP Extensibility Accelerator

The rapid adoption of Rich Internet Application (RIA) technologies and Web 2.0 innovations has significant implications for both web users and the teams involved in the functional testing of web-enabled applications. The advent of Web 2.0 applications has created unprecedented challenges for organizations that focus on automated functional testing.  Web 2.0 enabled applications can leverage various technologies on the client side and through web browsers. With Web 2.0, the client side of the application processes more scripted code and rich presentation frameworks than in traditional environments. This shift of processing to the client side challenges the capabilities of all toolsets designed for the functional testing of Web-Enabled Applications.

Testing challenges in Web 2.0:

Some of the typical challenges faced were as follows:

• Web pages are dynamic and asynchronous.
• Portions of web pages can now be refreshed automatically to give users updates on sports scores, stock quotes, etc.
• Current activities of people they connect with via social networking sites
• Users have more control than ever before. Via sites such as iGoogle, users can now create their own home pages that bring together information and content from across the web like, local weather forecasts, headlines from prominent news outlets, and videos from YouTube etc.
• Client side of the application processes more scripted code and rich presentation frameworks than in traditional environments

Why HP Extensibility Accelerator?

HP thought of putting forth advanced tools to test Web 2.0 technologies with HP Functional Testing. As a result, HP came up with a new accelerator for functional testing to overcome those challenges. The Extensibility Accelerator for HP Functional Testing provides a Visual Studio-like IDE that accelerates and facilitates the design, development and deployment of HP QuickTest Professional Add-in Extensibility support sets.

These support sets extend the HP Functional Testing Web Add-in so you can test Web controls that are not supported out-of-the-box.

Evolution of HP Extensibility Accelerator:

Extensibility is enhanced and accelerated with the new HP Extensibility Accelerator for Functional Testing software, which provides an environment that speeds the development of Web Add-in Extensibility toolkit.

What is HP Extensibility Accelerator?

HP Extensibility Accelerator for Functional Testing is a separate utility that can be used on a machine with or without an installed copy of HP Functional Testing.

It provides a user interface and special tools that help us define new test object classes, map those test object classes to the controls in our application, and teach QTP how to identify the controls, perform operations on the controls and retrieve their properties.

Features of HP Extensibility Accelerator:

• Creating and defining the test object classes using JavaScript functions for the custom controls.
• HP Extensibility Accelerator provides JavaScript editing capabilities and debugging tools to facilitate the writing of these functions.
• Maps the test object class to your control and application, and it automatically identifies the rules that will teach HP QuickTest Professional how to recognize the test object class in your application.
• The HP Extensibility Accelerator IDE simplifies the process of creating and editing the test object/toolkit configuration XML files.
• The HP Extensibility Accelerator deployment capabilities enable you to automatically deploy your new toolkit support set to HP QuickTest Professional or to package it so you can share it with other HP QuickTest Professional users

Conclusion:

With the HP Extensibility Accelerator for Functional Testing, we’re making it easy for our users and partners to create their own extensibility assets and extend our software to support web controls that are not supported out of the box. With the hundreds of Ajax toolkits in use today and new ones coming out each month, the HP Extensibility Accelerator provides an extremely important set of tools for your organization.

The software itself can be installed and used on a machine that does not have HP QuickTest Professional on it. Custom toolkits developed with the software can then be deployed on one or more systems that are running HP QuickTest Professional.

Marketing Automation in B2B – Separating the Wheat from the Chaff

The B2B landscape in its inherent form is a complex jar of beans primarily because the initial connection needs lots of nurturing with the right mix of appropriate communication to ensure the “best weather” for sales interaction. Marketers not only have to measure outcomes right up to revenue but also find the “sweet spot” for marketing and sales to drum up the right notes.

The year 2009 and the first half of 2010 saw a marked shift towards marketing automation worldwide. All of this has helped channelize information and reach out to prospects better, yet it is pertinent to note the today’s internet savvy prospect is also armed with qualifying information about your brand, your products and your competitors as never before. To get inside the mind of the B2B buyer, marketers not only need to understand his intent from his digital body language but also ensure that automated lead generation processes in place scale up in terms of the following pertinent factors at any point of time.

• Are lead recycling programs in place for not-sales-ready leads?
• Has social media, inbound marketing and marketing automation been integrated seamlessly?
• Is your marketing communication supported by buyer-centric collaterals that help the buyer decide in your favor?
• Has your data been data washed and scrubbed clean?
• Does your web metrics provide actionable information for user profiling and conversion?
• Are sales and marketing on the same latitude to proving your prospect the best buying experience?
• Does your Social media spin influence the markets conversation about your brand effectively?
• Is your opt-in list getting fresh brew in the form of persuasive communication and supporting newsletter value?
• Is your marketing funnel measurable and process definitions flexible to innovation?
• How effective is your conversation model, does it ensure that you are at top of mind when prospects decide to bite the bait?
• Are you able to capitalize on marketing automation’s great benefit – reporting effectively and use it as a strategic tool?
• Does your data-centric marketing plans lead the way for greater customer intelligence since value of data will not be a constant ?

The above are just a few important cogs that can make or break your lead generation wheel. As marketers brace themselves to capitalize on marketing automation to enhance pipeline opportunities, trends all point to an explosive growth in marketing automation adoption. It is highly imperative that automation vendors provide more sophisticated reporting, better sales engagement processes and social media integration.

To help marketing efficiently separate the wheat from the chaff, marketing automation should not just serve as a driver of operational efficiency but more importantly enhance continuity of dialogue with prospects throughout the decision making/buying cycle at all relevant touch points.

Ultimately it is all about the harvest – the pipeline and revenue, the executive leadership would not mind how you do it.

Read More about Marketing Automation

Oracle Fusion Middleware

Applications can be integrated using either of the following integration solutions:

• P2P (Point To Point) – It involves creating a direct connection between each pair of application to be integrated.
• Hub-and-Spoke – It involves connecting each application to a central integration server running middleware.

In comparison to P2P integration strategy, Hub-and-Spoke methodology is preferred. Adding an additional application requires only one new data transport to and from the integration server. The numbers of data transports are reduced from n² to n.

Oracle provides a strong Integration Server architecture known as Oracle Fusion Middleware Service Oriented Architecture (SOA) suite. This architecture comprises of Oracle Application server, Enterprise Service Bus (ESB), Business Process Execution Language Process Manager (BPEL PM) and other components for security, BI, administration, monitoring etc.

1. Oracle Application Server provides a J2EE container for other applications and third party applications.
2. ESB is an engine which routes and transforms the data. It can have multiple data entry points.
   Example – The source and target systems are having different schemas. Data transformation can be done using Siebel data mapper and can be transported using the Business Service. The same requirement can also be solved using ESB service. Apart from this, ESB service can be initiated from Siebel CRM after importing the “wsdl” of the Outbound Web Service.
3. BPEL PM automates the business processes across multiple applications. It uses a visual coupled with programmatic constructs.
   Example – Combined information from Siebel CRM, Credit Card Company and a shipping company to generate final form of order. BPEL PM can be used to orchestrate and automate these multiple applications using a single entry point.

SOA suite resides on the Integration Server (IS). Siebel CRM lies on one side of the IS and third party applications reside on other side of the IS. Siebel can thus behave as a Sender or Receiver.

Read More about Oracle Fusion Middleware