Data Object Window

See also:

 

 

Introduction

Many datasets are simply not practical to import into Excel (especially if the datasets are very large and frequently updated) and, if done, we might not want them to continuously perform (for example) a regression analysis each time a model opens. To address this issue, a unique ModelRisk Data Object functionality has been developed that has the ability to:

• Import data from common databases in an easy-to-use manner;

• Construct custom SQL queries with filtering and sorting;

• Create links to spreadsheets with data that are not loaded in the memory;

• Easily Update queries when needed;

• Create Data Object functions that refer to these datasets and place them in a spreadsheet;

• Link ModelRisk function to Data Objects.

The DataObjects collate the data from relevant databases to be called by the calculation routines and the graphical user interfaces. This eliminates the need for the user to have to open and query numerous databases.

VoseDataObject functionality can be used in several types of ModelRisk functions:

• Probability calculation functions, e.g. =VoseNormalProb(VoseDataObject(<link to data source>), Mean, Stdev, Cumulative)

• Distribution fitting functions, e.g. =VoseBetaFit(VoseDataObject(<link to data source>))

• Copula fitting functions, e.g. =VoseCopulaMultiClaytonFit(VoseDataObject(<link to data source>))

• Time series fitting functions, e.g. = VoseTimeGBMFit(VoseDataObject(<link to data source>))

Window elements

ModelRisk Data Object interface allows easy linking to data located in Excel worksheets or in databases that support ODBC driver connection.

 

 Vose Data Object main window view

 

In the “Define Data Source” field you can define the source of the data. The two buttons on the right of this field allow the creation of a new data source, which can be either a link to worksheet range (left button), or a link to a database (right button).

If connection to a database needs authorization, check on the “Authorization

needed” field and fill in the details for the “Login” and “Password” fields.

Checking the connection with the data source can be done by clicking the “Verify database connection” button. If the check was done successfully, you will get a confirmation message.

Linking to data in the databases can be done by typing the SQL queries directly into the Query string field or using the Query constructor (click “Wizard” button).

 

Query constructor window view.

 

Query constructor window has three tabs:

“Select data source”

This tab is for constructing the main query line. The “Database table fields” lists all database tables and fields that the user can connect to. Just move all required fields into the “Selected fields” list.  The “Query string” field below will show the main query line for the selected data.

“Define filter options”

This tab is for filtering the selection made in the first tab.

Filtering consists of two levels of filters:  Joining condition and Filter condition.

In the Joining condition you can specify the logic for combining the filters by selecting necessary value from the list:

 

Filter condition is set by the left argument (“Condition Left argument” field), comparison sign (“Condition” field), and the right argument (Condition Right argument” field). Arguments can be single values as well as database table fields.

 

To select a database table field as a condition argument, the user should click the following button:

Comparison sign should be picked up from the list (“Condition” field):

When the filter is created it should be added to the filters list by clicking “Add filter to list” button. To delete it from the filters list, select it and click the “Delete filter” button.

Query string with the comprised filters is reflected in the “Query string” field.

“Define sorting options”

This tab allows adding sorting options to the selected entries. The left pane (Database tables fields”) lists all fields that are available for sorting. To sort the data, select the fields that need sorting and move them to the “Sorted fields order” list choosing the sorting direction in the control above.

The final query string will be reflected in the “Query string” field.

When the query is constructed, press the “OK” button and you will get back to the main window “Vose Data Object”.

If desired, you can click the “Run query” button. This will run the constructed query provided that query has been constructed correctly. The “Query results” window will then display the query results in tabular form.

Query results window view  

 

The tabulated data can be exported into Microsoft Excel or Microsoft Word by checking the required Export type and clicking the “Export” button.

Attention: avoid exporting large data sets to Word, as it can take long time.

By closing the results window, you will get back to the main window “Vose Data Object”.  After clicking the “OK” button, a VoseDataObject() function with the parameters (reference to database/range on the worksheet ,  selection query  etc.) will be placed in the range that was specified in the “Output location” field.

 

ModelRisk

Monte Carlo simulation in Excel. Learn more

Tamara

Adding risk and uncertainty to your project schedule. Learn more

Navigation

FREE MONTE CARLO SIMULATION SOFTWARE

For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download ModelRisk Basic now

FREE PROJECT RISK SOFTWARE

For Primavera & Microsoft Project

Download your free copy of Tamara Basic today. Professional quality project risk software and no catches.

Download Tamara Basic now
-->