Understanding Databases

SQL Database Tables store data in Epicor CPQ. Different from "Local Tables" which are fast and small to store general information, Database tables are slightly slower, but can store large amounts of sensitive information securely. They are a common source of data for Safe Functions, Option Filters and Queries in configurators, scenes, workflows, and other code.

Understanding Data Storage Options

Tabular data (a grid of data, stored in columns and rows) can be stored in various ways in Epicor CPQ.   

• "Local" Tables 

  Local Tables are quick to create and edit under Resources > Tables in the admin interface. No database or SQL skills are required. They are easy to update: just edit each cell through the admin interface, or copy and paste thousands of rows from your favorite spreadsheet. Known as "local" tables, these are downloaded to your user's device when the configurator or scene loads, so they are in-memory as your customer uses the configurator and their response time is fast.  However, they should not be big (your user would be confused by the long loading time). They should not contain sensitive data.

• Database Tables

  Database tables require a separate tool to create and edit, and knowledge of SQL can be helpful. They require more effort to create or update, but accept different kinds of source data: you can upload CSV or Tab-delimited data into them.  These tables are not downloaded to your user's device: rather, they remain in the cloud and only the specific data required is sent when needed to your user's device.  In this way they can handle very large tables (millions of cells), and they can store sensitive information. Learn more about database table uses and benefits.

• "Local" Tables sourced from Database Tables

  If you have information coming from an integration stored in a database table, but it's not sensitive and you need it with fast response times, use this technique.  By sourcing a local table to a database table, it will automatically refresh when the database table changes.

Use this comparison of the table storage options to see which is best for your needs.

Places to Use Table Data	Local Tables (no source)	Local Tables (database source)	Database Tables
Ways to use tabular data
I'm not sure where to start.  Where's the best place to begin storing tabular data?
Store choices to appear in option filters.
Store results for queries written in Snap used on the user's device, such as in a configurator or scene.
Store results for queries written in Snap used in the cloud, such as in a workflow rule, pricing rule, or safe function.
Store options for auto-complete fields (help your user find an item from a long list, such as a city or state for a shipping address)
Table Maintenance
Manually update the data by copy/pasting cells from Excel.
Manually update the data by uploading a file, such as a CSV, SQL, or tab-delimited datafile.
Automatically update the data using my company's integration platform from our ERP, manufacturing system, or other internet-connected database.
Types of Data
Store text, numbers, date/time values, colors, boolean values, or links to media.
Store sensitive or proprietary information (such as component cost or supplier), so a configurator can use the data, but it remains secure.
Store public information, such as part dimensions or descriptions, for fast retrieval so the configurator responds quickly.

Key: Green checkmark means this is recommended. Yellow warning means this technique is possible, but may have slower performance or security concerns, depending on the data stored in the table.

Understanding Database Tables

Your Epicor CPQ license comes with your own 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 list information about Users, Quotes, Configured Products, Roles, and the like. They are updated automatically: 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 on the server can be used just like a table in your configurators. It could 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.

Technical Details

Some points to remember:

• Many implementations don't use database tables at all, opting for local tables instead. They are easy to administer, and they are often better suited for most purposes that have smaller datasets.
• Since Epicor CPQ has a unified query language in Snap, you can easily start your design based on local tables, then scale up to the power and flexibility of database tables later.
  
• Database structure and data (or "schema") are shared within your Epicor CPQ dev, test, or production environment and can be used by any Configurator, Quote, Workflow, Scene, or Safe Function.
• Database schema is not shared across the dev, test, and production environments. For example, deleting a table or a row from a table in Dev does not cause that same deletion in Test or Production. If you want to have the same table structure or data appear in multiple environments – like across Dev, Test, and Production – you must create the tables and load them each environment. 
• Our deploy feature that migrates behaviors from one environment to another does not include database schema or data. This means you can update your databases when you want, as often as you want, separately from your deployments.
• 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.
• Custom database tables can be read/written through integrations only, not through our Rest API. 
• Stored procedures are not available.  For encapsulating business logic shared across multiple configurators, try hosting that code either within Epicor CPQ as a Safe Function, or outside of Epicor CPQ as a web service for Epicor CPQ to consume. 
• We provide a basic database management tool in the admin interface, but you are not limited to it. If you or your colleagues are more familiar with industry-standard tools, you can use Microsoft's SSMS or Azure Data Studio.
• To move data into or out of your database tables, you'll need an external tool.
  You cannot "replicate" database tables on their own.  Instead, set up an automated process outside Epicor CPQ, such as an ETL tool, that will move the data back and forth.  Most larger corporations already have an ETL tool in-house, which means you can continue using the tools you know, rather than having to learn and maintain something new.