There has been a lot of focus and opinions shared in the data warehouse community on the pros and cons of various enterprise data warehouse reporting solutions over the past years. These products deserve the focus and attention because they provide the main information delivery means for your users to access the data warehouse. The ability of these products to provide easy, intuitive and accurate navigation of the data warehousing data store will determine to a large part whether the project will be a success or failure.

While the enterprise reporting products provide end users with an effective information resource, data warehouse development and support personnel frequently need to interrogate data more directly and quickly in all phases of the project. Some common questions that arise from this need are:

  • What tools do subject matter experts, architects, developers and infrastructure support personnel use for data analysis throughout all phases of the data warehouse project?
  • What product should be used to obtain and analyze the contents of the source systems before real development begins on the data warehouse?
  • What product should be used to query the data warehouse directly for performance optimization purposes?

In many organizations, a back-end query product is utilized to provide native SQL access and analysis of the various data stores being supported. These products provide an effective method to quickly access information available in the environment. They provide a fast alternative for internal personnel to access the data needed. They provide a quick means to test and validate information results from the enterprise reporting products during quality assurance and user acceptance testing phases of the data warehouse project. Enterprise reporting products typically require extensive setup and establishment of a meta data layer before data analysis can begin. Additionally, enterprise reporting products require data aliases to be established in the meta data, and complex joins cannot be easily supported. The back-end query product provides an additional means for data access and review. The query product typically provides SQL access to databases, ODBC data source and other mechanisms to access information available in the data warehouse environment. Depending on the organizations, the query product is used by analysts, ETL (extract, transform and load) and report developers, testers, DBAs and support personnel for analysis purposes. The level of access and capabilities available through the back-end query tool will vary depending on the role of the individual and the organizational security needs. In some organizations that are fortunate enough to use a single database management system (DBMS) or limited set, the SQL access utility available with the database engine will be sufficient for their needs and budget. In other firms, the vast array of DBMSs and data sources will build the need for a more versatile product that can span these various information stores. What features and capabilities are needed in a back-end data query product?
The query product's administration features will be limited to the database or data store access restrictions probably established by the DBAs or data source owners. The query product will provide the user schema object creation, modification and deletion capabilities limited to these access rules. These types of capabilities will typically be limited to the DBAs except in certain circumstances such as for prototypes or sandbox test areas where users may be granted limited authority. This same level of access would also be limited to the importing and exporting of data to/from the data stores. These products typically provide some type of command-line interface so queries can be included in scripts or for query scheduling. The query tool may have extended DBMS capabilities for administration of table spaces and indexes. In some cases, the query tool product may also provide advanced space analysis (e.g., fragmentation detection), table join analysis/recommendations beyond explains, query function creation and use, and governing (e.g., result set size, CPU usage) capabilities for the user.

The query product security features will typically be limited to the security services provided through the database or data store. Query product features may be controlled through tailored configuration files or different product type versions (e.g., standard versus extended product versions). Some advance security capabilities such as establishment of data transaction limits (e.g., stop processing if 5 percent or more rows deleted in production database) may be available.

The query product will typically provide accessibility to a variety of the major DBMSs and data sources via ODBC or other data store connectivity method. Advance capabilities may include the ability to easily migrate (e.g., wizard) data stores from one DBMS to another, data editing functionality and schema object comparison analysis. Data content reporting into delimited text, Excel, comma separated values, HTML and XML output is commonly provided for data analysis.

Some of the query products support advanced features typically limited to DBA or power user groups. These features include real-time database monitoring for performance and resource utilization (sorts, sort heaps, buffer pool activity, hash joins, overflows, cache statistics, timings). Advanced tuning wizards provide users with optimization and index recommendations plus syntax learning capabilities for commands to minimize query input and syntax errors.

Back-end query products fill a needed role in the development of data ware warehouse environment for analysts, developers and support personnel. They provide a quick, effective and complementary data analysis capability for data warehouse development in addition to enterprise reporting products commonly used by end users. Advanced features in these products can provide enhanced insights into data analysis, performance tuning and data store utilization, which can benefit overall utilization of the data warehouse. 

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access