Dimodelo Data Warehouse Studio vs Wherescape RED

Productivity vs complexity

Dimodelo Data Warehouse Studio is less complex to use, and therefore more productive.

Dimodelo Data Warehouse Studio and Wherescape are different in their complexity. Wherescape requires a more in-depth understanding of SQL, stored procedures and Data Warehousing. Unless you have a deep understanding of ETL patterns, you can introduce data handling issues into the generated code. With Wherescape you also model at almost a physical level. For example, with a fact table you need to define what the surrogate keys to dimensions are.

In contrast Dimodelo allows you to model at logical level. For example, you define that a relationship exists between a fact and dimension, but don’t need to define the physical surrogate key, name and data type etc.  Dimodelo Data Warehouse Studio applies many best practice assumptions, but still has the flexibility to allow users to affect the physical outcome through advanced options and access to code generation templates. In the example above you can, if you wish, change the name and data type of dimension surrogate keys through advanced options.

Deployment and Source Control

Dimodelo Data Warehouse Studio is a Visual Studio plug-in which works with your favourite source control and continuous integration tools. We even incorporate an MSBUILD script which will deploy your entire solution including Data Warehouse SSIS packages, Databases, Cube/Tabular, Custom stored procs and SSIS packages and ETL workflows with a single click. Dimodelo’s deployment is non-destructive, only ever soft deleting objects, and correctly handles column name changes. In contrast the Wherescape deployment requires the generation of a proprietary deployment ‘application’ that must be copied to a server. It doesn’t integrate with your existing development environment tools. Wherescape implements its own proprietary code versioning.

ETL Performance

Dimodelo Data Warehouse Studio generates SSIS packages which perform at least 30% faster than the equivalent Wherescape SQL stored procedures. Wherescape will only generate SSIS packages for Extracting data from source systems. All other ETL code is SQL stored procedures. Dimodelo Data Warehouse Studio generates the entire ETL stack as SSIS packages. Dimodelo Data Warehouse Studio’s testing shows that the source query for a fact table stored procedure runs 4.7 slower than the query required by an equivalent SSIS package. A SSIS package is inherently faster. A SSIS package is a pipeline of tasks running in parallel. SSIS can execute the source query at the same time it is executing dimension lookups, inserts and updates. Dimodelo Data Warehouse Studio’s SSIS packages are designer to achieve high throughput, including bulk insert and updates, and checksum change data capture.

Workflow and ETL Jobs

Dimodelo Data Warehouse Studio lets you define your ETL job workflow as a group of phases and tasks. Tasks execute a set of SSIS packages or stored procedures that meet a given wild carded name pattern. This means the workflow doesn’t need to be changed every time you introduce a new Extract or Transformation SSIS package. SSIS packages are generated with a naming convention that you can customize. All tasks within a phase run in parallel and the number of concurrent running tasks is configurable. Workflows are executed via the Dimodelo Management Console on a server, which can be scheduled using your preferred scheduling tool (SQL Server Job agent, Windows task scheduler etc)

Using Wherescape, you need to generate a ‘job’ for a given object (or set of objects) every time a new load, staging, dimension or fact table is introduced. These jobs have to be scheduled using Wherescape’s inbuilt proprietary scheduler.

Batch Management and Analysis

Dimodelo Data Warehouse Studio provides in-depth analysis of ETL job processing. Just compare the two images below. In contrast the Wherescape deployment requires the generation of a proprietary deployment ‘application’ that must be copied to a server. It doesn’t integrate with your existing development environment tools. Wherescape implements its own proprietary code versioning.