Member Article

Automating business intelligence and data warehousing

Colin Beasty is product marketing manager for Advanced Systems Concepts, Inc; a New Jersey-based provider of workload automation solutions. He shares his expertise on business intelligence processes.

In recent years, the democratization of analytics and business intelligence (BI) solutions has become a driving force in the growing complexity of data integration and data warehousing processes. The use of BI applications is growing exponentially, making data repositories an essential component of enterprise IT organizations. Add in new forms of data, such as various ERP, inventory and supply chain management, web services applications, mobile devices, cloud-based sources and more, and it’s clear just how important real-time data warehouses have become.

As a result, the Extract, Transform and Load (ETL) and data integration processes to access and integrate large volumes of data from multiple sources are becoming increasingly intricate. These complex data pathways and their associated moving parts break, dependencies cannot be met, systems go offline, all without the visibility into each process step. As a result, reports will be incorrect, late, or both.

While many tools exist to automate the processes used to update data stores, most have significant limitations. For example, many leading enterprise data warehousing and BI solutions come with native scheduling capabilities, but these are typically limited in their respective functionality to scheduling on just that system, leaving IT to rely on error-prone and time consuming scripting to pass data and manage dependencies across the vast array of BI and data warehousing solutions. Developing and testing a custom script or program to automate these processes can take weeks and even months.

To solve this problem, IT process automation has become a cornerstone to allow IT to efficiently integrate and manage workflows across their data warehousing environments for improved data quality and reporting. Workload automation solutions provide a single automation solution to integrate data pathways into automated, repeatable and schedulable processes that deliver a high degree of control over all steps of the ETL and BI process. A good workload automation solution will simplify the creation of these workflows, reducing or eliminating the custom scripting and/ or manual intervention that have been traditionally required to execute these processes.

Workload automation enhances the automation of these processes via robust scheduling and automation capabilities, including Date/Time scheduling, triggers, event automation, constraints, alerting and monitoring. These solutions should also support your particular IT environment by providing cross-platform and cross-application support. Whether you operate a mix of Linux, UNIX or Windows, are using PL/SQL, Java, Web Services, databases such as Oracle or SQL Server, or OpenPGP to encrypt and decrypt tasks, an IT automation solution should be robust enough to handle it all. This should also include direct integration with many of the leading data warehousing and BI solutions on the market today and support indirect integration with other solutions via support for web services, APIs, etc.

Finally, ease of authorship of both simple and complex workflows in a script-less fashion is critical. When combined with workload automation’s other capabilities, BI and ETL professionals can increase efficiency, reduce manual errors from scripting and develop end-to-end workflows faster and more reliably, allowing them to focus on innovation and supporting the business.

This was posted in Bdaily's Members' News section by Colin Beasty .