Difference between revisions of "Support FAQ"
m (add explanation on how to look at memory chart in AMI) |
|||
(424 intermediate revisions by 9 users not shown) | |||
Line 1: | Line 1: | ||
+ | = Upload Files and Contact Support From 3forge Portal = | ||
+ | Step1: Log in to the account: [https://3forge.com/login.html Log in ] <br> | ||
+ | Step2: '''''Click Account''''' -> '''''Help''''', where you can detail your support questions and send attachments to the support team.<br> | ||
+ | Step3: Click '''''Contact Us'''''. The support team will be notified of the support ticket you just created.<br> | ||
+ | [[File:ContactSupport.png]] | ||
= Helpful Tricks = | = Helpful Tricks = | ||
== Inserting Data from Datamodel to AMI DB == | == Inserting Data from Datamodel to AMI DB == | ||
Line 598: | Line 603: | ||
[[File:DisplayTable.png|1000px|frameless|center]] | [[File:DisplayTable.png|1000px|frameless|center]] | ||
− | Firstly, we will create a table visualisation on the Country table (or table or your choice). Next we want to create a callback such that every time we click on a values in a row it will generate the display table (table in the right panel above). To do this we will use the ''onCellClicked(column,val,rowval)'' callback under amiscript callbacks for the table panel. The following script will get the row values and the corresponding column names and save it in the table. | + | Firstly, we will create a table visualisation on the Country table (or table or your choice). |
+ | |||
+ | Next create a datamodel with the display table: | ||
+ | |||
+ | <syntaxhighlight lang="amiscript"> | ||
+ | { | ||
+ | create table RowTable (ColumnName String, Value String); | ||
+ | } | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | Next we want to create a callback such that every time we click on a values in a row it will generate the display table (table in the right panel above). To do this we will use the ''onCellClicked(column,val,rowval)'' callback under amiscript callbacks for the table panel. The following script will get the row values and the corresponding column names and save it in the table. | ||
<syntaxhighlight lang="amiscript"> | <syntaxhighlight lang="amiscript"> | ||
Line 614: | Line 629: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | + | Enable the ''displayTable'' datamodel from the Variable Tree on the right so that the script knows what datamodel to process: | |
− | + | [[File:VariableTreeEnable.png|1000px|frameless|center]] | |
− | |||
− | |||
− | |||
− | |||
− | |||
+ | Now we need to make the columns clickable so choose one column (or any number) and under edit column turn on clickable. | ||
− | Test this and then create a visualisation on the display table in the right panel. Now to have the datamodel containing the display table to update with the row values from the main table, we will update the | + | Test this and then create a visualisation on the display table in the right panel. Now to have the datamodel containing the display table to update with the row values from the main table, we will update the displayTable code snippet to: |
<syntaxhighlight lang="amiscript"> | <syntaxhighlight lang="amiscript"> | ||
{ | { | ||
// create table RowTable (ColumnName String, Value String); | // create table RowTable (ColumnName String, Value String); | ||
+ | Table RowValTable = wheres.get("RowValTable"); | ||
create table RowTable as select * from RowValTable; | create table RowTable as select * from RowValTable; | ||
} | } | ||
Line 666: | Line 678: | ||
= Flashing Cells = | = Flashing Cells = | ||
+ | To make table cell flash in response to cell value changing, right click on the table -> style<br> | ||
+ | [[File:Cell flash1.png]]<br> | ||
+ | |||
+ | In the CELL FLASH section, you can configure the ''flash up/down color'' and ''flash Duration''.<br> | ||
+ | [[File:Cellflash2.png]]<br> | ||
+ | |||
+ | Note:<br> | ||
+ | *The flash Duration is in the unit of milliseconds. | ||
+ | *Flash up color is the color the cell changes to when the cell value goes up. | ||
+ | *Flash down color is the color the cell changes to when the cell value goes down. | ||
+ | |||
+ | = Apply row-wise filters based on a specific column = | ||
+ | oftentimes, the user might want to only show specific rows and hide the other rows based on a specific column. The following example demonstrates how we can apply row-wise filter based on a specific column in a real time table, as well as how to show summary of a given table and how to align two tables in the panel programmatically. | ||
+ | |||
+ | 1.Creating the demo table<br> | ||
+ | For this demonstration, we need to create a real time table with the following schema and data. | ||
+ | |||
+ | <syntaxhighlight lang="sql"> | ||
+ | create public table RT_table(System string, Symbol string, Quantity integer); | ||
+ | |||
+ | insert into RT_table values("sys1","TSLA",20),("sys2","AAPL",30),("sys3","AAPL",30),("sys1","TEST",60), | ||
+ | ("sys2","AAPL",10),("sys3","TEST",80),("sys3","TSLA",80),("sys2","TSLA",45); | ||
+ | </syntaxhighlight> | ||
+ | [[File:Table.png]] | ||
+ | |||
+ | == Set visible columns == | ||
+ | Suppose we only want to visualize the columns "System" and "Quantity", hiding column "Symbol". We can do this via ami script, using the ami script function setVisibleColumns() against your real time table panel. To achieve this, one solution is to create a button such that when the user clicks the button, the setVisibleColumns() function will be triggered. If we double click the button, go to the tab Ami Script, and enter the following command: | ||
+ | |||
+ | <syntaxhighlight lang="sql"> | ||
+ | list VisibleCols = new list("Quantity","System"); | ||
+ | aggPnl.setVisibleColumns(VisibleCols); | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | [[File:Button.png]] | ||
+ | <br> | ||
+ | Inside the function setVisibleColumns(), we can specify the names of the columns we want to make visible and pass in this as a list. Once we click the button, now the real time table only has columns "System" and "Quantity". | ||
+ | |||
+ | [[File:Hiding column.png]] | ||
+ | |||
+ | 2. Apply row-wise filters to rows where '''Symbol=="TEST"'''<br> | ||
+ | Suppose we want to aggregate on the table excluding the rows where Symbol=="TEST", here is how we can configure this using ami script to programmatically set up the filter. If we go to our panel, and right click on the central button, select AmiScript Callbacks | ||
+ | [[File:OnColumnArrangedCallback.png]] | ||
+ | |||
+ | Go to the tab onColumnsArranged(), and enter the following command: | ||
+ | |||
+ | <syntaxhighlight lang="java"> | ||
+ | if (visiblePnl.getVisibleColumns().contains("System") && !visiblePnl.getVisibleColumns().contains("Symbol")) //if visible cols contain system but not symbol | ||
+ | { | ||
+ | visiblePnl.setCurrentWhere("\"Symbol\"!=\"TEST\"")//anything whose symbol is not equal to TEST will be displayed, i.e. set filter to filter out rows where symbol=="TEST" | ||
+ | } | ||
+ | else | ||
+ | { | ||
+ | visiblePnl.setCurrentWhere("true"); //display everything | ||
+ | } | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | Now if I hit the submit button, everytime there is a change in the column arrangement, the '''onColumnsArranged()''' callback gets fired. In this case, all the rows whose symbol=="TEST" will not be displayed. | ||
+ | |||
+ | [[File:AfterSettingUpCallback.png]] | ||
+ | |||
+ | 3. Aggregate on the visible columns and rows <br> | ||
+ | Once the filters are properly set up, we might also want to do a real-time aggregate table on this. Let's open up a new panel and right click on the central button, select '''create realtime table/visualization''' and choose '''Aggregate Table''' widget against ''' aggPnl''' (which is the panel after we apply row & column-wise filters) | ||
+ | [[File:Setup1.jpg]] | ||
+ | [[File:Step2.jpg]] | ||
+ | |||
+ | Next fill in the fields to specify on what columns you want to do aggregation on. Suppose we want to group by only on '''System''' and return the '''sum(Quantity)''', then we can configure the table like this: <br> | ||
+ | [[File:Config1.jpg]] | ||
+ | |||
+ | The final window that consists both panels may look something like this:<br> | ||
+ | [[File:Result.jpg]] | ||
+ | |||
+ | 4.Show summary of table <br> | ||
+ | We may also want to show a summary of the table. The way to do this is to right click the central button on the table you want to show summary of, and go to '''settings'''. Then scroll all the way down and enable '''summary of rows'''<br> | ||
+ | [[File:Show summary1.jpg]] | ||
+ | [[File:Enable summary.jpg]] <br> | ||
+ | |||
+ | Once enabled, now we can select the rows we want to show summary of and right click -> '''Summarize selected''' <br> | ||
+ | [[File:Summarize selected.jpg]] | ||
+ | [[File:Summary result.jpg]] | ||
+ | |||
+ | == Align the tables in the panels == | ||
+ | Suppose we want to align the following two tables that are situated in two separate panels.<br> | ||
+ | [[File:AlignTableCase.jpg]]<br> | ||
+ | First, we need to make sure that the two tables must have identical column headers. Let's modify the second column header name '''Total Quantity''' to '''Quantity''' to match the corresponding column header name in table 1. | ||
+ | To do this, let's right click on the second column in table 2 and select '''edit column''' | ||
+ | [[File:Edit column.jpg]] | ||
+ | <br> | ||
+ | Go to the '''Column Header''', and modify '''Title''' field<br> | ||
+ | [[File:ChangeName1.jpg]] | ||
+ | [[File:ChangeName2.jpg]] | ||
+ | |||
+ | |||
+ | Right click on table1, go to '''AmiScript Callbacks''' and then go to '''OnColumnSized''', enter the following amiscripts and hit '''submit'''<br> | ||
+ | <syntaxhighlight lang="sql"> | ||
+ | list sourceCols = aggPnl.getVisibleColumns(); | ||
+ | for (String col: sourceCols) | ||
+ | { | ||
+ | summaryPnl.getColumn(col).setWidth(aggPnl.getColumn(col).getWidth()); | ||
+ | } | ||
+ | </syntaxhighlight> | ||
+ | [[File:S1.jpg]] | ||
+ | [[File:Amiscropt.jpg]] | ||
+ | |||
+ | Now the two tables are fully aligned. You could drag either one of the tables however you want, you will find both tables will always stay aligned and in sync. | ||
+ | |||
+ | [[File:AlignRes.jpg]] | ||
+ | |||
+ | = Include and Apply New Style To Your Dashboard = | ||
+ | AMI has several layout styles you could choose from. These layout styles are encoded in JSON format files and are placed in amione/data/styles directory. In this example, we have a stylesheet called '''DARKMATTER.amistyle.json''' and show how to include it in AMI.<br> | ||
+ | 1.Navigate to the AMI installation path and place the attached JSON file inside the styles directory (amione/data/styles) <br> | ||
+ | [[File:Dir1.jpg]] | ||
+ | <br> | ||
+ | 2.Navigate to /amione/config and add the following inside local.properties file | ||
+ | <pre> | ||
+ | ami.style.files=data/styles/*amistyle.json | ||
+ | </pre> | ||
+ | [[File:L2.jpg]] | ||
+ | [[File:L3.png]] | ||
+ | |||
+ | 3.Restart AMI | ||
+ | shut down the current AMI instance and restart | ||
+ | <br> | ||
+ | |||
+ | 4.Log in and open Dashboard > Style Manager<br> | ||
+ | [[File:L31.jpg]] | ||
+ | <br> | ||
+ | |||
+ | 5.Select Layout Default from tree and Dark Matter from the dropdown list | ||
+ | Your dashboard should now pick up the styles from the Dark Matter stylesheet.<br> | ||
+ | [[File:Layout.png]] | ||
+ | [[File:Like.jpg]] | ||
+ | |||
+ | = Encrypting passwords in access.txt = | ||
+ | 3forge recommends using the access.txt Authentication (AmiAuthenticatorFileBacked | ||
+ | Authentication Plugin) only for demo purposes, for production environments we recommend | ||
+ | implementing a AmiAuthenticator Plugin. | ||
+ | By default, AMI does not encrypt passwords in the access.txt. This document details the steps | ||
+ | for encrypting passwords in your access.txt. | ||
+ | |||
+ | 1. Ensure your 3forge AMI Application is running. Your access.txt by default would be located in | ||
+ | your data directory. | ||
+ | |||
+ | [[File:Files in AMI data folder.png]] | ||
+ | |||
+ | 2. This document will contain a list of all users and their passwords as well as their | ||
+ | permissions. | ||
+ | |||
+ | [[File:Access file.png]] | ||
+ | |||
+ | 3. To start encrypting the passwords you will need to generate the encrypted string for | ||
+ | each password. To do so you will need to telnet to your ami.db.console.port whose | ||
+ | default value is 3290 and login to a user with DB permissions. | ||
+ | |||
+ | [[File:Putty Configuration.png]] | ||
+ | |||
+ | [[File:PuTTY Login.png]] | ||
+ | |||
+ | 4.To encrypt each password, run the AMIScript method on the console | ||
+ | strEncrypt("your-password"). This command will return your encrypted password. | ||
+ | |||
+ | [[File:Encryption Procedure.png]] | ||
+ | |||
+ | 5. For each password in the access.txt update the password with the encrypted value. | ||
+ | |||
+ | [[File:Passwords Updating.png]] | ||
+ | |||
+ | 6. Add a new property in a local.properties or create one in your config directory. In the file | ||
+ | add the following property: | ||
+ | users.access.file.encrypt.mode=password | ||
+ | |||
+ | [[File:Local.properties.png]] | ||
+ | |||
+ | 7. The final step is to restart your 3forge AMI Application | ||
+ | |||
+ | 1st Note: Store the amikey.aes securely so that you have a recovery mechanism setup for | ||
+ | this in case of data loss. This key by default is set by the property and configured with: | ||
+ | ami.aes.key.file=persist/amikey.aes. If lost, users will not be able to login to their | ||
+ | accounts. | ||
+ | |||
+ | 2nd Note: To change the the access.txt file set the property: | ||
+ | users.access.file=pathToYourAccess.txt | ||
+ | |||
+ | = Window Visibility Permission Control Over Different User Groups = | ||
+ | AMI has its own way of doing permission control over different groups of users so that certain windows are only visible to users with entitlements. Suppose we have two windows named '''Restricted''' and '''Everyone'''. The Restricted window should '''NOT''' be visible to the users whose role is '''NOT''' Admin. The following screenshot shows how to set up such a scenario. <br> | ||
+ | 1.Go to Dashboard -> Custom Callbacks <br> | ||
+ | [[File:P1.jpg]] | ||
+ | <br> | ||
+ | 2.Go to '''onStartup''' tab and enter the following AMI script commands:<br> | ||
+ | [[File:P3.png]] | ||
+ | <br> | ||
+ | <syntaxhighlight lang="java"> | ||
+ | string role = session.getProperty("ROLE"); | ||
+ | if (role != "Admin"){ | ||
+ | for (window w: session.getWindows()){ | ||
+ | if (w.getName()=="Restricted"){ | ||
+ | w.setType("HIDDEN"); | ||
+ | w.minimize(); | ||
+ | } | ||
+ | } | ||
+ | } | ||
+ | </syntaxhighlight> | ||
+ | <br> | ||
+ | 3. Hit '''Submit'''<br> | ||
+ | Now the users should be able to see different windows based on their entitlements.<br> | ||
+ | [[File:P5.jpg]] | ||
+ | |||
+ | = Subscribe to Realtime Tables/feeds From a Datamodel and Listen for Changes to Update Form Fields = | ||
+ | Suppose we have two real time tables: '''ReconFailure''' and '''ReconSuccess''' that keeps track of successful and failed transactions across different systems in real time, initially set to zero<br> | ||
+ | [[File:ReconFailureSchema.jpg]] | ||
+ | [[File:ReconSuccessSchema.jpg]]<br> | ||
+ | You can use the following code to follow along this tutorial<br> | ||
+ | <syntaxhighlight lang="sql"> | ||
+ | drop table if exists ReconFailure; | ||
+ | create public table ReconFailure(FailureCount int, System string); | ||
+ | insert into ReconFailure values(0,"s1"),(0,"s2"),(0,"s3"); | ||
+ | |||
+ | drop table if exists ReconSuccess; | ||
+ | create public table ReconSuccess(SuccessCount int, System string); | ||
+ | insert into ReconSuccess values(0,"s1"),(0,"s2"),(0,"s3"); | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | |||
+ | 1. Build a datamodel against two real time tables '''ReconFailure''' and '''ReconSuccess'''<br> | ||
+ | [[File:BuildDatamodel.jpg]]<br> | ||
+ | 2. Configure the form fields (using text field in this case)<br> | ||
+ | In a new panel, hit the central button, go to '''Create HTML Panel'''<br> | ||
+ | [[File:Create field.jpg]]<br> | ||
+ | Hit the central button in the middle, go to '''Add Field'''-> '''Text Field'''<br> | ||
+ | [[File:Field2.jpg]]<br> | ||
+ | Make 6 Text fields as below:<br> | ||
+ | [[File:Field3.jpg]] | ||
+ | |||
+ | 3. Edit the datamodel and set up form field variables that match the two real time table schemas<br> | ||
+ | [[File:ConfigureVariable.png]]<br> | ||
+ | |||
+ | 4.Subscribe to two real time feeds: '''ReconFailure''' and '''ReconSuccess'''<br> | ||
+ | [[File:ConfigureAMIScript.jpg]]<br> | ||
+ | 5. Go to '''OnProcess''' and enter the following code script | ||
+ | <syntaxhighlight lang="java"> | ||
+ | boolean isSnapshotProcessing = !session.isFeedSnapshotProcessed("ReconSuccess") && !session.isFeedSnapshotProcessed("ReconFailure"); | ||
+ | if (isSnapshotProcessing) | ||
+ | return; | ||
+ | |||
+ | RealtimeEvent rte = rtevents; | ||
+ | while(rte != null) { | ||
+ | if (rte.getFeed() == "ReconSuccess") { | ||
+ | String system = rte.getValues().get("System"); | ||
+ | int count = rte.getValues().get("SuccessCount"); | ||
+ | if (system == "s1") | ||
+ | s1SuccessField.setValue(count); | ||
+ | else if (system == "s2") | ||
+ | s2SuccessField.setValue(count); | ||
+ | else if (system == "s3") | ||
+ | s3SuccessField.setValue(count); | ||
+ | } else if (rte.getFeed() == "ReconFailure") { | ||
+ | String system = rte.getValues().get("System"); | ||
+ | int count = rte.getValues().get("FailureCount"); | ||
+ | if (system == "s1") | ||
+ | s1FailField.setValue(count); | ||
+ | else if (system == "s2") | ||
+ | s2FailField.setValue(count); | ||
+ | else if (system == "s3") | ||
+ | s3FailField.setValue(count); | ||
+ | } | ||
+ | rte = rte.getNext(); | ||
+ | } | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | 6. Set up timers to simulate real time feeds <br> | ||
+ | For the sake of demonstration, I am using two timers here to update two real time tables '''ReconFailure''' and '''ReconSuccess'''. In real life scenarios, you may connect to some external real time feeds. | ||
+ | |||
+ | <code> | ||
+ | //timer1 to update ReconSuccess | ||
+ | </code> | ||
+ | <syntaxhighlight lang="sql"> | ||
+ | create timer SuccessTransaction oftype AMISCRIPT on "500" USE script= "list Systems = new list(\"s1\",\"s2\",\"s3\"); int SysID = rand(3); String sys = Systems.get(SysID); Update ReconSuccess set SuccessCount=SuccessCount+1 where System==sys;" | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | <code> | ||
+ | //timer2 to update ReconFailure | ||
+ | </code> | ||
+ | |||
+ | <syntaxhighlight lang="sql"> | ||
+ | create timer FailureTransaction oftype AMISCRIPT on "500" USE script= "list Systems = new list(\"s1\",\"s2\",\"s3\"); int SysID = rand(3); String sys = Systems.get(SysID); Update ReconFailure set FailureCount=FailureCount+1 where System==sys;" | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | Once enabling the timer, the figures in the text fields will be in sync with the counts in two real time tables.<br> | ||
+ | [[File:SyncResult.jpg]] | ||
+ | |||
+ | = Real time Data model = | ||
+ | We can have our data model subscribe to a real time feed to make it a real time data model. <br> | ||
+ | Let's say we have a real time feed '''transaction(TransactionID Long,sym String,price Double)''' <br> | ||
+ | 1. create a data model called '''realtimeDM''' and subscribe to real time feed '''transaction'''<br> | ||
+ | [[File:RealtimeDM0.jpg]]<br> | ||
+ | |||
+ | 2. You could also configure queries to construct derived tables<br> | ||
+ | In this example, we created another table showing the top3 symbol with the most total price.<br> | ||
+ | [[File:RealtimeDMDerived.jpg]] | ||
+ | <br> | ||
+ | Additionally, you could also configure the '''conflate time''' parameter to control how frequently you want your data model to rerun and refresh.<br> | ||
+ | In this example, it is set to 10 seconds, which means the data model will rerun 10 seconds per time.<br> | ||
+ | [[File:RealtimeDM conflate.jpg]]<br> | ||
+ | 3. Create real time visualization panel off of the real time data model that we just created<br> | ||
+ | In this example, we created a real time heatmap off of the '''top3sym''' table.<br> | ||
+ | [[File:RealtimeDM vp.jpg]]<br> | ||
+ | [[File:RealtimeDMChooseTable.jpg]]<br> | ||
+ | 4. Final Heatmap<br> | ||
+ | [[File:RealtimeDMHeatmap.jpg]] | ||
+ | |||
+ | '''Note that you can press space on the heatmap to zoom in/out.''' | ||
+ | |||
+ | = Import data model and panel configuration to AMI = | ||
+ | 1. Create a '''/myConfigs''' (can be any name you want) directory local to your AMI installation and put the configuration files (usually in the format of JSON) in it <br> | ||
+ | The example below has the configuration file named CountryTemplate.json. You can have multiple configuration files<br> | ||
+ | [[File:ImportDM1.jpg]] <br> | ||
+ | 2. Read, Parse the config files and import the data model and panel to the AMI session<br> | ||
+ | Also additionally, you can configure data model's '''WHERE''' variable.<br> | ||
+ | The amiScript below show how to achieve these where each visualization panel has exactly one underlying data model: <br> | ||
+ | |||
+ | <syntaxhighlight lang="java"> | ||
+ | filesystem fs=session.getFileSystem(); | ||
+ | String Panelconfig = fs.readFile("myConfigs/testPanelConfig.json"); | ||
+ | String DMconfig = fs.readFile("myConfigs/testDMConfig.json"); | ||
+ | map Panelparsed = parseJson(Panelconfig); | ||
+ | map DMparsed = parseJson(DMconfig); | ||
+ | datamodel dm=session.importDatamodel(DMparsed); | ||
+ | map m = Panelparsed.jsonPath("portletConfigs.0.portletConfig.dm.0"); //get the id from the data model that we imported | ||
+ | m.put("dmadn", dm.getId()); //put the id above into the panel's configuration | ||
+ | dm.process(new map("WHERE", "Code=\"ABW\"")); // configure WHERE variable | ||
+ | session.importWindow("newWindow1", Panelparsed); //import the window | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | = Progress Bar in Column Formatting = | ||
+ | On the frontend table GUI, we can add and configure additional columns to make it look like a progress bar. <br> | ||
+ | Suppose we have a GUI table '''progress''' like the following:<br> | ||
+ | [[File:ProgressBar.jpg]]<br> | ||
+ | We can configure an additional column that displays the ratio of the number of Completed and the total number and shows the current progress<br> | ||
+ | [[File:ProgressBar2.jpg]]<br> | ||
+ | Additionally, we can also configure the style of the progress bar. For example:<br> | ||
+ | [[File:ProgressBar3.jpg]] | ||
+ | |||
+ | = Breadcrumb Filter Example = | ||
+ | This is a basic example of a breadcrumb filter through the use of HTML and Javascript in a Form Div field. <br><br> | ||
+ | [[File:Breadcrumb1.png|500px|500px]] <br><br> | ||
+ | The layout follows the breadcrumb trail of tiers (T1 - T4) as filter levels. Most of the script can be found in the custom methods of the layout, and some in the onAmiJsCallback callback of the breadcrumb panel (for handling clicks to reset filters to indicated level/tier). <br><br> | ||
+ | Layout: <br> | ||
+ | <blockquote><pre> | ||
+ | {"layouts":[{"data":{"includeFiles":[],"metadata":{"amiScriptMethods":["{\n"," string getBreadCrumbStyle() {\n"," return \"<style>\n"," ul.breadcrumb {padding: 10px 10px;\n"," list-style: none;\n"," background-color: #eee;\n"," }\n"," ul.breadcrumb li {\n"," display: inline;\n"," font-size: 18px;\n"," }\n"," ul.breadcrumb li+li:before {\n"," padding: 8px;\n"," color: black;\n"," content: \\\"/\\\";\n"," }\n"," ul.breadcrumb li a {\n"," color: #0275d8;\n"," text-decoration: none;\n"," }\n"," ul.breadcrumb li a:hover {\n"," color: #01447e;\n"," text-decoration: underline;\n"," }\n"," </style>\";\n"," };\n"," \n"," boolean setFilter(){\n"," //get panels\n"," TablePanel tier1 = layout.getPanel(\"tier1\");\n"," TablePanel tier2 = layout.getPanel(\"tier2\");\n"," TablePanel tier3 = layout.getPanel(\"tier3\");\n"," TablePanel tier4 = layout.getPanel(\"tier4\");\n"," \n"," //build where clause for filtering & values for breadcrumbs\n"," string whereClause = \"\";\n"," string t1Vals = \"\";\n"," string t2Vals = \"\";\n"," string t3Vals = \"\";\n"," string t4Vals = \"\";\n"," if (tier1.getSelectedRows().size()!=0){\n"," row firstRow = tier1.getSelectedRows().get(0);\n"," string firstRowVal = firstRow.getValue(\"T1\");\n"," whereClause += \"T1 IN (\\\"${firstRowVal}\\\"\";\n"," t1Vals += firstRowVal;\n"," for (int i = 1; i < tier1.getSelectedRows().size(); ++i){\n"," row r = tier1.getSelectedRows().get(i);\n"," string rVal = r.getValue(\"T1\");\n"," whereClause += \",\\\"${rVal}\\\"\";\n"," t1Vals += \", ${rVal}\";\n"," }\n"," whereClause += \")\";\n"," }\n"," if (tier2.getSelectedRows().size()!=0){\n"," if (whereClause != \"\")\n"," whereClause += \" && \";\n"," row firstRow = tier2.getSelectedRows().get(0);\n"," string firstRowVal = firstRow.getValue(\"T2\");\n"," whereClause += \"T2 IN (\\\"${firstRowVal}\\\"\";\n"," t2Vals += firstRowVal;\n"," for (int i = 1; i < tier2.getSelectedRows().size(); ++i){\n"," row r = tier2.getSelectedRows().get(i);\n"," string rVal = r.getValue(\"T2\");\n"," whereClause += \",\\\"${rVal}\\\"\";\n"," t2Vals += \", ${rVal}\";\n"," }\n"," whereClause += \")\";\n"," }\n"," if (tier3.getSelectedRows().size()!=0){\n"," if (whereClause != \"\")\n"," whereClause += \" && \";\n"," row firstRow = tier3.getSelectedRows().get(0);\n"," string firstRowVal = firstRow.getValue(\"T3\");\n"," whereClause += \"T3 IN (\\\"${firstRowVal}\\\"\";\n"," t3Vals += firstRowVal;\n"," for (int i = 1; i < tier3.getSelectedRows().size(); ++i){\n"," row r = tier3.getSelectedRows().get(i);\n"," string rVal = r.getValue(\"T3\");\n"," whereClause += \",\\\"${rVal}\\\"\";\n"," t3Vals += \", ${rVal}\";\n"," }\n"," whereClause += \")\";\n"," }\n"," if (tier4.getSelectedRows().size()!=0){\n"," if (whereClause != \"\")\n"," whereClause += \" && \";\n"," row firstRow = tier4.getSelectedRows().get(0);\n"," string firstRowVal = firstRow.getValue(\"T4\");\n"," whereClause += \"T4 IN (\\\"${firstRowVal}\\\"\";\n"," t4Vals += firstRowVal;\n"," for (int i = 1; i < tier4.getSelectedRows().size(); ++i){\n"," row r = tier4.getSelectedRows().get(i);\n"," string rVal = r.getValue(\"T4\");\n"," whereClause += \",\\\"${rVal}\\\"\";\n"," t4Vals += \", ${rVal}\";\n"," }\n"," whereClause += \")\";\n"," }\n"," \n"," //apply where clause\n"," TablePanel mainTable = layout.getPanel(\"mainTable\");\n"," mainTable.setCurrentWhere(whereClause);\n"," \n"," //call breadcrumb builder\n"," t1Vals = t1Vals == \"\" ? \"All\" : t1Vals;\n"," t2Vals = t2Vals == \"\" ? \"All\" : t2Vals;\n"," t3Vals = t3Vals == \"\" ? \"All\" : t3Vals;\n"," t4Vals = t4Vals == \"\" ? \"All\" : t4Vals;\n"," map crumbValues = new map();\n"," crumbValues.put(\"t1\", t1Vals);\n"," crumbValues.put(\"t2\", t2Vals);\n"," crumbValues.put(\"t3\", t3Vals);\n"," crumbValues.put(\"t4\", t4Vals);\n"," buildBreadCrumb(crumbValues);\n"," \n"," return true;\n"," };\n"," \n"," boolean buildBreadCrumb(map crumbValues){\n"," String breadCrumb = getBreadCrumbStyle();\n"," breadCrumb += \"<body>\n"," <ul class=\\\"breadcrumb\\\">\";\n"," for (int i = 1; i < crumbValues.size() + 1; ++i){\n"," string crumbVal = crumbValues.get(\"t${i}\");\n"," if (crumbVal == \"All\"){\n"," boolean allBreak = true;\n"," for (int j = i; j < crumbValues.size() + 1; ++j){\n"," string allCheck = crumbValues.get(\"t${j}\");\n"," if (allCheck != \"All\")\n"," allBreak = false;\n"," }\n"," if (allBreak) \n"," break;\n"," }\n"," breadCrumb += \"<li><a onclick='amiJsCallback(this,${i}, 0);'>${crumbVal}</a></li>\";\n"," }\n"," breadCrumb += \"</ul>\n"," </body>\";\n"," \n"," FormPanel breadCrumbPnl = layout.getPanel(\"breadcrumbPnl\");\n"," FormDivField breadcrumbDiv = breadCrumbPnl.getField(\"breadcrumbDiv\");\n"," breadcrumbDiv.setValue(breadCrumb);\n"," };\n","}"],"customPrefsImportMode":"reject","dm":{"dms":[{"callbacks":{"entries":[{"amiscript":["{\n"," CREATE TABLE Sample(T1 String, T2 String, T3 String, T4 String, value string);\n"," \n"," for (int i1=0; i1<2; ++i1){\n"," string t1 = \"1.${i1+1}\";\n"," for (int i2=0; i2<4; ++i2){\n"," string t2 = \"2.${i2+1}\";\n"," for (int i3=0; i3<8; ++i3){\n"," string t3 = \"3.${i3+1}\";\n"," for (int i4=0; i4<8; ++i4){\n"," string t4 = \"4.${i4+1}\";\n"," insert into Sample values (t1, t2, t3, t4, \"Sample\");\n"," }\n"," }\n"," }\n"," }\n","}\n"],"hasDatamodel":true,"linkedVariables":[],"name":"onProcess","schema":{"tbl":[{"cols":[{"nm":"T1","tp":"String"},{"nm":"T2","tp":"String"},{"nm":"T3","tp":"String"},{"nm":"T4","tp":"String"},{"nm":"value","tp":"String"}],"nm":"Sample","oc":"ask"}]}}]},"lbl":"mainTable","lm":0,"lower":[],"queryMode":"startup","test_input_type":"OPEN","test_input_vars":"String WHERE=\"true\";","to":0},{"callbacks":{"entries":[{"amiscript":["{\n"," CREATE TABLE Tier1 AS SELECT T1 FROM Sample WHERE ${WHERE} group by T1;\n","}"],"hasDatamodel":true,"inputDm":["mainTable"],"linkedVariables":[],"name":"onProcess","schema":{"tbl":[{"cols":[{"nm":"T1","tp":"String"},{"nm":"T2","tp":"String"},{"nm":"T3","tp":"String"},{"nm":"T4","tp":"String"},{"nm":"value","tp":"String"}],"nm":"Sample","oc":"ask"},{"cols":[{"nm":"T1","tp":"String"}],"nm":"Tier1","oc":"ask"}]}}]},"lbl":"tier1","lm":0,"lower":["mainTable"],"test_input_type":"OPEN","test_input_vars":"String WHERE=\"true\";","to":0},{"callbacks":{"entries":[{"amiscript":["{\n"," CREATE TABLE Tier2 AS SELECT T2 FROM Sample WHERE ${WHERE} group by T2;\n","}\n"],"hasDatamodel":true,"inputDm":["mainTable"],"linkedVariables":[],"name":"onProcess","schema":{"tbl":[{"cols":[{"nm":"T1","tp":"String"},{"nm":"T2","tp":"String"},{"nm":"T3","tp":"String"},{"nm":"T4","tp":"String"},{"nm":"value","tp":"String"}],"nm":"Sample","oc":"ask"},{"cols":[{"nm":"T2","tp":"String"}],"nm":"Tier2","oc":"ask"}]}}]},"lbl":"tier2","lm":0,"lower":["mainTable"],"test_input_type":"OPEN","test_input_vars":"String WHERE=\"true\";","to":0},{"callbacks":{"entries":[{"amiscript":["{\n"," CREATE TABLE Tier3 AS SELECT T3 FROM Sample WHERE ${WHERE} group by T3;\n","}\n"],"hasDatamodel":true,"inputDm":["mainTable"],"linkedVariables":[],"name":"onProcess","schema":{"tbl":[{"cols":[{"nm":"T1","tp":"String"},{"nm":"T2","tp":"String"},{"nm":"T3","tp":"String"},{"nm":"T4","tp":"String"},{"nm":"value","tp":"String"}],"nm":"Sample","oc":"ask"},{"cols":[{"nm":"T3","tp":"String"}],"nm":"Tier3","oc":"ask"}]}}]},"lbl":"tier3","lm":0,"lower":["mainTable"],"test_input_type":"OPEN","test_input_vars":"String WHERE=\"true\";","to":0},{"callbacks":{"entries":[{"amiscript":["{\n"," CREATE TABLE Tier4 AS SELECT T4 FROM Sample WHERE ${WHERE} group by T4;\n","}\n"],"hasDatamodel":true,"inputDm":["mainTable"],"linkedVariables":[],"name":"onProcess","schema":{"tbl":[{"cols":[{"nm":"T1","tp":"String"},{"nm":"T2","tp":"String"},{"nm":"T3","tp":"String"},{"nm":"T4","tp":"String"},{"nm":"value","tp":"String"}],"nm":"Sample","oc":"ask"},{"cols":[{"nm":"T4","tp":"String"}],"nm":"Tier4","oc":"ask"}]}}]},"lbl":"tier4","lm":0,"lower":["mainTable"],"test_input_type":"OPEN","test_input_vars":"String WHERE=\"true\";","to":0}]},"fileVersion":3,"rt":{"processors":[]},"stm":{"styles":[{"id":"LAYOUT_DEFAULT","lb":"Layout Default","pt":"DEFAULT"}]}},"portletConfigs":[{"portletBuilderId":"amidesktop","portletConfig":{"active":"Div1","amiPanelId":"@DESKTOP","amiStyle":{"pt":"LAYOUT_DEFAULT"},"windows":[{"header":true,"height":697,"hidden":false,"left":582,"portlet":"Div1","pos":0,"state":"flt","title":"Window","top":442,"width":1235,"zindex":2},{"header":true,"height":137,"hidden":false,"left":558,"portlet":"breadcrumbPnl","pos":1,"state":"flt","title":"Window - 2","top":277,"width":1280,"zindex":1}]}},{"portletBuilderId":"div","portletConfig":{"amiPanelId":"Div1","amiStyle":{"pt":"LAYOUT_DEFAULT","vl":{"div":{"divAlign":"start"}}},"child1":"Div2","child2":"mainTable","dir":"v","isMin":false,"offset":0.14736842105263157,"upid":"Div1"}},{"portletBuilderId":"div","portletConfig":{"amiPanelId":"Div2","amiStyle":{"pt":"LAYOUT_DEFAULT"},"child1":"tier1","child2":"Div3","dir":"h","isMin":false,"offset":0.15460992907801419,"upid":"Div2"}},{"portletBuilderId":"div","portletConfig":{"amiPanelId":"Div3","amiStyle":{"pt":"LAYOUT_DEFAULT"},"child1":"tier2","child2":"Div4","dir":"h","isMin":false,"offset":0.24283305227655985,"upid":"Div3"}},{"portletBuilderId":"div","portletConfig":{"amiPanelId":"Div4","amiStyle":{"pt":"LAYOUT_DEFAULT"},"child1":"tier3","child2":"tier4","dir":"h","isMin":false,"offset":0.48654708520179374,"upid":"Div4"}},{"portletBuilderId":"amiform","portletConfig":{"amiPanelId":"breadcrumbPnl","amiStyle":{"pt":"LAYOUT_DEFAULT"},"amiTitle":"","buttons":[],"callbacks":{"entries":[{"amiscript":["int selected = action;\n","int totalTiers = 4;\n","\n","for (int i = selected + 1; i < totalTiers + 1; ++i){\n"," TablePanel tierPanel = layout.getPanel(\"tier${i}\");\n"," tierPanel.selectRows(false);\n","}"],"linkedVariables":[],"name":"onAmiJsCallback"}]},"dm":[],"fields":[{"callbacks":{},"disabled":false,"dme":"","heightPx":59,"help":"","hidden":true,"l":"breadcrumbDiv","labelHidden":true,"leftPosPx":160,"n":"breadcrumbDiv","style":"","t":"divField","template":"","topPosPx":40,"widthPx":840,"zidx":1}],"guides":[],"htmlTemplate2":null,"snap":20,"titlePnl":{"title":""},"upid":"breadcrumbPnl"}},{"portletBuilderId":"amistatictable","portletConfig":{"amiCols":[{"fm":"T1","id":"T1","location":0,"tl":"T1","tp":"text","width":188},{"fm":"T2","id":"T2","location":1,"tl":"T2","tp":"text","width":214},{"fm":"T3","id":"T3","location":2,"tl":"T3","tp":"text","width":215},{"fm":"T4","id":"T4","location":3,"tl":"T4","tp":"text","width":268},{"fm":"value","id":"value","location":4,"tl":"Value","tp":"text","width":130},{"id":"D","width":100}],"amiPanelId":"mainTable","amiStyle":{"pt":"LAYOUT_DEFAULT"},"amiTitle":"Sample","curtimeUpdateFrequency":1000,"dm":[{"dmadn":"mainTable","dmtbid":["Sample"]}],"dynamicColumns":"false","editDblClk":true,"editInplace":false,"editMenuTitle":"Edit Row(s)","editMode":0,"editRerunDM":true,"filters":{},"pinCnt":0,"rollupEnabled":false,"scrollToBottomOnAppend":false,"showCommandMenu":true,"showLastRuntime":true,"titlePnl":{"title":"Sample"},"upid":"mainTable","varTypes":{"T1":"String","T2":"String","T3":"String","T4":"String","value":"String"}}},{"portletBuilderId":"amistatictable","portletConfig":{"amiCols":[{"ei":"","eof":"","fm":"T1","id":"T1","location":0,"sy":"\"center\"","tl":"T1","tp":"text","width":174},{"id":"D","width":100}],"amiPanelId":"tier1","amiStyle":{"pt":"LAYOUT_DEFAULT"},"amiTitle":"Tier1","callbacks":{"entries":[{"amiscript":"setFilter();","linkedVariables":[],"name":"onSelected"}]},"curtimeUpdateFrequency":1000,"dm":[{"dmadn":"tier1","dmtbid":["Tier1"]}],"dynamicColumns":"false","editDblClk":true,"editInplace":false,"editMenuTitle":"Edit Row(s)","editMode":0,"editRerunDM":true,"filters":{},"pinCnt":0,"rollupEnabled":false,"scrollToBottomOnAppend":false,"showCommandMenu":true,"showLastRuntime":true,"titlePnl":{"title":"Tier1"},"upid":"tier1","varTypes":{"T1":"String"}}},{"portletBuilderId":"amistatictable","portletConfig":{"amiCols":[{"ei":"","eof":"","fm":"T2","id":"T2","location":0,"sy":"\"center\"","tl":"T2","tp":"text","width":169},{"id":"D","width":100}],"amiPanelId":"tier2","amiStyle":{"pt":"LAYOUT_DEFAULT"},"amiTitle":"Tier2","callbacks":{"entries":[{"amiscript":"setFilter();","linkedVariables":[],"name":"onSelected"}]},"curtimeUpdateFrequency":1000,"dm":[{"dmadn":"tier2","dmtbid":["Tier2"]}],"dynamicColumns":"false","editDblClk":true,"editInplace":false,"editMenuTitle":"Edit Row(s)","editMode":0,"editRerunDM":true,"filters":{},"pinCnt":0,"rollupEnabled":false,"scrollToBottomOnAppend":false,"showCommandMenu":true,"showLastRuntime":true,"titlePnl":{"title":"Tier2"},"upid":"tier2","varTypes":{"T2":"String"}}},{"portletBuilderId":"amistatictable","portletConfig":{"amiCols":[{"ei":"","eof":"","fm":"T3","id":"T3","location":0,"sy":"\"center\"","tl":"T3","tp":"text","width":168},{"id":"D","width":100}],"amiPanelId":"tier3","amiStyle":{"pt":"LAYOUT_DEFAULT"},"amiTitle":"Tier3","callbacks":{"entries":[{"amiscript":"setFilter();","linkedVariables":[],"name":"onSelected"}]},"curtimeUpdateFrequency":1000,"dm":[{"dmadn":"tier3","dmtbid":["Tier3"]}],"dynamicColumns":"false","editDblClk":true,"editInplace":false,"editMenuTitle":"Edit Row(s)","editMode":0,"editRerunDM":true,"filters":{},"pinCnt":0,"rollupEnabled":false,"scrollToBottomOnAppend":false,"showCommandMenu":true,"showLastRuntime":true,"titlePnl":{"title":"Tier3"},"upid":"tier3","varTypes":{"T3":"String"}}},{"portletBuilderId":"amistatictable","portletConfig":{"amiCols":[{"ei":"","eof":"","fm":"T4","id":"T4","location":0,"sy":"\"center\"","tl":"T4","tp":"text","width":167},{"id":"D","width":100}],"amiPanelId":"tier4","amiStyle":{"pt":"LAYOUT_DEFAULT"},"amiTitle":"Tier4","callbacks":{"entries":[{"amiscript":"setFilter();","linkedVariables":[],"name":"onSelected"}]},"curtimeUpdateFrequency":1000,"dm":[{"dmadn":"tier4","dmtbid":["Tier4"]}],"dynamicColumns":"false","editDblClk":true,"editInplace":false,"editMenuTitle":"Edit Row(s)","editMode":0,"editRerunDM":true,"filters":{},"pinCnt":0,"rollupEnabled":false,"scrollToBottomOnAppend":false,"showCommandMenu":true,"showLastRuntime":true,"titlePnl":{"title":"Tier4"},"upid":"tier4","varTypes":{"T4":"String"}}}]},"location":"breadcrumbs.ami","type":"ABSOLUTE"}],"rootLayout":{"location":"breadcrumbs.ami","type":"ABSOLUTE"}} | ||
+ | </pre></blockquote> | ||
+ | |||
+ | = Sending emails from AMI = | ||
+ | Below is an minimal example setup for setting up email in the properties file (''local.properties''): <br><br> | ||
+ | <code> | ||
+ | email.client.host=smtp.office365.com <br> | ||
+ | email.client.port=587 <br> | ||
+ | email.client.username=demo@outlook.com <br> | ||
+ | email.client.password=samplePassword | ||
+ | </code><br><br> | ||
+ | Change the host and port properties accordingly (gmail, outlook, etc). <br> | ||
+ | More email configuration properties can be found here: [[AMI_Configuration_Guide#Email_Configuration_Properties|3forge_Email_Configuration_Properties]]<br> | ||
+ | |||
+ | == Layout Setup == | ||
+ | There are two methods in amiscript for sending emails: <br> | ||
+ | <code> | ||
+ | sendEmail (sends an email and returns immediately with the email-send-UID)<br> | ||
+ | sendEmailSync (sends an email and returns with the email result) | ||
+ | </code><br><br> | ||
+ | |||
+ | Method description:<br> | ||
+ | [[File:EmailExample1.png|700px|700px]] <br><br> | ||
+ | |||
+ | == List of supported attachment file formats == | ||
+ | * Application: | ||
+ | atom | ||
+ | , json | ||
+ | , jar | ||
+ | , js | ||
+ | , ogg, ogv | ||
+ | , pdf | ||
+ | , ps | ||
+ | , woff | ||
+ | , xhtml, xht, xml | ||
+ | , dtd | ||
+ | , zip | ||
+ | , gz | ||
+ | , xlsx | ||
+ | *Audio: | ||
+ | au, snd | ||
+ | ,mid, rmi | ||
+ | ,mp3 | ||
+ | ,aif, aifc, aiff | ||
+ | ,m3u | ||
+ | ,ra, ram | ||
+ | ,wav | ||
+ | *Image: | ||
+ | ,bmp | ||
+ | ,cod | ||
+ | ,gif | ||
+ | ,ief | ||
+ | ,jpe, jpeg, jpg | ||
+ | ,jfif | ||
+ | ,png | ||
+ | ,svg | ||
+ | ,tif, tiff | ||
+ | ,ras | ||
+ | ,cmx | ||
+ | ,ico | ||
+ | ,pnm | ||
+ | ,pbm | ||
+ | ,pgm | ||
+ | ,ppm | ||
+ | ,rgb | ||
+ | ,xbm | ||
+ | ,xpm | ||
+ | ,xwd | ||
+ | *Message: | ||
+ | mht, mhtml, nws | ||
+ | *Text: | ||
+ | css | ||
+ | ,323 | ||
+ | ,htm, html, stm | ||
+ | ,uls | ||
+ | ,bas, c, h, txt, ami | ||
+ | ,rtx | ||
+ | ,sct | ||
+ | ,tsv | ||
+ | ,htt | ||
+ | ,htc | ||
+ | ,etc | ||
+ | ,vcf | ||
+ | *Video: | ||
+ | mp2, mpa, mpe, mpeg, mpg, mpv2 | ||
+ | ,mov, qt | ||
+ | ,lsf, lsx | ||
+ | ,asf, asr, asx | ||
+ | ,avi | ||
+ | ,movie | ||
+ | *X-world: | ||
+ | flr, vrml, wrl, wrz, xaf, xof | ||
+ | |||
+ | = Troubleshooting SSL-related issues = | ||
+ | |||
+ | Here's some guidance on how to troubleshoot the following SSL-related issues, such as '''''No available authentication scheme''''','''''Unsupported or unrecognized SSL message''''',or '''''SSL Protocol Error''''' | ||
+ | |||
+ | In our experience these issues are not caused by 3forge AMI but are generic error messages indicating that there was a problem in how the certificate was generated. <br> | ||
+ | |||
+ | Here is our recommended procedure for the generation:<br> | ||
+ | |||
+ | 1) Download the root keystore for your environment (Should not matter if using jks or pem) | ||
+ | <code>keytool -importkeystore -srckeystore cacerts.jks -destkeystore web.keystore </code> | ||
+ | Or<br> | ||
+ | <code>keytool -import -file cacert.pem -keystore web.keystore </code> | ||
+ | |||
+ | 2) Generate Certificate Signing Request (CSR) - Modifies keystore * keypass and storepass should match '''''https.keystore.password''''', source password is the cacerts password<br> | ||
+ | |||
+ | <code>keytool -genkeypair -keystore web.keystore -alias server -keyalg RSA -keysize 2048 -sigalg SHA256withRSA -dname "CN=...,OU=...,O=C=..."</code> | ||
+ | |||
+ | 3) Through your Certificate Authority (CA) create a Certificate Request -> Certificate Type, MS CA WebServer to generate a Certificate<br> | ||
+ | |||
+ | 4) Import the certificate into the keystore<br> | ||
+ | <code>keytool -import -v -trustcacerts -alias root -keystore web.keystore -file cert.cer -keypass [pass] -storepass [pass]</code> | ||
+ | |||
+ | |||
+ | 4b) In our experience the following command didn't work and was the cause of the above SSL-related errors<br> | ||
+ | <code>keytool -importcert -keystore web.keystore -alias server -file cert.cer</code> | ||
+ | |||
+ | = Memory Overload = | ||
+ | |||
+ | == Overview == | ||
+ | <big>There are several places in AMI from where memory consumption info can be collected. Then a few steps can be taken to optimize the consumption.</big> | ||
+ | |||
+ | |||
+ | |||
+ | == How do we know if AMI is facing performance issues? == | ||
+ | |||
+ | <big>1.1 The AMI Data Statistics bar will turn red indicating AMI slowdown</big> | ||
+ | [[File:Screenshot 2023-07-11 140228.png|thumb|left]]<br> | ||
+ | |||
+ | |||
+ | <br> | ||
+ | |||
+ | |||
+ | <big>1.2 Chart irregularity in the AMI Data Statistics Window</big> | ||
+ | [[File:Screenshot 2023-06-20 160206.png|820px|thumb|left]] | ||
+ | |||
+ | '''Keep in mind that the memory usage is shown as troughs (dips) in the chart. E.g. Tip of the chart = AMI memory usage + unused references (garbage). In other words, for a more accurate assessment of memory usage, look at the memory usage after the garbage collection finishes.''' | ||
+ | |||
+ | |||
+ | |||
+ | <br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br> | ||
+ | |||
+ | |||
+ | 1. Upper Graph - memory footprint of the webserver | ||
+ | <br> | ||
+ | 2. Bottom Graph - memory footprint of the center | ||
+ | <br> | ||
+ | 3. This graph is designed so that the top of the graph is the maximum memory capacity. '''If the line gets near the top, AMI is closer to memory overload.''' <br> | ||
+ | |||
+ | |||
+ | <big>1.3 AMI Log Viewer</big> | ||
+ | |||
+ | Setup of Log Viewer can be found in 3forge documentation -> | ||
+ | [[Guides#Log_Viewer_Layout]] | ||
+ | |||
+ | * Upload the file AMIOne.amilog in the filename bar and open the ''Memory Details'' tab | ||
+ | [[File:Screenshot 2023-07-11 115533.png|850px|thumb|left]] | ||
+ | |||
+ | <br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br> | ||
+ | |||
+ | 1. The red line is the maximum memory capacity of AMI. If the Used Memory and Allocated Memory reaches to the max, it suggests AMI will face performance issues. | ||
+ | |||
+ | 2. The table in the panel below gives insightful details of the AMI like | ||
+ | * Percentage of free memory | ||
+ | * Values of maximum, allocated, and used memory. | ||
+ | [[File:Screenshot 2023-07-11 115748.png|700px|thumb|left]] | ||
+ | |||
+ | |||
+ | |||
+ | <br><br><br><br><br><br><br><br><br><br><br><br> | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | 3. We can use these values to better understand how much memory AMI used up and would need to let go. | ||
+ | |||
+ | ==Troubleshooting== | ||
+ | <big>Data lives both in the webserver and the center. The most important step is to identify where the memory issue lies. | ||
+ | </big> | ||
+ | |||
+ | === <big>Web Server </big>=== | ||
+ | <big>1. Open the ''Data Model Tables'' tab from the ''AMI Data Statistics'' Window in the upper right corner of AMI</big> | ||
+ | [[File:Datamodel tables.png|700px|thumb|left]] | ||
+ | <br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br> | ||
+ | |||
+ | This tab contains information of the tables created in the current webserver the user is in. | ||
+ | * The Cells column should be looked at as it shows which table has the most data. | ||
+ | * You can also view the count of rows and columns. | ||
+ | Looking at the count of cells, we can recognize the most populated table from the web server to modify. | ||
+ | |||
+ | ====<big>SOLUTION</big> - Dropping a Table from the Web Server==== | ||
+ | Data models often use intermediary tables which aren't used in any visualizations. These tables can be dropped at the end of the data model to reduce memory footprint. | ||
+ | [[File:Droptablept2.png|900px|thumb|left]] | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | <br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br> | ||
+ | |||
+ | * In this example, the tables that are joined (price,quantity) are no longer needed in the data model as the joined table output will be used for visualizations. | ||
+ | * Hence, tables price and quantity can be dropped. | ||
+ | |||
+ | === <big>Center</big> === | ||
+ | <big>1. Open the AMI DB Shell Tool from Dashboard</big><br> | ||
+ | We can view all the tables in the center through the command line by writing | ||
+ | <syntaxhighlight lang="amiscript"> | ||
+ | SHOW TABLES; | ||
+ | </syntaxhighlight> | ||
+ | [[File:Screenshot 2023-06-20 105849.png|850px|thumb|left]] | ||
+ | |||
+ | <br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br> | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | We can view the row count of every table and find the most populated table in order to reduce memory footprint. | ||
+ | |||
+ | |||
+ | <big>2. Diagnose a Table</big><br> | ||
+ | For deeper analysis, we can diagnose a table through the AMI shell tool by writing | ||
+ | <syntaxhighlight lang="amiscript"> | ||
+ | DIAGNOSE TABLE table_name; | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | [[File:Screenshot 2023-06-20 111748.png|850px|thumb|left]] | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | <br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br> | ||
+ | |||
+ | |||
+ | The DIAGNOSE table will show all the necessary information about each column related to memory consumption (ignore TABLE_OVERHEAD) | ||
+ | * EST_MEMORY shows the memory for each column. | ||
+ | * COMMENT shows the datatypes and the byte size of each column. | ||
+ | |||
+ | ====<big>SOLUTION</big> - Datatype Conversion==== | ||
+ | If a string column exists in the table, it is recommended for the column to be stored as a '''STRING BITMAP''' or '''STRING ONDISK''' to reduce the high memory usage of a regular string column. | ||
+ | |||
+ | More info on efficient column types for a string can be found in our 3forge documentation -> [[AMI_Realtime_Database#Choosing_the_Best_Column_Type_for_String]] | ||
+ | |||
+ | If data type conversion cannot be done then dropping a table from the center would be the last option. | ||
+ | |||
+ | = Timeout expired while waiting for read lock = | ||
+ | |||
+ | == Overview == | ||
+ | |||
+ | This issue typically happens when there is a read request while AMIDB is processing a write request. AmiDB is designed to allow multiple reads but only single write at any given time. So while the write request is being processed, the incoming read request will be queued and when the timeout value is reached, it outputs the timeout exception. | ||
+ | |||
+ | === Recommendation === | ||
+ | |||
+ | The most common cause is a slow running Timer or Trigger. A short term solution is to increase the timeout value so the read threads can wait longer. However, the long term solution would be to carefully inspect and optimize the code that is doing the write so it is performant enough to avoid the timeout issue in the first place. There are a few approaches we can use to identify the problematic Timer/Trigger: | ||
+ | |||
+ | # Use AMIDB diagnostic queries ('''[[AMI_Realtime_Database#DIAGNOSE|diagnose tableName]]''', '''[[AMI_Realtime_Database#Realtime%20Tools|show timers]]''', show triggers) to find Timers/Triggers with higher average or max run time | ||
+ | # Review the AmiOne.log by hand to see what Timer/Trigger was running when the timeout expired | ||
+ | # Use the [[Guides#Log_Viewer_Layout|'''log viewer''']] dashboard to see the runtime for each Timer/Trigger. After setting up the dashboard and uploading the AmiOne.log file, navigate to the Timers and Triggers tabs and look for Timers/Triggers with significantly higher Total Run Time or significantly higher Run Time when the timeout expired | ||
+ | |||
+ | Once you have identified the slow running Timer or Trigger review the code to see what could be causing the slowness. Some common causes of inefficient Timers/Triggers include: | ||
+ | |||
+ | * Calling large Update/Insert queries | ||
+ | * Calling lots of Select/Insert/Update/Delete queries without an index on the columns in the WHERE clause | ||
+ | * Calling expensive stored procedures | ||
+ | |||
+ | = Table with CRUD Operations = | ||
+ | |||
+ | CRUD stands for Create, Read, Update, Delete and describes the four key operations for editing the contents of a table. The guide below will show how to create a Realtime Table in AMI with CRUD operations that change the underlying table in the AMIDB. | ||
+ | |||
+ | [[File:CRUD final.png]] | ||
+ | |||
+ | == Read == | ||
+ | |||
+ | First, create a table by running the AmiScript below in '''Dashboard > AMIDB Shell Tool''': | ||
+ | |||
+ | <syntaxhighlight lang="amiscript"> | ||
+ | CREATE PUBLIC TABLE crudDemo(id long, v1 string, v2 string); | ||
+ | INSERT INTO crudDemo VALUES (1,"A","O123"),(2,"A","O124"),(3,"B","O123"),(4,"A","O121"); | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | Next, create a new Window/Panel, then '''Create Realtime Table / Visualization'''. Select the Realtime Feed '''crudDemo''', click Next, then Finish. There should now be a Realtime Table with 3 columns and 4 rows. | ||
+ | |||
+ | == Update == | ||
+ | |||
+ | Click on the table, then go to '''Settings''' and change "In Table Editing" to "Single Row" or "Multiple Rows": | ||
+ | |||
+ | [[File:CRUD table settings.png|500px]] | ||
+ | |||
+ | Click on the column header of "Id" and select '''Edit Column'''. Then scroll down and change the '''Edit''' setting to '''Readonly'''. Do the same for columns "V 1" and "V 2" setting them to '''Text Field'''. | ||
+ | |||
+ | [[File:CRUD column settings.png|500px]] | ||
+ | |||
+ | Click on the table, then go to '''AmiScript Callbacks...''' and navigate to the '''onEdit(vals, oldVals)''' tab and add in the following AmiScript: | ||
+ | |||
+ | <syntaxhighlight lang="amiscript"> | ||
+ | for (row r: vals.getRows()) { | ||
+ | String updateString = ""; | ||
+ | for (String key: r.getKeys()) { | ||
+ | updateString += key + "=\"" + r.get(key) + "\", "; | ||
+ | } | ||
+ | updateString = updateString.beforeLast(",",true); | ||
+ | use ds="AMI" execute update crudDemo set ${updateString} where id==${r.get("id")}; | ||
+ | } | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | == Create == | ||
+ | |||
+ | Click on the table, then go to '''Custom Menus > Add Menu Item''', set Display to "Add Row", then go to the '''Callbacks''' tab and add in the following AmiScript: | ||
+ | |||
+ | <syntaxhighlight lang="amiscript"> | ||
+ | use ds="AMI" execute insert into crudDemo from select max(id)+1,"","" from t; | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | == Delete == | ||
+ | |||
+ | Go to '''Custom Menus > Add Menu Item''', set Display to "Delete Row(s)", then go to the '''Callbacks''' tab and add in the following AmiScript: | ||
+ | |||
+ | <syntaxhighlight lang="amiscript"> | ||
+ | TablePanel t = this.getOwner(); | ||
+ | for (Row r: t.getSelectedRows()) { | ||
+ | use ds="AMI" execute delete from crudDemo where id=="${r.get("id")}"; | ||
+ | } | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | = Logging = | ||
+ | == Turn off logging for a particular class == | ||
+ | Any time you wish to mute certain messages from showing up in your AmiOne.log, you can use the following template, replace '''CLASSNAME''' with the full class name, and put that inside your local.properties file: | ||
+ | |||
+ | <syntaxhighlight> | ||
+ | speedlogger.stream.CLASSNAME=BASIC_APPENDER;FILE_SINK;OFF | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | ''Note that each line turns off logging for a single class only.'' | ||
+ | |||
+ | <br> | ||
+ | Here is an example of extracting the class name from AmiOne.log: | ||
+ | |||
+ | INF 20231108-13:32:25.009 EST5EDT [F1POOL-04] <span style='color:blue;font-weight:bold;'>com.f1.ami.amicommon.centerclient.AmiCenterClientState</span>::onUserSnapshotRequest: localhost:3341 (seqnum=721) User demo requested: [team] of which [team] will be requested from the Ami Center | ||
+ | |||
+ | |||
+ | == Adjusting log levels in AMI == | ||
+ | |||
+ | {| class="wikitable" | ||
+ | |- | ||
+ | ! Code !! Full label | ||
+ | |- | ||
+ | | ALL || All | ||
+ | |- | ||
+ | | FNE || Fine | ||
+ | |- | ||
+ | | INF || Info | ||
+ | |- | ||
+ | | WRN || Warning | ||
+ | |- | ||
+ | | SVR || Severe | ||
+ | |- | ||
+ | | OFF || OFF | ||
+ | |} | ||
+ | |||
+ | |||
+ | You may adjust the log level for a certain class of logs by replacing the part after the last semicolon with the code in the above table: | ||
+ | |||
+ | speedlogger.stream.CLASSNAME=BASIC_APPENDER;FILE_SINK;<span style='color:red'>WRN</span> | ||
+ | |||
+ | == Logging Configuration == | ||
+ | The following set of configuration allows you to redirect logging to Stdout: | ||
+ | ===Basic=== | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.appender.BASIC_APPENDER.pattern</span>=%P %d{YMD-h:m:s.S z} [%t] %c::%M: %m %D%n | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.appender.BASIC_APPENDER.timezone</span>=EST5EDT | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.appender.BASIC_APPENDER.type</span>=BasicAppender | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.sink.FILE_SINK.type</span>=file | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.sink.FILE_SINK.fileName</span>=${f1.logs.dir}/${f1.logfilename}.log | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.sink.FILE_SINK.maxFiles</span>=10 | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.sink.FILE_SINK.maxFileSizeMb</span>=1000 | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.stream.</span>=BASIC_APPENDER;FILE_SINK;INFO | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.stream.AMI</span>=BASIC_APPENDER;FILE_SINK;OFF | ||
+ | |||
+ | ===AmiScript=== | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.appender.SIMPLE_APPENDER.pattern</span>=%P %d{YMD-h:m:s.S z} [%t] %c: %m %D%n | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.appender.SIMPLE_APPENDER.timezone</span>=EST5EDT | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.appender.SIMPLE_APPENDER.type</span>=BasicAppender | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.stream.AMISCRIPT.LOGINFO</span>=SIMPLE_APPENDER;FILE_SINK;INF | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.stream.AMISCRIPT.LOGWARN</span>=SIMPLE_APPENDER;FILE_SINK;WRN | ||
+ | |||
+ | ===Backend Messages=== | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.appender.AMIMESSAGES_APPENDER.pattern</span>=%d{YMD-h:m:s.S z} %m%n | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.appender.AMIMESSAGES_APPENDER.timezone</span>=EST5EDT | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.appender.AMIMESSAGES_APPENDER.type</span>=BasicAppender | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.sink.AMIMESSAGES_SINK.type</span>=file | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.sink.AMIMESSAGES_SINK.fileName</span>=${f1.logs.dir}/AmiMessages.log | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.sink.AMIMESSAGES_SINK.maxFiles</span>=10 | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.sink.AMIMESSAGES_SINK.maxFileSizeMb</span>=1000 | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.stream.AMI_MESSAGES</span>=AMIMESSAGES_APPENDER;AMIMESSAGES_SINK;INFO | ||
+ | |||
+ | ===AMI Statistics=== | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.appender.AMISTATS_APPENDER.pattern</span>=%m%n | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.appender.AMISTATS_APPENDER.type</span>=BasicAppender | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.sink.AMISTATS_SINK.type</span>=file | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.sink.AMISTATS_SINK.fileName</span>=${f1.logs.dir}/${f1.logfilename}.amilog | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.sink.AMISTATS_SINK.maxFiles</span>=10 | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.sink.AMISTATS_SINK.maxFileSizeMb</span>=1000 | ||
+ | * <span style="font-family: courier new; color: blue;">speedlogger.stream.AMI_STATS</span>=AMISTATS_APPENDER;AMISTATS_SINK;ALL | ||
+ | |||
+ | ==Redirecting log to another location== | ||
+ | To redirect all logging('''AmiOne.log'''/'''AmiMessages.log'''/'''AmiOne.amilog''') to another location, you will need to override the below property in local.properties to point to the new folder: | ||
+ | <span style="font-family: courier new; color: blue;">f1.logs.dir=absolute/or/relative/path/to/new/folder | ||
+ | |||
+ | = Controlling layouts with URL parameters = | ||
+ | |||
+ | The <code>onUrlParams(Map params)</code> callback can be used to control a dashboard based on the URL parameters. For this example we'll be assuming the base URL for accessing AMI is <code>localhost:33332</code>. First, create a new window, click on the green icon, then select '''Place Highlighted In Tab''': | ||
+ | |||
+ | [[File:Url place highlighted.png]] | ||
+ | |||
+ | Select the green icon for the Tabs and select "Add Tab": | ||
+ | |||
+ | [[File:Url new tab.png]] | ||
+ | |||
+ | Click on the dropdown arrow for the first tab to access the tab's settings then Change both the title and id for the first tab. Repeat this for the second tab: | ||
+ | |||
+ | [[File:Url tab settings.png]] | ||
+ | |||
+ | [[File:Url rename tab options.png]] | ||
+ | |||
+ | Now that we have two tabs, navigate to '''Dashboard > Custom Callbacks''' then navigate to the <code>onUrlParams(Map params)</code> callback. Note that you may have to use the arrows on the right hand side to scroll to the `onUrlParams(params)` callback. | ||
+ | |||
+ | Finally, we can import the tabs using the right hand menu and add the following AMIScript to select tabs based on the URL parameters: | ||
+ | |||
+ | <syntaxhighlight lang="amiscript"> | ||
+ | String selectedTab = params.get("tab"); | ||
+ | |||
+ | if (selectedTab == "first") { | ||
+ | Tabs1.setSelectedTab(first_tab); | ||
+ | } else if (selectedTab == "second") { | ||
+ | Tabs1.setSelectedTab(second_tab); | ||
+ | } | ||
+ | </syntaxhighlight > | ||
+ | |||
+ | The final callback should appear as shown below: | ||
+ | |||
+ | [[File:Url callback.png]] | ||
+ | |||
+ | Finally, we can test the URL by accessing <code>http://localhost:33332/3forge?tab=first</code> and <code>http://localhost:33332/3forge?tab=second</code>. |
Latest revision as of 20:01, 7 February 2024
Upload Files and Contact Support From 3forge Portal
Step1: Log in to the account: Log in
Step2: Click Account -> Help, where you can detail your support questions and send attachments to the support team.
Step3: Click Contact Us. The support team will be notified of the support ticket you just created.
Helpful Tricks
Inserting Data from Datamodel to AMI DB
In this example, we will show how to insert data from one datasource to another by using the Datamodel. Here the Country table exists in a datasource called WORLD. We want to take this table and create a copy of it in AMI datasource. To create a copy of the Country table, we need the schema which we get using the DESCRIBE clause.
{
CREATE TABLE Country AS USE EXECUTE SELECT * FROM `Country` WHERE ${WHERE};
string s = SELECT SQL from DESCRIBE TABLE Country;
s = strReplace(s, "CREATE TABLE", "CREATE PUBLIC TABLE IF NOT EXISTS");
// session.log(s);
USE ds="AMI" LIMIT = -1 EXECUTE ${s};
// USE ds="AMI" EXECUTE DELETE FROM Country;
USE ds="AMI" INSERT INTO Country FROM SELECT * FROM Country;
}
Firstly, create a Datamodel that is attached to the WORLD Datasource. Copy and paste the above script and run.
This will now give you the Country table in the AMI datasource.
String Template (and Procedures)
For this example we will only use the back-end. First let us create a simple table and add 4 values to it:
CREATE PUBLIC TABLE A (ID String, Price Double);
INSERT INTO A VALUES ("I001",100),("I002",200),("I003",300),("I004",400);
This gives us the following table:
SELECT * FROM A;
ID
String |
Price
Double |
---|---|
I001 | 100 |
I002 | 200 |
I003 | 300 |
I004 | 400 |
Now let's create a string and assign A to it:
String T = "A";
Try running the following script:
SELECT * FROM ${T};
This produces an error as string_template=off.
Let's set string_template=on. Note we find string_template under the setlocal command:
To set this to 'on' run the following:
setlocal string_template=on;
Try running the following script again:
SELECT * FROM ${T};
This will now output table A.
Using PROCEDURES
Let's roll back and set string_template=off. This time we will create the following PROCEDURE:
CREATE PROCEDURE testProc OFTYPE AMISCRIPT USE arguments="String T" script="Int n = 2; Table t = SELECT * FROM ${T} LIMIT n; SELECT * FROM t;";
and then call the procedure as such:
CALL testProc(T);
Copying Style from one Dashboard to Another
To copy a dashboard style from one dashboard to another we can use the Import/Export Style function in Style Manager. Let's take a pre-styled dashboard as such:
To copy this style, enter Development mode and under Dashboard select Style Manager. Select the style to copy, right click to Export Style and copy:
Next, open the dashboard in which you would like to import this style. Under Style Manager select Import Style. Paste the copied text - you can rename the id and lb to your preferred names (here we have named both to CopiedStyle):
Finally, under Layout Default, select CopiedStyle to inherit from. This will update the style of the entire dashboard to this style.
Charts
Plotting Bar Chart Side-by-Side
In this example, we will be plotting a grouped bar chart to show the GDP per capita for UK, USA, JPN and AUS between 2017 and 2020. The result looks as such:
To start off with, create a Datamodel with the code snippet below. Once we have a table with our data, in this case GDPPerCapita table we need to prepare the dataset to plot the data. The following code snippet prepares the data by creating a table called GDPPerCapitaPlot.
{
create table GDPPerCapita (Year string, UK double, USA double, JPN double, AUS double);
insert into GDPPerCapita values ("2017", 45000, 58000, 42000, 50000);
insert into GDPPerCapita values ("2018", 50000, 55500, 42500, 46000);
insert into GDPPerCapita values ("2019", 47500, 59500, 45500, 42700);
insert into GDPPerCapita values ("2020", 42500, 54700, 43500, 45700);
Table t = select * from GDPPerCapita;
List yearList = select Year from GDPPerCapita;
List valTypesList = t.getColumnNames();
valTypesList.remove(t.getColumnLocation("Year"));
create table GDPPerCapitaPlot (Year string, valType string, x double, val double);
int n = 1;
for (int i = 0; i < yearList.size(); i++) {
Row r = t.getRow(i);
string Year = r.get("Year");
double ld = 0.0;
int cnt = 0;
for (string valType : valTypesList) {
double val = r.get(valType);
insert into GDPPerCapitaPlot values (Year, valType, n, val);
ld += n;
n += 1;
cnt += 1;
}
ld = ld / cnt;
insert into GDPPerCapitaPlot values (Year, "label", ld, 1.0);
n+=1;
}
}
Next, we will add a visualisation to the Datamodel on the GDPPerCapitaPlot table. Choose the ‘2D Chart’ – ‘Advanced’, and fill in the options as below (For ‘Color’ in ‘MARKERS’ section choose Custom_Series and add series of colours in hex code for example ‘#E4572E,#29335C,#F3A712,#A8C686,#669BBC’):
NB. Make sure the options under Marker Position Override are filled in as shown.
This will give us the following bar plot:
To add axis and get the final result, add a panel below the bar chart, then add a visualisation based on GDPPerCapitaPlot table with the following information:
Editing and styling the axis and the divider gives us the desired chart.
Plotting Multiple Line Graphs on Same Panel
In this example we will show how to plot multiple line graphs on the same chart panel. We will use a sample dataset that shows the GDP Growth of several countries:
Output will look as such:
The method we will follow is similar to the one before. Firstly, we want to be able to choose the countries we plot, so we will create a HTML panel with a 'Multiple Checkbox' field called 'variables'. Once we have this field set up, insert the following snippet into a Datamodel.
{
List vars = variables.getSelected();
String varsStr = strJoin(",", vars);
CREATE TABLE PlotSeries (Year long, yVals double, var string);
for (string v : vars) {
INSERT INTO PlotSeries (Year, yVals, var) FROM SELECT Year, ${v}, "${v}" FROM GDPGrowth;
}
}
This creates a table PlotSeries which will be used to plot the graph:
Next create a visualisation on the Datamodel from above using the PlotSeries table and choose '2D Chart' - 'Advanced'. We will add 'Year' on the X-axis and 'yVals' on the Y-axis. We want to group the data by the Countries so add 'vars' in the 'Group By' option. Finally, we want to have the lines for each country represented by a different colour - in the 'Line Colour' option choose 'Series' (to get predetermined colours) or 'Custom Series' (to choose custom colours) and __series_num:
Dynamic Aggregation on Visible Columns
In this example we go through the steps showing how a user can use visible columns to drive aggregate columns. This is done by getting the visible columns and using the column names to drive a new query whenever the columns are hidden or shown.
We start by creating a datamodel named Orders that produces the Orders table with the following data:
{
CREATE TABLE Orders(ID int, Country string, Size long, Price double, OrdType string, Side string);
INSERT INTO Orders Values(1,"HK",5000,3.00,"1","B"), (2,"HK",3000,5.00,"2","B"), (3,"CH",2000,6.00,"1","B"), (4,"CH",1500,8.00,"2","S");
}
Next, create a blender named OrdersGroupBy on the above datamodel. We need to add and access an argument called groupByClause which will be passed into this blender and will store the column names that drive the aggregation. This argument can be accessed from the wheres map passed into onProcess(WHERE,wheres,rtevents).
Our argument groupByClause can be accessed like so:
String groupByClause = wheres.get("groupByClause");
Use the following code to produce a new table that will have some aggregate columns, in this example we have a column that sums all of the sizes and a column that gives the average price.
We want to add some conditions using an if statement, this is where we make use of the ‘groupByClause’ we defined previously. Since this argument will contain the column names that drive the aggregation we want to make sure that a table is still created even if the argument is empty. We do this by adding a condition to create a table from our existing columns if no new column is provided.
If we are given a new column name we add this to a select statement. We add the column name both in the select portion and then at the end to group the results of the query.
{
CREATE TABLE Orders AS SELECT * FROM Orders WHERE ${WHERE};
String groupByClause = wheres.get("groupByClause");
if (groupByClause == null || groupByClause == "") {
groupByClause = "Country, OrdType, Side";
create table OrdersGroupBy as select ${groupByClause}, sum(Size), avg(Price) from Orders group by ${groupByClause};
// create table OrdersGroupBy as select sum(Size), avg(Price) from Orders;
}
else {
create table OrdersGroupBy as select ${groupByClause}, sum(Size), avg(Price) from Orders group by ${groupByClause};
}
}
Next, create a visualization for OrdersGroupBy table.
Now, go to the AmiScript Callbacks for the OrdersGroupBy table just created. Within the onColumnsArranged() paste the following code. This code first gets a list of all of the visible columns. Then we remove our aggregate columns, in this case we want to remove the sum(Size) and average(Price) columns, these are removed as they are not used in the aggregation but instead just show the results of the aggregation. We add each visible column name to a map m. We process our blender OrdersGroupBy and provide our map as the parameter, this map will be parsed as the wheres map that we use in the blender.
{
list visCol = this.getVisibleColumns();
list visColProcessed = new List();
for (string c : visCol) {
if (!strStartsWith(c, "avg", true) && !strStartsWith(c, "sum", true)) {
c = strReplace(c, " ", "");
visColProcessed.add(c);
}
}
string groupByClauseStr = strJoin(", ", visColProcessed);
// session.alert(groupByClauseStr);
Map m = new map();
m.put("groupByClause", groupByClauseStr);
OrdersGroupBy.process(m);
}
Before we perform any aggregates, we need to edit the OrdersGroupBy blender so that when the groupByClause is null or empty we only see the sum(Size) and avg(Price) columns. Our blender should now have the following code snippet:
{
CREATE TABLE Orders AS SELECT * FROM Orders WHERE ${WHERE};
String groupByClause = wheres.get("groupByClause");
if (groupByClause == null || groupByClause == "") {
// groupByClause = "Country, OrdType, Side";
// create table OrdersGroupBy as select ${groupByClause}, sum(Size), avg(Price) from Orders group by ${groupByClause};
create table OrdersGroupBy as select sum(Size), avg(Price) from Orders;
}
else {
create table OrdersGroupBy as select ${groupByClause}, sum(Size), avg(Price) from Orders group by ${groupByClause};
}
}
We are now ready to hide/show columns and watch the aggregation happen. You can use the Hide This Column... option or Arrange Columns... option to hide/show columns.
Autofill Text Fields Using a Lookup
In this example we will show how to use the Country field to populate the Country Code, Continent and Population field. We will use the Country table for this example.
First create a HTML Panel and add a 'Text' field attached to the Country Datamodel and choose Name (here the Name column contains Country names) as the display value:
Use the same method to add the other fields of interest (ie. Country Code, Continent, Population). Next we will add a button called 'Lookup' with the following script - when this button is clicked the remaining fields will populate based on the chosen Country.
Table dbTable = layout.getDatamodel("Country").getData().get("Country");
string countryName = countryname.getValue();
Table dbTableInfo = dbTable.query("select Continent, Code, Population from this where Name==\"${countryName}\" limit 1");
string Continent = dbTableInfo.getRow(0).get("Continent");
string Code = dbTableInfo.getRow(0).get("Code");
string Population = dbTableInfo.getRow(0).get("Population");
if (strIs(Continent)) {
continentname.setValue(Continent);
}
else {
continentname.setValue("");
}
if (strIs(Code)) {
countrycode.setValue(Code);
}
else {
countrycode.setValue("");
}
if (strIs(Population)) {
population.setValue(Population);
}
else {
population.setValue("");
}
This scripts uses a function called query to get the values corresponding to the chosen Country. When searching for a Country we also get a dropdown list of the Countries:
Performing a lookup on Belgium, we can see the other fields are populated:
Filtering in Select Fields
In this example we will use the Country table and create 3 select fields. The output of the first select field will be used in the WHERE clause to give the filtered selection of data which acts as the input of the second select field.
First we will create 3 select fields and call them Continent, Region and Country Name. Then create a blender on the Country datamodel called CountryFiltered and insert the following snippet:
{
CREATE TABLE Region AS SELECT Region FROM Country WHERE Continent==continent.getValue();
CREATE TABLE CountryInRegion AS SELECT Name FROM Country WHERE Region==region.getValue() and Continent==continent.getValue();
}
Here the continent.getValue() and region.getValue() gets the value in the select field named Continent and Region respectively.
Next we will set up the three fields as shown below. We get the relevant data from the CountryFiltered datamodel.
Finally, in order for the select field options to update each time we select a different Continent or Region, we will need to reprocess the datamodel. Thus for the Continent and Region select field, add the following AMI Script:
layout.getDatamodel("CountryFiltered").reprocess();
Transpose Tables
In this example we will show how to transpose the data in a table from rows to columns in the front end and the backend.
Frontend Datamodel
First let's create a dummy table from the frontend datamodel:
{
USE ds="AMI" EXECUTE CREATE PUBLIC TABLE HouseBills (House string, BillType string, Amount float);
USE ds="AMI" INSERT INTO HouseBills VALUES ("House 1", "Gas", 5.2), ("House 1", "Electricity", 15.9), ("House 1", "Water", 10.3),
("House 2", "Gas", 4.5), ("House 2", "Electricity", 12.4), ("House 2", "CouncilTax", 20), ("House 2", "Water", 11),
("House 3", "Gas", 8.7), ("House 3", "Electricity", 22.5), ("House 3", "CouncilTax", 30),
("House 4", "Gas", 6.9), ("House 4", "Electricity", 20.1), ("House 4", "CouncilTax", 25.5), ("House 4", "Water", 11.8);
}
This gives us the following table:
Now we want to transpose this table so that the column headers are the different bill types and the rows contain the amount for each house. Thus since there are 4 houses, we will get a dataset with 4 rows. By using the following script we can generate our desired output:
{
CREATE TABLE HouseBills AS USE EXECUTE SELECT House, BillType, Amount FROM HouseBills WHERE ${WHERE};
list HouseName = select House from HouseBills group by House;
list billType = select BillType from HouseBills group by BillType;
string columnHeaders = "House, " + strJoin(", ", billType);
string columnNameSchema = "House string, " + strJoin(" float, ", billType) + " float";
create table TransposeTable (${columnNameSchema});
for (int i = 0; i < HouseName.size(); i++) {
string insertVals = "";
string house = HouseName.get(i);
insertVals += "\"${house}\"";
for (int j = 0; j < billType.size(); j++) {
string bill = billType.get(j);
float amt = select Amount from HouseBills where House == "${HouseName.get(i)}" and BillType == "${billType.get(j)}";
insertVals += ", " + (amt != null ? amt : "null");
}
insert into TransposeTable (${columnHeaders}) values (${insertVals});
}
}
We first create a datamodel on the HouseBills table that is stored in AMI. Next we extract the distinct houses names and bill types, and store it in a list named HouseName and billType respectively.
We will need to create a schema for the new transposed table - to do this we use unique list containing the bill type and perform a strJoin by adding the data type of those columns (in this case we assign their column type as a float). So the columnNameSchema string is:
House string, Gas float, Electricity float, Water float, CouncilTax float
This can now be used to create the TransposeTable.
Finally we want to get the amount corresponding to each house and bill type so we perform a for-loop that extracts the required amount and appends it to the string insertVals. Note if the amount for a particular bill type doesn't exist then we will assign a null value. Once we have the string of values to insert in the correct format we can insert it into the transpose table. We get the final result as such:
Backend Using Procedures
We can use the same script as above with a few minor changes to transpose data in the backend. The main difference will come from escaping characters:
CREATE PROCEDURE TransposeProcedure OFTYPE AMISCRIPT USE arguments="string T" script="list HouseName = select House from ${T} group by House; list billType = select BillType from ${T} group by BillType; string columnHeaders = \"House, \" + strJoin(\", \", billType); string columnNameSchema = \"House string, \" + strJoin(\" float, \", billType) + \" float\"; create table TransposeTable (${columnNameSchema}); for (int i = 0; i < HouseName.size(); i++) { string insertVals = \"\"; string house = HouseName.get(i); insertVals += \" \\\"${house}\\\" \"; for (int j = 0; j < billType.size(); j++) { string bill = billType.get(j); float amt = select Amount from ${T} where House == \"${HouseName.get(i)}\" and BillType == \"${billType.get(j)}\"; insertVals += \", \" + (amt != null ? amt : \"null\"); } insert into TransposeTable (${columnHeaders}) values (${insertVals}); } select * from TransposeTable;"
NB: for readability purposes the above has new lines and tabs - when writing the procedure ensure that it flows and there are no line breaks:
CREATE PROCEDURE TransposeProcedure OFTYPE AMISCRIPT USE arguments="string T" script="list HouseName = select House from ${T} group by House; list billType = select BillType from ${T} group by BillType; string columnHeaders = \"House, \" + strJoin(\", \", billType); string columnNameSchema = \"House string, \" + strJoin(\" float, \", billType) + \" float\"; create table TransposeTable (${columnNameSchema}); for (int i = 0; i < HouseName.size(); i++) {string insertVals = \"\"; string house = HouseName.get(i); insertVals += \" \\\"${house}\\\" \"; for (int j = 0; j < billType.size(); j++) {string bill = billType.get(j); float amt = select Amount from ${T} where House == \"${HouseName.get(i)}\" and BillType == \"${billType.get(j)}\"; insertVals += \", \" + (amt != null ? amt : \"null\"); } insert into TransposeTable (${columnHeaders}) values (${insertVals});} select * from TransposeTable;"
Snapping/Unsnapping Dividers
In this example we discuss how to get different charts popping up depending on the data filtering. We will again use the Country table to showcase the example.
First lets create a window with 4 panels as such:
The left panel contains two Text field - one for Continent and other for Region. The Continent text field will display the values in the Continent column in the Country table from the Country datamodel:
Next, let's create a blender on the Country Datamodel called CountryFiltered which will contain the following script:
{
CREATE TABLE RegionInContinent AS SELECT Region, sum(Population) as TotalPopulation FROM Country WHERE Continent==continent.getValue() group by Region;
CREATE TABLE CountryInContinentRegion AS SELECT Name, Population FROM Country WHERE Continent==continent.getValue() and Region==region.getValue();
}
where continent.getValue() and region.getValue() get the values from the Continent and Region text fields respectively.
Then the Region text field will display the values in the Region column in the RegionInContinent table in the CountryFiltered datamodel:
Now that the left panel is done, we will split the right panel into three and create three V Bar charts. The top most bar chart will plot Continent vs. Population from the Country table:
The middle bar chart will plot Region vs. TotalPopulation from the RegionInContinent table:
Similarly, the bottom bar chart will plot Name vs. Population from the CountryInContinentRegion table.
Now that we have the panels completed, we will show how to maximise the different bar charts depending on the filtering of the text fields. We want the logic as follows:
- if the Continent text field is empty we should have the Continent vs. Population plot
- if the Continent text field has a value (and Region text field is empty) we should have the Region vs. TotalPopulation plot where the Regions are in the chosen Continent
- if the Region text field has a value we should have the Country Name vs. Population plot
To get this we need to add the following script to the Continent text field:
if (continent.getValue() == "") {
SummaryDivider.unsnap();
SummaryDivider.setSnapDirection("Bottom");
SummaryDivider.snap();
}
else {
SummaryDivider.unsnap();
SummaryDivider.setSnapDirection("Top");
SummaryDivider.snap();
RegionDivider.setSnapDirection("Bottom");
RegionDivider.snap();
}
layout.getDatamodel("CountryFiltered").reprocess();
and the following script to the Region text field:
if (region.getValue() == "") {
RegionDivider.unsnap();
RegionDivider.setSnapDirection("Bottom");
RegionDivider.snap();
}
else {
RegionDivider.unsnap();
RegionDivider.setSnapDirection("Top");
RegionDivider.snap();
}
layout.getDatamodel("CountryFiltered").reprocess();
where the SummaryDivider is the divider between Continent vs. Population plot and Region vs. TotalPopulation plot, and RegionDivider is the divider between Region vs. TotalPopulation plot and Name vs. Population plot.
Transferring Historical Data from one Center to a Historical Center
In this example we will write a simple procedure to transfer historical data from one center to another.
In our working center we will use a table with the following schema;
CREATE PUBLIC TABLE DataTable (AccountID String, Price double, Quantity Double, Date Long, D long);
Similarly, in the center with historical data we have a table with the same name but a different schema:
CREATE PUBLIC TABLE DataTable (AccountID String, Price double, Quantity Double, Date Long, SourceD Long, HistDate Long);
NB: D is an auto-generated incrementing unique id for the row which unique across all tables (see Reserved Columns on Public Tables - https://docs.3forge.com/mediawiki/AMI_Realtime_Database#Reserved_columns_on_public_Tables).
Next we will use the below procedure to transfer the historical data. The arguments for the procedure are as follows:
- tableName - this is the table you will be transferring data from and to (ie. DataTable)
- histCenter - this is the name of the datasource where the historical data will sent to
- whereClause - argument that can be used to get the data you want to transfer (ie. you may want to send across data where Date == 20220101)
- batchSize - argument to specify the number of data rows to send across in each go
Note that this procedure uses column D to decide which data to send.
CREATE PROCEDURE MoveRowsToHist OFTYPE AMISCRIPT USE arguments="string tableName, string histCenter, string whereClause, int batchSize" script="long histDate = formatDate(timestamp(), \"yyyyMMdd\", \"UTC\"); long srcTblDmax = select max(D) from ${tableName} where ${whereClause}; srcTblDmax = srcTblDmax != null ? srcTblDmax : 0L; int destTblDmax = use ds=${histCenter} execute select max(SourceD) from ${tableName} where ${whereClause} AND HistDate == ${histDate}; destTblDmax = destTblDmax != null ? destTblDmax : 0; while (srcTblDmax > destTblDmax) { use ds=${histCenter} insert into ${tableName} from select * except(D), D as SourceD, ${histDate} as HistDate from ${tableName} where ${whereClause} AND D > destTblDmax limit batchSize order by SourceD; destTblDmax = use ds=${histCenter} execute select max(SourceD) from ${tableName} where ${whereClause} AND HistDate == ${histDate}; }"
CREATE PROCEDURE MoveRowsToHist OFTYPE AMISCRIPT USE arguments="string tableName, string histCenter, string whereClause, int batchSize" script="long histDate = formatDate(timestamp(), \"yyyyMMdd\", \"UTC\"); long srcTblDmax = select max(D) from ${tableName} where ${whereClause}; srcTblDmax = srcTblDmax != null ? srcTblDmax : 0L; int destTblDmax = use ds=${histCenter} execute select max(SourceD) from ${tableName} where ${whereClause} AND HistDate == ${histDate}; destTblDmax = destTblDmax != null ? destTblDmax : 0; while (srcTblDmax > destTblDmax) {use ds=${histCenter} insert into ${tableName} from select * except(D), D as SourceD, ${histDate} as HistDate from ${tableName} where ${whereClause} AND D > destTblDmax limit batchSize order by SourceD; destTblDmax = use ds=${histCenter} execute select max(SourceD) from ${tableName} where ${whereClause} AND HistDate == ${histDate};}"
Create Table to Display Values from Particular Row
In this example we will show how to display the row values from a table in another table. This will be the end result:
Firstly, we will create a table visualisation on the Country table (or table or your choice).
Next create a datamodel with the display table:
{
create table RowTable (ColumnName String, Value String);
}
Next we want to create a callback such that every time we click on a values in a row it will generate the display table (table in the right panel above). To do this we will use the onCellClicked(column,val,rowval) callback under amiscript callbacks for the table panel. The following script will get the row values and the corresponding column names and save it in the table.
Table RowValTable = new Table("RowValTable","ColumnName String, Value String");
for (string c : rowvals.getKeys()) {
string elem = rowvals.get(c);
if (!strStartsWith(c, "!", true)) {
RowValTable.addRow(c, elem);
}
}
map m = new Map();
m.put("RowValTable", RowValTable);
displayTable.processSync(m);
Enable the displayTable datamodel from the Variable Tree on the right so that the script knows what datamodel to process:
Now we need to make the columns clickable so choose one column (or any number) and under edit column turn on clickable.
Test this and then create a visualisation on the display table in the right panel. Now to have the datamodel containing the display table to update with the row values from the main table, we will update the displayTable code snippet to:
{
// create table RowTable (ColumnName String, Value String);
Table RowValTable = wheres.get("RowValTable");
create table RowTable as select * from RowValTable;
}
NB. when you test this you may get a runtime error of unknown table; you can ignore this error.
Finally, clicking on an clickable cell in the Country table will automatically populate the display table.
Duplicate Panels Programmatically
In this example we will show how to duplicate any panel programmatically instead of importing/exporting the panel manually.
Let's first create panel that we want to duplicate; here we have a window with the City table on the left and Country table on the right. We've named the left panel with the City table as CityWorld and the right panel with the Country table as CountryWorld. There is also a divider between the two tables which we have named CityCountryDiv (to rename this go to green cog of the Divider -> Settings -> PanelID).
Note that this divider is how we would access both the left and right panel.
Let's create a HTML Panel with a button that duplicates this window when clicked on. We'll call this button Duplicate Panel:
Add the following snippet of code in the Ami Script tab of the button - this code exports the config of the named panel and re-imports it as a new window:
Map config = layout.getPanel("CityCountryDiv").exportConfig();
session.importWindow("New Window",config);
Exit the development mode and click on the Duplicate Panel button - this now generates a new window called New Window containing the duplicate of the panel.
NB: If you only wish to duplicate the CityWorld panel, replace getPanel("CityCountryDiv") with getPanel("CityWorld").
Flashing Cells
To make table cell flash in response to cell value changing, right click on the table -> style
In the CELL FLASH section, you can configure the flash up/down color and flash Duration.
Note:
- The flash Duration is in the unit of milliseconds.
- Flash up color is the color the cell changes to when the cell value goes up.
- Flash down color is the color the cell changes to when the cell value goes down.
Apply row-wise filters based on a specific column
oftentimes, the user might want to only show specific rows and hide the other rows based on a specific column. The following example demonstrates how we can apply row-wise filter based on a specific column in a real time table, as well as how to show summary of a given table and how to align two tables in the panel programmatically.
1.Creating the demo table
For this demonstration, we need to create a real time table with the following schema and data.
create public table RT_table(System string, Symbol string, Quantity integer);
insert into RT_table values("sys1","TSLA",20),("sys2","AAPL",30),("sys3","AAPL",30),("sys1","TEST",60),
("sys2","AAPL",10),("sys3","TEST",80),("sys3","TSLA",80),("sys2","TSLA",45);
Set visible columns
Suppose we only want to visualize the columns "System" and "Quantity", hiding column "Symbol". We can do this via ami script, using the ami script function setVisibleColumns() against your real time table panel. To achieve this, one solution is to create a button such that when the user clicks the button, the setVisibleColumns() function will be triggered. If we double click the button, go to the tab Ami Script, and enter the following command:
list VisibleCols = new list("Quantity","System");
aggPnl.setVisibleColumns(VisibleCols);
Inside the function setVisibleColumns(), we can specify the names of the columns we want to make visible and pass in this as a list. Once we click the button, now the real time table only has columns "System" and "Quantity".
2. Apply row-wise filters to rows where Symbol=="TEST"
Suppose we want to aggregate on the table excluding the rows where Symbol=="TEST", here is how we can configure this using ami script to programmatically set up the filter. If we go to our panel, and right click on the central button, select AmiScript Callbacks
Go to the tab onColumnsArranged(), and enter the following command:
if (visiblePnl.getVisibleColumns().contains("System") && !visiblePnl.getVisibleColumns().contains("Symbol")) //if visible cols contain system but not symbol
{
visiblePnl.setCurrentWhere("\"Symbol\"!=\"TEST\"")//anything whose symbol is not equal to TEST will be displayed, i.e. set filter to filter out rows where symbol=="TEST"
}
else
{
visiblePnl.setCurrentWhere("true"); //display everything
}
Now if I hit the submit button, everytime there is a change in the column arrangement, the onColumnsArranged() callback gets fired. In this case, all the rows whose symbol=="TEST" will not be displayed.
3. Aggregate on the visible columns and rows
Once the filters are properly set up, we might also want to do a real-time aggregate table on this. Let's open up a new panel and right click on the central button, select create realtime table/visualization and choose Aggregate Table widget against aggPnl (which is the panel after we apply row & column-wise filters)
Next fill in the fields to specify on what columns you want to do aggregation on. Suppose we want to group by only on System and return the sum(Quantity), then we can configure the table like this:
The final window that consists both panels may look something like this:
4.Show summary of table
We may also want to show a summary of the table. The way to do this is to right click the central button on the table you want to show summary of, and go to settings. Then scroll all the way down and enable summary of rows
Once enabled, now we can select the rows we want to show summary of and right click -> Summarize selected
Align the tables in the panels
Suppose we want to align the following two tables that are situated in two separate panels.
First, we need to make sure that the two tables must have identical column headers. Let's modify the second column header name Total Quantity to Quantity to match the corresponding column header name in table 1.
To do this, let's right click on the second column in table 2 and select edit column
Go to the Column Header, and modify Title field
Right click on table1, go to AmiScript Callbacks and then go to OnColumnSized, enter the following amiscripts and hit submit
list sourceCols = aggPnl.getVisibleColumns();
for (String col: sourceCols)
{
summaryPnl.getColumn(col).setWidth(aggPnl.getColumn(col).getWidth());
}
Now the two tables are fully aligned. You could drag either one of the tables however you want, you will find both tables will always stay aligned and in sync.
Include and Apply New Style To Your Dashboard
AMI has several layout styles you could choose from. These layout styles are encoded in JSON format files and are placed in amione/data/styles directory. In this example, we have a stylesheet called DARKMATTER.amistyle.json and show how to include it in AMI.
1.Navigate to the AMI installation path and place the attached JSON file inside the styles directory (amione/data/styles)
2.Navigate to /amione/config and add the following inside local.properties file
ami.style.files=data/styles/*amistyle.json
3.Restart AMI
shut down the current AMI instance and restart
4.Log in and open Dashboard > Style Manager
5.Select Layout Default from tree and Dark Matter from the dropdown list
Your dashboard should now pick up the styles from the Dark Matter stylesheet.
Encrypting passwords in access.txt
3forge recommends using the access.txt Authentication (AmiAuthenticatorFileBacked Authentication Plugin) only for demo purposes, for production environments we recommend implementing a AmiAuthenticator Plugin. By default, AMI does not encrypt passwords in the access.txt. This document details the steps for encrypting passwords in your access.txt.
1. Ensure your 3forge AMI Application is running. Your access.txt by default would be located in your data directory.
2. This document will contain a list of all users and their passwords as well as their permissions.
3. To start encrypting the passwords you will need to generate the encrypted string for each password. To do so you will need to telnet to your ami.db.console.port whose default value is 3290 and login to a user with DB permissions.
4.To encrypt each password, run the AMIScript method on the console strEncrypt("your-password"). This command will return your encrypted password.
5. For each password in the access.txt update the password with the encrypted value.
6. Add a new property in a local.properties or create one in your config directory. In the file add the following property: users.access.file.encrypt.mode=password
7. The final step is to restart your 3forge AMI Application
1st Note: Store the amikey.aes securely so that you have a recovery mechanism setup for this in case of data loss. This key by default is set by the property and configured with: ami.aes.key.file=persist/amikey.aes. If lost, users will not be able to login to their accounts.
2nd Note: To change the the access.txt file set the property: users.access.file=pathToYourAccess.txt
Window Visibility Permission Control Over Different User Groups
AMI has its own way of doing permission control over different groups of users so that certain windows are only visible to users with entitlements. Suppose we have two windows named Restricted and Everyone. The Restricted window should NOT be visible to the users whose role is NOT Admin. The following screenshot shows how to set up such a scenario.
1.Go to Dashboard -> Custom Callbacks
2.Go to onStartup tab and enter the following AMI script commands:
string role = session.getProperty("ROLE");
if (role != "Admin"){
for (window w: session.getWindows()){
if (w.getName()=="Restricted"){
w.setType("HIDDEN");
w.minimize();
}
}
}
3. Hit Submit
Now the users should be able to see different windows based on their entitlements.
Subscribe to Realtime Tables/feeds From a Datamodel and Listen for Changes to Update Form Fields
Suppose we have two real time tables: ReconFailure and ReconSuccess that keeps track of successful and failed transactions across different systems in real time, initially set to zero
You can use the following code to follow along this tutorial
drop table if exists ReconFailure;
create public table ReconFailure(FailureCount int, System string);
insert into ReconFailure values(0,"s1"),(0,"s2"),(0,"s3");
drop table if exists ReconSuccess;
create public table ReconSuccess(SuccessCount int, System string);
insert into ReconSuccess values(0,"s1"),(0,"s2"),(0,"s3");
1. Build a datamodel against two real time tables ReconFailure and ReconSuccess
2. Configure the form fields (using text field in this case)
In a new panel, hit the central button, go to Create HTML Panel
Hit the central button in the middle, go to Add Field-> Text Field
Make 6 Text fields as below:
3. Edit the datamodel and set up form field variables that match the two real time table schemas
4.Subscribe to two real time feeds: ReconFailure and ReconSuccess
5. Go to OnProcess and enter the following code script
boolean isSnapshotProcessing = !session.isFeedSnapshotProcessed("ReconSuccess") && !session.isFeedSnapshotProcessed("ReconFailure");
if (isSnapshotProcessing)
return;
RealtimeEvent rte = rtevents;
while(rte != null) {
if (rte.getFeed() == "ReconSuccess") {
String system = rte.getValues().get("System");
int count = rte.getValues().get("SuccessCount");
if (system == "s1")
s1SuccessField.setValue(count);
else if (system == "s2")
s2SuccessField.setValue(count);
else if (system == "s3")
s3SuccessField.setValue(count);
} else if (rte.getFeed() == "ReconFailure") {
String system = rte.getValues().get("System");
int count = rte.getValues().get("FailureCount");
if (system == "s1")
s1FailField.setValue(count);
else if (system == "s2")
s2FailField.setValue(count);
else if (system == "s3")
s3FailField.setValue(count);
}
rte = rte.getNext();
}
6. Set up timers to simulate real time feeds
For the sake of demonstration, I am using two timers here to update two real time tables ReconFailure and ReconSuccess. In real life scenarios, you may connect to some external real time feeds.
//timer1 to update ReconSuccess
create timer SuccessTransaction oftype AMISCRIPT on "500" USE script= "list Systems = new list(\"s1\",\"s2\",\"s3\"); int SysID = rand(3); String sys = Systems.get(SysID); Update ReconSuccess set SuccessCount=SuccessCount+1 where System==sys;"
//timer2 to update ReconFailure
create timer FailureTransaction oftype AMISCRIPT on "500" USE script= "list Systems = new list(\"s1\",\"s2\",\"s3\"); int SysID = rand(3); String sys = Systems.get(SysID); Update ReconFailure set FailureCount=FailureCount+1 where System==sys;"
Once enabling the timer, the figures in the text fields will be in sync with the counts in two real time tables.
Real time Data model
We can have our data model subscribe to a real time feed to make it a real time data model.
Let's say we have a real time feed transaction(TransactionID Long,sym String,price Double)
1. create a data model called realtimeDM and subscribe to real time feed transaction
2. You could also configure queries to construct derived tables
In this example, we created another table showing the top3 symbol with the most total price.
Additionally, you could also configure the conflate time parameter to control how frequently you want your data model to rerun and refresh.
In this example, it is set to 10 seconds, which means the data model will rerun 10 seconds per time.
3. Create real time visualization panel off of the real time data model that we just created
In this example, we created a real time heatmap off of the top3sym table.
4. Final Heatmap
Note that you can press space on the heatmap to zoom in/out.
Import data model and panel configuration to AMI
1. Create a /myConfigs (can be any name you want) directory local to your AMI installation and put the configuration files (usually in the format of JSON) in it
The example below has the configuration file named CountryTemplate.json. You can have multiple configuration files
2. Read, Parse the config files and import the data model and panel to the AMI session
Also additionally, you can configure data model's WHERE variable.
The amiScript below show how to achieve these where each visualization panel has exactly one underlying data model:
filesystem fs=session.getFileSystem();
String Panelconfig = fs.readFile("myConfigs/testPanelConfig.json");
String DMconfig = fs.readFile("myConfigs/testDMConfig.json");
map Panelparsed = parseJson(Panelconfig);
map DMparsed = parseJson(DMconfig);
datamodel dm=session.importDatamodel(DMparsed);
map m = Panelparsed.jsonPath("portletConfigs.0.portletConfig.dm.0"); //get the id from the data model that we imported
m.put("dmadn", dm.getId()); //put the id above into the panel's configuration
dm.process(new map("WHERE", "Code=\"ABW\"")); // configure WHERE variable
session.importWindow("newWindow1", Panelparsed); //import the window
Progress Bar in Column Formatting
On the frontend table GUI, we can add and configure additional columns to make it look like a progress bar.
Suppose we have a GUI table progress like the following:
We can configure an additional column that displays the ratio of the number of Completed and the total number and shows the current progress
Additionally, we can also configure the style of the progress bar. For example:
Breadcrumb Filter Example
This is a basic example of a breadcrumb filter through the use of HTML and Javascript in a Form Div field.
The layout follows the breadcrumb trail of tiers (T1 - T4) as filter levels. Most of the script can be found in the custom methods of the layout, and some in the onAmiJsCallback callback of the breadcrumb panel (for handling clicks to reset filters to indicated level/tier).
Layout:
{"layouts":[{"data":{"includeFiles":[],"metadata":{"amiScriptMethods":["{\n"," string getBreadCrumbStyle() {\n"," return \"<style>\n"," ul.breadcrumb {padding: 10px 10px;\n"," list-style: none;\n"," background-color: #eee;\n"," }\n"," ul.breadcrumb li {\n"," display: inline;\n"," font-size: 18px;\n"," }\n"," ul.breadcrumb li+li:before {\n"," padding: 8px;\n"," color: black;\n"," content: \\\"/\\\";\n"," }\n"," ul.breadcrumb li a {\n"," color: #0275d8;\n"," text-decoration: none;\n"," }\n"," ul.breadcrumb li a:hover {\n"," color: #01447e;\n"," text-decoration: underline;\n"," }\n"," </style>\";\n"," };\n"," \n"," boolean setFilter(){\n"," //get panels\n"," TablePanel tier1 = layout.getPanel(\"tier1\");\n"," TablePanel tier2 = layout.getPanel(\"tier2\");\n"," TablePanel tier3 = layout.getPanel(\"tier3\");\n"," TablePanel tier4 = layout.getPanel(\"tier4\");\n"," \n"," //build where clause for filtering & values for breadcrumbs\n"," string whereClause = \"\";\n"," string t1Vals = \"\";\n"," string t2Vals = \"\";\n"," string t3Vals = \"\";\n"," string t4Vals = \"\";\n"," if (tier1.getSelectedRows().size()!=0){\n"," row firstRow = tier1.getSelectedRows().get(0);\n"," string firstRowVal = firstRow.getValue(\"T1\");\n"," whereClause += \"T1 IN (\\\"${firstRowVal}\\\"\";\n"," t1Vals += firstRowVal;\n"," for (int i = 1; i < tier1.getSelectedRows().size(); ++i){\n"," row r = tier1.getSelectedRows().get(i);\n"," string rVal = r.getValue(\"T1\");\n"," whereClause += \",\\\"${rVal}\\\"\";\n"," t1Vals += \", ${rVal}\";\n"," }\n"," whereClause += \")\";\n"," }\n"," if (tier2.getSelectedRows().size()!=0){\n"," if (whereClause != \"\")\n"," whereClause += \" && \";\n"," row firstRow = tier2.getSelectedRows().get(0);\n"," string firstRowVal = firstRow.getValue(\"T2\");\n"," whereClause += \"T2 IN (\\\"${firstRowVal}\\\"\";\n"," t2Vals += firstRowVal;\n"," for (int i = 1; i < tier2.getSelectedRows().size(); ++i){\n"," row r = tier2.getSelectedRows().get(i);\n"," string rVal = r.getValue(\"T2\");\n"," whereClause += \",\\\"${rVal}\\\"\";\n"," t2Vals += \", ${rVal}\";\n"," }\n"," whereClause += \")\";\n"," }\n"," if (tier3.getSelectedRows().size()!=0){\n"," if (whereClause != \"\")\n"," whereClause += \" && \";\n"," row firstRow = tier3.getSelectedRows().get(0);\n"," string firstRowVal = firstRow.getValue(\"T3\");\n"," whereClause += \"T3 IN (\\\"${firstRowVal}\\\"\";\n"," t3Vals += firstRowVal;\n"," for (int i = 1; i < tier3.getSelectedRows().size(); ++i){\n"," row r = tier3.getSelectedRows().get(i);\n"," string rVal = r.getValue(\"T3\");\n"," whereClause += \",\\\"${rVal}\\\"\";\n"," t3Vals += \", ${rVal}\";\n"," }\n"," whereClause += \")\";\n"," }\n"," if (tier4.getSelectedRows().size()!=0){\n"," if (whereClause != \"\")\n"," whereClause += \" && \";\n"," row firstRow = tier4.getSelectedRows().get(0);\n"," string firstRowVal = firstRow.getValue(\"T4\");\n"," whereClause += \"T4 IN (\\\"${firstRowVal}\\\"\";\n"," t4Vals += firstRowVal;\n"," for (int i = 1; i < tier4.getSelectedRows().size(); ++i){\n"," row r = tier4.getSelectedRows().get(i);\n"," string rVal = r.getValue(\"T4\");\n"," whereClause += \",\\\"${rVal}\\\"\";\n"," t4Vals += \", ${rVal}\";\n"," }\n"," whereClause += \")\";\n"," }\n"," \n"," //apply where clause\n"," TablePanel mainTable = layout.getPanel(\"mainTable\");\n"," mainTable.setCurrentWhere(whereClause);\n"," \n"," //call breadcrumb builder\n"," t1Vals = t1Vals == \"\" ? \"All\" : t1Vals;\n"," t2Vals = t2Vals == \"\" ? \"All\" : t2Vals;\n"," t3Vals = t3Vals == \"\" ? \"All\" : t3Vals;\n"," t4Vals = t4Vals == \"\" ? \"All\" : t4Vals;\n"," map crumbValues = new map();\n"," crumbValues.put(\"t1\", t1Vals);\n"," crumbValues.put(\"t2\", t2Vals);\n"," crumbValues.put(\"t3\", t3Vals);\n"," crumbValues.put(\"t4\", t4Vals);\n"," buildBreadCrumb(crumbValues);\n"," \n"," return true;\n"," };\n"," \n"," boolean buildBreadCrumb(map crumbValues){\n"," String breadCrumb = getBreadCrumbStyle();\n"," breadCrumb += \"<body>\n"," <ul class=\\\"breadcrumb\\\">\";\n"," for (int i = 1; i < crumbValues.size() + 1; ++i){\n"," string crumbVal = crumbValues.get(\"t${i}\");\n"," if (crumbVal == \"All\"){\n"," boolean allBreak = true;\n"," for (int j = i; j < crumbValues.size() + 1; ++j){\n"," string allCheck = crumbValues.get(\"t${j}\");\n"," if (allCheck != \"All\")\n"," allBreak = false;\n"," }\n"," if (allBreak) \n"," break;\n"," }\n"," breadCrumb += \"<li><a onclick='amiJsCallback(this,${i}, 0);'>${crumbVal}</a></li>\";\n"," }\n"," breadCrumb += \"</ul>\n"," </body>\";\n"," \n"," FormPanel breadCrumbPnl = layout.getPanel(\"breadcrumbPnl\");\n"," FormDivField breadcrumbDiv = breadCrumbPnl.getField(\"breadcrumbDiv\");\n"," breadcrumbDiv.setValue(breadCrumb);\n"," };\n","}"],"customPrefsImportMode":"reject","dm":{"dms":[{"callbacks":{"entries":[{"amiscript":["{\n"," CREATE TABLE Sample(T1 String, T2 String, T3 String, T4 String, value string);\n"," \n"," for (int i1=0; i1<2; ++i1){\n"," string t1 = \"1.${i1+1}\";\n"," for (int i2=0; i2<4; ++i2){\n"," string t2 = \"2.${i2+1}\";\n"," for (int i3=0; i3<8; ++i3){\n"," string t3 = \"3.${i3+1}\";\n"," for (int i4=0; i4<8; ++i4){\n"," string t4 = \"4.${i4+1}\";\n"," insert into Sample values (t1, t2, t3, t4, \"Sample\");\n"," }\n"," }\n"," }\n"," }\n","}\n"],"hasDatamodel":true,"linkedVariables":[],"name":"onProcess","schema":{"tbl":[{"cols":[{"nm":"T1","tp":"String"},{"nm":"T2","tp":"String"},{"nm":"T3","tp":"String"},{"nm":"T4","tp":"String"},{"nm":"value","tp":"String"}],"nm":"Sample","oc":"ask"}]}}]},"lbl":"mainTable","lm":0,"lower":[],"queryMode":"startup","test_input_type":"OPEN","test_input_vars":"String WHERE=\"true\";","to":0},{"callbacks":{"entries":[{"amiscript":["{\n"," CREATE TABLE Tier1 AS SELECT T1 FROM Sample WHERE ${WHERE} group by T1;\n","}"],"hasDatamodel":true,"inputDm":["mainTable"],"linkedVariables":[],"name":"onProcess","schema":{"tbl":[{"cols":[{"nm":"T1","tp":"String"},{"nm":"T2","tp":"String"},{"nm":"T3","tp":"String"},{"nm":"T4","tp":"String"},{"nm":"value","tp":"String"}],"nm":"Sample","oc":"ask"},{"cols":[{"nm":"T1","tp":"String"}],"nm":"Tier1","oc":"ask"}]}}]},"lbl":"tier1","lm":0,"lower":["mainTable"],"test_input_type":"OPEN","test_input_vars":"String WHERE=\"true\";","to":0},{"callbacks":{"entries":[{"amiscript":["{\n"," CREATE TABLE Tier2 AS SELECT T2 FROM Sample WHERE ${WHERE} group by T2;\n","}\n"],"hasDatamodel":true,"inputDm":["mainTable"],"linkedVariables":[],"name":"onProcess","schema":{"tbl":[{"cols":[{"nm":"T1","tp":"String"},{"nm":"T2","tp":"String"},{"nm":"T3","tp":"String"},{"nm":"T4","tp":"String"},{"nm":"value","tp":"String"}],"nm":"Sample","oc":"ask"},{"cols":[{"nm":"T2","tp":"String"}],"nm":"Tier2","oc":"ask"}]}}]},"lbl":"tier2","lm":0,"lower":["mainTable"],"test_input_type":"OPEN","test_input_vars":"String WHERE=\"true\";","to":0},{"callbacks":{"entries":[{"amiscript":["{\n"," CREATE TABLE Tier3 AS SELECT T3 FROM Sample WHERE ${WHERE} group by T3;\n","}\n"],"hasDatamodel":true,"inputDm":["mainTable"],"linkedVariables":[],"name":"onProcess","schema":{"tbl":[{"cols":[{"nm":"T1","tp":"String"},{"nm":"T2","tp":"String"},{"nm":"T3","tp":"String"},{"nm":"T4","tp":"String"},{"nm":"value","tp":"String"}],"nm":"Sample","oc":"ask"},{"cols":[{"nm":"T3","tp":"String"}],"nm":"Tier3","oc":"ask"}]}}]},"lbl":"tier3","lm":0,"lower":["mainTable"],"test_input_type":"OPEN","test_input_vars":"String WHERE=\"true\";","to":0},{"callbacks":{"entries":[{"amiscript":["{\n"," CREATE TABLE Tier4 AS SELECT T4 FROM Sample WHERE ${WHERE} group by T4;\n","}\n"],"hasDatamodel":true,"inputDm":["mainTable"],"linkedVariables":[],"name":"onProcess","schema":{"tbl":[{"cols":[{"nm":"T1","tp":"String"},{"nm":"T2","tp":"String"},{"nm":"T3","tp":"String"},{"nm":"T4","tp":"String"},{"nm":"value","tp":"String"}],"nm":"Sample","oc":"ask"},{"cols":[{"nm":"T4","tp":"String"}],"nm":"Tier4","oc":"ask"}]}}]},"lbl":"tier4","lm":0,"lower":["mainTable"],"test_input_type":"OPEN","test_input_vars":"String WHERE=\"true\";","to":0}]},"fileVersion":3,"rt":{"processors":[]},"stm":{"styles":[{"id":"LAYOUT_DEFAULT","lb":"Layout Default","pt":"DEFAULT"}]}},"portletConfigs":[{"portletBuilderId":"amidesktop","portletConfig":{"active":"Div1","amiPanelId":"@DESKTOP","amiStyle":{"pt":"LAYOUT_DEFAULT"},"windows":[{"header":true,"height":697,"hidden":false,"left":582,"portlet":"Div1","pos":0,"state":"flt","title":"Window","top":442,"width":1235,"zindex":2},{"header":true,"height":137,"hidden":false,"left":558,"portlet":"breadcrumbPnl","pos":1,"state":"flt","title":"Window - 2","top":277,"width":1280,"zindex":1}]}},{"portletBuilderId":"div","portletConfig":{"amiPanelId":"Div1","amiStyle":{"pt":"LAYOUT_DEFAULT","vl":{"div":{"divAlign":"start"}}},"child1":"Div2","child2":"mainTable","dir":"v","isMin":false,"offset":0.14736842105263157,"upid":"Div1"}},{"portletBuilderId":"div","portletConfig":{"amiPanelId":"Div2","amiStyle":{"pt":"LAYOUT_DEFAULT"},"child1":"tier1","child2":"Div3","dir":"h","isMin":false,"offset":0.15460992907801419,"upid":"Div2"}},{"portletBuilderId":"div","portletConfig":{"amiPanelId":"Div3","amiStyle":{"pt":"LAYOUT_DEFAULT"},"child1":"tier2","child2":"Div4","dir":"h","isMin":false,"offset":0.24283305227655985,"upid":"Div3"}},{"portletBuilderId":"div","portletConfig":{"amiPanelId":"Div4","amiStyle":{"pt":"LAYOUT_DEFAULT"},"child1":"tier3","child2":"tier4","dir":"h","isMin":false,"offset":0.48654708520179374,"upid":"Div4"}},{"portletBuilderId":"amiform","portletConfig":{"amiPanelId":"breadcrumbPnl","amiStyle":{"pt":"LAYOUT_DEFAULT"},"amiTitle":"","buttons":[],"callbacks":{"entries":[{"amiscript":["int selected = action;\n","int totalTiers = 4;\n","\n","for (int i = selected + 1; i < totalTiers + 1; ++i){\n"," TablePanel tierPanel = layout.getPanel(\"tier${i}\");\n"," tierPanel.selectRows(false);\n","}"],"linkedVariables":[],"name":"onAmiJsCallback"}]},"dm":[],"fields":[{"callbacks":{},"disabled":false,"dme":"","heightPx":59,"help":"","hidden":true,"l":"breadcrumbDiv","labelHidden":true,"leftPosPx":160,"n":"breadcrumbDiv","style":"","t":"divField","template":"","topPosPx":40,"widthPx":840,"zidx":1}],"guides":[],"htmlTemplate2":null,"snap":20,"titlePnl":{"title":""},"upid":"breadcrumbPnl"}},{"portletBuilderId":"amistatictable","portletConfig":{"amiCols":[{"fm":"T1","id":"T1","location":0,"tl":"T1","tp":"text","width":188},{"fm":"T2","id":"T2","location":1,"tl":"T2","tp":"text","width":214},{"fm":"T3","id":"T3","location":2,"tl":"T3","tp":"text","width":215},{"fm":"T4","id":"T4","location":3,"tl":"T4","tp":"text","width":268},{"fm":"value","id":"value","location":4,"tl":"Value","tp":"text","width":130},{"id":"D","width":100}],"amiPanelId":"mainTable","amiStyle":{"pt":"LAYOUT_DEFAULT"},"amiTitle":"Sample","curtimeUpdateFrequency":1000,"dm":[{"dmadn":"mainTable","dmtbid":["Sample"]}],"dynamicColumns":"false","editDblClk":true,"editInplace":false,"editMenuTitle":"Edit Row(s)","editMode":0,"editRerunDM":true,"filters":{},"pinCnt":0,"rollupEnabled":false,"scrollToBottomOnAppend":false,"showCommandMenu":true,"showLastRuntime":true,"titlePnl":{"title":"Sample"},"upid":"mainTable","varTypes":{"T1":"String","T2":"String","T3":"String","T4":"String","value":"String"}}},{"portletBuilderId":"amistatictable","portletConfig":{"amiCols":[{"ei":"","eof":"","fm":"T1","id":"T1","location":0,"sy":"\"center\"","tl":"T1","tp":"text","width":174},{"id":"D","width":100}],"amiPanelId":"tier1","amiStyle":{"pt":"LAYOUT_DEFAULT"},"amiTitle":"Tier1","callbacks":{"entries":[{"amiscript":"setFilter();","linkedVariables":[],"name":"onSelected"}]},"curtimeUpdateFrequency":1000,"dm":[{"dmadn":"tier1","dmtbid":["Tier1"]}],"dynamicColumns":"false","editDblClk":true,"editInplace":false,"editMenuTitle":"Edit Row(s)","editMode":0,"editRerunDM":true,"filters":{},"pinCnt":0,"rollupEnabled":false,"scrollToBottomOnAppend":false,"showCommandMenu":true,"showLastRuntime":true,"titlePnl":{"title":"Tier1"},"upid":"tier1","varTypes":{"T1":"String"}}},{"portletBuilderId":"amistatictable","portletConfig":{"amiCols":[{"ei":"","eof":"","fm":"T2","id":"T2","location":0,"sy":"\"center\"","tl":"T2","tp":"text","width":169},{"id":"D","width":100}],"amiPanelId":"tier2","amiStyle":{"pt":"LAYOUT_DEFAULT"},"amiTitle":"Tier2","callbacks":{"entries":[{"amiscript":"setFilter();","linkedVariables":[],"name":"onSelected"}]},"curtimeUpdateFrequency":1000,"dm":[{"dmadn":"tier2","dmtbid":["Tier2"]}],"dynamicColumns":"false","editDblClk":true,"editInplace":false,"editMenuTitle":"Edit Row(s)","editMode":0,"editRerunDM":true,"filters":{},"pinCnt":0,"rollupEnabled":false,"scrollToBottomOnAppend":false,"showCommandMenu":true,"showLastRuntime":true,"titlePnl":{"title":"Tier2"},"upid":"tier2","varTypes":{"T2":"String"}}},{"portletBuilderId":"amistatictable","portletConfig":{"amiCols":[{"ei":"","eof":"","fm":"T3","id":"T3","location":0,"sy":"\"center\"","tl":"T3","tp":"text","width":168},{"id":"D","width":100}],"amiPanelId":"tier3","amiStyle":{"pt":"LAYOUT_DEFAULT"},"amiTitle":"Tier3","callbacks":{"entries":[{"amiscript":"setFilter();","linkedVariables":[],"name":"onSelected"}]},"curtimeUpdateFrequency":1000,"dm":[{"dmadn":"tier3","dmtbid":["Tier3"]}],"dynamicColumns":"false","editDblClk":true,"editInplace":false,"editMenuTitle":"Edit Row(s)","editMode":0,"editRerunDM":true,"filters":{},"pinCnt":0,"rollupEnabled":false,"scrollToBottomOnAppend":false,"showCommandMenu":true,"showLastRuntime":true,"titlePnl":{"title":"Tier3"},"upid":"tier3","varTypes":{"T3":"String"}}},{"portletBuilderId":"amistatictable","portletConfig":{"amiCols":[{"ei":"","eof":"","fm":"T4","id":"T4","location":0,"sy":"\"center\"","tl":"T4","tp":"text","width":167},{"id":"D","width":100}],"amiPanelId":"tier4","amiStyle":{"pt":"LAYOUT_DEFAULT"},"amiTitle":"Tier4","callbacks":{"entries":[{"amiscript":"setFilter();","linkedVariables":[],"name":"onSelected"}]},"curtimeUpdateFrequency":1000,"dm":[{"dmadn":"tier4","dmtbid":["Tier4"]}],"dynamicColumns":"false","editDblClk":true,"editInplace":false,"editMenuTitle":"Edit Row(s)","editMode":0,"editRerunDM":true,"filters":{},"pinCnt":0,"rollupEnabled":false,"scrollToBottomOnAppend":false,"showCommandMenu":true,"showLastRuntime":true,"titlePnl":{"title":"Tier4"},"upid":"tier4","varTypes":{"T4":"String"}}}]},"location":"breadcrumbs.ami","type":"ABSOLUTE"}],"rootLayout":{"location":"breadcrumbs.ami","type":"ABSOLUTE"}}
Sending emails from AMI
Below is an minimal example setup for setting up email in the properties file (local.properties):
email.client.host=smtp.office365.com
email.client.port=587
email.client.username=demo@outlook.com
email.client.password=samplePassword
Change the host and port properties accordingly (gmail, outlook, etc).
More email configuration properties can be found here: 3forge_Email_Configuration_Properties
Layout Setup
There are two methods in amiscript for sending emails:
sendEmail (sends an email and returns immediately with the email-send-UID)
sendEmailSync (sends an email and returns with the email result)
List of supported attachment file formats
- Application:
atom , json , jar , js , ogg, ogv , pdf , ps , woff , xhtml, xht, xml , dtd , zip , gz , xlsx
- Audio:
au, snd ,mid, rmi ,mp3 ,aif, aifc, aiff ,m3u ,ra, ram ,wav
- Image:
,bmp ,cod ,gif ,ief ,jpe, jpeg, jpg ,jfif ,png ,svg ,tif, tiff ,ras ,cmx ,ico ,pnm ,pbm ,pgm ,ppm ,rgb ,xbm ,xpm ,xwd
- Message:
mht, mhtml, nws
- Text:
css ,323 ,htm, html, stm ,uls ,bas, c, h, txt, ami ,rtx ,sct ,tsv ,htt ,htc ,etc ,vcf
- Video:
mp2, mpa, mpe, mpeg, mpg, mpv2 ,mov, qt ,lsf, lsx ,asf, asr, asx ,avi ,movie
- X-world:
flr, vrml, wrl, wrz, xaf, xof
Here's some guidance on how to troubleshoot the following SSL-related issues, such as No available authentication scheme,Unsupported or unrecognized SSL message,or SSL Protocol Error
In our experience these issues are not caused by 3forge AMI but are generic error messages indicating that there was a problem in how the certificate was generated.
Here is our recommended procedure for the generation:
1) Download the root keystore for your environment (Should not matter if using jks or pem)
keytool -importkeystore -srckeystore cacerts.jks -destkeystore web.keystore
Or
keytool -import -file cacert.pem -keystore web.keystore
2) Generate Certificate Signing Request (CSR) - Modifies keystore * keypass and storepass should match https.keystore.password, source password is the cacerts password
keytool -genkeypair -keystore web.keystore -alias server -keyalg RSA -keysize 2048 -sigalg SHA256withRSA -dname "CN=...,OU=...,O=C=..."
3) Through your Certificate Authority (CA) create a Certificate Request -> Certificate Type, MS CA WebServer to generate a Certificate
4) Import the certificate into the keystore
keytool -import -v -trustcacerts -alias root -keystore web.keystore -file cert.cer -keypass [pass] -storepass [pass]
4b) In our experience the following command didn't work and was the cause of the above SSL-related errors
keytool -importcert -keystore web.keystore -alias server -file cert.cer
Memory Overload
Overview
There are several places in AMI from where memory consumption info can be collected. Then a few steps can be taken to optimize the consumption.
How do we know if AMI is facing performance issues?
1.1 The AMI Data Statistics bar will turn red indicating AMI slowdown
1.2 Chart irregularity in the AMI Data Statistics Window
Keep in mind that the memory usage is shown as troughs (dips) in the chart. E.g. Tip of the chart = AMI memory usage + unused references (garbage). In other words, for a more accurate assessment of memory usage, look at the memory usage after the garbage collection finishes.
1. Upper Graph - memory footprint of the webserver
2. Bottom Graph - memory footprint of the center
3. This graph is designed so that the top of the graph is the maximum memory capacity. If the line gets near the top, AMI is closer to memory overload.
1.3 AMI Log Viewer
Setup of Log Viewer can be found in 3forge documentation -> Guides#Log_Viewer_Layout
- Upload the file AMIOne.amilog in the filename bar and open the Memory Details tab
1. The red line is the maximum memory capacity of AMI. If the Used Memory and Allocated Memory reaches to the max, it suggests AMI will face performance issues.
2. The table in the panel below gives insightful details of the AMI like
- Percentage of free memory
- Values of maximum, allocated, and used memory.
3. We can use these values to better understand how much memory AMI used up and would need to let go.
Troubleshooting
Data lives both in the webserver and the center. The most important step is to identify where the memory issue lies.
Web Server
1. Open the Data Model Tables tab from the AMI Data Statistics Window in the upper right corner of AMI
This tab contains information of the tables created in the current webserver the user is in.
- The Cells column should be looked at as it shows which table has the most data.
- You can also view the count of rows and columns.
Looking at the count of cells, we can recognize the most populated table from the web server to modify.
SOLUTION - Dropping a Table from the Web Server
Data models often use intermediary tables which aren't used in any visualizations. These tables can be dropped at the end of the data model to reduce memory footprint.
- In this example, the tables that are joined (price,quantity) are no longer needed in the data model as the joined table output will be used for visualizations.
- Hence, tables price and quantity can be dropped.
Center
1. Open the AMI DB Shell Tool from Dashboard
We can view all the tables in the center through the command line by writing
SHOW TABLES;
We can view the row count of every table and find the most populated table in order to reduce memory footprint.
2. Diagnose a Table
For deeper analysis, we can diagnose a table through the AMI shell tool by writing
DIAGNOSE TABLE table_name;
The DIAGNOSE table will show all the necessary information about each column related to memory consumption (ignore TABLE_OVERHEAD)
- EST_MEMORY shows the memory for each column.
- COMMENT shows the datatypes and the byte size of each column.
SOLUTION - Datatype Conversion
If a string column exists in the table, it is recommended for the column to be stored as a STRING BITMAP or STRING ONDISK to reduce the high memory usage of a regular string column.
More info on efficient column types for a string can be found in our 3forge documentation -> AMI_Realtime_Database#Choosing_the_Best_Column_Type_for_String
If data type conversion cannot be done then dropping a table from the center would be the last option.
Timeout expired while waiting for read lock
Overview
This issue typically happens when there is a read request while AMIDB is processing a write request. AmiDB is designed to allow multiple reads but only single write at any given time. So while the write request is being processed, the incoming read request will be queued and when the timeout value is reached, it outputs the timeout exception.
Recommendation
The most common cause is a slow running Timer or Trigger. A short term solution is to increase the timeout value so the read threads can wait longer. However, the long term solution would be to carefully inspect and optimize the code that is doing the write so it is performant enough to avoid the timeout issue in the first place. There are a few approaches we can use to identify the problematic Timer/Trigger:
- Use AMIDB diagnostic queries (diagnose tableName, show timers, show triggers) to find Timers/Triggers with higher average or max run time
- Review the AmiOne.log by hand to see what Timer/Trigger was running when the timeout expired
- Use the log viewer dashboard to see the runtime for each Timer/Trigger. After setting up the dashboard and uploading the AmiOne.log file, navigate to the Timers and Triggers tabs and look for Timers/Triggers with significantly higher Total Run Time or significantly higher Run Time when the timeout expired
Once you have identified the slow running Timer or Trigger review the code to see what could be causing the slowness. Some common causes of inefficient Timers/Triggers include:
- Calling large Update/Insert queries
- Calling lots of Select/Insert/Update/Delete queries without an index on the columns in the WHERE clause
- Calling expensive stored procedures
Table with CRUD Operations
CRUD stands for Create, Read, Update, Delete and describes the four key operations for editing the contents of a table. The guide below will show how to create a Realtime Table in AMI with CRUD operations that change the underlying table in the AMIDB.
Read
First, create a table by running the AmiScript below in Dashboard > AMIDB Shell Tool:
CREATE PUBLIC TABLE crudDemo(id long, v1 string, v2 string);
INSERT INTO crudDemo VALUES (1,"A","O123"),(2,"A","O124"),(3,"B","O123"),(4,"A","O121");
Next, create a new Window/Panel, then Create Realtime Table / Visualization. Select the Realtime Feed crudDemo, click Next, then Finish. There should now be a Realtime Table with 3 columns and 4 rows.
Update
Click on the table, then go to Settings and change "In Table Editing" to "Single Row" or "Multiple Rows":
Click on the column header of "Id" and select Edit Column. Then scroll down and change the Edit setting to Readonly. Do the same for columns "V 1" and "V 2" setting them to Text Field.
Click on the table, then go to AmiScript Callbacks... and navigate to the onEdit(vals, oldVals) tab and add in the following AmiScript:
for (row r: vals.getRows()) {
String updateString = "";
for (String key: r.getKeys()) {
updateString += key + "=\"" + r.get(key) + "\", ";
}
updateString = updateString.beforeLast(",",true);
use ds="AMI" execute update crudDemo set ${updateString} where id==${r.get("id")};
}
Create
Click on the table, then go to Custom Menus > Add Menu Item, set Display to "Add Row", then go to the Callbacks tab and add in the following AmiScript:
use ds="AMI" execute insert into crudDemo from select max(id)+1,"","" from t;
Delete
Go to Custom Menus > Add Menu Item, set Display to "Delete Row(s)", then go to the Callbacks tab and add in the following AmiScript:
TablePanel t = this.getOwner();
for (Row r: t.getSelectedRows()) {
use ds="AMI" execute delete from crudDemo where id=="${r.get("id")}";
}
Logging
Turn off logging for a particular class
Any time you wish to mute certain messages from showing up in your AmiOne.log, you can use the following template, replace CLASSNAME with the full class name, and put that inside your local.properties file:
speedlogger.stream.CLASSNAME=BASIC_APPENDER;FILE_SINK;OFF
Note that each line turns off logging for a single class only.
Here is an example of extracting the class name from AmiOne.log:
INF 20231108-13:32:25.009 EST5EDT [F1POOL-04] com.f1.ami.amicommon.centerclient.AmiCenterClientState::onUserSnapshotRequest: localhost:3341 (seqnum=721) User demo requested: [team] of which [team] will be requested from the Ami Center
Adjusting log levels in AMI
Code | Full label |
---|---|
ALL | All |
FNE | Fine |
INF | Info |
WRN | Warning |
SVR | Severe |
OFF | OFF |
You may adjust the log level for a certain class of logs by replacing the part after the last semicolon with the code in the above table:
speedlogger.stream.CLASSNAME=BASIC_APPENDER;FILE_SINK;WRN
Logging Configuration
The following set of configuration allows you to redirect logging to Stdout:
Basic
- speedlogger.appender.BASIC_APPENDER.pattern=%P %d{YMD-h:m:s.S z} [%t] %c::%M: %m %D%n
- speedlogger.appender.BASIC_APPENDER.timezone=EST5EDT
- speedlogger.appender.BASIC_APPENDER.type=BasicAppender
- speedlogger.sink.FILE_SINK.type=file
- speedlogger.sink.FILE_SINK.fileName=${f1.logs.dir}/${f1.logfilename}.log
- speedlogger.sink.FILE_SINK.maxFiles=10
- speedlogger.sink.FILE_SINK.maxFileSizeMb=1000
- speedlogger.stream.=BASIC_APPENDER;FILE_SINK;INFO
- speedlogger.stream.AMI=BASIC_APPENDER;FILE_SINK;OFF
AmiScript
- speedlogger.appender.SIMPLE_APPENDER.pattern=%P %d{YMD-h:m:s.S z} [%t] %c: %m %D%n
- speedlogger.appender.SIMPLE_APPENDER.timezone=EST5EDT
- speedlogger.appender.SIMPLE_APPENDER.type=BasicAppender
- speedlogger.stream.AMISCRIPT.LOGINFO=SIMPLE_APPENDER;FILE_SINK;INF
- speedlogger.stream.AMISCRIPT.LOGWARN=SIMPLE_APPENDER;FILE_SINK;WRN
Backend Messages
- speedlogger.appender.AMIMESSAGES_APPENDER.pattern=%d{YMD-h:m:s.S z} %m%n
- speedlogger.appender.AMIMESSAGES_APPENDER.timezone=EST5EDT
- speedlogger.appender.AMIMESSAGES_APPENDER.type=BasicAppender
- speedlogger.sink.AMIMESSAGES_SINK.type=file
- speedlogger.sink.AMIMESSAGES_SINK.fileName=${f1.logs.dir}/AmiMessages.log
- speedlogger.sink.AMIMESSAGES_SINK.maxFiles=10
- speedlogger.sink.AMIMESSAGES_SINK.maxFileSizeMb=1000
- speedlogger.stream.AMI_MESSAGES=AMIMESSAGES_APPENDER;AMIMESSAGES_SINK;INFO
AMI Statistics
- speedlogger.appender.AMISTATS_APPENDER.pattern=%m%n
- speedlogger.appender.AMISTATS_APPENDER.type=BasicAppender
- speedlogger.sink.AMISTATS_SINK.type=file
- speedlogger.sink.AMISTATS_SINK.fileName=${f1.logs.dir}/${f1.logfilename}.amilog
- speedlogger.sink.AMISTATS_SINK.maxFiles=10
- speedlogger.sink.AMISTATS_SINK.maxFileSizeMb=1000
- speedlogger.stream.AMI_STATS=AMISTATS_APPENDER;AMISTATS_SINK;ALL
Redirecting log to another location
To redirect all logging(AmiOne.log/AmiMessages.log/AmiOne.amilog) to another location, you will need to override the below property in local.properties to point to the new folder: f1.logs.dir=absolute/or/relative/path/to/new/folder
Controlling layouts with URL parameters
The onUrlParams(Map params)
callback can be used to control a dashboard based on the URL parameters. For this example we'll be assuming the base URL for accessing AMI is localhost:33332
. First, create a new window, click on the green icon, then select Place Highlighted In Tab:
Select the green icon for the Tabs and select "Add Tab":
Click on the dropdown arrow for the first tab to access the tab's settings then Change both the title and id for the first tab. Repeat this for the second tab:
Now that we have two tabs, navigate to Dashboard > Custom Callbacks then navigate to the onUrlParams(Map params)
callback. Note that you may have to use the arrows on the right hand side to scroll to the `onUrlParams(params)` callback.
Finally, we can import the tabs using the right hand menu and add the following AMIScript to select tabs based on the URL parameters:
String selectedTab = params.get("tab");
if (selectedTab == "first") {
Tabs1.setSelectedTab(first_tab);
} else if (selectedTab == "second") {
Tabs1.setSelectedTab(second_tab);
}
The final callback should appear as shown below:
Finally, we can test the URL by accessing http://localhost:33332/3forge?tab=first
and http://localhost:33332/3forge?tab=second
.