ExtendX Data Unlimited 2.0

As a business intelligence (BI) consultant, I regularly develop dashboards and reports as well as the extraction, transformation, and loading (ETL) and OLAP data structures that support them. Clients often desire tools that let them perform ad-hoc analyses of existing data. This is more complex to develop than it sounds. There are many products that offer portions of this capability, but they can be quite expensive.

I recently had the opportunity to try Meta X Database Intelligence's ExtendX Data Unlimited 2.0, which works with an in-memory data structure. Similar to the functionality seen in Microsoft Excel, ExtendX provides drag-and-drop functionality for creating pivot tables and pivot charts. ExtendX lets you analyze a relational data set consisting of one or more tables, much in the same way you analyze an OLAP data source and with similar query speed.

ExtendX requires the Microsoft .NET Framework 4.0 or later. It supports data stored in SQL Server, DB2, Microsoft Access, and Pervasive.SQL.

Installing the product is very simple and quick. You download the executable from CNET's Download.com using a link provided by ExtendX, then run the executable.

Once installed, you just need to configure three connection strings and enter the license key. The connection strings are created using a familiar interface that builds each string for you and utilizes either Windows or SQL Server authentication. The product needs the connections to access the main database (which holds the data you want to analyze), repository database (which stores views of your data), and ExtendX database (which stores metadata used by the product). You only need read permissions on the main database, as it's never altered. The repository and ExtendX databases require full rights because the product creates and manipulates objects in these databases. They can be created by a DBA in advance. Users should be granted full rights to these databases to facilitate the installation and use of ExtendX.

To use the product, you set up a view that ExtendX calls a star schema object. This view is created with the ExtendX Table Selector Wizard, a simple query-builder interface that's aware of any foreign key relationships, making it easy to join tables. The wizard also lets you create expressions and column aliases, making the resulting data a little more user friendly.

Using the Table Selector Wizard, I created my own star schema object from a sample database I keep around for a T-SQL class that I teach. My star schema object consisted of five tables and three expressions. My tables all came from a single database, but ExtendX lets you combine tables from different databases.

After you create a star schema object, you can perform analyses by dragging and dropping items (i.e., columns) in the star schema object to the Column, Row, Filter, and Data Areas. For example, I created a pivot table by dragging the Year item into the Column Area, the Ext Price item into the Data Area, the Productname item into the Filter area, and the Category, Country, Region, and City items into the Row Area, simulating a hierarchy. The product behaved as expected, producing a pivot table with expandable rows, as Figure 1 shows.

Figure 1: Creating a pivot table in ExtendX

After my pivot table was created, I could close ExtendX and reopen it to the same pivot view, which is a nice feature that makes the application easier to use. I also discovered some other features worth mentioning:

There are some areas that could be improved. The selection of chart types is somewhat limited. I'd like to see it expanded to match other products that are available in the market. For example, Excel and PowerPivot offer significantly more types of charts. I'd also like the ability to centrally store named sets, hierarchies, and calculations so that all ExtendX users could them and common business rules could be easily applied to the analyses.

With that being said, I like ExtendX and find it to be an intuitive way to browse and analyze company data. Extend X is a great tool for those who don't have advanced SQL skills or the ability or budget to create a full-blown BI solution.