Should You Use An ETL Tool?

The Extract, Transformation, and Load (ETL) system is the most time-consuming and expensive part of building a data warehouse and delivering business intelligence to your user community. A decade ago the majority of ETL systems were hand crafted, but the market for ETL software has steadily grown and the majority of practitioners now use ETL tools in place of hand-coded systems.

Does it make sense to hand-code an ETL system in 2008, or is an ETL tool a better choice? Kimball Group now generally recommends using an ETL tool, but a custom-built approach can still makes sense. This article summarizes the advantages and disadvantages of ETL tools and offers advice on making the choice that's right for you.

ADVANTAGES OF ETL TOOLS

Visual flow and self-documentation.The single greatest advantage of an ETL tool is that it provides a visual flow of the system's logic. Each ETL tool presents these flows differently, but even the least-appealing of these user interfaces compare favorably to custom systems consisting of stored procedures, SQL and operating system scripts, and a handful of other technologies. Ironically, some ETL tools have no practical way to print the otherwise-attractive self documentation.

    Learn about a predictive analytics solution that provides forward-looking insights, and offers best practices to ensure you maximize the value of your company's customer relationships
    Best Practices for Collaboration in the Enterprise

Structured system design. ETL tools are designed for the specific problem of populating a data warehouse. Although they are only tools, they do provide a metadata-driven structure to the development team. This is particularly valuable for teams building their first ETL system.

Operational resilience. Many of the home-grown ETL systems I've evaluated are fragile: they have too many operational problems. ETL tools provide functionality and practices for operating and monitoring the ETL system in production. You can certainly design and build a well instrumented hand-coded ETL application, and ETL tool operational features have yet to mature. Nonetheless, it's easier for a data warehouse / business intelligence team to build on the management features of an ETL tool to build a resilient ETL system.

Data-lineage and data-dependency functionality. We would like to be able to right-click on a number in a report and see exactly how it was calculated, where the data was stored in the data warehouse, how it was transformed, when the data was most recently refreshed, and what source system or systems underlay the numbers. Dependency is the flip side of lineage: we'd like to look at a table or column in the source system and know which ETL modules, data warehouse tables, OLAP cubes, and user reports might be affected by a structural change. In the absence of ETL standards that hand-coded systems could conform to, we must rely on ETL tool vendors to supply this functionality — though, unfortunately, few have done so to date.

Advanced data cleansing functionality. Most ETL systems are structurally complex, with many sources and targets. At the same time, requirements for transformation are often fairly simple, consisting primarily of lookups and substitutions. If you have a complex transformation requirement, for example if you need to de-duplicate your customer list, you should use a specialized tool. Most ETL tools either offer advanced cleansing and de-duplication modules (usually for a substantial additional price) or they integrate smoothly with other specialized tools. At the very least, ETL tools provide a richer set of cleansing functions than are available in SQL.

Performance. You might be surprised that performance is listed last under the advantages of the ETL tools. It's possible to build a high-performance ETL system whether you use a tool or not. It's also possible to build an absolute dog of an ETL system whether you use a tool or not. I've never been able to test whether an excellent hand-coded ETL system outperforms an excellent tool-based ETL system; I believe the answer is that it's situational. But the structure imposed by an ETL tool makes it easier for an inexperienced ETL developer to build a quality system.

Software licensing cost.The greatest disadvantage of ETL tools in comparison to hand-crafted systems is the licensing cost for the ETL tool software. Costs vary widely in the ETL space, from several thousand dollars to hundreds of thousands of dollars.

Uncertainty. We've spoken with many ETL teams that are uncertain – and sometimes misinformed – about what an ETL tool will do for them. Some teams under-value ETL tools, believing they are simply a visual way to connect SQL scripts together. Other teams unrealistically over-value ETL tools, imagining that building the ETL system with such a tool will be more like installing and configuring software than developing an application.

Reduced flexibility. A tool-based approach limits you to the tool vendor's abilities and scripting languages. Build a Solid Foundation

    Learn about a predictive analytics solution that provides forward-looking insights, and offers best practices to ensure you maximize the value of your company's customer relationships
    Best Practices for Collaboration in the Enterprise

There are some over-arching themes in successful ETL system deployments regardless of which tools and technologies are used. Most important — and most frequently neglected — is the practice of designing the ETL system before development begins. Too often we see systems that just evolved without any initial planning. These systems are inefficient and slow, they break down all the time, and they're unmanageable. The data warehouse team has no idea how to pinpoint the bottlenecks and problem areas of the system. A solid system design should incorporate the concepts described in detail in Kimball University: The Subsystems of ETL Revisited, by Bob Becker.

Good ETL system architects will design standard solutions to common problems such as surrogate key assignment. Excellent ETL systems will implement these standard solutions most of the time but offer enough flexibility to deviate from those standards where necessary. There are usually half a dozen ways to solve any ETL problem, and each one may be the best solution in a specific set of circumstances. Depending on your personality and fondness for solving puzzles, this can be either a blessing or a curse.

One of the rules you should try to follow is to write data as seldom as possible during the ETL process. Writing data, especially to the relational database, is one of the most expensive tasks that the ETL system performs. ETL tools contain functionality to operate on data in memory and guide the developer along a path to minimize database writes until the data is clean and ready to go into the data warehouse table. However, the relational engine is excellent at some tasks, particularly joining related data. There are times when it is more efficient to write data to a table, even index it, and let the relational engine perform a join than it is to use the ETL tool's lookup or merge operators. We usually want to use those operators, but don't overlook the powerful relational database when trying to solve a thorny performance problem.

Whether your ETL system is hand-coded or tool-based, it's your job to design the system for manageability, auditability, and restartability. Your ETL system should tag all rows in the data warehouse with some kind of batch identifier or audit key that describes exactly which process loaded the data. Your ETL system should log information about its operations, so your team can always know exactly where the process is now and how long each step is expected to take. You should build and test procedures for backing out a load, and, ideally, the system should roll back transactions in the event of a midstream failure. The best systems monitor data health during extraction and transformation, and they either improve the data or issue alerts if data quality is substandard. ETL tools can help you with the implementation of these features, but the design is up to you and your team.

Should you use an ETL tool? Yes. Do you have to use an ETL tool? No. For teams building their first or second ETL system, the main advantage of visual tools are self-documentation and a structured development path. For neophytes, these advantages are worth the cost of the tool. If you're a seasoned expert — perhaps a consultant who has built dozens of ETL systems by hand — it's tempting to stick to what has worked well in the past. With this level of expertise, you can probably build a system that performs as well, operates as smoothly, and perhaps costs less to develop than a tool-based ETL system. But many seasoned experts are consultants, so you should think objectively about how maintainable and extensible a hand-crafted ETL system might be once the consultant has moved on.

Don't expect to reap a positive return on investment in an ETL tool during the development of your first system. The advantages will come as that first phase moves into operation, as it's modified over time, and as your data warehouse grows with the addition of new business process models and associated ETL systems.

SOURCE:http://www.informationweek.com/news/software/bi/showArticle.jhtml?articleID=207002081&pgno=3&queryText=&isPrev=

k-means Clustering

k-means clustering is a data mining/machine learning algorithm used to cluster observations into groups of related observations without any prior knowledge of those relationships. The k-means algorithm is one of the simplest clustering techniques and it is commonly used in medical imaging, biometrics and related fields.
The k-means Algorithm
The k-means algorithm is an evolutionary algorithm that gains its name from its method of operation. The algorithm clusters observations into k groups, where k is provided as an input parameter. It then assigns each observation to clusters based upon the observation’s proximity to the mean of the cluster. The cluster’s mean is then recomputed and the process begins again. Here’s how the algorithm works:

   1. The algorithm arbitrarily selects k points as the initial cluster centers (“means”).
   2. Each point in the dataset is assigned to the closed cluster, based upon the Euclidean distance between each point and each cluster center.
   3. Each cluster center is recomputed as the average of the points in that cluster.
   4. Steps 2 and 3 repeat until the clusters converge. Convergence may be defined differently depending upon the implementation, but it normally means that either no observations change clusters when steps 2 and 3 are repeated or that the changes do not make a material difference in the definition of the clusters.

Choosing the Number of Clusters
One of the main disadvantages to k-means is the fact that you must specify the number of clusters as an input to the algorithm. As designed, the algorithm is not capable of determining the appropriate number of clusters and depends upon the user to identify this in advance. For example, if you had a group of people that were easily clustered based upon gender, calling the k-means algorithm with k=3 would force the people into three clusters, when k=2 would provide a more natural fit. Similarly, if a group of individuals were easily clustered based upon home state and you called the k-means algorithm with k=20, the results might be too generalized to be effective.

SOURCE:http://databases.about.com/od/datamining/a/kmeans.htm

Kimball University: Industry Standard Data Models Fall Short

Industry-standard data models are an appealing concept at first blush, but they aren't the time savers they are cracked up to be. What's more, these prebuilt models may inhibit data warehouse project success.

Vendors and proponents argue that standard, prebuilt models allow for more rapid, less risky implementations by reducing the scope of the data model design effort.

Every manufacturer takes orders and ships products to fulfill the orders. Every insurance company sells policies and processes claims. Every transportation company moves cargo between an origin and a destination. The list goes on across other industries.

Why bother "recreating the wheel" by designing custom data models to support these common business processes when you can buy an industry-standard model instead?

    Discover how a high-performing, secure, and scalable commercial solution delivers virtualization functionality with the management tools organizations need -- at a fraction of the cost of competing virtualization solutions
    Accelerate The deployment of Virtualization in your Environment

Yes, most businesses in a given industry perform common functions. But if everyone's approach to these business functions was so similar, then why are there so many alternative organizations? Don't most organizations do things slightly differently than their industry peers? And if so, how do these "competitive advantages" get addressed by a pre-defined industry model?

True business intelligence requires the injection of an organization's own intellectual capital. Would you really want to use the identical industry solution as your peers?

In virtually every data warehouse design and delivery project, the vocabulary used by the operational source system's data model needs to be translated into business vernacular. Some might argue that the source system speaks "geek" rather than Greek. Embracing an industry-standard model introduces the need for yet another pocket dictionary.

First, the data in the source system's language needs to be translated and transformed into the industry model's generic language. This is no small feat; while some data will translate without too much compromise, other data will need to be wrestled and coerced into the pre-defined model and invariably some source data just won't fit.

Once the source has been manipulated into the model's supposedly universal language, the data then needs to go through a second translation so that the vocabulary used in the final presentation layer makes sense to the business users. The challenges surrounding these multiple transformations, and the opportunity to lose something in the translations between three languages -- source system, industry model, and business usage -- are extensive but often overlooked when considering a standardized model.

Of course, the transformation effort is less onerous if the source data capture system and industry model are supplied by the same vendor. But there are still some sharp corners lurking even in this scenario. First, you'll need to incorporate any custom extensions or flex field data elements from your implementation into the vendor's generic model. Secondly, you'll need to worry about the integration of any source data that's outside the vendor's domain.

Can you readily conform the industry model's dimensions with other internally available master data? If not, the industry model is destined to become another isolated stovepipe dataset. Clearly, this outcome is unappealing, but it may be less of an issue if all your operational systems are supported by the same ERP vendor or you’re a very small organization without an IT shop doing independent development.

What can you realistically expect to gain from an industry-standard model? A pre-built generic model can help identify core business processes and associated common dimensions for a given industry. That provides some comfort for data warehouse project teams feeling initially overwhelmed by design tasks.

However, is this knowledge worth six figures? Alternatively, you could likely gain this same insight by spending a few weeks with the business users; you'd not only improve your understanding of the business's needs, but you'd also begin "bonding" business users to the DW/BI initiative.

Ultimately, the business's ownership and stewardship of the effort are critical to its long term success. Even if you buy an industry-standard model, you're still going to need to spend time with the business to understand the final translations required to address their requirements. There's no dodging this necessary step. In Kimball Group's experience, after a few days or weeks studying the standard model, most teams typically gain enough confidence to want to customize the schema extensively for "their data."

It's also worth mentioning that just because you spend thousands of dollars on a standard model doesn't mean it exhibits generally-accepted dimensional modeling best practices. Unfortunately, some pre-built models embody common dimensional modeling design flaws; this isn't surprising if the model's designers have focused more on best practices for source system data capture rather than those required for business reporting and analytics.

SOURCE:http://www.informationweek.com/news/showArticle.jhtml?articleID=227400287&pgno=2&queryText=&isPrev=

Extending Data Integration to the Cloud

Cloud computing has become the latest computing platform, offering substantial benefits to both business and IT. There are three main types of cloud computing technology:

   1. Software as a Service (SaaS) provides business users with cost-effective, multitenant solutions that are easy to provision, easy to manage, and easy to use.
   2. Platform as a Service (PaaS) allows developers and IT to build custom applications and run them in the cloud.
   3. Infrastructure as a Service (IaaS) provides IT with an economical infrastructure that offers virtually unlimited server and storage resources that can be provisioned as and when required, on a "pay as you go" or standard licensing approach

The rapid adoption of these cloud solutions has resulted in more fragmented data and the need to integrate data “in the cloud” with data in on-premise applications and databases. Some common uses cases for cloud data integration include:

    * Back office synchronization. As more front office applications (e.g., sales force automation, customer service, human resources) are deployed in the cloud, your organization needs to synchronize the data within them with back office applications (e.g., general ledger, accounts payable, payroll). This synchronization is critical to ensuring timely, relevant, and trustworthy information is delivered throughout your enterprise.
    * Customer master synchronization. Beyond the data that now resides in the cloud, your organization may still have financial data locked in Oracle applications and their customer master data in SAP. To break down these data silos and improve business processes, you need a single view of customer data. A cloud data integration solution can help create that single view, which can have positively impact on both customer satisfaction and your bottom line.
    * CRM integration. Your sales organization wants a single view the customer—orders in line with Account and Opportunity information. What about Cases, Leads, Campaigns? With more organizations adopting Salesforce CRM, the demand for self-service cloud-based data integration that can be managed by nontechnical business users is also growing. 
    * Data migration. As your organization modernizes its IT infrastructure by moving off legacy applications on premise to new cloud-based applications, you need to have confidence that you can easily migrate data to and from the cloud. Similarly, if your company or department has merged with another, or you’ve acquired a new entity, you need to migrate and merge data quickly to start recognizing return on your investment.
    * Data replication. Many companies are required to keep an on-premise copy of all cloud-based data for compliance, disaster recovery, or business intelligence reporting purposes.
    * Business / IT alignment. Line of business is administering the SaaS applications and IT wants to avoid data silos and untrustworthy point solutions. The right approach to cloud data integration can play a key role in aligning business users with IT.
    * Data Archiving. For regulatory compliance, organizations are required to retain data for a longer period, sometimes up to 10 years.  Inactive data from enterprise applications, such as Oracle E-Business Suite, PeopleSoft, Siebel, SAP, and other custom applications and databases can be archived to on-premise or cloud storage.
    *  Legacy application retirement. Many companies spend significant amount of their IT budget maintaining legacy applications which have depreciated in business value.  The data in these legacy applications can be retired to on-premise or cloud storage, and yet remain easily accessible for reporting or compliance audit purposes.

To gain full value from your enterprise data—whether it resides in the cloud or on premise—your organization must be able to access, integrate, and trust them. You need a comprehensive, unified, open, and economical cloud data integration solution.

SOURCE:http://www.informatica.com/solutions/on_demand/Pages/index.aspx

Build Your Business on a Scalable and Reliable Proven Data Aggregation Solution

As a data aggregator, data is your business—not a byproduct of your business. You buy data, transform it, scrub it, cleanse it, standardize it, match it, validate it, analyze it, statistically project it, and sell it. You need a rock-solid data aggregation solution as the foundation of your operations.
The ideal data aggregation solution should:

• Fully automate key steps of the data aggregation process so that your IT team can boost productivity, accelerate delivery times, and dramatically reduce costs. That automation lets your company focus on what it does best—your proprietary data sampling, data projecting, and data assembling core competencies that distinguish you from your competitors
• Transform any data, in any format, from any source so your company can create new products for new markets
• Detect data quality problems early so your company can synthesize high-quality marketing research, point-of-sale analysis, and other data products more quickly and at a lower cost

Informatica can help.
The Foundation for Data Aggregator Operations
With Informatica’s data aggregation solution, market research, market intelligence, advertising, and marketing services companies; credit rating and reporting agencies; and government organizations can rely on a single, scalable solution for their operations.
Unlike costly, resource-intensive, and error-prone hand coding or disparate point solutions, Informatica’s data aggregation solution provides all the tools necessary for data collection, validation, transformation, normalization, enrichment, mapping, and extraction in one powerful, easy-to-use environment.

What makes our solution indispensable to data aggregators is that its unique capabilities—universal data transformation, quality-assured data exchange, identity data resolution, data event management and monitoring, and partner and data source onboarding—are seamlessly integrated and available out of the box through a single, powerful environment that’s easy to use, maintain, and scale. No other solution on the market supplies a broader range of fully integrated data aggregation capabilities.
The Informatica solution has been proven in dozens of real-world deployments to help data aggregators:

• Deliver high-quality data products
• Boost productivity while dramatically reducing costs
• Accelerate time to market for new products and offerings
• Grow revenues by readily expanding into high-growth, emerging markets and adjacent segments

Based on the Industry-Leading Data Integration Platform
Informatica’s data aggregation solution is based on our industry-leading data integration platform. Highly scalable, reliable, and secure, the Informatica Platform enables you to access, discover, cleanse, integrate, and deliver timely, trusted data to the extended enterprise—any data, anywhere, at any time. It is designed to work with all systems and processes you have today or may add in the future.

New Features of PowerCenter 9.0.1

PowerCenter 9.0.1 delivers the full range of data integration capabilities to ensure that all data can be accessed, integrated, and delivered throughout the enterprise at any speed.
PowerCenter All Editions (Standard, Advanced, Real Time)

    * New upgrade wizard and installer
    * New unified administration console
    * Improvements to Mapping Analyst for Excel, including support for reusable rules and usability enhancements
    * Improvements to Mapping Architects for Visio, including support for PowerExchange data sources, transformation shortcuts and custom transformation editing
    * Enhanced support for lookup transformations including cache updates, database deadlock resilience, SQL overrides for uncached lookups and multiple rows return
    * Guaranteed exactly-once semantics extended to SQL transforms—if there is an interruption in processing a real-time message, PowerCenter guarantees once-only delivery and can recover without requiring the message to be sent again

PowerCenter Advanced Edition: Metadata Manager and Business Glossary

    * Improved impact analysis, with ability to view a list of the impacted objects
    * Approval workflow for business terms—create and edit draft business terms, propose the business terms for review by data stewards, and then publish the terms
    * Business terms change notifications to stakeholders—send emails to notify users to review draft business terms or new comments
    * Import/export business glossaries
    * Metadata audit trails for all user-definable metadata, with ability to view the history of changes to business glossary categories, business terms, and custom objects
    * Improvements to metadata bookmarks, including access to Metadata Manager and Business Glossary objects and features from external applications
    * Enhancements to metadata loading and linking processes, including automatic connection assignments, support for multiple PowerCenter parameter files, automated cross-resource linking, and resource connection testing
    * Support for Microsoft SSRS/SSAS metadata

PowerCenter Real Time Edition

    * Improved XML exception management—validate and route invalid XML to a separate output group
    * Dynamic lookup cache refresh—update the lookup cache based on the results of an expression
    * Session log file rotation—limit the size of session logs for real-time sessions by time or file size

9 Reasons to Upgrade to Informatica PowerCenter 9.0.1

   1. Support mission critical 24x7 operations
      Access, integrate and deliver your critical data whenever you need it with enhanced capabilities including high availability for PowerExchange and dynamic lookup cache for real-time data.

   2. Boost productivity with metadata management
      Handle changes more quickly and easily with Metadata Manager and Business Glossary’s optimized data lineage, impact analysis, approval workflow and change notifications.

   3. Reduce deployment and maintenance costs
      Simplify deployment and reduce management overhead with a new administration console, combining several existing consoles.

   4. Harden your real-time operations
      Take advantage of improvements to PowerCenter Real Time Edition including real-time change data capture enhancements, even greater Web services scalability, and guaranteed once-only message delivery.

   5. Minimize risk through data governance
      Implement data governance with Business Glossary, Metadata Manager, and integrated data quality support.

   6. Improve business and IT collaboration
      Empower developers, architects and analysts to collaborate more effectively and productively with role-based tools including Mapping Architect for Visio and Mapping Analyst for Excel to automatically generate mappings, and Reference Table Manager to simplify management of reference data.

   7. Improve mainframe access and performance
      Maximize your mainframe investments with access and performance enhancements including real-time change data capture, VSAM partitioning, and high availability.

   8. Bullet-proof your security
      Secure your data integration environment with centralized user and role administration, enhanced LDAP integration, and SSL remote access.

   9. Future-proof your deployment
      Rely on a secure, scalable, and stable product that will be supported for years to come.


SOURCE:http://www.informatica.com/products_services/powercenter/upgrade_center/Pages/index.aspx

Access, Integrate, and Deliver Data Quickly, Easily, and Cost-Effectively

Powerful market forces—globalization, new regulations, mergers and acquisitions, and business process outsourcing, for example—are pushing your company to operate more efficiently. Your business needs access to all its data faster than ever.
How does your IT organization respond? How does your IT organization access, integrate, and deliver critical data your business needs—when, where, and how it needs it—to help your company stay efficient, agile, and competitive?

Common Data Integration Challenges

    Rising complexity of data. Your IT organization is handling more data, in more formats, from more partners, and more systems than ever before. How do you provide a single, comprehensive view into your critical information assets? How do you handle the complexity of your enterprise data—its volume, its latency, its many formats and structures?

    Increasing business demands. Timely information fuels all your company’s competitive business initiatives, such as governance, risk, and compliance programs and mergers and acquisitions. How do you make data available when and how the business needs it—in batch, near real-time, and real-time modes—to increase operational efficiency?

    Shrinking IT budgets. Every business initiative spawns a new IT project. And each IT project requires data integration. How does your IT organization reuse data integration logic and skills across these projects to keep IT costs in check?
    Proliferating data quality issues. The business needs make decisions quickly, and it needs to base its decision making on reliable data. How does your IT organization proactively prevent questionable, inconsistent, or inaccurate data from proliferating throughout your business? How do you resolve and fix data quality issues at point of entry?



Informatica PowerCenter is highly scalable, highly available, high-performance software that lets IT organizations access and integrate data from virtually any business system, in any format, and deliver that data throughout the enterprise at any speed to improve operational efficiency. PowerCenter serves as the foundation for all data integration projects and enterprise integration initiatives, including:

    * B2B data exchange
    * Data governance
    * Data migration
    * Data synchronization and replication
    * Enterprise data warehousing
    * Integration Competency Centers (ICC)
    * Master data management (MDM)
    * Service-oriented architectures (SOA)

Business analysts, IT organizations, and global development teams all rely on PowerCenter to maximize the business value of enterprise data.

    Business Analysts. PowerCenter delivers trusted, timely data throughout the enterprise—in batch, in near real-time, and in real-time modes, or on demand—to meet the analytical and operational needs of the business. Comprehensive auditing and reporting capabilities enable business analysts to support governance, risk, and compliance initiatives.

    IT Management. PowerCenter delivers unparalleled performance, scalability, and high availability to support mission-critical, enterprise-wide data integration projects while reducing development time and costs.

    Developers/Global IT Teams. PowerCenter encourages collaboration between the business and IT and across global development teams. The platform allows teams to maintain, share, and reuse definitions and data mapping logic across platforms and projects. No recoding means higher productivity and faster time to results.

Understanding Database Basics - Part 1

Real-time data analysis and business intelligence is an essential in the modern business world to keep track of the vast amounts of information a business collects and to make the best use of it. Modern data analysis systems are highly complex, having the ability to cut and slice huge amounts of complex information and present it to the end user in a way that is both easy to understand and useful.
In fact, collecting this information, analyzing it and making business decisions based on this data is the key to success for many businesses. However, many end users do not understand the basics of even the simplest database, or how the information is organized or stored. So in this two-part article we look at the basics of databases and database management.
Basically, a database is a collection of organized information that is stored on a computer. This information can be about anything - people, flights, cars, you name it. A library catalogue is a typical example of a working database, and we will continue to use this as an example in these articles.

A simple relational database will contain three essential components - tables, records and fields. So, using the library example you may have a table that contains the information on every book in the library. This table, lets call it books, will look like any table with rows and columns.
Each row on this table is known as a record and it represents a unique item in the database, in this case a book. However, there will be a lot of information to be stored about each book so this will be contained in the columns or fields. Therefore, each row will have at least one field, or column. In this case, for each book the fields will include information like a book ID number, book title, author, publisher, publication date, whether it is checked out and so on.
However, there are more things to keeping a library catalog than storing information about books. You will also need a table containing the details of library members. In this case, each record will represent a member and the fields will contain details like membership number, name, address, and so on.
There will probably be more tables as well. There may be a book categories table (fiction, non-fiction, autobiographies, reference, etc), a book status table (checked in, checked out, reserved, missing, etc), and many more tables.
This is all essential information for a library. In the second part of this article we will look at how tables are linked to form a relational database, and how the end user can make sense of the huge amount of data stored in the database.

SOURCE:http://www.relativitycorp.com/data/article2.html

Cubes

What is an OLAP Cube? As you saw in the definition of OLAP, the key requirement is multidimensional. OLAP achieves the multidimensional functionality by using a structure called a cube. The OLAP cube provides the multidimensional way to look at the data. The cube is comparable to a table in a relational database.
The specific design of an OLAP cube ensures report optimization. The design of many databases is for online transaction processing and efficiency in data storage, whereas OLAP cube design is for efficiency in data retrieval. In other words, the storage of OLAP cube data is in such a way as to make easy and efficient reporting. A traditional relational database treats all the data in a similar manner. However, OLAP cubes have categories of data called dimensions and measures. For now, a simple definition of dimensions and measures will suffice. A measure represents some fact (or number) such as cost or units of service. A dimension represents descriptive categories of data such as time or location.
The term cube comes from the geometric object and implies three dimensions, but in actual use, the cube may have more than three dimensions.
The following illustration graphically represents the concept of an OLAP cube.


Figure 1: OLAP Cube

In figure 1, time, product, and location represent the dimensions of the cube, while 174 represents the measure. Recall that a dimension is a category of data and a measure is a fact or value.
Three important concepts associated with analyzing data using OLAP cubes and an OLAP reporting tool are slicing, dicing, and rotating.


Slicing
 

A slice is a subset of a multidimensional array corresponding to a single value for one or more members of the dimensions not in the subset. For example, if the member Actuals is selected from the Scenario dimension, then the sub-cube of all the remaining dimensions is the slice that is specified. The data omitted from this slice would be any data associated with the non-selected members of the Scenario dimension, for example Budget, Variance, Forecast, etc. From an end user perspective, the term slice most often refers to a two- dimensional page selected from the cube.


Figure 2: Slicing-Wireless Mouse

Figure 2 illustrates slicing the product Wireless Mouse. When you slice as in the example, you have data for the Wireless Mouse for the years and locations as a result. Stated another way, you have effectively filtered the data to display the measures associated with the Wireless Mouse product.


Figure 3: Slicing-Asia

Figure 3 illustrates slicing the location Asia. When you slice as in the example, you have data for Asia for the product and years as a result. Stated another way, you have effectively filtered the data to display the measures associated with the Asia location.

Dicing
 

A related operation to slicing is dicing. In the case of dicing, you define a subcube of the original space. The data you see is that of one cell from the cube. Dicing provides you the smallest available slice.
Figure 4 provides a graphical representation of dicing.


Figure 4: Dicing

Rotating
 

Rotating changes the dimensional orientation of the report from the cube data. For example, rotating may consist of swapping the rows and columns, or moving one of the row dimensions into the column dimension, or swapping an off-spreadsheet dimension with one of the dimensions in the page display (either to become one of the new rows or columns), etc. You also may hear the term pivoting. Rotating and pivoting are the same thing.


Figure 5: Rotating

Dimensions

Recall, that a dimension represents descriptive categories of data such as time or location. In other words, dimensions are broad groupings of descriptive data about a major aspect of a business, such as dates, markets, or products. The value of OLAP in reporting data is having levels within the dimensions. Each dimension includes different levels of categories. Dimension levels allow you to view general things about your data and then look at the details of your data.
Think of the levels of categories as a hierarchy. For example, your OLAP cube could have a time dimension. The time dimension then could have year, quarter, and month as the levels, as in Figure 6. Another example is location as a dimension. For the location dimension, you could have region, country, and city as the levels (shown in Figure 6). An important concept to OLAP is drilling. Drilling refers to the ability to drill-up or drill-down. These levels of categories (hierarchies) are what provide the ability to drill-up or drill-down on data in an OLAP cube. When you drill-down on a dimension, you increase the detail level of viewing the data. For example, you start with the year and view that data, but you want to see the data by each quarter of the year. You would drill-down to see the quarterly data. You could drill-down again to see the monthly data within a specific quarter.



Figure 6: Dimensions

Categories

Dimensions have members or categories. A category is an item that matches a specific description or classification such as years in a time dimension. Categories can be at different levels of information within a dimension. You can group any category into a more general category. For instance, you can group a set of dates into a month, a set of months into quarters, and a set of quarters into years. In this example, years, quarters, and months are all categories of the time dimension.
Categories have parents and children. A parent category is the next higher level of another category in a drill-up path. For example, 2003 is the parent category of 2003 Q1, 2003 Q2, 2003 Q3, and 2003 Q4. A child category is the next lower level category in a drill-down path. For example, January is a child category of 2003 Q1.
Figure 7 below shows you the time, product, and location dimensions with the parent and children categories. In the time dimension, you see the parent Year Category and the children Quarter Category and Month Category.


Figure 7: Categories

Measures

The tutorial to this point has provided you with a definition of OLAP, a description of cubes, the meaning of dimensions, and a description of categories. The focus now is an explanation of measures.
The measures are the actual data values that occupy the cells as defined by the dimensions selected. Measures include facts or variables typically stored as numerical fields, which provide the focal point of investigation using OLAP. For instance, you are a manufacturer of cellular phones. The question you want answered is how many xyz model cell phones (product dimension) did a particular plant (location dimension) produce during the month of January 2003 (time dimension). Using OLAP, you found that plant a produced 2,500 xyz model cell phones during January 2003. The measure in this example is the 2,500.
Additionally, measures occupy a confusing area of OLAP theory. Some believe that measures are like any other dimension. For example, one can think of a spreadsheet containing cell phones produced by month and plant as a two-dimensional picture, but the values (measures)—the cell phones produced—effectively form a third dimension. However, although this dimension does have members (e.g. actual production, forecasted production, planned production), it does not have its own hierarchy. It adopts the hierarchy of the dimension it is measuring, so production by month consolidates into production by quarter, production by quarter consolidates into production by year.
The example in Figure 8 shows the measure Volume of Product. Each value in Figure 8 is the measure of Volume of Product per year listed by product. From Figure 8, ABC Company produced 84,000 modems in 2003.


Figure 8: Measures

Nesting

Nesting is a function in which you have more than one dimension or category in a row or column. Nesting provides a display that shows the results of a multi-dimensional query that returns a sub-cube, i.e., more than a two-dimensional slice or page. The column/row labels will display the extra dimensionality of the output by nesting the labels describing the members of each dimension.
Figure 9 illustrates nesting. In the top table, the nested dimensions are time (year) and location (countries) in rows. By nesting in this example, you can see the volume of products for each North American Country in 2003. In the bottom table, the nested dimensions are products (devices) and location (countries) in columns. By nesting in this example, you can see the volume of products by quarter for each North American Country for 2003 Q1 and Q2.


Figure 9: Nesting


Aggregation

One of the keywords for OLAP is fast. Recall that fast refers to the speed that an OLAP system is able to deliver most responses to the end user. Essential for OLAP is to produce fast query times. This is one of the basic tenets for OLAP—the capability to naturally control data requires quick retrieval of information. In general, the more calculations that OLAP needs to perform in order to produce a piece of information, the slower the response time. Consequently, to keep query times fast, pieces of information that users frequently will access, but need to be calculated, are pre-aggregated. Pre-aggregated data means that the OLAP system calculates the values and then stores them in the database as new data. An example of a type of data that may be precalculated is summary data, such as failure rate for days, months, quarters, or years.
Approaches to aggregation affect both the size of the database and the response time of queries. The more values a system precalculates, the more likely a user request already has a value that has already been calculated, thus increasing the the response time because the value does not need to be requested. On the other hand, if a system precalculates all possible values, not only will the size of the database be unmanageable, but also the time it takes to aggregate will be long. Additionally, when values are added to the database, or perhaps changed, that information again needs to be propagated through the precalculated values that depend on the new data. Thus, updating the database can be time-consuming if too many values are pre-calculated for the database.


Figure 10: Aggregation

Summary

To summarize what you learned in this tutorial:

• OLAP is a category of software tools that provides Fast Analysis of Shared Multidimensional Information (FASMI).
• An OLAP cube is the concept that achieves the multidimensional functionality that provides the method to look at data from a variety of angles.
• Slicing, dicing, and rotating are methods in OLAP that change the view of the data in different ways.
• Dimensions are the descriptive categories of data that apply to major aspects of a business and that dimensions have hierarchies.
• Categories are items that match a precise organization that you use to perform drill-up or drill-down operations.
• Measures are the actual data values for a select set of dimensions.
• Nesting provides the ability to add more than one dimension or category to a column or row.
• Aggregation is the process of precalculating summary data values

SOURCE:http://training.inet.com/OLAP