WhereScape

Die Data-Warehouse-Automatisierungssoftware von WhereScape löst eines der größten Probleme der IT: Wie baue ich ein Data Warehouse mit Effizienz, reduziertem Risiko und langfristiger Rentabilität auf?

Mithilfe von Automatisierung, Vorlagen und integrierter Intelligenz steigert WhereScape die Produktivität von Architekten und Entwicklern, bietet Skalierbarkeit mit Standards und ermöglicht es Ihnen, die Kontrolle über Ihre eigenen Daten zu übernehmen und gleichzeitig die Zusammenarbeit zwischen IT und Unternehmen zu fördern.

Mit seiner umfassenden technischen Unterstützung, die praktisch jede Quelle, jedes Ziel und jede Entwurfsmethodik umfasst, hilft WhereScape Ihnen, Ihren Data Vault (oder jede andere Datenarchitektur) schnell und einfach zu entwerfen, zu entwickeln, bereitzustellen und zu betreiben.

Erstellen Sie Raw Vault, Business Vault und Star Schema in nur wenigen Minuten und profitieren Sie von Leistungs- und Qualitätsverbesserungen, indem Sie Hand-Offs, Toolwechsel und andere Fallstricke der handgeschriebenen oder teilautomatisierten Entwicklung reduzieren.

When a self-referencing relationship exists in one table, then a hierarchical link can be used to maintain the relationship. Hierarchical links are defined as entities within WhereScape 3D and their business keys tagged as type, Link Business Key. Naming standards are applied (e.g. tablename_hl) to differentiate from other link types. The ingestion mechanism is the same as any normal link.

Hierarchies can be resolved or navigated within downstream objects, such as the information mart, built in WhereScape RED.

WhereScape – 01

There are multiple options to handle this.

Option #1 as a multi-active satellite by setting the PK column “von” as a Multi-Active Key and a Multi-Active attribute. The rest of the attributes are defined as Multi-Active attributes too. A multi-active satellite with the suffix _ma will be generated.

Option #2: LivingAddress as Hub with Business keys of both primary keys (KundeId and Von) and generating a link and satellite. No link satellite necessary.

WhereScape – 02

The relationship between the Customer and the AssociationPartner is defined by the foreign key (FK) relationship. The AssociationPartner FK can change and be Null, valid or invalid. The KundeID is set as the Driving Key. To maintain the history and effectiveness of the relationship an effectivity satellite is added to the link. Satellite suffix: _effectivity

WhereScape – 03

When a table such as the Delivery Service table does not have its own primary key but holds keys of several other related tables, WhereScape can generate a non-historized link. The foreign keys are tagged as type, Link Business Key. Non-descriptive attributes, such as PosID, that are not part of the business key, can be tagged as type, Link Dependent Attribute, and WhereScape will add them to the Link table as a dependent child. Descriptive attributes can be tagged as type, Satellite Transaction, and will be implemented in a Satellite.

WhereScape – 04

If early integration is needed, views can easily be defined, and DDL generated, to split the Roadshow Order table (RS_Bestellung) and map these views to the Order and Position tables. WhereScape can also be designed to map and source a subset of the RS_Bestellung and Webshop table attributes to the Order and Position hubs and merge the data into their respective hubs via separate update scripts for each source. This allows independent, isolated and flexible loading whichever source is delivering data.

WhereScape – 05

Reference tables (for example, sourced from csv files) can be profiled and structures discovered and then mapped as sources, with PKs and FKs defined, all attributes tagged as reference attribute and generated as table objects in the DW. Prefix: r_

For historized reference tables the PK attribute is tagged as a reference attribute and the rest of the attributes are tagged as reference history attributes. All reference history attributes are maintained in a separate satellite and linked to the reference table via FK relationship to maintain the historical changes of the reference table. Satellite suffix: _refhist

WhereScape – 06

Duplicate data arrives within the same batch timestamp.

#1 Technical DV duplicate (two identical rows).We could choose to de-duplicate using either a batch id which is different from the Business key or by using a SQL windowed function like ROW_NUMBER() to insert only the first row

#2 Two rows with same BK (different data). Source is erroneous

solution

Change the satellite as a multi-active satellite and load the received data as is
Communicate with the team delivering the source data and let them fix it
add an error reporting table using error handling
Apply soft rules in the BV to fix the data

WhereScape – 07

Rows arriving without business keys might be meaningless for the business object. One can ignore those rows and put them in the error mart. In case there is a need to maintain ‘unknown keys’ in the hubs, one could create a dummy record in the hub for the NULL keys as zero key records as used traditionally in Kimball-style dimension tables.

WhereScape – 08

The satellites take care of any change in the historically loaded descriptive information of the hubs and links and any other data vault objects. In case there is any mistrust in the data, we use an additional ODS layer to stage the data vault and maintain the historically loaded source data. It is also possible to create a view to reconstruct the source from the data vault objects and compare it to the loaded data.

WhereScape – 09

A CDC mechanism needs to exist to capture the changes. For example for the customer hub there is a satellite for status tracking. The history is maintained in the satellite and can be retrieved to validate the business keys over time. Satellite suffix: _tracking

WhereScape – 10

DeliveryAddress and Customer are created as hubs with their respective satellites and there is a link object between the delivery address and the hub. As the customer hub is also sourced from the delivery address, the customer hub is also populated with the customer business keys from the delivery address even if they do not, at the time, have corresponding records in the Customer table. The link is maintained properly.

WhereScape – 11

If business keys are deleted between the dates of Deliveries, we need to maintain a status tracking satellite for the Orders hub. As the Orders are relevant for counting we need to add/generate the measures and transformations in the fact tables within WhereScape RED.

WhereScape – 12

The Product Category is a link object which maintains the relationship between the Product and the Category hubs. The Product Category dimension based on this link and the satellite attributes reflects the changes over time.

WhereScape – 13

WhereScape supports the creation of a Dimensional Mart on top of the Data Vault. We used the WhereScape RED tool to create dimensions for Association Partner, Customer, and Product as well as separate fact tables for Webshop and Roadshow contain all the different metrics requested.

WhereScape – 14

WhereScape supports the generation of business vault objects such as PIT and Bridge tables, current views and historical views, with generated end dates and record version numbers on top of the raw vault satellites.

Business rules and data transformations can be added at any time in WhereScape RED.

The connection between Webshop Orders and Association Partner was modelled in 3D to generate the Link tables. We also modelled a Sames-As Link table to resolve the alternate Customer ID based on credit card information.

WhereScape – 15

All objects generated in the data vault or other data foundation layer is fully documented as all object details are stored in a repository. The tool generates the documentation and the graphical data lineage of tables, columns and related procedures, functions, scripts, transformation codes etc. automatically.

WhereScape – 16

All errors are maintained in the error and audit log tables stored within WhereScape’s metadata repository. Errors are displayed within the WhereScape scheduler console or can be sent as notifications – such as email/teams/slack – to notify responsible people.

WhereScape – 17

WhereScape’s job scheduler enables full orchestration of steps needed to load and transform data through the various layers of the data vault/data warehouse. The ingestion of the load table, and the scripts to generate ODS/PSA, stage and DW objects are defined as tasks. The tasks can be prioritised, ordered and grouped by object or process type. Jobs could be configured to depend on other jobs to run. Job dependencies can be visualised and are included in the generated technical documentation.

WhereScape – 18

WhereScape generates deployment packages to deploy objects to different metadata repositories (e.g. development, UAT and production). A deployment could be a reload or new load or handle deletes and partial changes. DDL deltas are detected during deployment and ALTER statements generated. Versioning is managed within WhereScape and can also be integrated with existing processes and external tools such as Git.

WhereScape – 19

WhereScape comes with a built-in cross-platform, enterprise-grade batch workflow job scheduler. Running on Windows or UNIX/LINUX or a mix of both, the scheduler supports load balancing, resolves ordering through job dependencies and provides an easy to use web user interface to maintain and track workflows. Users wanting to use their own scheduler can either execute the WhereScape generated scripts directly or by triggering WhereScape jobs via REST API.

WhereScape – 20

WhereScape is continuously developing its enablement packs to support target platforms and currently supports the following:

Amazon Redshift
Databricks / Deltalake / SparkSQL
Exasol
Google BigQuery
Greenplum
Hive
IBM DB2
Microsoft Azure Synapse + APS/PDW
Microsoft SQL Server + Azure SQL Database + Managed Instance
MySQL
Netezza
Oracle
PostgreSQL
SAP Hana
Singlestore
Snowflake
Teradata

WhereScape can be installed on premise, in a cloud environment or a hybrid. Metadata is stored in MS SQL Server/PostgreSQL/Teradata/Oracle and can run locally or on cloud services such as RDS. Depending on the target platform additional connection ODBC, native loading tools and python 3.0+ are required.

WhereScape – 22

WhereScape

Willibalds Data Warehouse

Automation Tools