Replacing Brio

Introduction

Is your organization still using Brio for its database reporting?

Brio, or Hyperion Interactive Reporting, was a staple of reporting for a long time. Oracle® announced in February 2015 that version 11.1.2.4 would be the terminal release, and premier support for Brio would end in April 2015. You may be able to get by without support for a while, but even the best data analysts can't work forever with obsolete tools.

This e-book examines the crossroads at which many organizations find themselves as they replace Brio. It explains the environment, common use cases and must-haves for a self-service data preparation tool for reporting. Data analysts can use this e-book as a checklist for moving away from Brio toward another platform for accessing, integrating and preparing data.

The role of the data analyst

First we examine the role of data analysts: what they need to deliver their work every day, how they deal with data sources and what their deliverables look like.

The work of analyzing data is not limited to data analysts. In most organizations, many users take data at rest in one format, then prepare and present it with context that makes sense to a given audience. Those users are analysts, even if "analyst" is not in their job title. They work in all areas, from the loading dock to the C-suite.

Making the Case for Reporting Tools

Data preparation has existed for decades. Spreadsheets and PCs kicked off the data analysis revolution by giving ordinary users a set of basic tools. But analysts found that spreadsheet programs fell short in handling deep data analysis, performing SQL extractions and working with data sets that required manipulation like multiple JOINs.

Brio offered easy query capabilities against relational databases, but through ODBC or a meta-connection against an ODBC source.

Along came a wave of tools like Brio for easy, ad hoc data analysis for business users. Brio offered easy query capabilities against relational databases, but through Open Database Connectivity (ODBC) or a meta-connection against an ODBC source.

Now, a data generation later, expectations have changed for the tools, the type of data going through them and the speed at which users want insights.

Main Pain Points for Data Analysts

• Data source proliferation — With data spread across silos, analysts must collect data from spreadsheets, structured databases, unstructured databases and everything in between.
• Skill set gap — Writing SQL queries is not the same from one data source to another. That puts analysts in perpetual catch-up mode, focused on new coding practices instead of on business insights.
• Tool proliferation — The variety of data sources breeds a variety of tools to work with them. To keep up with the data, analysts must keep up with native tools, custom APIs, business intelligence (BI) platforms and analytics apps.
• Spreadsheet sprawl — Pulling together data from disparate sources means using some other application to integrate them. The most common tool for that purpose is the spreadsheet, which does not allow for traceability or repeatability.
• Data delivery engines and roadmaps — Analysts who depend on IT to deliver data subject themselves to another external set of schedules and priorities.
• Data lineage and data quality issues — Where has the data come from? How accurate is it? Is it consistent with data from other sources? Here, analysts contend with traceability, transparency and standardization.
• Manual processes — With greater variety comes the need for more massaging and manipulation to make the data useful. Working with data manually slows reporting and hampers productivity.

That brings us to Brio.

Figure 1: Main pain points for data analysts

Why Replace Brio?

Brio has long been a staple of data analysis, evolving through multiple acquisitions. With terminal release 11.1.2.4 in February 2015, Oracle announced the end of product updates. The terminal release stopped at web support for Internet Explorer 10 and desktop support for Windows 8. It offered no mobile support or strategy.

Features for sharing and collaboration have become important requirements of any data analysis tool, but Brio users are stuck with them as they are.

The trusted tool has gone away, but the need to access, integrate and prepare data has not. No matter how adept the data analyst who is using Brio's frozen set of features, the fact is that business and technology will grow and eventually outpace the tool.

Already, technologies far past ODBC are required for access to cloud storage like Azure and Amazon Web Services, frameworks like Hadoop, and NoSQL data sources like MongoDB and SimpleDB. Even with conventional data sources like Oracle, SQL Server and MySQL, the goal is native access rather than going through an API like ODBC or OLE DB. A native connection allows analysts to construct more-granular, transaction-level SQL queries that result in precise data sets.

Beyond querying, analysts need an evolving tool so they can profile data, move it into files or databases, apply mathematical and statistical operations, and perform advanced data preparation to deliver a perspective or a result set.

Features for sharing and collaboration have become important requirements of any data analysis tool, but Brio users are stuck with them as they are, frozen at the product's terminal release in 2015.

Top 3 Workflows

What has most data analysts worried when it comes to replacing Brio? Figuring out how they're going to get their new product to do everything they used to do with Brio.

Here are the top three tasks and associated workflows that data analysts perform:

1. Generating ad hoc reports — The first step in using a query or data preparation tool is to connect to one or more data sources. Those can be as simple as a delimited file, as complex as Hadoop, and everything in between. This task includes scripting queries in an editor or constructing them in a visual query builder, with query parameters, grouping, aggregations, filters and the like. The results are written to file for analysis. Especially for relational databases, native connectivity allows for transactional SQL to be executed against the databases, helping construct the most complex statements inside your reports.
2. Building upon ad hoc reports for automation and scheduling — After the initial query comes data preparation to convert data into information consumable by specific users, which entails profiling and cleansing the data. Analysts look for discrepancies, patterns and duplicates, then they apply transformations and business rules. They export the clean data into a file, temporary storage, a database or a server, then create batch workflows and schedule regular reports.
3. Setting up dashboards and collaborative reporting for teams — Now that tools for publishing and sharing abound, analysts can use the data generated from automated workflows for advanced analytics or dashboards. That means they can grant their co-workers access to the main data output on the server and to project artifacts like scripts, connections and queries.

Once they can accomplish those tasks in a new tool after Brio, analysts can quickly return to the reporting lifecycle: connecting, querying, getting results and working with them.

Must-haves for replacing Brio

To reach the same — or higher — level of reporting that they enjoyed with Brio, analysts can examine potential replacement tools along the following functional lines.

Accessing Data

With built-in connectivity to relational and non-relational sources, data no longer needs to move back and forth among applications. Even when IT teams are using different technologies like Oracle, Hadoop or NoSQL for enterprise platforms upstream, the business users downstream continue their processes unaffected.

Three types of connectivity need to be supported:

1. Native connectivity uses client drivers and allows the construction of transactional SQL statements against data sources.
2. ODBC is an open standard used for accessing databases. It uses SQL statements and translates them into statements tailored to the syntax of each brand of database. Because of the translation, queries run through ODBC cannot take advantage of all the features of every database, as native connectivity allows.
3. Data warehouse connectivity is for enterprise metrics, in implementations like BusinessObjects, for example. But many users need to extend that to spreadsheet files or SQL Server databases for process-specific data. Strong tools can extend a data warehouse implementation by connecting to the query through web services and joining it with the other data sources.

Consider tools that can offer connectivity through native and ODBC drivers for relational databases and also offer connectivity to data warehouses.

Ideally, a single application offers all three types of connectivity.

Building Queries and Constructing Workflows

Having connected to the database, the next step is to build queries.

Most data analysts are at a disadvantage here because they are far removed from the database design and don't know, for instance, the optimal way to join the Order ID field in the Orders table to the corresponding field in the Contact table. Visual, drag-and-drop tools simplify the process of building queries, reduce time spent debugging SQL and allow analysts to share models with fellow analysts.

The best tools make it easy to connect to data sources and construct workflow prototypes, so that analysts can automate their work.

That is especially valuable when connected to multiple data sources because it removes the need to write different SQL statements against each database. The most powerful tools automatically calculate relationships among tables, preclude invalid JOINs and other impossible operations, write the SQL in the background, represent anticipated query results graphically and allow for export of results to other file formats for wide distribution.

Many analysts construct entire workflows around useful queries, stringing together a series of steps such as these:

1. Querying data sources
2. Verifying data quality
3. Applying business rules to create specific, calculated columns
4. Constructing workflows to batch any combination of these steps
5. Scheduling these steps based on reporting requirements

The easier the tool makes it to connect to data sources and construct workflow prototypes, the more the analysts can automate their work and repurpose it in the future.

Integrating Data

Life is simple when there's just one data source, but it rarely stays that way for long. Most of the time, analysts have a need for data from multiple sources, such as Oracle, SQL Server and Microsoft® Access®. Good tools support that need by matching columns in one source to those in another source, populating fields based on filters and converting even in cross-connection query mode over many different sources to construct SQL.

Preparing Data

Analysts find discrepancies when joining tables from a wide variety of sources. To avoid unexpected behavior arising from merged tables, they need a tool that can first check the quality of the data, then clean and transform it.

Tools with profiling capabilities find and clean anomalies in the data set like discrepancies, null values, highest/lowest values, frequently occurring values, patterns (such as email address format), standards and duplicate records across sources. That leads to a massaging or transform step, in which the tool trims leading/trailing white space, replaces values by column, inserts calculated values and applies mathematical functions.

Strong tools store the transformations as business rules for later use with any type of data. They also publish the files, data and business rules so that team members can see what went into any given report, down to the SQL statements used.

Managing an Increasing Workload

This functional area is most important for data analysts who want a tool that picks up where Brio left off. Real progress in database reporting includes generating more reports from more data sources for more users — preferably with less effort from the analyst.

The right tool meets that requirement by making clean, transformed data sets available to users who want to manipulate them later. It should enable downstream users to create their own dashboards and reports in visualization tools or even spreadsheet programs. Upstream users want integration with partner applications like predictive analytic platforms and the opportunity to query across other databases and Hadoop.

The trick is for data analysts to make both downstream and upstream users happy without generating more work for themselves. Strong reporting tools extend the value of raw, disparate data — usually in silos — to users throughout the organization.

Replacing Brio with Toad Data Point and Toad Intelligence Central

The combination of Toad® Data Point and Toad Intelligence Central is ideal as a replacement for Brio (see Figure 2).

Toad Data Point is a desktop client for preparing workflows that support ad hoc or scheduled reporting. Toad Intelligence Central is a server based working environment offering security, governed collaboration, federated data sets, queries and automated workflows based on a variety of data sources. Toad Intelligence Central is built on MySQL, so data materialized or virtualized there can be accessed over a standard MySQL ODBC connection by applications like Microsoft Excel, Tableau or Qlik. Line-of-business users can create their own reports and dashboards, and data analysts can work on analytics platforms like Statistica.

Toad Intelligence Central also works with application integration products like Boomi.

Figure 2: Toad Data Point and Toad Intelligence Central

Conclusion

The world of data analysis is ready for a database reporting tool that picks up where Brio left off. In the important functional areas of accessing, querying, integrating, preparing and managing more data, there is no going forward with Brio, so organizations are in the market for a replacement tool.

Toad Data Point offers self-service data preparation for accessing, integrating and preparing data. Analysts use it to query a variety of data sources, to prepare data with profiling and transformation, and to automate workflows and reporting tasks. Toad Intelligence Central complements Toad Data Point with sharing tools for queries, files and data sets, and with collaboration tools that link analysts to other data consumers and applications. Combined, the products simplify the most common data analysis workflows and equip the organization for future trends in database reporting.

Simplify the most common data analysis workflows and equip the organization for future trends in database reporting.

About Quest

Quest helps our customers reduce tedious administration tasks so they can focus on the innovation necessary for their businesses to grow. Quest® solutions are scalable, affordable and simple-to-use, and they deliver unmatched efficiency and productivity. Combined with Quest's invitation to the global community to be a part of its innovation, as well as our firm commitment to ensuring customer satisfaction, Quest will continue to accelerate the delivery of the most comprehensive solutions for Azure cloud management, SaaS, security, workforce mobility and data-driven insight.