Click to enlarge
Applied Solution 2
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 comfortable 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 questions, 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, methods 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.
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 addition, latency in moving and transforming the information is costly.
Because Excel is so popular and powerful, many people are interested in connecting 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.)
Click to enlarge
When the OLAP connector first connects, 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 navigation 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 sophisticated 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 administrative tool, is provided with the OLAP connector for DBAs and other administrators. It defines, edits and publishes schemas, cubes, dimensions, hierarchies, levels, members, 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 schemas, 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.
The OLAP connector allows for seamless 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.