💾 Pt. 2 Importing data to WEBCON

In previous post we've set up a simple application for some sales tracking, now it's time to flood it with data. In this part I'll set up import using cyclic actions.

This article is split into three parts:

Example application
Import with cyclic actions - you are here :)
Import with REST API
I thought about this as one topic, but while writing article it just grew...

Database connection

If we want to access data from another database, then our first step is to add new Connection to that database. To do that we will need to create login/user/password on SQL server, or add read permissions to DOMAIN\pool.bps and DOMAIN\svc.bps WEBCON service users if you want to use Windows Integrated Authentication. In my case it's localhost\SQLEXPRESS, and the database name is AdventureWorksDW2019:

Data sources

Connection is ready, now using that connection we can query specific tables from that database. For this example we will need data from three tables - DimCustomer, DimProduct, FactInternetSales. We can imagine it could be some sales tracking system, I won't take a specific narrative on that, as I want to focus on the mechanics, rather than single use case. Here is config for those data sources:

Only thing changing are queries:

-- Customers
SELECT
    CustomerKey,
    CustomerAlternateKey,
    FirstName,
    MiddleName,
    LastName,
    BirthDate,
    Gender
FROM 
    AdventureWorksDW2019.dbo.DimCustomer
-- Products
SELECT
    ProductKey,
    ProductAlternateKey,
    EnglishProductName,
    ListPrice,
    Status
FROM 
    AdventureWorksDW2019.dbo.DimProduct
-- Sales
SELECT 
   ProductKey,
   CustomerKey,
   SalesOrderNumber,
   SalesOrderLineNumber,
   RevisionNumber,
   OrderQuantity,
   UnitPrice
FROM 
   AdventureWorksDW2019.dbo.FactInternetSales

Preparing actions

First of all we have to think about order of import, in our case, we have 3 objects - customer, product, and sale. We can't create a sale entry in system without customer, and product, so those are first ones to import.

For all imports we will go to workflow, and configure cyclic action, in the global actions menu. You can adjust the interval, and TOP limiter in queries in case you will get timeout exceptions. From what I've learned - timeout is after 120s , so running action every 3 minutes seems reasonable to me, and setting TOP to fit in that time is what you need. On fresh WEBCON install with 0 documents, running on a VM (VirtualBox) with 16GB of RAM, and 6 vCores I was able to go for TOP 400 for Customers, Products and Sales (after creating additional index on Order Number attribute).

Importing Customers

We will use Start a subworkflow action:

Make sure, to change data source to one that is using correct Connection. If your database is on another server, then consider configuring sql linked server, so you will be able to query both databases with one request. You can also manipulate that TOP limiter, depending on your system load, registered instances, and hardware it might be able to deal with more data at once.

That translating CASE could be prepared in a VIEW for example - I didn't bother to do it here, as it's not neccesary.

SELECT TOP 400
    CustomerKey AS WFD_AttText4,
    FirstName AS WFD_AttText1,
    MiddleName AS WFD_AttText3,
    LastName AS WFD_AttText2,
    BirthDate AS WFD_AttDateTime1,
    CASE
      WHEN Gender = 'M' THEN 'Male'
      WHEN Gender = 'F' THEN 'Female'
    END AS WFD_AttChoose1
FROM 
    AdventureWorksDW2019.dbo.DimCustomer
WHERE
    NOT EXISTS (
      SELECT NULL 
      FROM BPS_Content.dbo.WFElements 
      WHERE WFD_DTYPEID = 1003 AND WFD_AttText4 = CustomerKey
	)

This query selects 400 rows from external system which were not yet imported - the not exists part - and corrects Gender to make it in line with our application.

Importing Products

Products are imported basically the same as customers, but if you will watch closely there is one important difference.

There is no Path set in Basic configuration - that is due to fact, that our products might be active, or blocked - we are checking it on Status column in DimProduct table. That's pretty cool thing, and you can use it also for few other columns, as specified in documentation:

• COM_ID - company identifier,
• WF_ID – workflow identifier,
• DTYPE_ID – document type identifier,
• PATH_ID – path identifier,
• PRINT_LABEL – specifying if a bar code label should be printed,
• CHECK_PERM – specifying if current user’s permissions to add new workflow instances should be checked,
• OVERRIDDEN_AUTHOR - the overwritten author of a workflow element.

💡

Remember about F1 hotkey in designer studio :)

And here goes the SQL

SELECT TOP 400
    ProductKey AS WFD_AttText7,
    ProductAlternateKey AS WFD_AttText6,
    EnglishProductName AS WFD_AttText5,
    ISNULL(ListPrice, 0) AS WFD_AttDecimal1,
    CASE
        WHEN Status IS NULL THEN 2013
        WHEN Status = 'Current' THEN 2012
    END AS PATH_ID
FROM 
    AdventureWorksDW2019.dbo.DimProduct
WHERE
    NOT EXISTS (
      SELECT NULL FROM BPS_Content.dbo.WFElements WHERE WFD_DTYPEID = 2004 AND WFD_AttText7 = ProductKey
)

Importing Sales

This one will be more tricky, as we want to use WEBCON identifiers, to make sure that dropdowns will work correctly, so we can't just use external system id's, but we will have to map them. We should have Customers and Products already imported.

Our dropdowns are on item lists, so we will split the import into two parts:

Import data which is not on item lists.
Import data to lists.

First step is similar to previous imports, regular cyclic action.

SELECT DISTINCT TOP 400
   CONCAT(WFD_ID, '#', WFD_Signature) AS WFD_AttChoose2,
   SalesOrderNumber AS WFD_AttText8,
   SalesOrderNumber AS WFD_AttText9
FROM 
   AdventureWorksDW2019.dbo.FactInternetSales JOIN
   BPS_Content.dbo.WFElements ON CustomerKey = WFD_AttText4 AND WFD_DTYPEID = 1003
WHERE
    NOT EXISTS (
      SELECT NULL FROM BPS_Content.dbo.WFElements WHERE WFD_DTYPEID = 2003 AND WFD_AttText9 = SalesOrderNumber COLLATE DATABASE_DEFAULT
	)

Two notes on this:

I've had to set collation for WHERE, as AdvetureWorks uses different collation than WEBCON.
I'm using distinct, so only unique orders will be returned. This might not be enough in some cases to make it unique, but it's an SQL topic.

Second step - create action, which will update lists on path, which is used to register new instances.

SELECT
   SalesOrderLineNumber,
   CONCAT(WFD_ID, '#', WFD_Signature) AS Product,
   OrderQuantity AS Quantity,
   UnitPrice AS Price
FROM
   AdventureWorksDW2019.dbo.FactInternetSales JOIN
   BPS_Content.dbo.WFElements ON ProductKey = WFD_AttText7 AND WFD_DTYPEID = 2004 
WHERE
	SalesOrderNumber = 'SO45844' COLLATE DATABASE_DEFAULT
ORDER BY
	SalesOrderLineNumber

Summary

That's the end - 3 cyclic actions, one action on path, and data will be imported during the weekend, or night. Just make sure, that it's well tested on DEV environment.

Before exporting application with that kind of actions - remember to turn them off by default, you will have to activate them on other environments when it's time to do the import.

Below you can find exported application for WEBCON 2023.1.1.89

Data Import Example 2023.1.1.89

Data Import Example 2023.1.1.89.bpe

65 KB