Tagged with custom_sql - Visokio Forums

Idea: Parameter passing -Dynamic queries for user drilldowns?

acohen — Thu, 15 Jan 2015 09:41:22 -0500

I know this idea has come up in several other forms already, primarily via threads tagged/labelled 'parameter passing'. Essentially, it is often not practical to bring in all of the data one might need to see into an instance of Omniscope and so the data is aggregated at some level.

However a user needs the functionality to be able to see further detail in some instances. If the user could trigger a parametrised SQL query to reload/refresh the data with one or more parameters set, it would enable an additional, more granular and personalised view of that data that would be ideal.

For instance, the user is looking at monthly data of some sort. Daily data would be unmanageable and unnecessary as the dataset would contain tens of millions of rows. The idea is the user can click on a particular record/set a parameter, say the month and then behind the scenes a dynamic query is done to bring up the daily data which can be viewed in a seperate view/file.

Import: SQL view as a parameter in DataManager?

carlosmartinmari — Wed, 21 Jan 2015 07:04:24 -0500

Hi,

I'm trying to introduce a parameter, say "Sqlview" that is being used in the DataManager database source block to call a custom SQL view as follows:

select * from p = {{Sqlview}}

The problem we have is that when you give the variable type to the parameter and set it as text, in DM the variable is automatically written as:

select * from "my_table_name"

which returns an error because it should look like that:

select * from `my_table_name`

Introducing the correct symbols doesn't help (parameter enter as "`my_table_name`").

Is there any way to do that?

Thank you in advance,
Carlos

Parameters: On-demand parametric data set delivery

Veaceslav — Fri, 12 Dec 2014 06:55:27 -0500

Generate custom dashboards on demand

Scenario/Problem

You have an Omniscope server (2.9 or higher) installed and publicly available at your-omniscope.your-domain.com

In addition, you have another web server (Apache, Jetty, etc) providing services or any useful information to your customers/users. For the purpose of this post I'll consider that you have an apache web server at your-apache.your-domain.com, even though you can achieve this with most web servers.

On a particular page on your Apache web server you have a list of links that you want to open different dashboards based on some particular parameter (e.g. user id)
A link might look like this: http://your-apache.your-domain.com/users?userid=5

Clicking that link the user should see a dashboard page having data that concerns him/her, the dashboard would be generated on the fly.

You have a dashboard/IOK file configured to load data from a particular SQL database.

You want to load data that matches a parameter for a particular user. E.g.
SELECT * FROM [dbo].[bigtable] WHERE [somefield]={{Userid}}

Solution

IOK File

1. Open the IOK file, DataManager tab and add a parameter. E.g. Name: Userid, Type: Fixed value, Data type: Integer, Value: 3

2. Open database source block and add your custom SQL query. E.g.
SELECT * FROM [dbo].[bigtable] WHERE [Liq Cat]={{Userid}}

3. Save your file

Your apache web server

1. Implement a request handler to deal with your special GET requests http://your-apache.your-domain.com/users?userid=5 and read user id value. The server will not send a response unless all next steps finish executing (either successfully or not)

2. Generate an action file (e.g. Action.xml) that:

Sets data manager parameter value (for Userid parameter)
Refreshes from source, this sub-action will execute the SQL query again and load records matching the new user id
Save IOK file to a new unique location in mobile folder. E.g. mobile/custom-dashboards/12345.iok

3. Execute the new action
E.g. On windows (same parameters on Linux -executeAction /some-path/Action.xml)
OmniscopeEnterprise.exe -executeAction "some-path\Action.xml"

4. Once the scheduler action is executed successfully, your Apache web server will return a redirect response to the client

The client/browser will be redirected to your-omniscope.your-domain.com/custom-dashboards/12345.iok

You may want to set up a proper back-end configuration, hardware load balancer + software load balancer, caching requests, etc. Also, you may want to make sure your paths are randomly generated, to prevent other users from viewing other dashboards.

Merge/Join: Sequencing multiple merge operations?

kattasureshkumar — Mon, 12 Nov 2012 04:27:18 -0500

We have prepared individual IOK Files for each flat file which contains data from different modules. Now we are planning to combine all these IOK Files and make them as a single IOK and all the flows will be created one after the other. How can we control the sequence of the processing? Once the flow in one flat file is completed, then it should process the second flat file since for some flat files there is some linking and it requires data from the initial flat file.

Suppose we have employee , department & salary data. Initially we had prepared individual IOK Files and were running in the desired sequence Department --> Employee --> Salary . But now, we want to combine them to a single IOK File. Are we be able to control the sequencing in the Omniscope DataManager?

Also attached the sample IOK File into which we are importing Emp, Dept & Salary Data and at the last all the info from tables is joined and Excel output is generated from the custom SQL. Can we control sequencing here, so that the custom SQL runs last, after all the data from the flat files has been uploaded to the Oracle tables?

Thanks, Suresh.

Performance: Custom SQL - Data retrieval time?

kattasureshkumar — Wed, 14 Nov 2012 07:50:37 -0500

We are using the Database Table as input to retrieve data from the table which is having about 2 Million Records. But the Omniscope is taking long time to load the data more than an hour. Please let us know if we can shorten this time.

System used to check this performance : 16 GB RAM, i3 3.3 GHz Processor, 64 Bit.

Thanks, Suresh.

DataManager: Custom SQL tab review wthout refresh?

bfromson1 — Fri, 09 Nov 2012 07:09:31 -0500

If you use the Custom SQL tab in a DataManager Database source block, then whenever you click on the "Edit SQL" tab, it invalidates the data and forces a reload. It would be useful to have a way to view the custom SQL that has been used without forcing the reload.

Filtering: Import with parametric custom SQL queries?

farboud — Fri, 17 Feb 2012 00:21:06 -0500

I am trying to convert an existing report from another system into Omniscope. There is an existing stored procedure with parameters that was used in the previous system to filter the data retrieved from an SQL database. In the DataManager I am now able to use the custom SQL in a database table source and pull data using the same stored procedure.

However, I am not certain what is the best way to provide the parameter values that can change dynamically from, say, within the DataExplorer. Otherwise I will have to import millions of data rows which is not practical. What is the best way to handle this situation? Are parameters useful here? Can parameters be seen from the DataExplorer?

Thanks, Kaveh

Parameters: Passing in external query parameters inside the link URL?

TomRiggs — Tue, 14 Oct 2014 09:53:52 -0400

I need to be able to pass a parameter from an external URL into the Omniscope 2.9 dashboard and then use that parameter value in the custom SQL query for the data for the dashboard. I can make this work between two Omniscope dashboards with no issue but there seems to be no way to dynamically make this happen. The parameter in the URL needs to be sent directly and used for the custom SQL query before the dashboard loads. It also needs to be adaptable as there will be many calls to this same dashboard from many users for different values.