Exercise: Query - Behind the Scenes

Exercise Prerequisites

While working with Query within Infinity, you may be saying to yourself, "Gee, it looks like the Revenue source view that my ad-hoc query is based upon has a lot of fields. I'd hate to have to recreate the source view every time just to do a quick query and explore some data." You may also say to yourself, "What tables are used to create these source views?" or "How can I determine exactly which tables and columns are used within a source view?" I'm glad you asked. In this exercise, we will walk through some Infinity and SQL Server tools that can solve the mystery of the hidden source view query fields.

Step 1 -  Navigate to the Analysis functional area and select the Query task to open the Query page.

Step 2 -  Navigate to the Analysis functional area and select the Information Library task to open the Information Library page.

Step 3 -  Select Revenue within the Type filter and apply the filter to display your Revenue ad-hoc queries.

Step 4 -  To edit the 2008 Annual Fund Big Spenders ad-hoc query, you can select it and click the Edit action.

Step 5 -  To view the source view for the 2008 Annual Fund Big Spenders query, close the query and click its Properties action.

Step 6 -  Open the BlackbaudEnterpriseCRM.chm compiled HTML help file and search for V_QUERY_REVENUE.

The Query Properties dialog window displays the name of the source view, V_QUERY_REVENUE. This is the name of a view within SQL Server. A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced. Views are created by defining the SELECT statement that retrieves the data to be presented by the view. The data tables referenced by the SELECT statement are known as the base tables for the view. In this example, titleview in the pubs database is a view that selects data from three base tables to present a virtual table of commonly needed data:

CREATE titleview
AS
	SELECT title, au_ord, au_lname, price, ytd_sales, pub_id
	FROM authors AS a
	JOIN titleauthor AS ta ON (a.au_id = ta.au_id)
     	JOIN titles AS t ON (t.title_id = ta.title_id)

You can then reference titleview in statements in the same way you would reference a table:

SELECT *
FROM titleview

Navigate to the SDK\Documentation folder on the computer where the SDK has been installed.

Double click the BlackbaudEnterpriseCRM.chm file to open the help file.

Using the Search tab, type V_QUERY_REVENUE for the keyword to find and select the List Topics button to begin the search.

Select V_QUERY_REVENUE (View) within the Select Topic to display list box and click the Display button.

Step 7 -  Select the View Definition link (green highlight above).

Click the View Definition link (green highlight above) to display the CREATE VIEW SQL statement used to create the V_QUERY_REVENUE view.  Notice the names of the tables that prefix the column names. 

Step 8 -  Open Microsoft SQL Server Management Studio.

Next we will explore another way to view the tables and columns that are displayed by a view. SQL Server Management Studio is an application that is used to manage SQL Server. We can write a query that interrogates the metadata within SQL Server. This metadata is used by SQL Server to keep track of the tables, columns, views, and stored procedures that are managed by SQL Server. 

Open SQL Server Management Studio by selecting SQL Server Management Studio from the Windows Start menu:

Step 9 -  Connect to SQL Server using Microsoft SQL Server Management Studio.

Select View\Object Explorer from the main menu within Microsoft SQL Server Management Studio to display the Object Explorer pane. 

From within the Object Explorer, select the Connect\Database Engine… from the popup menu to begin the process of connecting to an instance of SQL Server. Click the Connect on the Connect to Server dialog window to connect to your local instance of SQL Server.

Step 10 -  Create a query to interrogate the SQL Server metadata.

Click the New Query button to create a query. 

To the left you will see a new query window. Enter the following SQL query to interrogate the SQL server metadata. Click the Execute button to run the query. The query displays the database name, table name, and column name via the VIEW_CATALOG, TABLE_NAME, and COLUMN_NAME columns respectively. 

USE INFINITY
GO

SELECT TOP 200 VIEW_CATALOG, TABLE_NAME, COLUMN_NAME
FROM BBInfinity21Demo.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE VIEW_NAME = 'V_QUERY_REVENUE'
ORDER BY TABLE_NAME

Note: The name of the database may differ depending on how your Infinity application has been installed on your SQL Server. In this example the name of our database is Infinity.

Step 11 -  Investigate the Results and List 5 Major Tables

Using the Results of the query, list 5 major tables that are used in the view. For this exercise a major table is one that is not a CHANGEAGENT table or a table that ends with CODE, ex;  CHANNELCODE.