Get trial
English
AnalyticsCreator Congress 2022 | Modernization of a Data Warehouse | by Kulmbacher Brauerei AG
This session shows how Kulmbacher Brauerei and Überpoint generated AnalyticsCreator repository objects automatically using SQL scripts and existing data warehouse metadata. The approach reduces manual modelling effort for large SAP-based repositories and demonstrates how source, staging, persisting, core, raw, deployment, and package structures can be created programmatically.
Duration: 52:53
Updated: Nov 2022
Level: advanced
Platform: AnalyticsCreator, SQL Server, SAP ECC, SSIS, T-SQL
For: Data Warehouse Developers, BI Architects, SAP BI Teams, AnalyticsCreator Partners
Questions
- How can AnalyticsCreator repository objects be generated automatically?
- Why did Kulmbacher Brauerei use SQL scripts for repository generation?
- How is SAP metadata used to create AnalyticsCreator objects?
- How are staging and persisting layers generated?
- How are friendly names and references created from metadata?
- How can deployment packages be generated from repository metadata?
Platform shown
AnalyticsCreator
Related tooling
SAP ECC, SQL Server, SSIS, T-SQL, Data Warehouse Metadata, SAP Metadata
Key Takeaways
- Large SAP-based AnalyticsCreator repositories can contain thousands of objects.
- Manual creation of these objects would take significant time.
- Kulmbacher Brauerei and Überpoint use SQL scripts to generate repository objects from existing metadata.
- The generator database is called AC Repository Generator.
- Metadata includes tables, columns, references, packages, workflows, friendly names, and security expressions.
- Source, staging, persisting, core, raw, and deployment structures can be generated programmatically.
- SAP metadata is used to standardise names and reduce manual modelling effort.
- The persisting layer is used instead of standard historization because the existing SAP mirror already provides historical or incremental data.
- AnalyticsCreator can be adapted to customer-specific architecture patterns through its open repository.
- This approach is especially useful for large repositories with many SAP tables and generated SSIS packages.
Transcript
Kulmbacher Brauerei and Überpoint introduce their goal: avoiding manual creation of thousands of AnalyticsCreator repository objects. The team explains that they developed SQL scripts to generate objects directly in the AnalyticsCreator repository database using their existing data warehouse metadata.
The existing architecture consists of a relational SQL Server data warehouse and a self-developed SQL/SAP mirror. SAP ECC data is mirrored into SQL Server, including historization and incremental change detection, so other systems can consume raw SAP data more efficiently.
The team evaluated tools because the existing warehouse had increasing complexity, high special development effort, and no user interface for design and documentation. AnalyticsCreator was selected because it works on-premise, supports Microsoft BI skills, avoids runtime lock-in, and can support future cloud scenarios.
The team created an AC Repository Generator database written mainly in T-SQL. It uses schemas for repository logic, metadata, configuration, imports, system information, logging, synonyms, and a templating framework. This generator transforms existing metadata into AnalyticsCreator repository structures.
The demo shows how metadata is used to create connectors, sources, columns, references, staging tables, import packages, persisting structures, procedures, raw views, and core views. Friendly names are applied so SAP technical names can be exposed as business-readable names.
The final steps create deployment definitions, workflow package configuration, SSIS package variables, and deployment scripts. Dimitri confirms that this approach is a practical way to create large AnalyticsCreator repositories programmatically, especially when manual creation would take weeks.
