AnalyticsCreator Congress 2022 I Using the full Power of Power BI Premium

The solution includes an import package, which imports all required tables from SAP. For the SAP import, we use the Theobald connector. Packages that use this connector require a Theobald licence.

There is also a historization package. Historization is performed through stored procedures, which are executed as part of the package flow. In addition, the solution includes a persisting package that persists data from the fact transformation.

I will now execute the workflow package.

The DACPAC file has been generated and will now be deployed. The import package has also been generated.

I will open the package and start it. The import package now runs and imports data from the SAP system. The largest table is BSEG, which contains the booking positions. The import has finished, historization is almost complete, and persisting is being performed.

The data has now been loaded. When we check the data warehouse, we can see that the tables have been filled with data from SAP.

Next, I will process the Analysis Services database. The processing completes successfully.

Now I will create a small Power BI Desktop report using data from Analysis Services. Here we can see the OLAP model.

I can select company codes, company keys, document types, customers, vendors, and measures. For example, we can analyse which company works with which customer and view the corresponding amount measure.

Power BI reports can be created directly on top of the data stored in this tabular OLAP model. In this example, we only loaded data for the year 2013. The report can be filtered by specific companies and other attributes. This is a typical Power BI report built on the generated data warehouse.

So far, we have created a fully on-premise solution. I will now transfer the same solution to the cloud.

First, I need to add one new property: a description property for the document types. Then I will create a new deployment package that deploys the data warehouse into the cloud.

The model itself does not need to be changed. The same model that was deployed on-premise can also be deployed to the cloud.

I will create a new deployment package and call it “Deploy Cloud”.

The package will create the DACPAC file and deploy it to Azure SQL Server. The server name and database name are defined, and I will use standard security.

Next, the OLAP cube will be generated. I will show the Microsoft Power BI workspace. This is an empty workspace called AV, where we will create a new data source.

Because this is Power BI Premium, we can access the Power BI Premium XMLA endpoint. We will use the XMLA endpoint address here. It is important to use the Power BI compatibility level.

The model will be deployed into the cloud, while the Integration Services packages will continue to be used for extracting data from SAP.

If an Azure Data Factory pipeline is available for the required SAP import scenario, AnalyticsCreator can generate Azure Data Factory pipelines instead of Integration Services packages.

I will open Visual Studio again. This is the most recently generated project. I will execute the workflow package, which exports the data from the SAP system into the Azure database.

The import of the largest table has finished. Historization is almost complete, and persisting has also finished. The full process is now complete.

Now let us look at the Power BI workspace. In this workspace, we have a new data source called Demo SAP, which was generated just now. Usually, the next step is to confirm the data source credentials by signing in. After that, we refresh Demo SAP.

I will now connect Power BI Desktop to this cloud Power BI model. Here we have the same model stored in Power BI.

Let us create a report. We select company, customers, vendors, document types, and measures. This is a typical Power BI report.

The important point is that the same model used on-premise has now been deployed to the cloud. I can publish this report to Power BI, and the report works as expected.

One more feature I will show is data export. For example, I will export data from the data mart layer into Microsoft Azure Blob Storage.

First, I need to create a new connector. This is the Azure Blob Storage connector. I add the storage account and connection information.

In Microsoft Azure Blob Storage, we have an SAP directory, which is currently empty. We will export data from the SAP data warehouse into this storage location.

To do this, I can take the customer table from the data mart layer and add an export. The data will be stored in a customer directory with “customers” as the file name. I will create an export package and do the same for vendors.

Now I have export tasks that will export objects from the data mart layer into flat files: SAP customers and SAP vendors.

I will create a deployment package. In this case, it is not necessary to create a DACPAC or an OLAP cube. I only need to generate the packages.

There is an error caused by the configuration. The export packages can be generated, and they can be used to export data to Azure Blob Storage or to another database. However, I cannot connect to Blob Storage right now because there is an issue with the storage configuration.

After correcting the issue, I execute the workflow package containing the export packages, and the export works. In Blob Storage, we can see two files: customers and vendors.

Let us open one of the files. This is a CSV file containing customer information. This shows how the export to Azure Blob Storage works.

There is a question from Tarek: could I show again where Power BI measures are set up in AnalyticsCreator?

The measures are defined in the fact transformation. Here I can create measures using tabular statements or DAX statements.

For example, I will add a new measure called “Rows”. I can use standard aggregates such as sum, min, max, and distinct count, or I can define my own tabular statements to create measures.

Finally, I will show the Power BI report that I modified.

We exported data from SAP FI and Controlling into the Power BI model. Here we can see customer payments, payment issues, and related information.

This is a typical report. For example, if I want to understand what happened with a specific material, I can see the related document types. If I select receipts only, I can see the corresponding vendor and all documents related to that material.

This type of report is common, but AnalyticsCreator makes it much easier to create the underlying data warehouse and Power BI model. That concludes the demonstration