SVB Data Configurations
A standard Data Configuration makes it possible for users to query a database via a single Statistica Enterprise database connection. It also provides the SQL Wizard and Advance SQL Wizard tools, which enable users to define a query by dragging and dropping objects in a graphical UI.
An SVB Data Configuration makes it possible for users to define one or more queries from one or more databases, so data from multiples databases can be combined in a completely general way.
In the Enterprise Manager System View, right-click on a folder, and select New SVB Data Configuration.
A new SVB Data Configuration node will be displayed as a child of that folder. Name the new node PovertyData.
Expand that node, and select the Code/Parameter child node.
In the Use the following SVB code box, a template of the SVB code needed for a custom query is displayed, written in SVB, to be returned when the SVB Data Configuration is run.
Following is an example of how such a custom query can be implemented in this SVB code.
Click the Edit button at the lower-right of the box containing the template. Statistica will open, and a macro will be displayed containing the SVB template code. We can edit the code here.
Much of the work in this SVB code is created using an object called a StaSpreadsheet. A StaSpreadsheet is a light-weight version of a regular Statistica spreadsheet. A StaSpreadsheet object can do most things that a regular spreadsheet object can do, except it cannot be made visible in Statistica. But, it consumes less memory, which makes it ideal for this job.
The code in this SVB template is all commented out, but it provides an outline of what needs to be done. We can uncomment the lines of code we need, and write the other necessary code ourselves.
For example, assume that we want to query a table of demographic data related to poverty. First, we create a new StaSpreadsheet object, and set its number of variables to equal the number of fields the query will return. Do this as follows:
Dim ss As New StaSpreadsheet
ss.SetSize(1,8)
We must set the number of variables (columns) to equal the number of fields the query will return, in this case 8. But we do not need to know the number of cases (rows) the query will return. The initial number of cases is set to 1. When the query runs, the StaSpreadsheet object will automatically expand to accommodate all the retrieved records.
Next, set the spreadsheet variable types to match the type of data in each field to be retrieved by the query:
ss.VariableType(1) = scDouble
ss.VariableType(2) = scDouble
ss.VariableType(3) = scDouble
ss.VariableType(4) = scDouble
ss.VariableType(5) = scDouble
ss.VariableType(6) = scDouble
ss.VariableType(7) = scDouble
ss.VariableType(8) = scDouble
Now, set the spreadsheet variable names to match the names of the fields that will be retrieved. This is optional since the field names will be retrieved when the query runs. However, we will see later that there is an SVB Data Configuration option that enables users to display the field names without waiting for the query to run. If we want this option to work, we must set those field names in the SVB code here.
ss.VariableName(1) = "ID"
ss.VariableName(2) = "POP_CHNG"
ss.VariableName(3) = "N_EMPLD"
ss.VariableName(4) = "PT_PHONE"
ss.VariableName(5) = "PT_POOR"
ss.VariableName(6) = "TAX_RATE"
ss.VariableName(7) = "PT_RURAL"
ss.VariableName(8) = "AGE"
The next section of example code can be used as it is:
If RunContext.RunMode = "columns" Then
Set SVBDataConfiguration = ss
Exit Function
End If
This code lists the names and types of the fields to be returned by the query. It is used whenever a user clicks the Retrieve Columns button in the Columns properties page of the SVB Data Configuration.
Now we are ready for the main work of this SVB Data Configuration—retrieving data. We can do this using any data query API. ADO is one common example. For this example, we will use the built-in query tools in a Statistica spreadsheet object.
We need to create a new spreadsheet object to run the query. The StaSpreadsheet object we created earlier does not have this ability.
Dim ssResults As Spreadsheet
Set ssResults = Spreadsheets.New
We need the connection string to the database we are querying. The example below is for illustration only. Your connection string will be different.
Dim strConn As String
strConn = "enter appropriate database connection string here”
We also need to define the SQL code for the query:
Dim strSQL As String
strSQL = "SELECT * FROM POVERTY"
And we need to add a Statistica Query object to the spreadsheet. That Query object references the database connection string, and the SQL code:
Dim qry As Query
Set qry = ssResults.Queries.Add("PovertyQuery",strConn,1,1,strSQL)
Now we run the query by refreshing the query object. We wait until the query object is finished refreshing:
qry.Refresh(True)
While qry.Refreshing
Wait 1
Wend
At this point, the spreadsheet to which the Query object is attached will be populated by the query results.
Finally, we can return the query results from this function. But, the function expects a StaSpreadsheet object to be returned, while our query results reside in a Spreadsheet object. Fortunately, we can convert between the two object types as follows:
Set ss = Application.StaSpreadsheetFromSpreadsheet(ssResults)
Now we can return the results from the function by setting the function name equal to the StaSpreadsheet object:
Set SVBDataConfiguration = ss
The final SVB function looks like this:
Function SVBDataConfiguration(RunContext As SVBDataMonitorRunContext) As Object
'SVB Data Configurations expect a return of a single spreadsheet.
Dim ss As New StaSpreadsheet
’r;One-case spreadsheet with appropriate number of variables.
ss.SetSize(1,8)
'Now set the variable types.
ss.VariableType(1) = scDouble
ss.VariableType(2) = scDouble
ss.VariableType(3) = scDouble
ss.VariableType(4) = scDouble
ss.VariableType(5) = scDouble
ss.VariableType(6) = scDouble
ss.VariableType(7) = scDouble
ss.VariableType(8) = scDouble
'Variable names to be displayed when clicking the "Retrieve Columns" button.
ss.VariableName(1) = "ID"
ss.VariableName(2) = "POP_CHNG"
ss.VariableName(3) = "N_EMPLD"
ss.VariableName(4) = "PT_PHONE"
ss.VariableName(5) = "PT_POOR"
ss.VariableName(6) = "TAX_RATE"
ss.VariableName(7) = "PT_RURAL"
ss.VariableName(8) = "AGE"
' If RunMode = "columns", then the system is using this to
' determine the type of the columns; the full set of data need
' not be present.
If RunContext.RunMode = "columns" Then
Set SVBDataConfiguration = ss
Exit Function
End If
' Now produce the full set of data.
'Create new spreadsheet object to run query.
Dim ssResults As Spreadsheet
Set ssResults = Spreadsheets.New
'Connection string to database that will be queried.
Dim strConn As String
strConn = "enter appropriate database connection string here"
'SQL code to be executed.
Dim strSQL As String
strSQL = "SELECT * FROM POVERTY"
'Add new Query object to new spreadsheet. New Query object
'references database connection string, and SQL code.
Dim qry As Query
Set qry = ssResults.Queries.Add("PovertyQuery",strConn,1,1,strSQL)
'Refresh query. Wait until query is completed, and
'spreadsheet is populated with query results.
qry.Refresh(True)
While qry.Refreshing
Wait 1
Wend
'Convert spreadsheet containing query results
'into StaSpreadsheet object.
Set ss = Application.StaSpreadsheetFromSpreadsheet(ssResults)
'Return StaSpreadsheet containing query results from function.
Set SVBDataConfiguration = ss
End Function
Save the macro file, and close Statistica. The SVB code will be displayed in the Use the following SVB code box.
Alternatively, we could save the macro as an *.svb file, and then save that file in the Enterprise Manager System View as a general document. Then, we could reference that general document from within the SVB Data Configuration via the Use this general document as SVB code option.
Now when we select the Columns node and click the Retrieve Columns button, we can see the column names and types that will be retrieved by the query.
As mentioned above when we wrote the SVB code, the column names and types will be retrieved without running the query. This will save time if the query takes a long time to run.
As with all other objects in Enterprise Manager, we need to set access permissions:
Commit the changes, select the main SVB Data Configuration node, and click the Explore button.
The results of the query will be displayed in a Statistica spreadsheet, just as with any other Data Configuration.
An SVB Data Configuration can also be used as input to an Analysis Configuration, again, just as with any other Data Configuration.