AnalyticsCreator Congress 2022 I Introduction AnalyticsCreator from the green field

There are two ways to add a connector. We can add a standard connector and select an existing data source, such as SQL Server, flat files, Excel, SAP, Azure Blob Storage, or other supported sources. AnalyticsCreator can also read metadata directly from an SAP system.

In this case, I will use a metadata connector. A metadata connector contains predefined information about the tables and columns in a specific data source, stored in the AnalyticsCreator cloud.

You can also create your own metadata connectors. For this demo, I will use the SAP FI metadata connector to create a new SAP-based data warehouse.

Here we can see the tables related to SAP FI. This metadata connector is available for use and provides the table information needed for the data warehouse model.

Next, I connect the metadata connector to an existing SAP system. The SAP system is provided by one of our business partners. To establish the connection, I need to enter the connection string.

To avoid storing passwords in plain text, AnalyticsCreator supports encrypted strings. I can create an encrypted string called “SAP password”, store the password securely, and then use this encrypted string in the connection string.

The connection is now established, and the metadata connector is bound to the existing SAP system.

Now I will create the data warehouse using the Data Warehouse Wizard. The wizard gives me two options: I can read the metadata directly from the SAP system, or I can use the metadata already stored in the metadata connector. In this case, I will use the metadata resources from the connector.

I select several well-known SAP tables, including booking headers, booking positions, customers, vendors, accounts, company codes, document types, and document type texts. The data warehouse will be created based on these tables.

For each table, I can decide what should happen to it. I will import the sources, historize the data, and create dimensions. I will also create the fact transformation using the booking position table.

There are two tables for document types: T003, which contains the document types, and T003T, which contains the document type texts. I will combine these two tables later in the staging layer.

To do this, I create a transformation. It is not necessary to historize the text table separately, because I can combine it with the document type table in the transformation.

On the next screen, I can define object names and additional properties. After I finish the wizard, AnalyticsCreator analyses the metadata stored in the metadata connector.

The result is a typical data layer diagram, shown from left to right. It starts with the source layer, followed by the staging layer, where data is imported.

The persisted staging layer contains historized data or slowly changing dimension data. The core layer contains transformations and is used to transform the imported data into facts and dimensions. The data mart layer serves as the interface layer of the data warehouse and exposes the final structures for reporting and analytical models.

Table T003 contains information about the document types, while table T003T contains the document type texts.

When I view the data in T003T, I can see a language column. Since I need the English text, I use the condition where language equals “E”. For each document type, the LTXT column contains the document name.

I will use this table to extend the T003 table.

In the transformation, I add table T003T and use the reference between T003 and T003T, which is already known from the metadata connector. I also add a filter where the language is equal to “E”.

With this transformation, I add the text column and call it “Text”. This means I have combined two tables in the staging layer and added the document type text to the T003 transformation.

AnalyticsCreator prepares the view automatically. The transformation is usually a view, and this view combines T003 with T003T to extend the document type table with the relevant text.

Next, I synchronize the model. When I click the synchronize button, the prepared model is materialized, and the empty data warehouse database is created on SQL Server based on this model.

Now I will add more logic to the data warehouse.

The booking header and booking position tables are very large in the source system, so I will restrict the amount of data imported.

In the data input definition, I add a filter using the business year. I create a variable called “Year” and import only data for the year 2020.

This variable can be changed before starting the generated Integration Services package. I also add the same filter to the booking position table.

After the data is imported, it will be historized. I will not go into the details of import and historization here, because there are separate videos available that explain how this works in AnalyticsCreator.

Next, I will extend the fact transformation with additional columns. In AnalyticsCreator, a standard transformation is represented by a blue object. The fact transformation was created by AnalyticsCreator, and I will now extend it with the document type table, T003.

The tables are joined and referenced to BSEG, the booking position table, and used in this transformation. I add the document type table and define the required reference.

Next, I add the calendar dimension. The calendar dimension was created by the Data Warehouse Wizard. It is a view, and I will use it in the transformation.

In BKPF, there is a BUDAT column, which represents the booking date. In SAP, this date is stored as a string, so it needs to be converted into a key from the calendar dimension. To do this, we can use macros.

A macro is a reusable SQL statement where parts of the statement are replaced by placeholders. For example, one macro converts a date or datetime value into an ID from the calendar dimension.

There is also a macro called “SAP date to ID”. Since an SAP date is stored as a string, it first converts the string into a datetime value using style 112. It then calls the date-to-ID macro to convert the datetime value into a calendar dimension key.

Now all dimensions are included in the fact transformation, so I will add measures from the table. The fact transformation is now complete.

I will synchronize the data warehouse again. During synchronization, AnalyticsCreator creates the database structure defined in the model. Each dimension is now connected to the fact table.

I will also persist the fact transformation because it is quite complex. Persisting the transformation improves access performance because the content of the view is stored in a table during processing.

Next, I look at the data mart layer. It contains views that retrieve data from the core layer transformations. Friendly names are important here because they are used in the OLAP cube as the names of the relevant tables.

In the measures section, we can see the measure names. You can define your own measure names or use measure name templates. Measure names in analytical models should be unique, which becomes difficult when working with hundreds or thousands of measures. Templates help define consistent and unique measure names.

I will add more measures, such as distinct count of vendors and distinct count of customers. These measures will be used in the Power BI model.

I will also declare every reference in the Power BI model as a double-sided reference.

Another useful feature is display folders for attributes. This is especially helpful for SAP because tables such as BSEG can contain more than 200 attributes, making it difficult to find the right fields. Display folders help organise attributes in the Power BI model.

The model is now ready, so I will create the deployment package.

I create the deployment package because AnalyticsCreator will now generate a Visual Studio solution. The project will be deployed on-premise.

For this deployment, I will create and deploy a DACPAC file. I will use SQL Server 2019 compatibility level for the database. I will also create Integration Services packages, process configuration, an XMLA script, and an OLAP cube during deployment.