Introduction

BD Select is a product in Bettrdata used to allow for select and count functionality using an intuitive interface. Once set up, the tool allows you to select criteria among any of the configured columns to allow for dynamic select of count and bulk data.

References

General

Scribe Add and Copy OR Tags to Create Segments | Scribe

Snowflake

Fabric

Manage Data Sources Configuration

For the BD Select data, we will need to create a datasource. This is a table or view on one of the available database types. Additionally, any of the other connection information will need to be configured.

Base Connection Setup

To create the base datasource, do these set of steps:

1. Log into your bettrdata instance
2. Click on the Data Sources link on the left navigation bar
3. Click on the BD Select Sources link on the top navigation bar

4. In the window that appears click on the Create Select Source button.
5. A Dialog box appears. In the dialog add some details
• Name: The name you want to name the data source. It should be a unique name
• Description: Add a description
• Table: Add in the table or view that was created

6. Click Create to Finish. A new datasource will be created under the Select Sources section in the dropdown.

Connection

Once the base connection is created, enter the connection details

1. Click on the datasource to finish the configuration. This will bring up the connection tab. Depending on the datasource type, different values will need to be filled in. As an example, we will use a Snowflake connection:
• Database type: SNOWFLAKE
• Account: the account code for the Snowflake instance
• This the account identifier. For instance if the url to your snowflake
• Example URL
• https://app.snowflake.com/a12345/b23456/#/homepage
• Account ID
• a12345-pb23456
• The other items, including warehouse, database, schema and so on

Warning

Changing or updating the view may invalidate the user permissions. Make sure to apply permissions if the view is updated

Details

The details tab is where the count and export configurations. You can create multiple “summary views” which are pre-configured queries that allow dynamic variables to be included. The steps to create a summary view are:

1. Click on the Create new summary view button. A new empty summary view set of fields this include
• Query Name: a unique name for the query
• Label: A short name for the query
• Export: A selection box to indicate whether this can be exported
• Sql Query: A query that can take dynamic variable, specifically, {{WHERE}}, which is filled in from an BD Select query
• Example
• select IS_CHILD_OF_KNOWN_CONSUMER, COUNT(*) AS COUNT from AD_DEMO.PUBLIC.CONSUMER_PEOPLE_ONLY WHERE {{WHERE}} GROUP BY 1
2. Click save to save the query

Note

If the “Overall” included query details is not useful, you can create a new query detail, with the query name, “overall”, and the label, “Overall” to replace it. This allows you to create a query similar to the other count queries. It is recommended to use it for an overall count.

Fields

The fields tab are where you can add criteria fields and types. These will be used in the BD Select queries to select the criteria to filter the data.

The fields page also has buttons to download and upload the queries configuration. It may be helpful to create the first few fields with the various field types you want to use, download the configuration to edit locally.

Creating a field through the ui has these steps

1. Click on the Create New Field button. A dialog box appears. File in these values
1. Field Name: This will be a column name in the source table or view. You can click on the field and enter the first few characters to narrow to the column you want to select
2. Label: Create a friendly label for the Field
3. Categories: Create a category or select an earlier created category. Click add to add the field to the category
4. Display type: This is the type of selection you can make for the field. This could be a boolean, a multi-select or other
5. Static: If this is chosen, the field can act as a variable that can be included in the Select queries to into the Query details SQL. See example below
6. Where tags: These a special set of tags that can be included and secondary where clauses in the query. See example below
7. Skip Default Where: This will not include this field for filtering in the main where clause of an query detail SQL statement
2. Click Save to create the field.

You can create any number of fields.

Field Types

There are a number of field types

• Number slider: Allows greater than or less than of a numeric value
• Number input: Allows addition of individual numeric values
• Range Slide: Allows a numeric range
• Selection Unique: Allows selection of one of a pre-selected set of values
• Selection Multi: Allows selection of one or more of a pre-selected set of values
• Boolean
• Multi-Text: Can add in text values to match
• Date picker: A date range
• Contains: Can add in one or more contains text items to match
• Zip-code Radius: Allows to collect a set of zip codes within a centroid zipcode
• Relation Table: Associated a separate table using the files imported in the table to choose what to join or exclude

BD Select Queries

Base Setup

Once datasource information id configured, it is possible to create BD Select queries. These are criteria selected through the UI for counts and exports. To create an BD Select query, do these steps:

1. On the left navigation bar, click on the BD Select link. A window appears. There may already be BD Select queries from earlier create. To create a new query, do these steps
1. Select and Owner. This would be an owner you previously created in the Data Source > Owners section
2. Click on Create Job. A page appears where you can configure the query. File in the top fields
1. Order Name: A name for the query
2. Select Owner: if already selected you can leave this as it is. Otherwise you could change the owner
3. Select Data Source: Select the datasource you created earlier, or one you will use
4. Description: A description of the query
3. Once the datasource is selected, the fields created in the fields step will appear in the data attributes section, contained in the categories chosen by you

Query Selection

From the Data Attributes Section you can select and configure the query for the counts or export you want. Typical steps are:

1. Expand a category to have access to the attributes
2. When hovering over an attribute, you will see a plus and a minus sign. Clicking on the plus sign will add the attribute in the include section and minus in the Exclude section. Included attributes will select records where the attribute variables are filled in. Exclude does the opposite
3. Depending on the field type, you may need to fill in some values or check some boxes.
4. Click Save Order to save the query

Get Order Count

If you click on the Get Order Count button, a dialog box will appear with tabs. These tabs were from the Details queries created earlier. Click on any tab to see the counts.

Exports

Export configuration were created during the Detail step. Whatever details had “export” checked will be available for export. Steps to export are:

1. From the export menu in the upper right, choose the export
2. From the export query selection you can either:
1. Click on Export button to export the data to the main bettrdata application
2. Click on the arrow icon on the right of the button to choose:
• Export
• Export and Download

Examples

Example 1

This simple example uses a static view or table to query from. We will use static field “LIMIT” to limit the number of records returned

SELECT FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, ZIP, EMAIL
FROM EXAMPLE_TABLE_1 WHERE {{WHERE}} LIMIT {{LIMIT}};

If we happen to be filtering by where column gender is “F” and LIMIT is 1000, the compiled query would be

SELECT FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, ZIP, EMAIL
FROM EXAMPLE_TABLE_1 WHERE GENDER IN ['F'] LIMIT 100;

Example 2

The next example uses the where tags and whether default or not. If the field ZIP is using a where tag of “person” for instance, and not using the default where and a second file field GENDER is using a where tag of “address” and is using the default where. The query description SQL could be more complex using a common table expression (CTE) and could look like this

with person_table filtered as (
	SELECT ID, FIRST_NAME, LAST_NAME FROM TABLE1 
	FROM PERSON
	WHERE {{WHERE.person}}
),
address_table_filter as (
	SELECT ID, ADDRESS, CITY, STATE, ZIP, EMAIL
	FROM ADDRESS
	WHERE {{WHERE.address}}
)
SELECT *
FROM person_table P
JOIN address_table_filter a
ON p.ID = a.ID
WHERE {{WHERE}}
;

again using the GENDER as “F” and STATE as “CO”, this would compile to

with person_table filtered as (
	SELECT ID, FIRST_NAME, LAST_NAME FROM TABLE1 
	FROM PERSON
	WHERE GENDER IN ['F']
),
address_table_filter as (
	SELECT ID, ADDRESS, CITY, STATE, ZIP, EMAIL
	FROM ADDRESS
	WHERE STATE IN ['CO']
)
SELECT *
FROM person_table P
JOIN address_table_filter a
ON p.ID = a.ID
WHERE STATE IN ['CO']
;