In 2.5, no. You can only link to the entire file.

In 2.6, yes, using DataManager. You can set up two Omniscope windows which communicate using DataManager "parameters". When you select a subset of aggregated data, the 2nd Omniscope updates a database query and loads the granular data for that subset.

If you need more detail on this, please let me know.

Steve, when do you recommend to use parameters in stead of using the second iok file as a source in the same DM as the first iok. Isn't it so that you can easily join the two sources on the vendor name field?
Or am I misunderstanding the parameter function?
Thanks

Parameters when used in this way are intended to solve the problem of having too much data for Omniscope to open at once.

Instead, you use an aggregated / summary overview of your data (file 1) which is, say, 100,000 records. You then use a database query to retrieve only a subset of the 100 million record unaggregated raw data (file 2). The records you select in file 1 define the query used in file 2. When you change your selection in file 1, file 2 automatically refreshes from the database, with different SQL WHERE constraints.

For example, your database is of 1000 historical stock prices per every 100,000 stocks.

You might have a database table of stocks (100,000, for file 1) and another table of prices (100 million, for file 2), with the "Ticker" field as the foreign key. Or, you might just have a table of prices, and use some process to aggregate the data into unique stocks by the "Ticker" field.

You use parameters in file 1 to define the ticker field as the exported parameter, and limit the number of tickers that can be exported to, say, 100.

In the 2nd file, you have a parameter which "mirrors" the parameter in file 1. It communicates with file 1 to do this. This parameter is used as a filter in the database block, which dynamically produces different server-side SQL depending on the parameter value(s).

When you select 100 or fewer stocks in file 1, file 2 gets notified, and re-queries the database, using a SQL statement such as SELECT ... FROM ... WHERE Ticker IN ('AAPL', 'GOOG', 'MSFT', ...).

Steve, finally got round to having a go at using the parameters. Are there any brief instructions you can share with me?

Thanks

Andy

I'm going to assume you have two database tables in the following example configuration:

• STOCKS which lists a record per stock (10,000 records).
  
• HISTORICAL which lists the stock historical prices, daily over 50 years (20,000 records per stock, 200,000,000 records in all).
  

You will set up two IOK files correspondingly and open them concurrently in two separate Omniscope instances:

• Stocks.iok will show every stock.
  
• Historical.iok will show the timeseries data for the stocks you have selected in Stocks.iok. We will limit this to, say, 5 stocks (100,000 timeseries records) - this is configurable.
  

For Stocks.iok:

1. Connect to database, and import all data from the STOCKS table.
   
2. In the Parameters tab of the DM sidebar, add a parameter called "Ticker", type "Highlighted/selected/filtered record IDs", field "Ticker", max values: 5.
   
3. Go to another tab and set up your views as appropriate for navigating stock information.
   
4. Leave the Omniscope window with Stocks.iok open to one side.
   

For Historical.iok:

1. Start a new instance of Omniscope.
   
2. Create a new file by clicking the DataManager icon on the welcome screen.
   
3. Add a database block, and configure it for the HISTORICAL table - don't execute it yet.
   
4. Add a parameter in the DM sidebar called "Ticker", type "Published by another Omniscope", external app [choose the app with Stocks.iok open], parameter "Ticker".
   
5. In the database block, switch to the Filters tab, and add a rule [Ticker] [=] then click the 3-circles parameter linking icon and link it to the "Ticker" parameter.
   
6. Click "Execute" and "Load".
   
7. Select "Data menu > Automatic refresh > Retrieve new data, notify and apply immediately".
   
8. Go to another tab and set up your views as appropriate for navigating stock timeseries data - e.g. a graph view with "Connect: Ticker" selected.
   

To explore your data:

1. Arrange your two Omniscope windows side-by-side.
   
2. In Stocks.iok, use the filters and/or selection in the views to identify no more than 5 stocks.
   
3. In Historical.iok, the data should immediately re-query, performing a server-side SQL query for your chosen stocks' tickers.
   

In this way you are able to explore a virtually unlimited amount of data, by relying upon server-side SQL filtering, with the limits determined only by the database server.

See also:
http://forums.visokio.com/discussion/comment/2189/#Comment_2189

Thanks

Am assuming this only works with a SQL db behind it. Say I had 3 or 4 IOK files with different data in. Could I open one of those and filter based upon a parameter - without it refreshing from a DB?

Yes, server-side filtering only works with an SQL database.

You can achieve exactly the same effect using two blocks (IOK + Record Filter) instead of one (Database). Record Filter has the same ability to link to parameter.

You wouldn't get the original point of this - unlimited data volumes - but would benefit from two separate linked Omniscope sessions for the two different data structures. Note there's an alternative way of doing this, if you're not going for massive datasets:
http://forums.visokio.com/discussion/comment/1135/#Comment_1135

Steve

Been playing with this and I have it working. Can you only have one parameter passed from one Omniscope to the other? I have created several paramenters, linked them all in a filter as you said in the post however, the first parameter gets passed to all of the filter conditions. For example I have a Supplier Name parameter and a Financial Year parameter. I have set 2 filters, Sup_Name = Supplier Name and FinYear = Financial Year. When the query runs it generates the following where clause in the SQL:

WHERE [Supplier]='Visokio' AND [FinYear]='Visokio' rather than

WHERE [Supplier]='Visokio' AND [FinYear]=2009

Ideally I would like to pass several parameters which can have several values (eg Financial Year in (2008,2009,2010) etc.)

Can this be done?

I am using 2.6.530 x64

Thanks

Andy

Andrew, this is a fault and has now been fixed. The fix should hopefully be available in tonight's build - b531 - but may miss the cut-off and make its appearance in b532 instead. Thanks for reporting.

Steve - thanks for sorting this. It works a treat now. Is there any limit to the number of parameters I can use?

There is no imposed limit. We haven't designed it for thousands, though - you might find it unmanageable, and performance may or may not be acceptable.

Guys,

I am just giving this ago. As we have the same kind of set up. When I feed it a same amount of data say 100 records it works a treat. However if I try to feed it 1000 it fails with "Database error" I have taken the code its producing and pasted it into SQL Management Studio and it works fine. Is there an issue with very large select statements?

Thanks

Dave

Dave - what is the Database error message that is shown? What type of Database are you connecting to? What seems to be the cutoff point in terms of the number of records (500, 600 etc)?

It just says database error. Nothing else. Seams to be about the 1000 mark.

Hi Dave, if you click on the error you should be able to see a full error message in a popup dialog.

"An error occurred connecting to or querying the database.
Prepared or callable statement has more than 2000 parameter markers."

So there is a limit to the amount of sql you can place in here?

Chris,

Whats the limit to what I can put in here?

Thanks

Dave

Dave, it appears that you have reached the limit of the number of values allowed allowed by this particular Database driver. This is not part of the Omniscope code, however there are several ways we might be able to get around this limitation.

I will shortly create a separate posting in the "Ideas" section of the forums so please vote on this if you would like it implemented.

In the meantime you could use a "List of values" block to read in your parameter values and write these out to the Database as a separate table. You could then use a query such as:

SELECT * FROM [TABLE_NAME] WHERE [FIELD_NAME] IN (SELECT * FROM [VALUES])

Hi,

I've now posted this idea on the forums:

http://forums.visokio.com/discussion/1035/parameterised-database-query-limit-workaround

Omniscope 2.8 now supports more parameter customisations. See here:
http://forums.visokio.com/discussion/1594/datamanager-parameters-2.8/p1

Hi All,

The above thread has been very useful. I only have a few questions based on the parameter passing option outlined above:

This obviously works for a full desktop licence of omniscope, but is there any way to make this a possibility with the viewer? If my company changes to a server licence does that alter anything?

Ideally I would like to provide this functionality to some of my clients but they will not have the full desktop licence enabled so am hoping there could be a solution that doesn't require me to load the entire database into a single file.

Thanks
Steve

Steve - Server Edition tools like batch personalisation/publishing allow you to automate the refresh/ filtering and delivery of personalised subsets of the reporting data set.

If you ask to upgrade your Forum account to Alpha Partner, you can follow the discussion of parameter passing from client to server via URLs:

http://forums.visokio.com/discussion/2375/idea-web-version-passing-parametersvariables-via-urlss