Tap right in to your data warehouse using Excel.

Click to enlarge


Applied Solution 2

Direct Access

Tap right in to your data warehouse using Excel.

One of the world’s most popular and widely used analysis tools is Microsoft Excel, which is on almost everyone’s desktop. People are comfort­able with its native features, and they rely on the application to analyze data and communicate results.

Excel’s functions, such as pivot tables and charts, allow analysts to slice, dice, drill and explore data in a multi-dimensional fashion. By browsing the “cube,” users can view and explore information in a self-service manner without having IT generate individual or group reports. People can swap dimensions from rows to columns, add and remove measures, filter certain criteria, and drill down through hierarchies. This interactive and real-time exploration is critical to finding answers to business ques­tions, whether they’re simple or complex.

Take a Shortcut

Excel is typically used for processing the final analytic steps to create reports and charts. Usually, other tools, meth­ods and techniques, such as ETL tools, hand-crafted SQL or even other business intelligence (BI) tools, extract the data. It is massaged and manipulated, then copied and pasted or inserted into a spreadsheet where it can be further manipulated, formatted and calculated.

Benefits of a Quick Connection

The advantages of the OLAP connector include:

  • No data extraction, transformation or loading latency
  • Familiar user interface
  • Real-time access
  • Lower total cost of ownership (TCO)
  • Leverages existing Teradata security infrastructure
  • Better workload management
  • Consistent results and numbers across the organization

The problem is that to use pivot tables or charts, the person must typically extract the information out of the data source either directly into Excel or into middle-tier OLAP servers. This leads to the spread-mart problem, which results in inconsistent data among users because it is extracted from the source and stored locally. In addi­tion, latency in moving and transforming the information is costly.

Because Excel is so popular and power­ful, many people are interested in con­necting it directly to their data warehouse system. Teradata recently released a tool, the OLAP connector, which allows pivot table users to do just that. The OLAP connector is part of the Teradata Business Intelligence Optimizer suite of products.

Providing direct access to the corporate data warehouse eliminates the need to extract and store the information in the workbook or on a multi-dimensional OLAP server. This also ensures a consistent view of all data and allows business users to work with their familiar reporting and analytics tool—Excel.

Using the OLAP Connector

The OLAP connector is a client-side OLE-DB for OLAP (ODBO) interface and multi-dimensional expression (MDX) engine that allows for access from any OLAP client (like pivot tables) directly to the database. (See figure.)

Figure: OLAP Connector Architecture

Click to enlarge

When the OLAP connector first con­nects, it retrieves information from the Teradata BI Optimizer repository, which is a relational database that contains metadata about the multi-dimensional definition and permissions.

When a user makes a pivot table naviga­tion or gesture, such as adding a measure, Excel emits MDX. The connector “listens” for this MDX to be emitted via the ODBO interface and parses the MDX. A sophisti­cated query engine converts the MDX to Teradata-optimized SQL, which is then sent to the database via a standard ODBC driver.

The Teradata Schema Workbench, an ad­ministrative tool, is provided with the OLAP connector for DBAs and other administra­tors. It defines, edits and publishes schemas, cubes, dimensions, hierarchies, levels, mem­bers, measures, calculated members, complex calculated measures and roles.

In a typical implementation process, the administrator identifies an existing or new solution. The schema definition is captured for the target solution using the Schema Workbench. The schema definition is published to the Teradata BI Optimizer repository. Excel users then connect to the database via the OLAP connector. They provide their user ID and password and are authenticated by the standard Teradata security methods. The OLAP connector leverages the existing database users and roles so no new infrastructure is required. Users will have access only to the sche­mas, cubes and attributes that have been assigned to them. They will then be able to navigate their cube in pivot tables with the SQL generated by the OLAP connector going directly to the database.

Leverage Power

The OLAP connector allows for seam­less connectivity from the world’s No. 1 analysis tool, Microsoft Excel, to the data warehouse. Users now have direct access, leveraging the power, scalability and parallelism of the database. As a result, they have more detailed analytics, better response times, and reduced latency and storage requirements.

Your Comment:
Your Rating:

Fuzzy Logix