Using views in RPI with SQL Server

Overview

This topic provides information and guidance about using views within Redpoint Interaction (RPI) when paired with SQL Server. This topic will help guide both an RPI user as well as a database administrator (DBA) in the right direction when setting up how RPI will interact with the SQL Server database.

Using views within RPI

Since RPI is the orchestration tool that sits on top of an underlying database, in this case SQL Server, RPI has the ability to see and also use any tables or views that have been created within that database.

Definition of views in RPI

Views are virtual tables that are created based on the result of a SQL query. They allow you to encapsulate complex queries and present data in a simplified or customized way without storing the data physically in the database.

Considerations of views

When using views, consider the following:

Database size: Larger datasets will cause slowness when using views within RPI.
Query complexity: Complex queries can be handled better by creating a view for RPI to use. This will alleviate campaign complexity in the end.

Benefits of using views in RPI

Data Simplification: Complex queries and joins between tables can be encapsulated in a view, making it easier for users to access data without needing to understand the underlying complexity.
Data Security: Views can limit the exposure of sensitive data by restricting the columns or rows available for querying.

Drawbacks of using views in RPI

Performance Overhead: Views in RPI, especially when used with complex queries involving multiple tables and joins, can become slow if not properly optimized. Since views are often just saved SQL queries, the performance depends heavily on how well the underlying query is optimized. Views that rely on non-indexed columns or involve complex computations can slow down query performance.

Performance may not be an issue at small volumes, but can cause slowness as the dataset grows in size.

Best practice recommendation

While views can simplify data retrieval and enhance security in RPI, they can also introduce performance issues. When using views within RPI, the performance issues outweigh the benefits due to the added processing time that is needed to successfully run a given interaction.

However, there will be cases when a view is needed to create the right dataset to successfully run a campaign. In this case, as a best practice, we recommend the following:

A DBA creates a view within the database.
Use that view to create a physical table.
Use that physical table within RPI to expose the correct data to use for interactions, etc.