New connector for exporting data from data warehouse to many target systems

Peter: AnalyticsCreator is a metadata-driven design application for data warehouse automation. It is designed for experts, but it can also be used by people with less technical experience. Instead of programming everything manually, AnalyticsCreator generates source code from the model.

AnalyticsCreator supports the full lifecycle of a data warehouse, data mart, and data lake, including design, development, maintenance, change management, and deployment. Common use cases include greenfield projects, modernisation of existing data warehouses, SAP integration, cloud migration, near-real-time platforms, and partner-managed services.

Our vision is independence. AnalyticsCreator is a pure design-time application. It generates source code and places it in the Microsoft environment. At runtime, no AnalyticsCreator component is required. The generated data warehouse continues to run even if the AnalyticsCreator subscription ends.

Dimitri: I will now show the new export functionality in AnalyticsCreator.

Usually, we import data into the data warehouse, transform it, and create analytical layers for reporting. Sometimes, however, we also need to export data from the data warehouse into external databases, text files, or Azure Blob Storage. This can now be automated with AnalyticsCreator.

I create a new data warehouse model called Test Export. The repository contains the definition of the data warehouse. For this demo, I use the Microsoft AdventureWorks 2019 database and create a SQL Server connector to it.

Dimitri: I start the Data Warehouse Wizard to create a draft data warehouse quickly. I select tables from the Human Resources schema. The wizard imports the tables, historizes them, creates dimensions, and creates facts from selected tables.

AnalyticsCreator analyses the source structure and prepares the model. The generated diagram shows the layers from left to right: source, staging, persisted staging, core, data mart, and later the new export layer.

The source layer contains the source tables. The staging layer contains the tables where data is imported. The persisted staging layer contains historized data. The core layer contains transformations, usually generated as views. The data mart layer contains facts and dimensions for analytical models such as OLAP cubes, Power BI, Tableau, or Qlik.

Dimitri: The import definition maps source columns to target columns. Filters and variables can be added to restrict imported data or support differential loading. Scripts can also run before or after the data input.

Historization can be configured per field using slowly changing dimension concepts. Full historization stores the history of changes for a column. SCD Type 1 updates the current field value without storing the previous values. AnalyticsCreator generates stored procedures for historization, and these procedures can be modified if required.

The core layer contains transformations. For example, the Department dimension is generated as a SQL Server view. More complex fact transformations can combine tables such as Employee Department History, Department, and Shift.

Dimitri: When historized tables are joined, the logic becomes more complex because each primary key can have several historical versions. To solve this, AnalyticsCreator uses snapshot historization.

The snapshot table contains at least the current date. For every historized table, AnalyticsCreator retrieves the data valid for a specific snapshot date using the condition that the snapshot date falls between Date From and Date To. If more snapshot dates are added, such as the end of previous months, the model can provide access to historical versions of the data.

I also add persisting to a complex fact transformation. Persisting stores the output of a transformation view physically in a table to improve performance. AnalyticsCreator generates the stored procedure for persisting and supports full, merge, incremental, and other persisting approaches.

Dimitri: Now I will show the new export functionality.

I export data from the dim Department object in the data mart layer into a text file. First, I create a new CSV connector. I can define additional properties for this connector, but for the demo I use the default settings.

Next, I add an export and select the CSV connector as the target. I can either select an existing target object or create a new one. When exporting to files, AnalyticsCreator can create the target files automatically. When exporting to a database, the target table should already exist.

The export definition mainly contains the mapping between source columns and target columns. An additional filter can also be added.

Dimitri: AnalyticsCreator now adds a new export layer to the data warehouse model. This layer contains the CSV export files where the data will be written.

I add two more exports for the Employee dimension and the Shift table. Then I define the folder where the files should be stored. This path can later be configured through the SSIS configuration table, but I can also enter initial values in the model.

Next, I generate the deployment package. For this demo, I only create the database and Integration Services packages. AnalyticsCreator creates a Visual Studio solution containing the deployment package, the DACPAC file, and the SSIS packages.

The generated solution contains a workflow package, which executes all other packages in the correct order. First, the import package runs. Then the historization package runs. After that, the export package and persisting package can run in parallel.

Dimitri: I start the workflow package. The import, persisting, and export steps finish successfully.

In the export directory, I now have three CSV files: Department, Employee, and Shift. This shows how the new export functionality works.

You can export data from different objects in the data warehouse, not only from the data mart layer. Supported targets include databases, text files, and Azure Blob Storage. For external databases, the target tables should already exist. SAP export is usually not possible because SAP connectors often support read access only, not write operations.

If you use Azure Data Factory instead of SSIS, AnalyticsCreator can also generate Azure Data Factory pipelines for the export process.

Dimitri: The second new feature is the OData connector.

I use an OData test server with a Northwind database as an example. AnalyticsCreator can now import data from OData sources in the same way as from other source systems.

OData can be useful with SharePoint because SharePoint lists often provide an OData interface. Some SAP installations also expose OData interfaces, so this can be another way to import SAP data if the Theobald connector is not used.

Peter: Thank you, Dimitri. The export functionality is simple to use, but very useful. If you export to text files, the files do not need to exist in advance. If you export to database tables, the tables should already be prepared and added to the connector.

You can try this functionality by requesting a trial version on our website. If you need help, we will support you.

Thank you for joining us. Our next presentation will be announced on the website, and the AnalyticsCreator Congress will take place on 10 November. You are welcome to join us there as well.