Read the article at My Tech Decisions

WhereScape data warehouse automation allows Bucknell University to focus business requirements instead of the low-level details

Bucknell University was implementing some new enterprise systems and the existing data warehouse required significant architectural changes to support them. Bucknell had historically used (and continued to use for many core processes) the Banner Ellucian ERP student information system that managed everything on the back-end, including BI and reporting—a turnkey system for higher education.

The Tech Decision

While the BI components are an effective solution, it is a “black box,” which made it very difficult to modify a model or transformation to meet their specific needs. It required special skill sets and digging through lower level logic and was a bit bulky for Bucknell.

In addition, performance and stability was starting to become an issue and the lack of agility in the platform was troublesome as they had a small development team on tight delivery deadlines.

Some significant architectural changes were required to support the addition of new cloud-based, best-of-breed enterprise systems as well as a multitude of new and emerging data sources. They also needed the new platform to be as future-proof as possible, being able to handle other types of data – not just relational data – as the cloud-based systems are not necessarily based on traditional relational databases.

Bucknell was specifically looking for a tool to help them build their data warehouse and initially thought they were going to need to invest in a full-blown ETL tool along with the significant learning curve required to come up to speed on that product.

Traditional ETL tools—while effective—are a one-size-fits-all solution. They can often operate like a programming language—you can do anything you could ever imagine, but you must manually create each instruction, resulting in a lack of agility.

Doing their due diligence, Bucknell discovered the concept of data warehouse automation and immediately saw that it was exactly what they needed. These products allow developers to automate monotonous and repetitive tasks, masking the complexities along the way. Bucknell is now able to automate these minor tasks, allowing us to focus on business-level problems.

The Data Warehouse Automation Solution

Bucknell selected WhereScape over traditional ETL for reasons noted previously and chose WhereScape over other data warehouse automation tools because they felt it was the most mature and proven of the available automation solutions. Their purchase of WhereScape was part of a larger re-architecture of our data warehouse. Their choice of WhereScape was largely based on its ability to rapidly speed up the development process through automation.

There are many key features and benefits of the product. Automation of documentation is arguably the best feature of the product. Based on your design, it creates two sets of documentation— one for your technical teams and one for end-users—which show the data model, metadata, and just about anything else you’d want to know about the model.

Another key feature is impact analysis. During the lifetime of a data warehouse, there is regularly a need to enhance and extend the warehouse, but it can often be difficult to understand how changes will impact other things downstream. With WhereScape® RED, you can simply click on an object and perform an impact analysis to see how changes to that object will impact other objects. You can also do the reverse of this, looking at an object and “tracking back” to see what objects are used to build it.

Code also will run on the databases. When you build objects in WhereScape (through a point-and click GUI), the product automatically generates target-specific code. So, for instance, if your target platform is SQL Server, it will generate T-SQL stored procedures for updating your warehouse objects. When jobs are run, this code gets executed on the target database. This essentially means that the ETL—or ELT, if you will—is serverless.

Finally, the pricing was much less expensive than traditional ETL tools. Licensing is very simple to understand and is developer based.

The Impact of WhereScape

WhereScape RED is meant for the specific task of building data warehouses. The product takes on much of the painful, time-consuming, manual and repetitive tasks, allowing you to focus on meeting business requirements. For a small development team, this is huge.

The key ingredient that makes WhereScape RED so powerful is its data lineage support—including track forward, track back and its change management capabilities. Doing this type of work in an ETL environment—where you want to make a change to a source table, for example—can be very difficult. In addition, trying to do any type of impact analysis via legacy ETL is a nightmare.

With WhereScape RED, both creating documentation and an impact analysis is now done with a click of the mouse.

Very powerful, and an invaluable part of everyday development work. Bucknell’s choice of WhereScape was largely based on its ability to speed up the development process through data warehouse automation. Bucknell also used WhereScape RED in validation efforts. If they view a discrepancy, they simply open up WhereScape, find that field and track it back. It is that simple.

WhereScape allowed Bucknell University to focus on the most important aspect of a data warehouse—business requirements— instead of focusing on the low-level details of the development platform. They can now build data models much faster. With WhereScape RED, they were able to build, validate and begin using the student module subject area within the new data warehouse twice as fast as it would have taken us using traditional ETL approaches.