Power tips

GIS: iGeolise isochrone/time maps operation

richard — Tue, 08 Jul 2014 07:05:01 -0400

Omniscope's DataManager supports generating isochrone/time maps around an origin point using a service provided by iGeolise. For example, this could be used to generate a customer delivery area of 30 minutes travel time (by car/van) from a retailer's distribution centre.

The following example assumes that you already have an iGeolise Travel Time account. You can sign up for an account by clicking on "iGeolise account help" in the DataManager block noted below and following the link.

ATTACHMENTS

Distribution centres.csv - A CSV file containing the locations of a company's distribution centres across London.

INSTRUCTIONS

Create a new file in Omniscope and go to DataManager.
Drag "Distribution centres.csv" onto the DataManager workspace.
Create a new "iGeolise Travel Time" operation and connect it to your workflow. This block will use the point data from the CSV and generate boundaries around each point to show how far can be travelled in a given time period.
Enter your iGeolise Travel Time application ID and key.
Choose the "Travel Time Maps" service.
Change the "Transport type" to "Driving" so the service will calculate results based upon how far the company's delivery vans can travel by road.
Enter a maximum journey duration, e.g. 10 minutes.
You can change the "Result type" to show a separate shape for each distribution centre, merge all shapes into one or even intersect all the shapes to identify the cross-over areas.
Execute the block, load the data into Omniscope and then drag a Map View into Omniscope to see the Time Maps.

ADVANCED
The "Travel Time Maps" service contains advanced options for configuring which fields contain contain the latitude/longitude values and the field for uniquely identifying individual points in the input dataset. If you are using KML or Shapefile sources then these fields will be configured automatically. Below are explanations for each option:

Point identifier - Field containing unique identifiers for each point in the input dataset. In our example this would be the names of the distribution centres.
Longitude - Field containing WGS84 longitude values.
Latitude - Field containing WGS84 latitude values.

GIS: Geometric filter operation

richard — Mon, 07 Jul 2014 13:04:14 -0400

Omniscope's DataManager supports filtering of latitude/longitude points against geographic areas. For example, this could be used to filter all customers outside a sales catchment area.

ATTACHMENTS

"UK major cities.csv" - A CSV file containing the locations of major cities in the United Kingdom.
"English postal areas.kml" - A KML file containing the boundaries of all postal areas in England.

INSTRUCTIONS

Create a new file in Omniscope and go to DataManager.
Drag "UK major cities.csv" and "English postal areas.kml" onto the DataManager workspace.
In the "English postal areas.kml" block, next to "Overlay identifier", select "Name". This means that Omniscope will use the postal area names to uniquely identify postal area shapes in the data.
Create a new "Geometric filter" operation. This block requires first a point dataset and second an area dataset to function. So connect the "UK major cities.csv", then the "English postal areas.kml" (please note that the point data must be connected first).
Select a Filter Type:
- All points (don't filter) - All points will be returned from the block. Use this in conjunction with 'Add "Areas" field' (see next step).
- Points inside any area - Keep only the points which fall inside at least one area shape.
- Points outside all areas - Keep only the points which do not fall inside any area shape.
Check 'Add "Areas" field' to add a new field and merge the "Overlay identifier" from the Area dataset into your new Point dataset. In our example this will add the postal area codes from the "English postal areas.kml" into the new cities dataset.
Execute the block, load the data into Omniscope and then drag a Map View into Omniscope to see the filtered point data.

ADVANCED
The "Geometric filter" block also contains additional controls for configuring which fields contain the latitude/longitude values and the fields for identifying individual features (shapes) in the input dataset. If you are using KML or Shapefile sources then these fields will be configured automatically. Below are explanations for each option:

Overlay identifier - Field containing unique identifiers for each point or area in the input dataset, e.g. a field containing country names or postal area codes.
Overlay sub-path indentifier - This is used internally within Omniscope to identify nested geometries within each shape. For example a shape for the United Kingdom could not be drawn with one continuous line. We use this field to breakdown the records into numerous islands which make up the country.
Longitude - Field containing WGS84 longitude values.
Latitude - Field containing WGS84 latitude values.

GIS: Geometric buffer operation

richard — Mon, 07 Jul 2014 12:28:04 -0400

Omniscope's DataManager supports buffering of geographic shapes by applying a fixed distance to extend/contract the original geometry. For example, this could be used to generate a sales area around a shop using a Euclidean (straight line) distance in all directions.

Geometric buffers can be applied to 3 types of shape data in Omniscope;

Point - A dataset containing pairs of latitude/longitude coordinates, e.g. a shop location.
Line - A dataset containing connected, and unfilled/unclosed, chains of points, e.g. a hurricane path or road.
Area - A dataset containing connected, and filled/closed, chains of points, e.g. a post code area or country border.

Below are instructions for buffering these 3 types of shape data. Please download and extract the contents of the ".zip" file:

POINT

Create a new file in Omniscope and go to DataManager.
Drag "United Kingdom cities.csv" onto the DataManager workspace. This contains 5 major cities in the United Kingdom with latitude/longitude coordinates.
Connect a "Geometric buffer" operation to the workflow.
Choose "Point" as the "Input data type". (For KML and Shapefile sources, this will be configured for you.)
Enter a distance (km) to buffer around each input point. By reducing the "Quality" you will increase the speed of this block but the output dataset will contain less records and the buffered outline will not be as smooth.
Check 'Include metadata' if you would like the output dataset to contain all other fields found in the input, i.e. "Country name".
Execute the block, load the data into Omniscope and then drag a Map View into Omniscope to see the buffered points.

LINE

Create a new file in Omniscope and go to DataManager.
Drag "Hurricane Katrina.csv" onto the DataManager workspace. This contains coordinates for the path of Hurricane Katrina in 2005.
Connect a "Geometric buffer" operation to the workflow.
Choose "Line" as the "Input data type". (For KML and Shapefile sources, this will be configured for you.)
Enter a distance (km) to buffer around the line (hurricane path). By reducing the "Quality" you will increase the speed of this block but the output dataset will contain less records and the buffered outline will not be as smooth.
Execute the block, load the data into Omniscope and then drag a Map View into Omniscope to see the buffered line.

AREA

Create a new file in Omniscope and go to DataManager.
Drag "United Kingdom.kml" onto the DataManager workspace. This contains coordinates defining a simplified boundary of the United Kingdom..
Connect a "Geometric buffer" operation to the workflow.
Ensure "Area" is selected as the "Input data type". (For KML and Shapefile sources, this will be configured for you.)
Choose your method of buffering:
- Expand area - The original area plus an outside perimeter.
- Contract area - The original area less an inside perimeter.
- Outside perimeter - The area within a certain distance outside of the perimeter.
- Inside perimeter - The area within a certain distance inside of the perimeter.
- Outside + inside perimeter - The area within a certain distance either side of the perimeter.
Enter a distance (km) to buffer around the United Kingdom. By reducing the "Quality" you will increase the speed of this block but the output dataset will contain less records and the buffered outline will not be as smooth.
Execute the block, load the data into Omniscope and then drag a Map View into Omniscope to see the buffered line.

ADVANCED
The "Geometric buffer" block also contains additional controls for configuring which fields contain the latitude/longitude values and the fields for identifying individual features (shapes) in the input dataset. If you are using KML or Shapefile sources then these fields will be configured automatically. Below are explanations for each option:

Overlay identifier - Field containing unique identifiers for each point, line or area in the input dataset, e.g. a field containing country names or postal area codes.
Overlay sub-path identifier - This is used internally within Omniscope to identify nested geometries within each shape. For example a shape for the United Kingdom could not be drawn with one continuous line. We use this field to breakdown the records into numerous islands which make up the country.
Longitude - Field containing WGS84 longitude values.
Latitude - Field containing WGS84 latitude values.

GIS: Geocoding (ESRI)

richard — Mon, 07 Jul 2014 08:57:03 -0400

Omniscope's DataManager supports geocoding of geographical place names into WGS84 latitude/longitude coordinates. Currently you have a choice of geocoding via 3 services:

Esri (batch) - Returns the highest ranking match per location. More efficient for large datasets than using Esri multi-match.
Esri (multi-match) - Can return multiple matches per specific place name, e.g. a place called "Aberdeen" exists in Scotland, Hong Kong etc.
Yahoo - Yahoo BOSS PlaceFinder.

Each of these services requires an account with the relevant service provider. Links to the provider's sign-up page are included in the Geocode block.

INSTRUCTIONS

Create a new file in Omniscope and go to DataManager.
Add a 'List of values' block to the DataManager workspace and insert some values (to test the feature) or use a dataset that already has a location field.
Connect a Geocode block to your workflow, select the field "Values" (containing your place names) and choose a geocoding service:
- Esri (batch)
  1. After signing up for an account, enter your username and password then click 'Connect'.
  2. Select the fields you want the geocoding service to return.
  3. Click "Restrict to country" if all of your place names exist within a single country. This can improve the accuracy of geocoded results.
  4. Click "Execute" to begin geocoding.
  NB: "Geocode Service URL" is an advanced option for overriding which Esri geocoding service Omniscope will use to generate results.
- Esri (multi-match)
  1. After signing up for an account, enter your username and password then click 'Connect'.
  2. Select the fields you want the geocoding service to return.
  3. Click "Restrict to country" if all of your place names exist within a single country. This can improve the accuracy of geocoded results.
  4. Configure "Max matches" to select how many results you would like returned for each input place name.
  5. Click "Execute" to begin geocoding.
  NB: "Geocode service URL" is an advanced option for overriding which Esri geocoding service Omniscope will use to generate results.
- Yahoo
  1. After signing up for an account, enter your OAuth consumer key and secret then click 'Connect'.
  2. Select the fields you want the geocoding service to return.
  3. Configure "Count" to select how many results you would like returned for each input place name.
  4. Click "Execute" to begin geocoding.
Load the data into Omniscope to see the geocoded place names and visualise the data in the Map View.

GIS: Importing an ESRI Shapefile

richard — Mon, 07 Jul 2014 08:00:00 -0400

Omniscope's DataManager supports importing geographical data from ESRI Shapefiles. Use the attached files and following steps for a basic example;

ATTACHED FILES

"postcode areas.shp" (inside ZIP) Contains geometries for each feature (shape) in the Esri Shapefile
"postcode areas.dbf" (inside ZIP) Contains meta-data for each feature (shape) in the Esri Shapefile
"postcode areas.prj" (inside ZIP) Contains the coordinates' projection, e.g. UTM, WGS84.

INSTRUCTIONS

Download the attached ".zip" file and extract the ".shp", ".dbf" and ".prj" files into the same folder.
Create a new file in Omniscope and go to DataManager.
Drag the "postcode areas.shp" file onto the DataManager workspace (or drag a "Data file" block onto the workspace and browse to the "postcode areas.shp" file). The ".dbf" and ".prj" files will be automatically detected.
The Esri Shapefile DataManager block contains 2 configurable options
- "Overlay identifier" - The field which contains unique identifiers for each feature (shape) in the file. In our Shapefile this would be the names of the postal areas, e.g. AB for Aberdeen, SW6 for South West London. If your data does not include such a field then selecting "Create new field" will allow Omniscope to generate internal unique identifiers using a field named "Overlay ID".
- "Meta data fields included" - The names of all fields which will be loaded from the Esri Shapefile into the block.
Load the data into Omniscope then drag a Map View into Omniscope to see the postcode areas.

ADVANCED
You will notice a field has also been created named "Overlay sub-path ID". This is used internally within Omniscope to identify nested geometries within each shape. For example a shape for the United Kingdom could not be drawn with one continuous line. We use this field to breakdown the records into numerous islands which make up the country.

GIS: Importing a KML (Keyhole Markup Language) file

richard — Mon, 07 Jul 2014 07:49:27 -0400

Omniscope's DataManager supports importing geographical data from KML (Keyhole Markup Language) files. Use the attached file and following steps for a basic example;

ATTACHED FILE

"postcode areas.kml" (inside ZIP) Contains geometries and meta-data for each feature (shape)

INSTRUCTIONS

Download the attached ".zip" file and extract the ".kml" file.
Create a new file in Omniscope and go to DataManager.
Drag the "postcode areas.kml" file onto the DataManager workspace (or drag a "Data file" block onto the workspace and browse to the "postcode areas.kml" file).
The KML DataManager block contains 3 configurable options:
- "Overlay identifier" - The field which contains unique identifiers for each feature (shape) in the file. In our KML file this would be the names of the postal areas, e.g. AB for Aberdeen, SW6 for South West London. If your data does not include such a field then selecting "Create new field" will allow Omniscope to generate internal unique identifiers using a field named "Overlay ID".
- "Meta data fields included" - The names of all fields which will be loaded from the KML file into the block.
- "Shape types included" - KML files can contain a combination of points, lines and areas. Use this option to filter which shape types you want to load.
Load the data into Omniscope then drag a Map View into Omniscope to see the postcode areas.

Group Queries

DBuzacott — Thu, 03 Jul 2014 01:14:59 -0400

Is it possible to create Group Queries in a similar way to how we can create group tabs?

One of our regular reports has approximately 40 queries. Unfortunately this clutters up the filters as although the top ~10 queries are used commonly, the remaining ~30 queries (predominantly used for tables tailored to specific user requirements) are also displayed and this pushes down the other filters on the right sidebar. Some people who view this report do not realise that they need to scroll down to find the other filters.

We would like to be able to group these queries, i.e. into a 'general queries' and a 'table queries' group so that we may collapse the options available in the filters.

Formulae: RANK Syntax-Ranking on aggregated values, etc

paola — Fri, 21 Feb 2014 14:27:18 -0500

Please see the demo file covering number of scenarios showing how to Rank all values or aggregated values; or rank on more than one field.
Some of the formulas used:
Rank function Syntax: RANK(value, field, isAscending, includeNulls, dataSubset)

Ranking Spend=
RANK([Spend])
# simple Rank comparing all values in the Spend field

Rank Spend per Week=
RANK([Spend], [Spend], false, false, SUBSET([Week]))
#Ranking Spend values inside each Week

SubRank Clicks =
RANK([Clicks], [Clicks], false, false,subset([Rank Spend per Week] ))
# secondary rank field, based on the Clicks value. Meaningful when multiple rows share the same [Rank spend per Week] value

Spend per Keyword=
SUBSET_SUM([Spend], SUBSET([Keyword]))
# total Spend for each Keyword

[One]=1
# basis for running total indexing

Running total per Keyword:
RUNNINGTOTAL([One], SUBSET([Keyword]))

Rank Totals per Keyword=
IF([Running total per Keyw]>1, null,
RANK([Spend per Keyword], [Spend per Keyword], false, true, SUBSET([Running total per Keyw],1,"=")))
# ignoring the values greater than 1, rank compares only the subset sum values where [Running total per Keyw] is 1, ensuring that only one value per category is compared.

Security: Server SSL Error-handshake alert:unrecognized_name

Veaceslav — Fri, 27 Jun 2014 05:30:44 -0400

Server SSL Error - handshake alert: unrecognized_name

You may get this SSL error if the server you are trying to access has not been properly configured.

For security reasons SNI extension has been enabled by default in Java 7. However, if you trust the server you are trying to connect you may want to disable SNI extension.

jsse.enableSNIExtension is a java system property. Server Name Indication (SNI) is a TLS extension, defined in RFC 4366. It enables TLS connections to virtual servers, in which multiple servers for different network names are hosted at a single underlying network address.

Some very old SSL/TLS vendors may not be able to handle SSL/TLS extensions. In this case, set this property to false to disable the SNI extension.

To disable SNL extension you need to do the following:

1. Open 'installconfig.properties' file located at {YOUR_OMNISCOPE_INSTALLATION_PATH}

{YOUR_OMNISCOPE_INSTALLATION_PATH} - this is where Omniscope is installed on your machine (e.g. C:\Users\slavvi\AppData\Local\Visokio Omniscope app)

2. Edit the file to add this line:
ADDITIONAL_JVM_ARGS=-Djsse.enableSNIExtension=false

3. Restart Omniscope

Note: After setting this property, you may get the following error if the server you want to access does not have a certificate issued by a certified authority:
The server you are connecting to does not have a valid certificate from an authorised authority
To resolve this error please read the following post:
http://forums.visokio.com/discussion/2613/security-the-server-you-are-connecting-to-does-not-have-a-valid-certificate
However, you will be unable to execute step 2 outlined in that forum post unless you disable the SNI extension while executing that command. Here is how that command may look like:
java -Djsse.enableSNIExtension=false InstallCert server-storing-my-iok-files.com:443

Security: Server you are connecting to does not have a valid certificate?

Veaceslav — Thu, 26 Jun 2014 08:07:06 -0400

Server SSL error - The server you are connecting to does not have a valid certificate from an authorised authority

Here is what you need to do to enable Omniscope access the url you have (e.g. "https://server-storing-my-iok-files.com/"):

1. Download InstallCert.zip archive and unzip it (it contains two java class files: InstallCert$SavingTrustManager.class, InstallCert.class), put both of them in a folder (e.g. c:\your_path\)

2. Open command prompt (cmd.exe) and change current dir to the folder containing the class files from step 1 (e.g. cd c:\your_path\) then execute this command:

java InstallCert server-storing-my-iok-files.com:443

Note: if you don't have java installed, use the java executable located at:

- {YOUR_OMNISCOPE_INSTALLATION_PATH}\x86\bin\java (if you have a 32 bit OS)
- {YOUR_OMNISCOPE_INSTALLATION_PATH}\x64\bin\java (if you have a 64 bit OS)

{YOUR_OMNISCOPE_INSTALLATION_PATH} - this is where Omniscope is installed on your machine (e.g. C:\Users\slavvi\AppData\Local\Visokio Omniscope app)

(to check whether you have a 32-bit or 64-bit OS: open Control Panel -> System and check 'System type:')
The command may look like this: "{YOUR_OMNISCOPE_INSTALLATION_PATH}\x64\bin\java" InstallCert server-storing-my-iok-files.com:443

If you get the SSL error - handshake alert: unrecognized_name while executing this command please read this post: handshake alert: unrecognized_name

3. You will be asked 'Enter certificate to add to trusted keystore or 'q' to quit: [1]', press 1 then press ENTER
Note: After executing step 3, this message will be displayed in the console:
Added certificate to keystore 'jssecacerts' using alias 'server-storing-my-iok-files.com-1'
which means that this particular certificate will be trusted by java applications if 'jssecacerts' keystore is provided

Notice that 'jssecacerts' file has been created in the folder you executed the command (e.g. c:\your_path\)

4. Open 'installconfig.properties' file located at {YOUR_OMNISCOPE_INSTALLATION_PATH}

5. Edit the file to add this line:
ADDITIONAL_JVM_ARGS=-Djavax.net.ssl.trustStore="c:\your_path\jssecacerts"

6. Restart Omniscope

Note 1: You will get this error if you miss step 5 or the path to 'jssecacerts' is wrong:
java.lang.RuntimeException: Unexpected error: java.security.InvalidAlgorithmParameterException: the trustAnchors parameter must be non-empty

Note 2: If -Djavax.net.ssl.trustStore="c:\your_path\jssecacerts" points to a directory instead of a file or to a non-existent path, all SSL connections will fail (regardless whether the certificates have been authorised by the most trusted authorities, SSL connections will NOT work at all) and the same error will be displayed:
java.lang.RuntimeException: Unexpected error: java.security.InvalidAlgorithmParameterException: the trustAnchors parameter must be non-empty

Note 3: There can be only one path set to -Djavax.net.ssl.trustStore="c:\your_path\jssecacerts". If you need more than one trust stores, you need to merge them into a single file and set that file to javax.net.ssl.trustStore property. If this is the case, the simplest solution is to use steps 1-3 mentioned above to add each individual URL. Each entry will be appended to the same jssecacerts file.

Date/Time: inconsistent formats in a Date field

paola — Mon, 23 Jun 2014 12:57:31 -0400

Attached file deals with issue of inconsistent date formats in a single field, where few extra steps are needed to bring all the values to the same format, including use of TEXTTODATE formula.

Another useful example :
4/18/2015 1:31:09.000000 AM
translates into Omniscope format
M/d/yyyy h:mm:ss.SSS a

Mobile: Client-side auto-refresh (2.9+)

mustafa — Mon, 09 Jun 2014 10:03:33 -0400

In the latest version of Omniscope 2.9 we have made improvements to what happens when the server IOK file is updated while a client is browsing it. These changes make it easier for you to collaborate or create a continuously updating dashboard.

Depending on the kind of change, the client may be able to continue interacting, or be forced to reload (refresh the browser). Changes that force a reload include adding/deleting records/fields. Changes that don't force a reload include adding/removing views/tabs.

How to configure

This behaviour is configured per-file, from the Desktop app. Open the IOK file in Desktop, then click on
Settings > Web sharing>Edit client settings

You will then be prompted with different settings you can persist in the IOK regarding Mobile. Click Edit alongside Client-side auto-refresh.

You'll see this dialog, currently showing the defaults:

Section: Check for changes to server copy of file...

This section defines when the file on the server is checked to see if it has changed.

Only when interacting with the file
The check will happen only when the user is actively interacting with the file e.g. when switching tabs or adjusting filters.
In the background
The check will happen periodically, in the background, while the client has the page open.

Note: this option may increase server load because the file would remain in server memory. For large files on shared servers this would prevent inactive apps from being unloaded from server memory, ultimately reducing performance in extreme cases.

Section: When a change is detected...

This section defines what happens when the client detects the file on the server has changed.

Show button
If ticked, a lighting flash icon will appear on the main toolbar of the client (if the toolbar is showing). Clicking it will reload the application.
Show prompt
If ticked, a prompt will be shown explaining what has happened. Depending on the change, the user could choose to continue working on the session.
Reload automatically
If ticked, the client will automatically reload immediately to show the new changes, but only if the user has not made any client-side changes of their own such as filtering, selection or view menus.

You should tick this option for a live-updating dashboard.

Formulae: Text-processing; determining Text or Number?

aknotts — Thu, 01 May 2014 08:23:16 -0400

If you want to see if the first character of a string is a text or a number, you can use the following function in a formula field

TYPEOF(value(left([my_string], 1)))

This returns the type of the argument passed to it (e.g. text, decimal, integer)

Scheduler: Running multiple instances concurrently

michael — Fri, 13 Apr 2012 11:12:26 -0400

To run 2..N Schedulers simultaneously:

In the text below:

1) Change YOUR_ACCOUNT_NAME for you correspondent account name.

2.1) If you have User specific installation (not system-wide) replace OMNISCOPE_INSTALL_DIR for:

C:\Users\YOUR_ACCOUNT_NAME\AppData\Local\Visokio Omniscope app

2.1) If you have system wide installation, replace OMNISCOPE_INSTALL_DIR for:

C:\Program Files (x86)\Visokio Omniscope

Make sure that the path exists.

==========================================================

1) In your service directory

"OMNISCOPE_INSTALL_DIR\service\"

Create 2..N folders s1..sN

2) In each directory create config.xml file
and set different/correspondent port and configurationFilePath:
E.g.:

OMNISCOPE_INSTALL_DIR\service\s1\config.xml

OMNISCOPE_INSTALL_DIR\service\s2\config.xml

3) Go to "OMNISCOPE_INSTALL_DIR"
and create executable file run_scheduler.bat (if you have x64 version, leave it as in the example below otherwise change for x86 )

For Omniscope 2.7
set CONF_DIR=%1
"OMNISCOPE_INSTALL_DIR\x64\bin\java.exe" -Dsun.java2d.d3d=false -DschedulerConfigLocation="OMNISCOPE_INSTALL_DIR\service\%CONF_DIR%\config.xml" -Xms64M -XX:MaxPermSize=128M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=30 -XX:NewRatio=8 -XX:+UseSerialGC -Xmx6183M -Dvisokio.localappdata="C:\Users\YOUR_ACCOUNT_NAME\AppData\Local" -Dvisokio.winproxyenable="0" -Dvisokio.winproxyserver="" -cp "OMNISCOPE_INSTALL_DIR\Main.jar;OMNISCOPE_INSTALL_DIR\lib\mail.jar;OMNISCOPE_INSTALL_DIR\lib\activation.jar;OMNISCOPE_INSTALL_DIR\plugins\bccrypto.jar" com.visokio.ent.EntLaunch -scheduler

From Omniscope 2.8
set CONF_DIR=%1
"OMNISCOPE_INSTALL_DIR\x64\bin\java.exe" -Dsun.java2d.d3d=false -DschedulerConfigLocation="OMNISCOPE_INSTALL_DIR\service\%CONF_DIR%\config.xml" -Xms64M -XX:MaxPermSize=128M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=30 -XX:NewRatio=8 -XX:+UseSerialGC -Xmx6183M -Dvisokio.localappdata="C:\Users\YOUR_ACCOUNT_NAME\AppData\Local" -Dvisokio.winproxyenable="0" -Dvisokio.winproxyserver="" -cp "OMNISCOPE_INSTALL_DIR\Main.jar;OMNISCOPE_INSTALL_DIR\lib\mailapi.jar;OMNISCOPE_INSTALL_DIR\lib\smtp.jar;OMNISCOPE_INSTALL_DIR\lib\pop3.jar;OMNISCOPE_INSTALL_DIR\lib\imap.jar;OMNISCOPE_INSTALL_DIR\lib\dsn.jar;OMNISCOPE_INSTALL_DIR\lib\activation.jar;OMNISCOPE_INSTALL_DIR\plugins\bccrypto.jar" com.visokio.ent.EntLaunch -server

From Omniscope 3.0
set CONF_DIR=%1
"OMNISCOPE_INSTALL_DIR\OmniscopeEnterprise.exe" "/jvmarg=-DschedulerConfigLocation=OMNISCOPE_INSTALL_DIR\service\%CONF_DIR%\config.xml"

4) Start schedulers using run_scheduler.bat and passing folder names s1, s2, ... sN as a parameter:
E.g.:

run_scheduler.bat s1
run_scheduler.bat s2
...
run_scheduler.bat sN

You can do it into two ways.

a) You can create Shortcuts. Right-click on your Desktop->New->Shortcut.

Specify the path to your run_scheduler.bat file and pass the parameter, for example on my computer it is:

"C:\Users\michael\AppData\Local\Visokio Omniscope app\run_scheduler.bat" s1

b) Alternatively you can run the command from Windows Console - cmd.exe

5) In C:\Users\YOUR_ACCOUNT_NAME\scheduler\log.txt you should see log messages from all schedulers.

You can use attached files as templates replacing (here it is text in bold) with your current file paths
and then renaming
config_xml_tempalte.txt => config.xml
run_scheduler_bat_template.txt => run_scheduler.bat

Formulae: Aggregate rankings; Copying values from above

DBuzacott — Wed, 04 Dec 2013 20:02:28 -0500

Just wanted to share two methods I've got for seemingly common data problems. I don't claim that these are perfect (or even particularly efficient), but they do work. So I've detailed them here so that no-one else has to reinvent the wheel.

Power Tip 1) Aggregate rankings
I've got a file with the basic structure as follows:
[Name] [Value] and a whole lot of other fields

Aggregated rankings can be calculated as per this discussion (http://forums.visokio.com/discussion/comment/7339/). However, I wanted something that would display these rankings on non-aggregated data, as well as driving formulas for 'Top 10' and 'Top 100' categories (in this example, one of the KPIs is spend with top 100 Names).

To do this, I implemented the following workflow
Split off data
Delete excess fields (i.e. all but name / value)
Aggregate by Name
Rank by value (do not preserve formulas in output)
Merge rankings back into the original data
A screenshot of this is attached

Power Tip 2) Copying values from above
We have a dataset with the format
[Site]
A
null
null
B
null
null

i.e. a field that is only populated for the first row of each section of data.
There is some discussion of how to copy these values down into the null cells here (http://forums.visokio.com/discussion/comment/8577/#Comment_8577) but I'm not familiar with these scripts and so didn't want to use them.

Instead, I created some new formula fields as follows:
[Linenumber] = currentrow()
[Linenumber Site] = if([Site]=null, null, currentrow())
[Site updated] = CELL([Site], SUBSET_MAX([Linenumber Site], SUBSET([Linenumber], CURRENTROW(), "<=")))

This creates a new field [Site updated] with the values copied down. With the current dataset (19 fields, 10k records) these forumlas run pretty much instantly.
If you wanted to get sneaky, you could then rename [Site updated] to [Site] and delete the added fields. This would eliminate the calculations from the final file.

Data Mapping: using Merge operation to assign value - de-duplicate variations of the same term

paola — Fri, 04 Oct 2013 11:30:05 -0400

Please see the demo file demonstrating how to assign uniform value to records that contain variation of the same name, or perhaps are a related group of records.
2 fields are corrected ([Provider LName] and [Address]), using two mapping lists with unique values, and Merge operation with multiple merge criteria, ensuring correct association is made, therefore avoiding many:many matches or incorrect association.

Following formula in the FieldOrganiser helps populate empty fields with the single value, for the given combination of 3 fields, where field [Provider Tax ID] is only partially populated.

SUBSET_FIRSTNONNULL([Provider Tax ID], 
SUBSET3([Provider Fname], [LnameCorrected], [AddressCorrected]))

Formulae: Subset Functions - conditionality?

davedunckley — Tue, 10 Sep 2013 03:27:47 -0400

Hi I am having a little trouble with Subset functions...

What I would like to be able to do is to work out the total per charterer in the last 52 weeks.

I have created a field called [islast52] that gives me a 'y' or 'n' depending. Then I am trying the following to get the results. However, I cant quite get it right. Some help would be great.

SUBSET_SUM([Total USD Fixture], SUBSET([Charterer.fixture]),
subset([IsLast52.fixture],"y","=")))

Thanks, Dave

Filtering: Global Filters

OmarKhan — Fri, 16 Aug 2013 10:07:58 -0400

It’s possible to have selection filters which apply to all tabs, i.e. if you select a time period on the first tab, advanced tab settings in all the other tabs will automatically select the same time period.

Open the existing file, and from

Tab > New select Copy of current tab (advanced)

You will be given the option to set this new version of the tab to use the filtering settings on previous tabs by un-ticking ‘Filter states’

Formulae: Entering literal Date/Time values in formulae

tjbate — Wed, 14 Aug 2013 14:38:32 -0400

You cannot enter literal Date/Time values directly into a formula as text. Instead, you must use a TEXTTODATE function and enclose the literal value characters, and the Date/Time formatting mask characters specifying the exact Date/Time format the literal value will be compared with inside single or double quotes:

Formulae: Using Variables in What-If Analysis

paola — Wed, 19 Jun 2013 13:39:27 -0400

Attached file is demonstrating use of variables in What if analysis (in shape of sliders); to see how will different resource allocation scenarios affect Total Cost (time allocated between 3 products), also how will the individual product Sale figures affect totals and net profit.
To make sure total resource allocation is limited to 100%, Dial view is in place to monitor and issue warnings.

Formulae: Cumulative Figures and RUNNINGTOTAL example

windsymphonic — Wed, 01 May 2013 02:00:44 -0400

I have a set of data:

Company Name | Date | Daily Sales
ABC | 22/03/2013 | 22
ABC | 22/03/ 2013 | 23
ABC | 23/03/ 2013 | 40
ABC | 23/03/ 2013 | 50
DEF | 22/03/2013 | 10
DEF | 22/03/2013 | 20
DEF | 23/03/2013 | 30
DEF | 23/03/2013 | 60

In Omniscope, I have aggregated by Company Name & Date which gives me:

Company Name | Date | Daily Sales
ABC | 22/03/2013 | 45
ABC | 23/03/2013 | 90
DEF | 22/03/2013 | 30
DEF | 23/03/2013 | 90

My next step is to calculate cumulative sales by company name & date. So I want my outcome to be:

Company Name | Date | Daily Sales | Cumulative Sale
ABC | 22/03/2013 | 45 | 45
ABC | 23/03/2013 | 90 | 135
DEF | 22/03/2013 | 30 | 30
DEF | 23/03/2013 | 90 | 120

So how do I derive the cumulative sales?

Cheers, Firman

Web view: Check your system's Internet Explorer settings

antonio — Fri, 23 Mar 2012 13:54:44 -0400

When using web view on Windows, the system's Internet Explorer settings will be used by default.

For example, if you have turned on the Compatibility view settings in Internet Explorer, the web view will use the same settings to display the web page. See here for more detail about Compatibility view settings.

Having compatibility view turned on may result in web pages not display properly in web view.

Formulae: New extensible SUBSET function syntax

steve — Tue, 19 Apr 2011 10:23:03 -0400

The SUBSET function has now been complemented in recent 2.6+ builds by:

- INTERSECTION - http://www.visokio.com/functions-guide#INTERSECTION
- UNION - http://www.visokio.com/functions-guide#UNION
- INVERSE - http://www.visokio.com/functions-guide#INVERSE

These greatly improve the simplicity, flexibility and power of subsets in formulas. They replace the complicated SUBSET2, SUBSET3 (etc.) functions, and more. Instead of these, use INTERSECTION(SUBSET(...), SUBSET(...), SUBSET(...)).

Examples:
The sum of Sales for all records with a different currency to the current record:
SUBSET_SUM( Sales, INVERSE(SUBSET(Currency)) )

The number of records with yield < 0.1 and in USD or GBP:
RECORDCOUNT(INTERSECTION(
UNION(SUBSET(Currency, "USD"), SUBSET(Currency, "GBP")),
SUBSET(Yield, 0.1, "<")
))

See Subset Functions Guide: http://www.visokio.com/kb/subset-functions

Formulae: Collapse values vs formula equivalent?

Alexander — Fri, 10 Aug 2012 05:46:24 -0400

Hi,

In various DataManager workflows, I use the Collapse Values operation.

Yet I am struggling with the performance of this operation. What can I do to make it faster? Is it an option to use the formula equivalent ([Field A]&"seperator"&[Field B]) or will this only be slowing down even more?

Thanks!

Deployment: SharePoint/IIS authentication settings

antonio — Tue, 10 Jul 2012 13:06:26 -0400

Omniscope users may experience the following error when trying to open a file from a SharePoint location:

401 Authorization Required

Even if the user is providing the correct authentication details, a misconfiguration of the IIS server (on which Sharepoint runs) may cause the aforementioned error.

Here follows 2 default Sharepoint/IIS configurations that you may try to apply to solve the problem:

Sharepoint 2007 with IIS 7.0
- Go to the IIS settings , choose the Sharepoint site you need to configure, and double click on the "Authentication" icon
- Enable the "Windows Authentication"
- Click on "Advanced settings" and ensure "Enable Kernel-mode Authentication" option is ticked.
Sharepoint 2010 with IIS 7.5
- Go to the IIS settings , choose the Sharepoint site you need to configure, and double click on the "Authentication" icon
- Enable the "Windows Authentication"
- Click on "Providers..." and ensure that "NTLM" is in the "Enabled Providers" list

Sources: Adding a unique numbering ID column

dszl136154 — Fri, 24 Feb 2012 10:30:00 -0500

Is there any way to add a column with unique numbers (eg. 1,2,3,...) ID column?

Extracting text using Javascript

Guy_Cuthbert — Wed, 07 Mar 2012 06:12:18 -0500

Since 2.6, Omniscope has had the ability to include Javascript inside formulae - very useful, but potentially daunting for non-programmers. Steve did a great intro to the topic here, and I thought further examples might be useful... so here is a tutorial on how to extract selected text from a field using regular expressions...

Firstly, the use case to answer the question "Why would I want to do this?":

Omniscope users familiar with the formula language may well have come across the SEARCH and SEARCHREGEX functions, which return the position at which a particular character/phrase (in the case of SEARCH) or regular expression pattern (in the case of SEARCHREGEX). These functions are often used to extract elements of text - for example, extracting parts of a postcode; the UK postcode comprises 4 sections:

So, from a full postcode, we might want to extract the postcode area (first 1-2 characters) or the postcode district (all characters before the space). Finding the space, and extracting everything left of the space, is fairly easy using the SEARCH function, so I will focus on the trickier example of extracting the postcode area.

Using SEARCHREGEX, I can do this in Omniscope by detecting the first alpha character (i.e. A-Z) and the first numeric character (i.e. 0-9) and then extract the characters between these two extremes. To do this we would use:

DECLARE(
/* Store position of first alpha (A-Z) and first nuymeric (0-9) */
	firstAZ, SEARCHREGEX("[A-Z]",[Postcode]),
	first09, SEARCHREGEX("[0-9]",[Postcode]),
	IF(
/* Check that we found both an alpha and a numeric, and that the numeric is after the alpha i.e. a valid postcode format */
		AND(firstAZ > 0,first09 > firstAZ),
/* Extract the alpha characters, if valid */
		MID([ Postcode], firstAZ, first09-firstAZ),
/* Return nothing if postcode invalid */
		NULL
	)
)

However, regex extraction is simpler and (once we're confident in regex - lots of tutorials online) allows for far more complex extractions. So here is the same function using Javascript:

SCRIPT(`
// Declare the pattern we want to extract, and then apply it to our input text
  var re = /([A-Z]*)/;
  var m = re.exec(text1);
// We should have one (or more) groups of character sets now
  if (m == null) {
// If not, then return "No"
    'No'
  } else {
// If we do have at least one character group, return the first (could return many, concatenated, if required)
    m[0]
  }
`, "text1", [Postcode])

The structure of this is:

Firstly we have the Javascript code itself, which includes the use of a variable called "text1"
Then, in the last line, we have the declaration of "text1" (this can be called anything, and needs to be the same name here as inside the script itself, and passing the value of our [Postcode] field into this variable, for use in the script

This only scrapes the surface of this (Javascript regex) approach - there are many ways to use this as the basis for far more complex, and valuable, text extractions... but as Steve's original article demonstrates, there are many, many more uses for Javascript.

Happy scripting...

Running multiple Omniscopes on a Mac

aknotts — Fri, 03 Feb 2012 05:09:19 -0500

Running Omniscope on Windows allows you to have a number of files open in different Omniscope instances at a time. On a Mac if you try and open an Omniscope file when a file is already open it prompts you to shut down the first file to open this new file. If you want to open a second (or third) instance of Omniscope, open a terminal window (from Applications/Utilities or by typing terminal in the spotlight search). When the terminal window is open type the following command:

open -n /Applications/"Visokio Omniscope.app"

this will open another Omniscope instance and you can open a second file and work with it as normal.

Vlookup like capability

schergr — Fri, 02 Dec 2011 14:13:11 -0500

Trying to filter a data block, based on a "look up" from contents of another data block. IN other words. I'm trying to Select * from BLOCK A WHERE COL = SELECT COLB FROM TABLE B

Any thoughts?

How can I classify records into quartiles or deciles?

Guy_Cuthbert — Thu, 04 Dec 2008 09:05:00 -0500

Omniscope does not yet have automatic grouping & ranking of data by numeric values (as of release 2.4) but it is relatively easy to create a formula field which will do the job for us.

To create deciles for a selected field ("GrossUnits" in this example, just replace with your field name):

10-INTFLOOR(RANK([GrossUnits],"GrossUnits")/(DATASET_NONEMPTYCOUNT("GrossUnits")/10))

to create quartiles instead, simply replace each 10 with 4 i.e.

4-INTFLOOR(RANK([GrossUnits],"GrossUnits")/(DATASET_NONEMPTYCOUNT("GrossUnits")/4)

Breaking this down...

1. Firstly we find out where each row ranks in the dataset, based on its GrossUnits value:
RANK([GrossUnits],"GrossUnits")
2. Then we calculate the number of rows of data which contain numeric values:
DATASET_NONEMPTYCOUNT("GrossUnits")
and then divide this by 4 or 10 (or any other integer e.g. 100 for percentiles) to calculate how many rows fit in each "band" of our grouping
3. Then we divide the rank by the position by the number of records in each band, and round down the result:
INTFLOOR(RANK([GrossUnits],"GrossUnits")/(DATASET_NONEMPTYCOUNT("GrossUnits")/4
4. Finally, we subtract the result from our grouping to get the result - the very highest value in the dataset will get a rank of 1, which when divided by the number of records in a band (more than 1), and then rounded down, will result in 0... which when subtracted from our grouping value will place it in the top band, correctly.

Note: The DATASET_NONEMPTYCOUNT function will not include rows where cells are blank (you will need to replace them with zeros to address this).