English
Data Vault 2 0 fully automated design & development with PBI on top
Questions
- How can AnalyticsCreator automate Data Vault 2.0 design and development?
- How does AnalyticsCreator use SAP metadata to create a data warehouse?
- Can AnalyticsCreator generate hubs, satellites, and links automatically?
- How does AnalyticsCreator deploy a generated data warehouse?
- Can AnalyticsCreator generate Power BI models from a data warehouse?
- How does AnalyticsCreator support Microsoft SQL Server and Azure environments?
Key Takeaways
- AnalyticsCreator supports both pull-down and data-driven approaches to data warehouse creation.
- SAP metadata can be imported through a real connector or a metadata connector.
- A metadata connector can be used without direct access to the SAP system.
- AnalyticsCreator can generate Data Vault 2.0 objects such as hubs, satellites, links, and link satellites.
- Hash keys can be generated automatically using reusable macros.
- The generated model can be synchronized into a SQL Server database.
- Data mart objects can be generated from Data Vault objects using facts and dimensions.
- Deployment packages can include DACPAC files, SSIS packages, workflow packages, and Power BI models.
- Generated SSIS packages can import SAP data and execute historization, persisting, and workflow steps.
- Power BI models can be generated and deployed through the XMLA endpoint when Power BI Premium is available.
Transcript
Richard: Welcome to our webinar on Data Vault 2.0: Fully Automated Design and Development with Power BI.
Today, we’ll show how AnalyticsCreator simplifies and accelerates the process of building a Data Vault 2.0 data warehouse and integrating it with Power BI for interactive analytics and reporting.
Dimitri will demonstrate how AnalyticsCreator supports the Data Vault 2.0 methodology and automates the design and development process, making it easier to build scalable and reliable data warehouses.
Before we begin, we’d like to understand how many of you already use automation tools or the Microsoft analytics stack in your projects, and roughly how much time you spend on data modeling or ETL development.
Many teams spend a significant amount of time on these tasks, so let’s look at how AnalyticsCreator can help automate them.
AnalyticsCreator provides data automation for analytical applications and is suitable for both beginners and experienced professionals.
By generating source code instead of requiring manual coding, AnalyticsCreator simplifies data integration and ETL development. It orchestrates the full lifecycle of data warehouses and data marts, including design, development, change management, and deployment.
With AnalyticsCreator, organizations can improve agility and reduce costs, achieving development speeds up to ten times faster than traditional ETL approaches.
Here are a few customer examples demonstrating the impact of AnalyticsCreator.
An automotive customer reduced time and costs for data warehousing projects in IT controlling by 80%, allowing teams to focus on more valuable work instead of manual coding and debugging.
MyMuesli, a company in the food and beverage sector, started with limited data warehousing experience. With AnalyticsCreator, they built a complete sales data warehouse with only four days of consulting and later expanded to multiple production and finance data warehouses managed by four full-time developers.
A real estate customer improved development speed significantly, achieving results 20 times faster in the ingestion layer. We recreated this scenario in a sandbox environment, which is available on our YouTube channel.
AnalyticsCreator is designed as a pure design-time tool with no runtime dependency. The generated source code belongs entirely to the customer or partner.
We focus on the Microsoft analytics stack and Azure, and the repository is open, allowing customers to build their own add-ons and extensions.
AnalyticsCreator follows a five-step approach:
- Connect data sources
- Read and manage metadata
- Generate a draft data warehouse model using the intelligent wizard
- Add calculations, cleansing, joins, APIs, and business logic
- Deploy the solution to Azure or SQL Server
This creates a streamlined and efficient development process for analytical solutions.
AnalyticsCreator integrates data from ERP, CRM, MRP, and other systems into environments such as Azure SQL, SQL Server, or cloud data warehouses.
It supports multiple modeling approaches, including Data Vault, Kimball, real-time architectures, and on-premises or cloud deployments.
The platform transforms data into tabular and OLAP models for tools such as Power BI Premium and consolidates everything into a holistic data model before generating the source code automatically.
AnalyticsCreator supports many use cases, including:
- Building new data warehouses
- Modernizing existing platforms
- Automating Data Vault models
- Creating Azure Synapse and data lake solutions
The main benefits include reduced development time and cost, ease of use, lower risk of manual errors, improved governance, and support for agile development.
AnalyticsCreator has consistently been positioned in the top-right quadrant of Gartner and BARC surveys for the past four years.
Dimitri: I’ll continue by sharing my screen.
Today, we’ll create a new SAP-based data warehouse using a Data Vault architecture. I’ll create a project called “Demo SAP Data Vault.”
The project repository is stored in a SQL Server database and contains the full metadata and structure of the data warehouse. This repository is open and accessible, making it the core of AnalyticsCreator.
There are two approaches to building a warehouse:
- A top-down approach, starting with facts and dimensions
- A data-driven approach, starting from source systems
In this demo, we’ll use the data-driven approach.
We will use SAP as the data source.
AnalyticsCreator supports connectors for SQL Server, Oracle, text files, SAP, and many other systems. In this case, we’ll use a metadata connector imported from the cloud.
The SAP FI metadata connector contains metadata for SAP Financial and Controlling tables, including relationships between tables such as BKPF and BSEG.
This approach allows you to design a data warehouse without direct access to the SAP system. Metadata connectors can also be refreshed later against the live SAP environment to retrieve custom fields and updated structures.
We start the Data Warehouse Wizard and select tables such as booking headers, booking positions, customers, accounts, company codes, and document types.
The wizard supports different architectures. In this case, we select Data Vault 2.0.
The wizard creates hubs and satellites for most tables and a link object for the BSEG table. It also generates dimensions and a fact transformation automatically.
Naming conventions for dimensions, hubs, and other objects can be configured during this step.
A question was raised regarding Git integration. Dimitri explained that repositories can currently be exported as SQL files and stored in Git, while deeper Git integration is under development.
Another question addressed metadata descriptions. Dimitri explained that metadata is imported directly from SAP and can later be refreshed or compared against the live source system.
The generated structure includes source tables and staging tables.
For example, the BKPF SAP table is imported into a staging table called Import BKPF.
AnalyticsCreator generates SSIS packages for importing SAP data using Theobald components. These packages can be generated without a Theobald license, although a license is required for execution.
Filters and variables can also be defined to support incremental or differential loading, such as filtering data by business year or company.
In the LFA1 table, AnalyticsCreator automatically generates calculated columns, including hash keys based on primary keys such as MANDT and LIFNR.
Hash keys are generated using reusable SQL macros. These macros concatenate fields, apply an MD5 hash, and convert the result into binary format.
Because the logic is centralized in macros, changing the hashing algorithm later automatically updates all dependent objects.
The generated hash keys are persisted computed columns, meaning the values are materialized and stored for faster access.
AnalyticsCreator generates transformations for hubs and satellites.
The hub transformation exposes the hub ID and business keys, while the satellite transformation contains all descriptive attributes plus the main hash key.
Historization is automatically implemented in the persistent staging layer. Satellite tables include additional columns such as valid-from, valid-to, and surrogate keys, supporting slowly changing dimension historization.
For the BSEG table, AnalyticsCreator creates a link transformation and a link satellite transformation.
Additional hash keys are generated for related tables, creating both business key relationships and hash key relationships automatically.
The link transformation exposes the hash keys, while the link satellite contains the descriptive attributes.
Historization is implemented using a “do-not-close” approach, meaning missing references remain active instead of being closed.
From the BKPF table, AnalyticsCreator generates a hub, satellite, link, and link satellite.
The model is then synchronized to SQL Server, creating the physical data warehouse database alongside the metadata repository database.
The repository stores metadata definitions, while the generated DWH database contains staging tables, views, and warehouse structures.
In the core layer, AnalyticsCreator creates facts and dimensions from the Data Vault objects.
Transformations are typically implemented as SQL views, although external scripts, stored procedures, SSIS packages, or custom logic can also be integrated.
The fact transformation combines link and satellite tables to create analytical structures for reporting.
Because satellite tables are historized, AnalyticsCreator uses snapshot logic to simplify joins across historical versions.
A snapshot table stores dates representing specific reporting points in time. Only records valid for a selected snapshot date are joined into the fact transformation.
This allows users to analyze both current and historical versions of the data.
The T003 document type table is added to the fact transformation through its relationship with BKPF.
Unknown-member functionality is also added to handle missing references gracefully.
AnalyticsCreator automatically generates a calendar dimension and supporting macros for converting SAP date strings into surrogate keys for the calendar table.
The posting date is converted into a foreign key and integrated into the fact transformation.
Measures such as quantity and amount are added to the fact transformation.
The data mart layer mirrors the structure of the future Power BI model. Relationships and measures are automatically configured.
AnalyticsCreator supports generated measure naming templates to ensure unique Power BI measure names.
In addition to standard aggregations, DAX measures can also be defined directly within AnalyticsCreator.
Repositories can be exported as SQL files and stored in Git or saved directly into the AnalyticsCreator cloud.
Cloud storage preserves historical versions of repositories, allowing access to previous versions when needed.
Next, the deployment package is configured for Azure deployment.
AnalyticsCreator generates DACPAC files for SQL Server deployment and SSIS packages for ETL execution.
Complex fact transformations can also be persisted into physical tables to improve performance. Multiple persisting strategies are available, including full, merge, historical, and incremental modes.
The deployment package includes:
- SQL deployment via DACPAC
- SSIS packages
- Power BI model deployment via XMLA endpoint
Power BI Premium is required for XMLA-based deployment.
AnalyticsCreator generates a complete Visual Studio solution containing the SQL structures, Integration Services packages, XMLA definitions, and deployment scripts.
For SAP integrations, SSIS is currently used instead of Azure Data Factory because of connector limitations.
The generated Visual Studio solution contains all deployment components.
Import packages load SAP data into staging tables using parallel execution for improved performance. Historization and persisting packages execute generated stored procedures.
A workflow package orchestrates the entire ETL process, including imports, historization, snapshot refreshes, and persisting steps.
AnalyticsCreator generates the Azure SQL database structure, SSIS packages, and the Power BI semantic model.
The Power BI model contains facts, dimensions, and relationships in a star-schema layout, ready for reporting and analytics.
Dimitri concludes by encouraging attendees to download the trial version and experiment with building the same solution themselves.
Annette: Thank you, Dimitri, for your presentation.
If you would like to discuss your business requirements, data warehouse architecture, or processes, you can book a meeting using the provided link. Additional links to our website, LinkedIn page, and YouTube channel have also been shared.
The webinar recording will be sent out within two weeks.
Thank you all for attending, and have a great day and weekend.
