A Data Analyst, Data Scientist, Data Engineer or SAP BW Developer needs to load data from the SAP Business Warehouse (SAP BW) into Google BigQuery.
The scope of this user guide is to assist the user through all aspects of setup and usage of the SAP BW EasyConnect solution.
The SAP BW EasyConnect is a data pipeline tool purpose built for extracting data from the SAP Business Warehouse into Google BigQuery.
The solution enables users and organisations currently using SAP BW to seamlessly extend existing BI scenarios for usage with the state-of-the-art data analytics offered provided by Google BigQuery.
SAP BW EasyConnect allows SAP BW teams, analysts, data engineer and scientists to leverage existing skills and BEx outputs extract trusted, analysis-ready data for enriched processing using the divers and exciting options made available by Google BigQuery.
The solution is procured from the Google Marketplace and deployed directly into your cloud infrastructure. With a monthly flat-subscription, there is a simple billing model permits unlimited usage to responsively meet the needs of your organisation.
SAP BW EasyConnect natively connects to the SAP Business Warehouse using SAP NetWeaver certified interfaces to provide the fullest possible integration with the existing SAP BW data model and BEx query portfolio This allows your team to focus on creating value-adding analysis with existing, trusted business data sources in SAP.
The solution is deployed into your Google Cloud Platform Project as a VM from the Google Marketplace.
Once deployed there are three steps to be completed in order to use the service:
- Obtain license (14-day trial version available) from www.axi.ai/demo
- Setup SAP BW
- Setup SAP BW EasyConnect environment
Obtain software license
A valid EULA or trial license must be obtained for usage.
This can be obtained by submitting a request at: www.axi.ai/demo
Setup SAP BW
An AXI AI custom Function Module should be installed to overcome certain limitations in SAP BW extraction.
SAP BW EasyConnect environment
Once the SAP BW EasyConnect solution has been deployed into your cloud, and prior perquisite steps completed, the service is available for usage.
There are 2 aspects to setting up the service:
- Setting up users
- Setting up connections to GCP BigQuery
Setting up users
Admin user credentials to login are provided upon provisioning the solution from the GCP Marketplace. These should be used to entered to login to the application.
To change the password, log in to the admin panel with the same admin credentials that are provided on initial deployment.
Once logged in to the admin panel you can change your password by clicking on the Change Password button as shown below. This will ask for your old and new password.
To manage users just click on the Users link under Profiles as shown below.
Setting up connections to GCP BigQuery
SAP BW EasyConnect uses GCP Service Accounts with the appropriate permissions for using BigQuery. A JSON credentials file for the service account may be downloaded from GCP which is used by SAP BW EasyConnect.
So the general steps for setting up a Google BigQuery connection are:
- Create a service account with access to the Google project and with BigQuery permissions and download the JSON credentials file.
- Setup the service account credentials inside the SAP BW EasyConnect service.
Setting up connections to GCP BigQuery
Google documentation on creating a service can be referred to for further details relating to this section.
Open the service accounts page in Google Cloud Console and select your project, if you haven’t created a project create one first.
Click the Create Service Account button.
Fill out the form as follows::
- Service account name: Name the account something like “AXIAI BQ.”
- Service account description: A good description of the service account’s purpose of creation.
- Then click Create
On the service account permission page grant the following role:
Click Create Key in the next page and select JSON as Key Type.
Download the JSON key. Be sure to remember where you downloaded it as you will need it later on.
Setup the Service Account in SAP BW EasyConnect
Service Accounts are managed through the Settings link on the sidebar:
Add Service Account
In order to add a new service account which you created in the GCP console, click the Add Service Account button shown (if you have not yet created a service account go back to Setup GCP BigQuery Connection Step)
Fill in the description of your GCP service account and click the browse button to choose the file from your local machine. Once you have chosen the service account JSON key file click Add and it will upload your service account.
Once uploaded you will see the newly added service account on the service account table in the Settings tab.
Update Service Account
To update an already added service account, click on the service account you would like to update then click the Update Service Account button.
You can change the service account description and/or the JSON key file and once done click the Update button.
Delete Service Account
To delete a service account, click on it in the service account table then click the Remove Service Account button.
A popup should ask for your confirmation to delete. If you want to continue with the delete click Yes and the service account will be deleted.
SAP BW to Google BigQuery Data Flow
Connections between SAP BW and Google BigQuery are implemented using ‘Flows’.
To create a new SAP BW to BigQuery flow, click on the SAP Connect tab on the sidebar.
And then click on the Create Flow button in the flow listing page.
Once you click Create Flow you will see the Setup SAP BW Connection page. Fill out the form with the requisite information and choose the GCP service account from the dropdown at the end which shows all the service account files that have been added. Finally click Connect to complete the setup:
Once you have clicked Connect and the connection has been established you will need to choose the data source type. Currently, only the BEx Query is supported and will be the default. Click Next to proceed:
In the next page, you will be asked to select the BEx query, you can use the search field to narrow down your selection, then click on the query object you would like as shown below. Once you have selected a query object, click Next.
In the next step you will need to configure the query, you will see all the variables, characteristics and key figures. As there may be several options to configure make sure to scroll to see them.
Once you have configured the query click Next.
The final step is to enter the BigQuery details where the data will be loaded. Choose the Project, Dataset and Table then enter the flow name at the end which will be used to identify this flow at the flow listing. Click Finish to complete the flow and have the data loaded.
This may take some time while the data is loaded into BigQuery. After it is done you can preview the data or view the data in BigQuery itself in the GCP console.
Clicking Home will take you to the flow listing page where you should see the flow you just created.
On the flow listing page you can choose to run a created flow. To do that simply click on the flow you would like and click on Run Flow.
After you have click on Run Flow you should see that the flow chosen is running as shown:
On successful completion of the run you should see a popup stating that it was successful.
To schedule a flow to run at a specific time of your choosing, click on the flow then click Schedule Flow
This will present you with a dialog box to choose the time you would like to schedule, the frequency, the days of the week and an option to send failure notifications to you.
To update a flow, choose the flow on the flow listing page and click on Update Flow.
This will present you with the Data Load page where you can change the details and click Finish to update the flow
The flow listing page should also refresh with the new details.
To delete an existing flow, choose the flow on the flow listing page and click Delete Flow.
This should present a dialog box asking you to confirm that you would like to delete the flow. If you are sure click Yes.
If you clicked Yes the flow will be deleted and the flow listing page will refresh with that information
ML Model Flow on SAP BW EasyConnect Plus
If you are on the SAP BW EasyConnect Plus version you will have the ML Model feature available.
Clicking on the ML Models opens up 2 options: Model and Prediction. Model option allows you to create models.
Click on Create Model to be able to create a classification or regression ML model.
The first step in creating a model is to specify the training data. You will need to fill in the form with your GCP service account, project id, dataset and table.
Next, you will select the prediction field from the table you chose. Choosing this field determines whether the model will be a regression or a classification model.
Then choose the predictor fields that you want to use in training your model.
You can specify the training split and sample size if you prefer not to use the default of 70% and 100% respectively. Click on Advanced Settings to reveal these options.
The last step for creating a model is to choose the name.
After clicking finish, the model is generated and the evaluation metrics will be displayed.
Clicking Back will show the model listing. Each model that you create will be listed here. You can update and/or delete the model from this page.
To generate predictions on a new dataset using a created model, click on the Prediction option under ML Models in the sidebar.
Then click Create Prediction.
The first step will be to choose the model with which to generate predictions.
Then you will be able to select input table. This table should be missing the field you wish to predict the values of.
The output table will need to be created.
Clicking on Create Table will reveal a popup where you can enter the name of this new table.
Thereafter you will be able to enter the name of the flow. Clicking Finish will use the model to generate predictions and populate the selected output table.
You will be able to view the predictions by clicking View Data in GCP BigQuery.
On clicking back the prediction flow will then be listed on the prediction lisitng page alongside other prediction flows. On this page you will be able to run, schedule, update and delete the prediciton flow similar to steps in the SAP connect flow above.