Rapid Prototyping Video: Agile Data Warehouse Design

Thank you Amir so let's go ahead. First of all I give you a short overview of Analytics Creator. Our headquarter is in Munich Germany. We started in 2017. The software is much older so it's now in the third generation. We developed three times the software completely new and we have now 15 years development experience with data automation and with Analytics Creator. We have at the moment around 50 Partners worldwide. We are a partner Centric organization of course we sell also directly to customers and our main focus technology focus is Microsoft so also our Target environment is at the moment always Microsoft. We operating in the EU and also in EPC. This is our focus at the moment we count around 690 developers so we don't know exactly all the customers because our partners have special license models, but we know the developers worldwide which are using Analytics Creator

We see really big challenges for all developers and also the legal responsible persons in a company to manage their data for analytical proposals. The ever increasing technical depth, and the continuously changing requirements results and difficulties for organizations, we see so many companies struggling and wasting their resources and money by doing everything manually. We believe that automation is the key to successfully building data analytics environment. Now I will show you the architecture and how Analytics Creator is working. On the left side you have really all sources that you can imagine. There are 250 different connectors you can use with Analytics Creator so we use a third party connector for that and we have our own connectors (around about 20). This almost is enough but if you have something special you can use a third body connectors there is in Analytics Creator. We have a holistic data model which covers everything in in the analytics deck, we use a metadata catalog, and this metadata catalog you're able to uh import all the metadata from your sources. This is a process which works really automated and on this metadata catalog we have many Wizards which help you to draft a data warehouse so they around about 20 Wizards which support the design and the development of your data warehouse architecture. Also Analytics Creator covers the deployment of your solution in the SQL DB in in the asro stack also the generation of the code in the asro data factory data breaks and SSS packages and you can store everything, can use the storage of a SQL database of a aser data L and also the AER signups environment and everything what you develop in the holistic data model is installed in a open repository. This means this is a SPL database and you can also develop in there or you create your own applications on the repository. Analytics Creator Support also to manage your data marks and the data marks deploy also the data in automated in the PowerBI and also in, for example, in a tabula or in Tableau model click and whatever and since half year we have a export in which this export you can deploy also the the the reporting layer the last layer uh in in in a ABS in a snowflake or in Oracle.

Let's talk about your challenges. We see really increasing requirements and a higher complexity in the next years of for companies, and data warehousing is really very resource intensive. It's really hard to get the right people for doing this. Everything is done with manual coding and there is no really documentation which is accurate, and you can manage their pipelines very easily. We see really big team pressure and fluctuation on those projects, and we see a lack of transparency and security on data warehouse projects. Two months ago a big survey showed that in data warehouse projects the data quality is the main issue of companies, so 80% of the companies said they have big issues with data quality. With Analytics Creator you get a full accelerated environment to manage your whole life cycle of your data warehouse and analytics platform so, from the design process, from the development and deployment process you have the possibility to use Agile development and Engineering. Everything is done automatically so we generate the source code and you can use this source code wherever you want. There is no vendor lock there is no runtime so also when the subscription ends you can use the whole environment. We support the developer and also the business with a really nice graphical user interface at data lineage you will see later in the presentation and we cover the knowledge with a knowledge management system which is embedded in Analytics Creator for the development or for the business and also we have really nice features for supporting your data governance and processes. 

 We are very proud to show this picture now. Four years in a row we won the first place at the Barc data management survey. Customers are very happy with our solution and we are really proud about. Now it's time for Alex and Amira . Thank you.

The topic of today is Rapid Prototyping - the secret to Agile data warehouse design, and as Peter has shown on one of his architecture slides, we will go through these four steps. The first one to import data to transform the data and enrich them and bring it into a data world model. Data World modeling is something like the for the core layer for example of some data warehouse to have different types of data and bring them together. The first step would be that we would deploy the structures the procedures and the ssis packages and our last step is to implement these structures on a database and run the SQL. A portfolio data ESG is something that stands for environment social and governance and a regulatory reporting obligation for mainly every European company and especially for insurance and banking companies, so which data is interesting for insurance companies regarding SG? Mainly the insurance contracts and the Capital Market data they have in their portfolio, so this is one use case which we also have in our Define team with Alex together where we want to translate the regulatory requirements of ESG into some data model and therefore you used, also for example in this case, the Analytics Creator to go through these four steps and have some prototyping in a very fast way. So as I mentioned here in the in the middle you can see that I highlighted this in step two because step two is one of the most complicated steps if you would do this manually, especially if you have many tables and many sources which you have to integrate into one common data model so to write this historization procedures for example or the table statements and the view statements this would cost you much time in writing these procedures and especially if you have something like automated code or SQL code generation it helps you to invest more time in the main subjects like modeling and designing your core model instead of preparing or writing the SQL code by hand so at this point I will switch to Alex who will present these four steps in a short demo.

Okay, thank you. Today I will show you how fast it is to arrive at a prototype level of a small database with AnalyticsCreator.

We start with an empty environment in AnalyticsCreator. Let me share my screen.

We’re in AnalyticsCreator, and it’s completely empty. We begin by creating a new repository. Click on “New” and create a repository. We call it “Rapid AC,” and since we are using ESG data, we name it accordingly.

Once created, the repository is stored in my local Microsoft SQL Server, as AnalyticsCreator is connected to it. You can already see the repository “Rapid AC ESG” created here.

In the next step, I directly sync this repository to a new database. This also creates a second empty database, which will serve as our target database. All objects—tables, views, and procedures—will be created there.

After setting up the repository, we add a connector. Click “Connector,” then “Add Connector.” Here you can choose between several types: Oracle, Microsoft SQL Server, individual files, or Azure Blob Storage.

We select Microsoft SQL Server and enter the name of my local server and the source database containing the ESG data. I call it “Source Data.” Then I save and test the connection—it works, so we’re ready to go.

Now comes the most interesting step for setting up a rapid prototype. We go to “Source Data” and start the Data Vault wizard.

In the wizard, we select the connector. Since we only have one, we choose that. From this connector, I select tables from the schema “Source ESG.” This dataset contains the active side of a portfolio—for example, from an insurance company—with transactions such as stocks, bonds, and other assets. The counterpart is the issuer of these assets.

I select both, and I choose to create a Data Vault 2.0 model.

If you’re not familiar with Data Vault: it consists of three main table types—hubs, satellites, and links. The hub is based on the business key, from which a technical key is derived. The satellite contains descriptive attributes on the same granularity. The link connects different hubs.

In this case, “Portfolio” becomes a hub and satellite structure, and “Counterparty” as well. The relationship between them becomes the link.

I also configure AnalyticsCreator to generate dimensional tables based on the satellites of portfolio and counterparty. I don’t select a fact table, because I want to show how to create a transformation based on satellite data and derive measures dynamically inside the core layer.

Then I click Next.

Here you can define naming conventions for tables and historization packages. I keep the defaults and continue.

In the next step, you can define schema names. By default, AnalyticsCreator also creates standard dimensions like a calendar dimension, which contains all dates in a defined period, and a snapshot dimension. The snapshot dimension is useful for business scenarios, for example to define valuation dates for KPI calculations.

There are also default transformations for different data types. I keep these settings and click Finish.

Now the Data Vault wizard generates a small Data Vault model based on the selected source tables. If you’re familiar with Data Vault modeling, you’ll recognize the structure: staging, historized staging, core layer, and the data mart or business layer.

We can start by looking at the source data. One thing worth highlighting is the clear data lineage. For example, you can filter on a source table and immediately see how the data flows from the source through staging into the business layer.

In the first step, the source tables are imported. If you open one of them, you see the list of source attributes, data types, and so on.

In the import table, you see similar information, but you also have the option to rename attributes or define new data types. You can extract the DDL or create table script and define calculated columns.

Because I selected a Data Vault model, AnalyticsCreator has already created the corresponding hub for the counterparty. It extracts metadata from the source table—such as primary keys, key relationships, and references to other tables—and uses this to construct the model.

You can also see this “get vault hash” macro. There are default macros available, and you can define your own. This one concatenates up to 20 attributes and applies an MD5 hash to generate a hash key.

After importing, the attributes are split into business key and technical key information, which flows into the hub. The technical key and descriptive attributes flow into the satellite.

You’ll notice these boxes, which represent different packages. “IP” stands for import package, which loads data from the source into the import layer. “HP” stands for historization package, which manages historization.

If you open a historization package, you can define the logic per attribute. For example, selecting SCD Type 2 means every change creates a new row with a new validity range. SCD Type 1 overwrites the value with the latest version.

You can also access the generated procedure, extract the code, and deploy it manually if needed. Alternatively, you can switch to manual mode and modify the procedure—for example, by adding steps before or after historization or applying a different logic such as SCD Type 4.

From the satellite, the data flows into the data mart. AnalyticsCreator generates the dimensional tables automatically.

The key point is that instead of spending time modeling staging and historization, you can focus on the business logic. This typically happens in the core layer.

Now I’ll show how to add a transformation and define a fact table. This fact table will be at the level of the counterparty.

Click “Add” and select “Transformation.” Define it as a fact table and rename it to “Counterparty Investment,” since we want to calculate the investment in a counterparty at a specific snapshot date.

After creating it, we join the required tables. Starting from the counterparty hub, we join to the link table using the hub ID. Then we join the link table to the portfolio satellite using the corresponding hub ID.

Next, we define the attributes. We rename the ID to something clearer, such as “Counterparty ID.” Then we aggregate the position values from the portfolio—summing them up as the total investment amount.

Looking at the transformation, you can see the joined tables and how the snapshot logic is applied. The snapshot date must fall within the validity range of each record.

After creating the transformation, we refresh the canvas and see that the fact table has been generated.

We then adjust the dimension table—for example, renaming the ID to ensure proper relationships in Power BI.

At this point, everything is ready. We can sync all changes to the target database. After syncing, the objects from the “Rapid AC ESG” repository are deployed to the database.

Now the deployment starts.

In the next step, I add a deployment. I create the target database that will contain the data in the end. I name it “Rapid AC Deployment.”

I select the option to deploy the Data Vault by copying this empty development database to the target database using a DACPAC. I also select some configuration-specific parameters—for example, ensuring that SSIS configurations are deployed correctly.

Additionally, I choose to deploy all packages as SSIS packages to my SSISDB on the SQL Server. These packages act as execution units—they run stored procedures on the database. For example, a workflow package executes the import and historization processes in sequence.

I save and start the deployment. This takes a moment, so I switch to SQL Server to explain what happens.

A new target database is created, called “Rapid AC ESG.” At this stage, it is simply a copy of the development database. In addition, AnalyticsCreator creates a project solution for the packages, which will be deployed to the Integration Services Catalog (SSISDB) via Visual Studio.

Back in AnalyticsCreator, the deployment of the project solution is still running.

Once finished, I switch to Visual Studio. I open the newly created project solution and deploy it to SSISDB. I select my SQL Server, choose the appropriate folder, and complete the deployment.

Then I return to SQL Server Management Studio. Under the project folder, the new deployment is visible, including the workflow packages.

Before executing the workflow package, I show that the target database is still empty. The fact table currently only reflects the structure—it contains no actual data.

I verify that the SSIS configuration is correct, especially the database connection. Then I execute the workflow package.

After execution, I confirm that it completed successfully. Running the same query again shows that the fact table is now populated with data.

Next, I switch to Power BI. I connect to the SQL Server, select the target database, and import the relevant tables—specifically the fact table, the snapshot dimension, and the counterparty dimension.

After loading the data, I create a simple visual, such as a matrix. I use the counterparty name from the dimension and the total amount from the fact table.

This gives us the total investment per counterparty. Switching to a bar chart, we can clearly see the distribution of investments across the portfolio at the current valuation date.

That’s what I would call rapid prototyping. It was straightforward to implement, and we now have a working analytical model.

We can now return to the slides.

Thank you very much, Alex, for this presentation.

Okay, let’s summarize what we have done.

We created a connector to a SQL Server database and selected several source tables. In our case, this was ESG portfolio data, which is currently highly relevant in regulatory contexts.

We used the data warehouse wizard in AnalyticsCreator to define how to process the metadata from the source tables. Based on that, we generated a fully automated data warehouse with a Data Vault model and a star schema structure.

The star schema consists of fact tables and dimensions. If you were to build this manually, it would require significant effort—creating tables, implementing historization procedures, and defining dimensions.

Here, by simply specifying the metadata of the source tables, you quickly get an end-to-end data lineage. Some customization is still required, but far less compared to a manual approach.

We also created a custom transformation based on the relevant business keys—in this case, the counterparty ID—and calculated KPIs using satellite data. This resulted in a basic fact table.

The SSIS packages for orchestration were deployed to SQL Server and implemented in SSISDB. We then executed the workflow packages to ingest data from the source tables into the target structure—first into the Data Vault layer and then into the star schema.

Finally, the data was loaded into Microsoft Power BI, where business analysts can define additional structures or KPIs based on the available data.

This completes the end-to-end flow—from source tables to the business layer in Power BI.

That was the summary of the demo.

We can now move on. If there are questions, we can address them afterward, or we can first complete the remaining slides and then open the discussion.

The next slides will be covered quickly, as we have limited time.

Define is a company primarily focused on banking and insurance projects, but it is also expanding into other industries such as healthcare, energy, and additional sectors. The company is growing internationally, with a presence in countries like France, England, Italy, and Spain, and offices in Germany, Milan, and London.

Define focuses on both analytical and technical expertise, with a balanced split between quantitative analysis and technical implementation.

The company has around 1,500 employees and operates across multiple locations. Its industry focus remains strongest in banking and insurance, while continuing to expand into energy, industrials, healthcare, consumer services, technology, and the public sector.

If you have any questions about the company, feel free to reach out to me or Alex.