The DriveWorks CPQ template is perfect for working with and automating company systems. Out of the box, the DriveWorks CPQ template uses Group Tables to store data. However, this is not an ideal deployment scenario.
Group Tables are not designed to handle large amounts of data and large numbers of concurrent users. To overcome this problem you should use SQL Server. SQL Server databases can handle large numbers of users and large amounts of data. This makes it a perfect storage place for CPQ and company data.
In this Help File topic we will look at how you create an integration between the DriveWorks CPQ template and an SQL database.
DriveWorks has native support for SQL Server connections. This makes it really easy to integrate the DriveWorks CPQ template with an SQL database.
It is possible to create a basic integration with SQL by entering an SQL Query into a View's List Query.
This can be done by navigating to Dashboard > Admin Panel > View Manager. Select your View and enter a new List Query for the View.
QueryDataValues( "Server=MACHINE-NAME\SQLEXPRESS;Database=Customers;"@"SELECT [CustomerName],[Address] FROM [Customers] WHERE ID <> @(DWConstantRefreshID) ORDER BY [CustomerName] ASC", "", "", TRUE)
This query gets data from the Customers table in our SQL database. The WHERE clause is being used to refresh the data. The WHERE says find everything in the table where the ID does not equal the RefreshID. Because the RefreshID is negative, it will return every value because the ID column only contains positive values.
This basic integration will work but it has some limitations. This SQL Query is not efficient and it will slow down with large amounts of data. A rule like this could potentially cause a slow implementation.
To overcome this, you need to modify your ListView Project to make it SQL ready.
An advanced integration will improve this SQL integration. You will be using SQL Queries that will improve performance and only get the required data.
Step 1. Create a ListView Project for SQL
Step 2. Create your SQL Query and View:
To improve performance, your query must only get the required data. You need to write a query that limits the amount of data obtained.
QueryDataValues( "Server=MACHINE-NAME\SQLEXPRESS;Database=Customers;", @"SELECT [CustomerName],[Address] FROM [Customers] WHERE ID <> @(DWConstantRefreshID) ORDER BY [CustomerName] ASC OFFSET @(IfError(( PagingReturn-1)*30,0) ) ROWS FETCH NEXT 30 ROWS ONLY", "", "", TRUE)
This is the same query as before but it is now using an OFFSET and FETCH clause. With this clause it is now possible to fetch a page or window of results. This allows us to be a lot more efficient with the data being returned. This means we can return only the results needed for a page and no more.
This query uses the PagingReturn value to calculate what data to show. This is a control value within the ListViewSQL Project. The value determines what row the query starts at and what data is returned.
Creating your View:
Step 3. Modify your ListViewSQL Project to handle SQL data
The query you just created relies on the ListView's page number and needs to refresh every time the page is changed. By default the ListView does not do this.
To modify this behavior, you need to carry out the following:
When you now select the next or previous page the data will be refreshed from SQL.
Step 4. Modify Table Data Variables
In the ListViewSQL Project there are several Variables that are used to produce the final table of data. Since we are using SQL, some of these Variables are no longer required.
To remove these Variables, we are going to bypass them by changing one Variable. Follow the steps below:
Step 5. Create a button to access your View
A button is required to access this new View. Use the View Buttons View in the CPQ template to add a new button to a View. You can find out more in View Buttons.