Visokio website     Downloads     Video tutorials     KnowledgeBase  
Parameters: Passing values between files - Visokio Forums
Parameters: Passing values between files
  • aknotts     aknotts November 30, 2010 11:17AM
    Hi

    I have an IOK file with aggregated data in it (due to file size/RAM limits). I have created a local link to another Omniscope IOK file which contains the details that I might need to see. Is there a way to configure the local link to pass a query to the other Omniscope file e.g. [Vendor Name] = 'Boots'

    If this isn't possible on the link is there a way to do it?

    Am interested to know the answer for both v2.5 and also v2.6 (as most of my customers are on 2.5 at the moment).

    Many thanks

    Andy
  • 23 Comments
  •     steve November 30, 2010 1:20PM
    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.
  •     acm December 3, 2010 9:03PM
    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
  •     steve December 7, 2010 10:34AM
    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', ...).
  • aknotts     aknotts January 26, 2011 7:38AM
    Steve, finally got round to having a go at using the parameters. Are there any brief instructions you can share with me?

    Thanks

    Andy
  •     steve January 26, 2011 8:41AM
    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.
  • aknotts     aknotts January 26, 2011 9:54AM
    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?
  •     steve January 26, 2011 10:10AM
    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
  • aknotts     aknotts February 21, 2011 9:58AM
    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
  •     steve February 21, 2011 3:12PM
    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.
  • aknotts     aknotts February 24, 2011 4:00AM
    Steve - thanks for sorting this. It works a treat now. Is there any limit to the number of parameters I can use?
  •     steve February 24, 2011 4:47AM
    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.
  •     davedunckley October 4, 2011 7:43AM
    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
  •     chris October 4, 2011 8:03AM
    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)?
  •     davedunckley October 4, 2011 8:15AM
    It just says database error. Nothing else. Seams to be about the 1000 mark.
  •     chris October 4, 2011 8:28AM
    Hi Dave, if you click on the error you should be able to see a full error message in a popup dialog.
  •     davedunckley October 4, 2011 8:46AM
    "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?

  •     davedunckley October 4, 2011 10:37AM
    Chris,

    Whats the limit to what I can put in here?

    Thanks

    Dave
  •     chris October 4, 2011 10:59AM
    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])
  •     chris October 4, 2011 12:20PM
  •     steve June 12, 2012 8:03AM
    Omniscope 2.8 now supports more parameter customisations. See here:
    http://forums.visokio.com/discussion/1594/datamanager-parameters-2.8/p1
  • SteveWBohemia November 19, 2013 1:45AM
    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
  •     tjbate December 2, 2013 2:44PM
    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

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership