Database Tables


Your Epicor CPQ license comes with your own dedicated, optional Microsoft Azure SQL Database in the cloud, which you can use as a source of data for your configurators and integrate with other business systems. Epicor CPQ's database service allows you to...

  • Create, read, write, and delete your own custom tables.
  • Create, read, write, and delete your own custom views, which can use SQL logic to filter and join data from your tables.
  • Read standard tables that are part of the core Epicor CPQ implementation.  These tables are always up-to-date, and list information about Users, Quotes, Configured Products, Roles, and the like. You cannot update these tables through a database connection.

Example uses of Database Tables

  • Create a custom table "Parts"
    to store basic information about components which could be used in your configurations, such as SKU, weight, and cost.  This table you may integrate with your own ERP or PLM system.
  • Create a custom table "Parts Images"
    to store references to an image for some parts.  This table you may maintain manually, with feedback from your marketing group or web team.
  • Create a custom view "Active Parts"
    which could filter only the rows from the Parts table that meet certain business logic, and join to the Parts Images table so the respective image for each part can be used.  This view would be the source for option filters or database queries in your Configurators.
  • Read the standard table "Users"
    from time to time as part of an internal security audit, to find unused logins.



Some points to remember:

  • Many implementations don't use database tables at all, opting for local tables instead. They are easy to administer and are often better suited for most purposes. Since Epicor CPQ has a unified query language, you can easily start your design based on local tables, and move to the power and flexibility of database tables later, when you need to.  
  • Database tables and views are shared throughout your Epicor CPQ dev, test, or production environment and can be used by any Configurator, Quote, Workflow, Scene, or Safe Function.
  • Database tables are not shared across the dev, test, and production environments. For example, your test data remains in the test environment.
  • You can integrate your Epicor CPQ cloud database with on-premise databases and ERP systems by using many widely-available middleware products.  Contact us for our current recommendations on middleware providers and connectors.


Requesting your database tables

To request database tables and have your database login created, please contact the EpicCare support desk.

Your login gives you full permissions to two schemas in your database. (A schema is a way to logically group together items, like a folder in a file system.)

  1. A schema named 'x',
    which is completely under your control.  You can add as many custom tables and views as you like.  Once added, these custom tables or views can be used as a data source for option filters, auto-complete fields, pricing rules, and custom queries written in Snap rules.  These tables and views will show up automatically in theses sources after being added.
  2. A second schema named 'dbo',
    which is read-only.  The tables found here can be used to learn more about the status of Quotes, the last login of Users, and other such administrative info.  In most cases, you can find the same information more easily through our web service API.

While you can connect to your database in many ways, step-by-step instructions are available for two free tools:

 

Database Security

Legacy keywords for search engines: blacklist, whitelist, black list, white list.

Your optional database tables and views are protected through firewalls and a separate password.

Database Firewalls

Allowing access through the CPQ database firewall
Our firewall ignores any inbound request to your CPQ database, unless it comes from an IP address you have specifically added to the firewall rules within Epicor CPQ.

Allowing access through your network firewall
Your own firewall may also need to allow outbound access to CPQ. Learn more about suggested settings for your firewall.

Database Passwords

Once allowed through any firewalls, any database session must authenticate with a special Epicor CPQ database admin username and password, separate from any Epicor CPQ user account. Credentials and endpoint information to access your database are provided when you first request your optional database tables.

 


Was this article helpful?