We will start 2010 post in this blog by step by step guide to install green plum client in your computer.  Here we use windows platform for the client.

During 2009 we have posted a lot of article related to Data Warehouse, especially Oracle BIEE and Greenplum in this blog. In the end of this 2009, we would like to list the best of the best article according to user pageview. Here’s the top 7 article in the Data Warehouse Solutions blog:

1. Green Plum Features and Benefits
Greenplum Database provides many advantages over traditional data warehousing systems:
- Open Source
- Commodity Hardware
- Shared Nothing Architecture
- High Availability
- Workload Management
- Scalability and Flexibility
- Performance

2. Greenplum: Open Source Data Warehouse
In 2005, Greenplum released an enterprise-level massively parallel processing (MPP) version of PostgreSQL called Greenplum Database. Greenplum Database is the industry’s first massively parallel processing (MPP) database server based on open-source technology. It is explicitly designed to support business intelligence (BI) applications and large, multi-terabyte data warehouses.

3. The Compelling Need For Data Warehousing
Why are companies rushing into data warehousing? Why is there a tremendous surge in interest? Data warehousing is no longer a purely novel idea just for research and experimentation. It has become a mainstream phenomenon. True, the data warehouse is not in every doctor’s office yet, but neither is it confined to only high-end businesses. More than half of all U.S. companies and a large percentage of worldwide businesses have made a commitment to data warehousing.

4. Pentaho as Open Source Business Intelligence
Pentaho is an open-source reporting application, with several enterprise capabilities such as chart generation, dashboards, data mining, and pivot table analysis. Pentaho is a 100% Java application, based on the JBOSS application server and JBOSS Portal for advanced user customization of dashboards.

5. Oracle BIEE Solution
The Most Comprehensive BI from the Worldwide Leader in Business Analytics Oracle Business Intelligence (BI) is the most comprehensive portfolio of technology and applications for enabling the insight-driven organization, including leading BI applications, BI platform technology, and data warehousing. With the #1 market share in business analytics, Oracle BI enables organizations to gain complete and timely insight, distribute intelligence pervasively, and drive more effective actions and processes.

6. How to Install Greenplum Database 3.3
We got the how to install Greenplum 3.3 from the Greenplum Documentation. Here are the steps to install and initialize Greenplum Database system.

7. Greenplum Database and PostgreSQL
The object-relational database management system known as PostgreSQL is derived from the POSTGRES package written at the University of California at Berkeley. With almost three decades of development behind it, PostgreSQL is now the most advanced open-source database available.

According to Gartner, open-source BI tools deployments will grow five-fold through 2012. The research firm also added that the open-source BI tool deployment is growing solidly. According to various analysts, an effective and integrated BI solution can improve business performance by having better decision making across the company. At present, various large houses like Microsoft, Oracle (Hyperion), IBM (Cognos), and SAP (Business Objects) offer BI solutions to achieve the success faster.

The Leading vendors in BI solutions are:
1. SAP – Business Objects
Business Object that recently take over by SAP, provides one of the extensive BI product ranges. The BI solution from the company is called as SAP Netweaver Business Intelligence (SAP BI).

The solution helps its consumers to extract data from a specific source, applying transformation rules, and loading it into the Data Warehouse Area. Also it helps the companies to run simulations and cost calculations.

2. IBM – Cognos,
Cognos similar to Business Object that had been bought by big company, Cognos owned by IBM and developed a business tool for handling intelligence that is used by thousands of companies in 135 countries. The software tool is known as IBM Cognos 8 Business Intelligence, which combines all the jobs of good performance management reporting, analysis, grading, data integration, event management, dashboards, etc. According to the company, with its BI solutions organizations will find an opportunity to make better decisions faster.

3. Microsoft BI solutions
Microsoft BI solutions based on ProClarity system that bought by microsoft in 2006, It can help various organizations quickly access the timely, relevant, and accurate information for better decision making. It also saves money, provide real time information and removes inefficiencies. However, various analysts suggest that the Redmond giant still lags behind pure-play vendors in terms of metadata management, reporting and ad hoc query capabilities.

4. Oracle – Hyperion
Oracle had acquired Hyperion to expand their BI solution. The company claimed to be a leader in EPM, unifying Performance Management and BI solutions, and it will support a broad range of strategic, financial and operational management processes. Oracle BI Apps or Oracle BI Enterprise Edition (OBIEE) offers firms to achieve management excellence – being smart, agile and aligned.

5. HP – BI services
In last two-three years, the top four BI vendors had acquired smaller vendors to excel in the field.

Bad planning and improper project management practice is the main factor for failures in data warehouse project planning. First of all, make sure that your company really needs data warehouse for their business support.  Then, prepare criteria for assessing the value expected from data warehouse. Decide the software on this project and make sure where the data warehouse will collects its data sources.  You need to make rules on who will be using the data and who will operate the new systems.  Next we will elaborate one by one this step in planning your data warehouse for your company.

Important Key Issues

How to make sure that the company is really needs the data warehouse? The best way to find out is by answers the important key issues in planning your data warehouse.  Following items is the key questions in prepare your data warehouse importance.

Value and Expectations.

Will your data warehouse help the management to do better planning? Will this system help them make the right decisions? How much this system could increase the company market share? What is management expectation with this data warehouse?, all these questions is the starting point to valuate your project planning.

Those all questions is the end to end guidelines in the all project phase.  Whenever the project face the difficulties and required the best solution, just simply go back to those primary questions.

Risk Assessment.

Assessment of risks in IT project is more than calculating the loss from the project costs.  We should also consider the risk for the company if they not implemented the system, how many opportunities will be missed by the company? What possible impact if the project is not finished by the plan for the company business plan? All of these need to include in your assessment of risk, besides also the loss of the project costs.

Top-down or Bottom-up.

Top-down approach starts at the enterprise-wide data warehouse. Data from the large enterprise-wide process by data warehouse and used in the departmental and subject data marts. Bottom-up approach starts from individual data marts to make the enterprise data warehouse.

The two approach that will be taken in your company data warehouse need to consider this following things:

-       Do you have enough resources, time and budget to build a corporate-wide data warehouse? But may advantages from the fully unified data warehouse.

-       Or your company need to prove the data warehouse usefulness by implementing small amount of data marts and continues to another data marts.

Build or Buy.

In a data warehouse, there is a large range of functions, such as: Data extraction, Data transformation and loading data from storage.  You have to decide are you going to buy all these function from vendor or making some function customize with your own company business needs.

Single Vendor or Best-of-Breed.

Choosing a single vendor solution has a few advantages:

-       High level of integration among the tools

-       Constant look and feel

-       Seamless cooperation among components

-       Centrally managed information exchange

-       Overall price negotiable

Advantages on choosing the best-of-breed vendor selection:

-       Could build an environment to fit your organization

-       No need to compromise between database and support tools

-       Select products best suited for the function

After brief explanation on data warehouse project planning, we will discuss on how to create the business requirements for you data warehouse projects.

Business Intelligence Reporting Tools : Do You Build It Yourself Or Buy One ?

Before considering building your own reporting tools or buying a new one, here are a few questions that you should consider

• How many reports will be made ? If your organization is planning on producing plenty of report, you might consider investing th
  

  Image via Wikipedia

  e budget on business intelligence reporting tools as creating the report application manually one by one could take a long time.

• How will the report be distributed ? If the executives in your organization has a different preference of consuming the report (web based, SMS alerts, email delivery etc.), it’ will consume a lot of resource to build reports for each of the distribution method
• Will the users will be able to create their own custom made report easily ? If the report end user are likely to able to create and alter the report tailor-made for their consumption,  then you should consider purchasing a flexible reporting system rather than building different report for different users which basically shows the same data.

Must Have Features Of Business Intelligence Reporting Tools

The report presentation is as important as managing the data in the data warehouse itself. Therefore you should pay attention to the following points when evaluating business intelligence reporting tools:

• Data source connection capabilities : The reporting tools must have the feature to be able to connect to various types of data source since it is easy to assume that the reporting source will come from aggregation of various source of data.
  
• Scheduling and distribution capabilities: With the scheduling and distribution feature, executive users will be able to flexibly consume the report in a timely fashion and will be able to make critical business decisions accordingly.
  
• Security Features: Security is an important issue in almost every software application, including reporting tools. You don’t want unauthorized access to important business intelligence reports in your organization do you ?
  

• Customization: Flexibility is another plus feature in current business intelligence reporting tools. Many executive users will expect the data presentation will match accordingly to their preference.
• Export capabilities: Many report users don’t consume the report data as it is. Most likely they will export it to another format (PDF, Excel and/or other Office supported formats) which then will be used for their work.
  

Popular Business Intelligence Reporting Tools We’ve Covered

• Pentaho : Open Source Business Intelligence

What is Pentaho?

Pentaho is an open-source reporting application, with several enterprise capabilities such as chart generation, dashboards, data mining, and pivot table analysis. Pentaho is a 100% Java application, based on the JBOSS application server and JBOSS Portal for advanced user customization of dashboards.

Pentaho also uses several other open-source components, such as:

JFreeChart for chart generation

JFreeReport and JasperReport for reporting

JPivot for pivot table analysis

Kettle for ETL

Mondrian as an OLAP server

Quartz as a job scheduler

By using these products, Pentahoe gives a lot of choices for implementation in an enterprise environment.

Pentaho can be used as a standalone application or embedded into your application.

Standalone Application

Standalone includes integrated distribution includes the Pentaho code and a customized JBoss application server, a JBOSS Portal instance, and a built-in HSQL database. This distribution focuses on ease of use and out-of-the-box experience. To try the distribution, just download, unpack, start the application server, and point the browser to the default page. After completing these steps, you will have a complete, working reporting server.To create and edit reports, Pentaho uses another industry standard like Eclipse as an IDE for its Pentaho Design Studio. The Pentaho Design Studio provides an easy-to-use environment for generating new reports and Action Sequence components.The latest Pentaho stable release is 1.2 and the development version is 1.5.2. To learn more about Pentaho and its features refer the website at www.pentaho.org.In this chapter, we’ll cover the basics of installing and integrating Pentaho with our OSWorkflow Workflow instance database to create graphical charts that help the decision making process as well as the monitoring of business processes.

Embedded Application

Enbedded application includes pentaho in the application different application, such as CRM application or ERP application.

Pentaho Business Intelligence Requirements

Pentaho is a complex Java system, requiring a J2EE application server, thus the memory consumption is huge. If you want to edit the Pentaho components simultaneously, you need to have at least 1 GB of RAM in your system. If you don’t have this amount, you’ll have to alternate between editing and testing. Pentaho needs at least Java version 5 to run.

We got the how to install Greenplum 3.3 from the Greenplum Documentation.  Here are the steps to install and initialize Greenplum Database system.

1. Run the installer on the Greenplum Database master host.
2. As root, set the OS tuning parameters for your platform on all Greenplum hosts.
3. Allocate a gpadmin user to own and run your installation. This user must exist on all Greenplum hosts.
4. Source the greenplum_path.sh file in your gpadmin user profile (.bashrc). This sets the environment variables needed by Greenplum Database.
5. Create your data directory locations on all Greenplum hosts.
6. Use the gpssh-exkeys utility to exchange SSH keys between all hosts in your Greenplum array. Note that for a single host demo configuration you still must exchange ssh keys between the current host and itself.
7. (multi-host configuration only) Use the gpscp and gpssh utilities to copy and install the Greenplum Database software on all segment hosts.
8. Use the gpinitsystem utility to initialize and start your Greenplum open source data warehouse system. This utility requires a configuration file.

For example:  gpinitsystem -c gp_init_config

A sample gp_init_config configuration file can be found in $GPHOME/docs/cli_help/gp_init_config_example

Edit this file to reflect your desired Greenplum Database array configuration.

Image via Wikipedia

In our previous article, we briefly discussed why companies need data warehouse. In this article we will discuss 5 defining features of data warehouse, which includes subject-oriented, integrated, time-variant, non-volatile and data granularity. After reading this article, you’ll be able to define data warehouse features and differentiate with operational database with by analyzing these points .

Subject-oriented

In operational database, we store data by individual applications. In the data sets for an order processing application, we keep the data for that particular application. These data sets provide the data for all the functions for entering orders, checking stock, verifying customer’s credit, and assigning the order for shipment. But these data sets contain only the data that is needed for those functions relating to this particular application. We will have some data sets containing data about individual orders, customers, stock status, and detailed transactions, but all of these are structured around the processing of orders.

In striking contrast, in the data warehouse, data is stored by subjects, not by applications. If data is stored by business subjects, what are business subjects? Business subjects differ from enterprise to enterprise. These are the subjects critical for the enterprise. For a manufacturing company, sales, shipments, and inventory are critical business subjects. For a retail store, sales at the check-out counter is a critical subject.

Integrated

For proper decision making, you need to pull together all the relevant data from the various applications. The data in the data warehouse comes from several operational systems.Source data are in different databases, files, and data segments. These are disparate applications, so the operational platforms and operating systems could be different. The file layouts, character code representations, and field naming conventions all could be different.

In addition to data from internal operational systems, for many enterprises, data from outside sources is likely to be very important. Companies such as Metro Mail, A. C. Nielsen, and IRI specialize in providing vital data on a regular basis. Your data warehouse may need data from such sources. This is one more variation in the mix of source data for a data warehouse.

Before the data from various disparate sources can be usefully stored in a data warehouse, you have to remove the inconsistencies. You have to standardize the various data elements and make sure of the meanings of data names in each source application. Before moving the data into the data warehouse, you have to go through a process of transformation, consolidation, and integration of the source data.

Time-variant

For an operational system, the stored data contains the current values. In an accounts receivable system, the balance is the current outstanding balance in the customer’s account. In an order entry system, the status of an order is the current status of the order. In a consumer loans application, the balance amount owed by the customer is the current amount. Of course, we store some past transactions in operational systems, but, essentially, operational systems reflect current information because these systems support day-to-day current operations.

On the other hand, the data in the data warehouse is meant for analysis and decision making. If a user is looking at the buying pattern of a specific customer, the user needs data not only about the current purchase, but on the past purchases as well. When a user wants to find out the reason for the drop in sales in the North East division, the user needs all the sales data for that division over a period extending back in time. When an analyst in a grocery chain wants to promote two or more products together, that analyst wants sales of the selected products over a number of past quarters.

A data warehouse, because of the very nature of its purpose, has to contain historical data, not just current values. Data is stored as snapshots over past and current periods. Every data structure in the data warehouse contains the time element. You will find historical snapshots of the operational data in the data warehouse. This aspect of the data warehouse is quite significant for both the design and the implementation phases.

Nonvolatile

Data extracted from the various operational systems and pertinent data obtained from outside sources are transformed, integrated, and stored in the data warehouse. The data in the data warehouse is not intended to run the day-to-day business. When you want to process the next order received from a customer, you do not look into the data warehouse to find the current stock status. The operational order entry application is meant for that purpose. In the data warehouse, you keep the extracted stock status data as snapshots over time. You do not update the data warehouse every time you process a single order.

Data from the operational systems are moved into the data warehouse at specific intervals. Depending on the requirements of the business, these data movements take place twice a day, once a day, once a week, or once in two weeks. In fact, in a typical data warehouse, data movements to different data sets may take place at different frequencies. The changes to the attributes of the products may be moved once a week. Any revisions to geographical setup may be moved once a month. The units of sales may be moved once a day. You plan and schedule the data movements or data loads based on the requirements of your users.

Data Granularity

In an operational system, data is usually kept at the lowest level of detail. In a point-of-sale system for a grocery store, the units of sale are captured and stored at the level of units of a product per transaction at the check-out counter. In an order entry system, the quantity ordered is captured and stored at the level of units of a product per order received from the customer. Whenever you need summary data, you add up the individual transactions. If you are looking for units of a product ordered this month, you read all the orders entered for the entire month for that product and add up. You do not usually keep summary data in an operational system.

When a user queries the data warehouse for analysis, he or she usually starts by looking at summary data. The user may start with total sale units of a product in an entire region. Then the user may want to look at the breakdown by states in the region. The next step may be the examination of sale units by the next level of individual stores. Frequently, the analysis begins at a high level and moves down to lower levels of detail.

In a data warehouse, therefore, you find it efficient to keep data summarized at different levels. Depending on the query, you can then go to the particular level of detail and satisfy the query. Data granularity, the last features of data warehouse, in a data warehouse refers to the level of detail. The lower the level of detail, the finer the data granularity. Of course, if you want to keep data in the lowest level of detail, you have to store a lot of data in the data warehouse. You will have to decide on the granularity levels based on the data types and the expected system performance for queries.

Definition of OLAP

OLAP : System used to change data stored in data warehouse transform the data to multi dimensional structure or cube.

Type of OLAP

• Relational OLAP: Extended RDBMS with multidimensional data mapping to standard relational operation.
• Multidimensional OLAP: Implemented operation in multidimensional data.
• Hybrid OLAP: Could use different partition that will be used.

OLAP Data Warehouse Query

• Roll-up : display data that increase in aggregation level
• Drill-down : display details using query for dimension table hierarchy
• Pivot : makes cross tabulation
• Slice and dice: Makes range selection on one or more dimension.

Definition of data warehouse

• Data Warehouse and OLAP are basic element in decision support. Especially for companies that uses large complex database.
• Data Warehouse helps manager, executive and analyst for making decision faster and easier.
• Datawarehouse is gathered data from various sources and stored in large capacity repository.
• Data warehouse let users to check data history, analyze data and takes decision based on analysis given.

Benefit of data warehouse

• Ability to access and analyze large complex data.
• Ability to had consistent data.
• Faster analysis on data.
• Able to find company redundancy.
• Finding gap in business process and business knowledge.
• Minimize administration process.
• Increase productivity by giving access for employee access and analyze data

Characteristics of data warehouse

• Subject Oriented: Data warehouse focus on high-level business entity.
• Integrated : Data stored in consistent format (naming convention, domain constraint, physical attributes)
• Time variant: Data associated with time.
• Non-volatile: Data could not change with read only permission.

Design a datawarehouse

• Design data warehouse architecture, capacity planning, picking storage server, server OLAP, database, and required tools.
• Integrate server, storage and client tools.
• Design data warehouse scheme and views.
• Manage physical database, data placement, partition, accessing method
• Connect source using gateway, ODBC driver, etc
• Design and implement script for data extraction, cleaning, transform, load and refresh
• Integrate repository with schema and view, script and metadata
• Design end user application

Differences with Database Table

• Data warehouse is stable storage
• Data warehouse not always relational, but also multidimensional.
• Data warehouse provide integrated and temporal data in large more than one database

Data Model

• Star scheme that is used widely in OLAP
• Forms multi dimensional data compatible for business needs.

Star Scheme

• The center is fact table.
• Fact table consist of main indicator of Key Performance Indicator.
• Tabel around fact table is dimension table
• Each dimension table relates with fact table based on primary key
• Star scheme implemented using relational database.

A simple example of data mining technique is the nearest neighbor prediction algorithm is that if you look at the people in your neighborhood (in this case those people that are in fact geographically near to you). You may notice that, in general, you all have somewhat similar incomes. Thus if your neighbor has an income greater than $100,000 chances are good that you too have a high income. Certainly the chances that you have a high income are greater when all of your neighbors have incomes over $100,000 than if all of your neighbors have incomes of $20,000. Within your neighborhood there may still be a wide variety of incomes possible among even your “closest” neighbors but if you had to predict someone’s income based on only knowing their neighbors you’re best chance of being right would be to predict the incomes of the neighbors who live closest to the unknown person.

The nearest neighbor prediction algorithm works in very much the same way except that “nearness” in a database may consist of a variety of factors not just where the person lives. It may, for instance, be far more important to know which school someone attended and what degree they attained when predicting income. The better definition of “near” might in fact be other people that you graduated from college with rather than the people that you live next to.

Nearest Neighbor techniques are among the easiest to use and understand because they work in a way similar to the way that people think – by detecting closely matching examples. They also perform quite well in terms of automation, as many of the algorithms are robust with respect to dirty data and missing data. Lastly they are particularly adept at performing complex ROI calculations because the predictions are made at a local level where business simulations could be performed in order to optimize ROI. As they enjoy similar levels of accuracy compared to other data mining techniques the measures of accuracy such as lift are as good as from any other.

Data Mining had a lot of benefit to any kind of business. The following list just a few sample of benefits of data mining that happens in real-world situations:

1. In a large company manufacturing consumer goods, the shipping department regularly short-ships orders and hides the variations between the purchase orders and the freight bills. Data mining detects the criminal behavior by uncovering patterns of orders and premature inventory reductions.
2. A mail order company improves direct mail promotions to prospects through more targeted campaigns.
3. A supermarket chain improves earnings by rearranging the shelves based on discovery of affinities of products that sell together.
4. An airlines company increases sales to business travelers by discovering traveling patterns of frequent flyers.
5. A department store hikes the sales in specialty departments by anticipating sudden surges in demand.
6. A national health insurance provider saves large amounts of money by detecting fraudulent claims.
7. A major banking corporation with investment and financial services increases the leverage of direct marketing campaigns. Predictive modeling algorithms uncover clusters of customers with high lifetime values.
8. A manufacturer of diesel engines increases sales by forecasting sales of engines based on patterns discovered from historical data of truck registrations.
9. A major bank prevents loss by detecting early warning signs for attrition in its checking account business.
10. A catalog sales company doubles its holiday sales from the previous year by predicting which customers would use the holiday catalog.

Problems that needs Data Mining solution:

We are planning to launch new product to market, what is the best product that our customer needs?

(Marketing Team)

We need a new loyalty program, what is the suiatble loyalty program today?

(CRM Team)

We couldn’t have any more problem in our network for this year, what is the best prevention actions?

(Network Team)

We need to adjust the health benefit for our employee, what is the best scheme?

(HRD Team)

Definition of Data Mining and Related Stuff

Data mining: Process to transform bulk data into information through pattern extraction

Data Warehouse: How to store huge amount of data that related to the business

Business Intelligence: How to present the data in data warehouse for business report

Techniques used in Data Mining

1. Statistics
2. Nearest Neighbor
3. Clustering
4. Decision Tree
5. Neural Network
6. Rule Induction

The Most Comprehensive BI from the Worldwide Leader in Business Analytics Oracle Business Intelligence (BI) is the most comprehensive portfolio of technology and applications for enabling the insight-driven organization, including leading BI applications, BI platform technology, and data warehousing. With the #1 market share in business analytics, Oracle BI enables organizations to gain complete and timely insight, distribute intelligence pervasively, and drive more effective actions and processes.

Oracle BI is hot-pluggable with Oracle and non-Oracle environments so customers can fully leverage existing data sources, systems and applications. At the same time, by offering comprehensive, integrated solutions that are optimized and pre-integrated with Oracle database, middleware, and applications, organizations can benefit from reduced cost and complexity and a unified BI infrastructure.

Corporate Performance Management and Daily Business Intelligence
The Oracle E-Business Suite Corporate Performance Management (CPM) and Daily Business Intelligence (DBI) applications enable organizations to achieve world-class performance by aligning the right information and resources to strategic objectives. Oracle Corporate Performance Management helps managers formulate strategies for profitable growth, align strategies with operational plans, actively monitor day-to-day operations, and collaborate across the enterprise. A unified data model provides a single, accurate view of enterprise-wide information, promoting transparency, actionable analysis, and rapid execution. And when Oracle’s Corporate Performance Management and DBI applications run on Oracle technology, you speed implementation, optimize performance, streamline support – and maximize ROI.

Why consider e-business suite corporate performance management and dbi?

1. Oracle DBI has up to 80% Lower TCO than SAP 1
2. Oracle continues to grow the BPM applications segment of the business as defined by IDC and is achieving traction across the BPM suite with innovative approaches to both consolidation and planning/budgeting, particularly with recent releases incorporating maturing market requirements.
3. We met our budget targets shortly after implementing Oracle Enterprise Planning and Budgeting. The software helped us standardize processes and made budget management easier and more flexible. It is a strategic business management tool.” China Eastern Airlines
4. Daily Business Intelligence provides a depth of operational reporting and business intelligence that is not otherwise available even if you implement a data warehouse .If you are already an Oracle user then you should be looking at Daily Business Intelligence. If you are not an Oracle user but are considering an ERP system, then the inclusion of Daily Business Intelligence at what will be a fraction of the cost of other systems, should play a significant part in your decision about your eventual provider.

By Patricia Stevens

To use a Sybase server, you are should have Netscape Enterprise Server. You cannot get an access to Sybase from Netscape FastTrack Server. Sybase has both one-line, and multiline drivers on some Unix-platforms. If Sybase has the multiline driver for the concrete Unix-machine, you are obliged to use LiveWire for this multiline driver. On these platforms your web-server will be unpredictable with the one-line driver. This requirement is applied both for local, and for the removed connections. It is not applied to Windows-platforms.

Company Sybase, which is a leading supplier of the software, and company Quest Software, a leading supplier of databases and decisions for data management have presented joint decisions Sybase Database Expert Option and Sybase SQL Expert Option for Sybase Adaptive Server Enterprise (ASE). The decisions, which Sybase Database Expert and Sybase SQL Expert provide, help for an optimum productivity and migration of applied systems on Sybase Adaptive Server Enterprise.

The databases created by SYBASE SQL Anywhere, represent relational databases. These databases consist of a set of objects. Such objects are data tables, keys, indexes, views, etc. Data tables store data, making the basic maintenance of a database. Keys are sets of the attributes forming keys (primary and external), intended for realization of the accelerated search of data and maintenance of reference integrity restrictions. Indexes are special tables intended for fast search of the demanded information in data tables. Views are connected sets of subsets of data tables, given to the users for restriction of their access to data tables. Thus access is absolutely forbidden to one table, and to other tables access is authorized only to some records of these tables.

The language SQL – Watcom SQL is used in SYBASE SQL Anywhere. It corresponds to standards of ANSI SQL/89 Level 2. Besides, the used language SQL supports new opportunities and expansions of ANSI SQL/92 standards.

Triggers are the subroutines, which become more active at approach of certain events, for example, at removal of the record from the table, at records updating, etc. Triggers are powerful means for maintenance of data integrity.

The user types of data are data types created by the user on the base data types. System tables store the whole information about the scheme of database and objects containing in it.

Sybase Mirror Activator is a new program Sybase decision, allowing uniting all the best of the technologies of synchronous mirroring disks and replication transactions of database. Sybase Mirror Activator is used as an addition to already existing system of disk mirroring. Novelty of the decision is that by means of disk mirroring on a reserve platform not the whole database is copied, but only its magazine of transactions that considerably reduces volume of the traffic.

Thus as disk mirroring is carried out in a synchronous mode, 100 percents reliability of data transmission are kept down to the last transaction. One more important advantage of the decision is that Mirror Activator enables reserve disk to be accessible in a mode “for reading”, which allows using it, for example, for systems of reports preparation, analytical systems, systems of decision-making, etc.

Data Warehouse

Data warehousing helps to provide information on the techniques involved in designing, building, maintaining and retrieving information, from a data warehouse. A data warehouse is premeditated and produced to support the decision-making process in an organization. The data that is obtained from the production databases are copied in the data warehouse, so that queries can be answered, without hindering the consistency of the production systems.

Data warehousing includes a set of important, new concepts and tools that have evolved into a technology. This makes it possible to counter the problems involved in providing all the key information, to the concerned people.

This field has evolved from the incorporation of a number of experiences and technologies, over the last two decades. Data warehousing is a well-organized and resourceful method of managing and reporting data from a variety of sources, non-uniform and scattered, throughout the company.

Data warehouses are vast, due to the hundreds of gigabytes of transactions. As a result, subsets, known as ‘data marts,’ are often designed for just one department or product line.

The data warehouse system serves as an influential and necessary platform, to merge the data from the old and new applications. Rules can also be transferred to the data warehouse, with very little effort. The most important and noteworthy features of a data warehouse is that it collects, records, filters and provides the basic data, to other organizational systems, at various higher levels.

Essentially, data warehouses are programmed to perform the summarization and pre-defining of the analysis, to automatically generate summary reviews. Data warehousing helps to analyze information for the users. This system is very useful for providing collective information to the users. Data warehousing systems have been created to support a variety of analysis, including elaborate queries on large amounts of data that require extensive searching.

What is ETL?

ETL is the process for Extracting, Transforming and Loading data from one database to another.

There are several ways for doing this, from coding your own processes to the more often used way of implementing ETL tools.

These data warehouse ETL tools can do the job very well, and if chosen wisely can save you a lot of coding efforts and money, since you can graphically build processes and in most cases without knowing how to program for databases.

There are a lot of ETL tools in the market right now. As and advice, I suggest you to invest some research time (and testing if possible) before choosing the one that suits your company’s needs.
They can range from open source free tools to high price commercial tools. Neither of them is perfect in every situation, and you will have to take into account your data volumes, the analysis and answers you want from your datawarehouse, and the periodicity needed of those answers, among other aspects.

By Tony Jacowski

The primary reason that corporations introduce Six Sigma into data warehousing boils down to cost reduction. Large corporations are incurring huge expenditures, most of the times running into millions of dollars, which eats into stakeholders margin, in creating and maintaining data warehouses. The criticality of data warehouses can be understood by their vital role in support to prediction of business performance.

There is no denying the fact that data warehousing is in a way, the powerhouse of Six Sigma deployment. In early stages of projects, data warehousing allows for better planning of deployment, design and tuning of the production environment.

Data Warehousing Basics

Data warehousing components are complex in nature and are multifaceted. The various components are either developed in house or by a third party or in joint development at the partys place of business. Typically, designers focus on functional and business needs and not on performance constraints faced by the production environment. The consequence of this costly mistake is the possibility of missing deadlines and reworking the project, which are manifestations of operational inefficiencies.

Challenges to Data Warehouse Design

It is not new that modern day data warehouses are built for auto refreshing and/or compatible for at least real time updating. ETL, as extraction, transformation and loading of data flow is a very resource-consuming exercise in data warehousing. The importance of data warehousing increases several times, considering the fact that data structures are both strategic and functional.

Even the real time refreshing of data becomes a daunting task with the refresh window getting clogged straining server resources. Then there are some other factors that have a play in affecting the performance of ETL.

Meeting the Challenge to Quantify the Data Warehouse Effect

Quantifying the effects of data warehouse is to project whether challenges can be scaled. The recent trend in data warehouse development is to treat them as belonging to the same family or group. Consider dedicating each family to a particular geographical location, and other subsets of respective hierarchical data. Warehousing modules for individual data groups (families) are developed at their initial stages and new ones are taken care off as and when they arise and are just plugged into the main data warehouse. The database could contain three fundamental tables such as tables to store attributes of data; storage of linking information; and finally, aggregated data ready for use.

Applying Six Sigma Elements into Software Development

Applying Six Sigma elements into software development typically helps in identifying potential problems in production if the development is done in the early stages of the project. Secondly, the mammoth task of data warehousing can return positive results if deployment plans are fine tuned before implementation.

The self-assessing nature and the provisions for internal auditing shed light on the course of implementation. At the same time, one cannot forget that databases developed remain tied to the system architecture on which they are built and bear heavily on the accuracy of predictions in a fluctuating business environment, ironically for which they are built.

SAP (Photo credit: Kenn Wilson)

SAP BW is a continuous SAP Data Warehouse Solution that uses former SAP technologies. This SAP BW is built on the Basis of 3-tier architecture and coded in the ABAP (Advanced Business Application Programming) language. This 3-tier architecture and code language uses ALE (Application Link Enabling) and BAPI (Business Application Programming Interface) to link BW with SAP systems and non-SAP systems.

The SAP Data Warehouse (BW) has three layers in it. The top layer is the reporting layer. This top layer may be BW Business Explorer (BEx) or a third-party reporting device. This BEx consists of two components: one is BEx Analyzer and other is BEx Browser.

BW Server is a middle layer that carries out three tasks: it administrates the BW system, stores data and retrieves the data. In The bottom layer it consists of source systems, which may be R/3 systems, BW systems, flat files, and other systems. In the source systems a SAP component called Plug-In must be installed. It contains extractors. An extractor is a set of ABAP programs, database tables, and other objects that BW uses, which helps to extract data from the SAP systems. This BW Server contain Administrator Workbench, Metadata Repository and Metadata Manager, Staging Engine, PSA, ODS and User Roles.

• This Administrator Workbench checks metadata and all BW objects. It has two components: one is BW Scheduler and other is BW Monitor. This component helps to load data and to monitor the data.

• This Metadata Repository contains information relating to SAP data warehouse. Metadata Repository contains two types one is business-related and other is technical. Metadata Manager is used to maintain Metadata Repository.

• PSA (Persistent Staging Area) is also a BW server. This PSA stores data in the original format while being imported from the source system. It ensures quality check of data before they are loaded in their destinations, such as ODS Objects or Info Cubes.

• This ODS (Operational Data Store) Objects helps to build a multilayer structure for operational data reporting. It is used for detail reporting.

• Info Cubes is an actual table and they are the associated dimension tables in a star schema.

• The OLAP Processor is the analytical processing engine. It analyzes and retrieves data as per users’ requests.

• Documents are stored in BDS (Business Document Services). The documents can appear in different formats like Microsoft Word, Excel, PowerPoint, PDF, and HTML.

SAP BW Business Content

The BW’s most powerful selling is Business Content. It contains standard reports and other associated objects. For standard reports, BW use a function called Generic Data Extraction. This function is used to extract R/3 data.

Nowadays, BW is rapidly evolving. It helps to plan SAP Data Warehouse projects and their scopes.

This sap e-business consists of three components: they are my SAP Technology, my SAP Services and my SAP Hosted Solutions.

• MySAP Technology provides an infrastructure for Web Application Server and for process-centric collaboration. This infrastructure contains a component called mySAP Business Intelligence.

• Another type of services called mySAP Services are the best services which support SAP offers to the customers. They offer for business analysis, technology implementation, and training to system support.

• This mySAP Hosted Solutions are the outsourcing services of SAP. With the help of this solution, customers need not want to maintain physical machines and networks.

By Clive Margolis

Business Intelligence (also known as BI) is big business. In a recent report market analyst Datamonitor predicts business intelligence spend by retail banking in North America, Europe, the Middle East and Asia-Pacific, will increase around 60.7%, from $5.6 billion in 2006 to $9 billion by 2012.

So what is business intelligence, and why would you even need it?
As is typical in the IT industry ‘Business Intelligence’ is means something slightly different to everyone. So I have come up with my own definition of BI. Here it is:

Business Intelligence is a system which enables organisations to collect, analyse and present business information to support business decisions.

Business Intelligence
BI is not a collection of diverse tools you put together and ‘hope for the best’. If you want to get good results you need to plan from the start. Don’t be dazzled by the impressive offerings of the software giants. Have a clear picture from the start of what you want to get from BI, and how you plan to get there, and your chances of getting real value from Business Intelligence multiply greatly.

One thing that differentiates BI systems from traditional systems like inventory, distribution and finance systems is flexibility – the ability to add measures and outputs as your organisation and its use of BI develop. But this flexibility does not excuse you from the planning stages – in fact it increases the need for planning.

Unlike many traditional computer systems, a single BI system can provide value to all departments within your organisation, but as with any system it is important not to expect delivery on everything at the same time. Build steadily, working down your priority list.

Data Collection
The first question you might ask is “why do I have to collect information I already have in my database”? The answer is, you probably don’t have it already. You might have some of the information you need, and even then it is probably not in the exact form you need it. Much of the other information you need is probably on spreadsheets on various peoples’ desks, or doesn’t exist at all and has to be collected.

Even if you have all the information you need already (which is unlikely) it is a good idea for the BI system to store it somewhere else. That way the data can be organised and aggregated to make it work quickly and efficiently in a BI system. Often you need to add history to your BI database, which may not be kept in your existing transactional systems.

The most popular way to collect the data is in a specially-designed data warehouse. It takes time and skill to develop a good data warehouse, but in most cases it is vital to an effective BI implementation. A good data warehouse need not be a huge, complex beast – the simpler the design, the lower the cost, and the more chance of success.

A good data warehouse design can be easily extended to allow for unforeseen business reporting requirements.

Using BI
Generally speaking, data is best suited to BI reporting when it is

1. summarised, and
2. organised in hierarchies

In large organisations with large amounts of data this number-crunching process can require millions of calculations and is often carried out overnight on a daily basis. Calculations, sometimes between quite diversely held data elements, allow you to create specific ‘key performance indicators’ (KPIs) such as Profit per Customer and Revenue per Employee.

Data held in multidimensional structures known as cubes contain this hierarchical, summarised information which allows managers to analyse KPIs at any level of the organisation – giving them the ability to see, for example, Revenue per Employee at National, Regional and Area levels, by month or summarised at the year level.

Business Information
Presentation of information is a key issue, and should be considered with the nature of the data and also the recipient in mind. Presentation methods in BI are constantly evolving and include:

• Online and printed reports and queries
• Graphs
• Multidimensional cubes
• Dashboards
• Scorecards

Mostly delivery is online but cubes can – depending on the software package used to create them – be taken offline and analysed on a non-networked laptop, for example. Recent features such as email alerts can be vitally important where metrics change rapidly and quick action must be taken to remedy them.

Support Business Decisions
The most common reason for collecting, analysing and presenting KPIs and metrics is to monitor and improve the performance of your organisation. In economic terms you need to get more from your BI system than you put in (ie the benefits should outweigh the costs). If this is not the case you can usually improve the balance by making more use of it, which often means adding more KPIs and users.

Summary
Business Intelligence systems maximise data use by collating the data into useful metrics and KPIs and presenting them effectively. How effective your Business Intelligence system becomes is related to how well it was planned and implemented. A well-implemented BI system can cut costs, improve productivity and make an organisation more competitive. An effective BI strategy is vital to success.

The object-relational database management system known as PostgreSQL is derived from the POSTGRES package written at the University of California at Berkeley. With almost three decades of development behind it, PostgreSQL is now the most advanced open-source database available.
Greenplum Database is built upon the PostgreSQL 8.2.5 code base and has many similarities to PostgreSQL. For example, many of the client and server applications, configuration files, supported SQL commands, and syntax will be the same or very similar to PostgreSQL.
Greenplum Database is essentially several PostgreSQL instances acting as one cohesive database management system. The internals of PostgreSQL have been modified or supplemented to support the parallel structure of Greenplum Database. For example the system catalog has been supplemented to track all of the segment instances that comprise a Greenplum database. The query parser, query planner, query optimizer, and query executor processes have been modified and enhanced to be able to execute queries in parallel across all of the segments.
Data Query and Manipulation Language (DQL/DML) is essentially supported as it is in PostgreSQL. SELECT, INSERT, UPDATE, and DELETE are DQL/DML commands. All other SQL commands are considered Data Definition Language (DDL) or utility commands. Most DDL and utility SQL statements are supported in Greenplum Database as they are in PostgreSQL, with a few minor exceptions. See “SQL Support” on page 24 for more information.