Data can be loaded into Rafn Analytics studio from a file (Excel, delimited, XML, json) or from a database (MS Access, SQL Server, SQLite, MySQL).
To load a file, drop it in the data area or select it from the File menu item. If you load an Excel file with multiple worksheets you will be prompted to choose one (if you have checked the Full automatic option, you will not be asked, instead each worksheet will be checked for data corresponding to a recognizable source, see below). If you load a delimited file you will be prompted to verify the appropriate delimiter (the most probable one is suggested to you, you may save your choice for the rest of your session). If you load an XML-file you will be prompted to select the appropriate node or table (you may preview your choice and also save it for the rest of your session). Json files are assumed to be two dimensional.
If you load files that correspond to a saved workflow and a recognizable source file, you may load an infinite number of files at one time (see Saving and running workflows below).
To load data from a database, click the Database menu item and select the database type. Depending on your choice, fields and controls for necessary inputs are made available. You can fetch data from a complete table or filter or get data from multiple tables by providing an SQL-query. You may save all of your import specifications and retrieve them from the Select saved query dropdown list for succeeding sessions.
To use any of the built-in transformation and analysis tools, highlight a column in the data area by clicking it, then select a category in the top menu to reveal a list of tools in the right-side menu area. (You may also right-click the selected column for a selection of tools). Clicking a tool will show necessary user input controls for supplying input or conditions, or for selecting additional columns to be included in your operation (for some tools, such as connecting to an export database, additional dialogs may appear). Clicking the Go button will perform the selected operation on the highlighted data column. With each tool, a short instruction is provided in the tool window. Please refer to the Tools-section of this website for in-depth descriptions.
Sometimes, you may want to create a workflow in which input parameters are only known by the final user. In such a situation, you may type the word UserInput in any of the text input controls. This will allow you to create a custom instruction to the final user, prompting him or her to supply input according to the instruction.
To delete rows from your data, select the Select rows radio button, highlight rows to be deleted and right click them to reveal the Delete selected rows option. Don’t forget to change back to Select columns before you continue. The 1st to header button will make the first row of your data the header row.
You may undo and redo your actions for ten steps in either direction.
Saving and running workflows
Before you start transforming and analyzing data, you may check the Save workflow as checkbox and name your workflow. Everything you do after naming your workflow (using tools, deleting rows, manually editing cells) will be saved. Clicking the Saved workflows menu item will reveal a list of all your saved workflows. Clicking a saved workflow will open a separate window detailing each saved step of your workflow. In this window you are given to option to Run your workflow, or to Step through your workflow one step at a time (you may move and resize the workflow window to work with it separately). If your workflow does not work as expected, stepping through it is a good way to find the step that causes the problem.
If you are loading files that correspond to a workflow and a recognizable source, you may drop or select an infinite number of files simultaneously.
Recognizing source data
When you click the Save workflow as checkbox, the Camera utility appears. If you click the camera, a snapshot of you source data is saved and associated with your workflow. If, at a later point, you check the Recognize source checkbox before loading data, your source data will be automatically recognized, and the corresponding workflow will be suggested to you. If the Full automatic checkbox is also checked, your workflow will automatically be run without any questions (useful if you load multiple files at one time and you don’t want to verify the match each time).
In the standard setting, recognition is performed by saving and searching for the text of the column headers of your source data. Headers must not be in the first row of your source data, in the Recognition settings menu, you may set the number of source data rows in which to look for a saved header row if the first row is not recognized. Set the Autodelete rows above header setting to decide if rows above the identified header row should automatically be deleted and the identified header row made the header row of your loaded data as well. If you want to save some data above the header row to memory for use in your workflow, you may also perform row removal and making a data row the header row by creating these steps in your workflow.
With Fuzzy recognition mode selected, Rafn Analytics Studio will evaluate if saved column headers associated with a workflow is present in your source data and, if so, remove columns not relevant to your workflow and reorder remaining columns to fit the appropriate workflow. To create a source data snapshot intended for use with fuzzy recognition, remove irrelevant columns before clicking the camera utility.
When Fuzzy recognition mode is set, you may also allow for some spelling deviation regarding your column headers. Set the number of letters that a header may be off and still be identified in the Recognition settings menu. In this menu you may also view and delete recognition templates. Deleting a workflow will not automatically delete recognitions associated with it.
Exporting, importing and editing workflows
Right clicking a workflow after selecting it in the list of saved workflows (made available by clicking the Saved workflows menu item) will reveal a context menu allowing you to delete, rename, copy or export the selected workflow, or to continue working with it. Selecting to export the selected workflow will open a save file dialog letting you save the workflow as an .rfn-file (any source data recognition associated with the workflow will be saved in the same file). Choosing to Continue workflow will allow you to continue adding steps after the last one in the selected workflow (load source data and run the workflow to this point before opting to continue).
You may import a saved workflow by dropping an .rfn-file into the list of saved workflows (any associated recognition will also be imported). If you have created or received a workflow that performs a specific task that is to be used as a subset of a larger workflow, you may import it into another workflow. Simply open the workflow window by clicking the relevant workflow, and drop the .rfn-file at the appropriate position.
You may edit or export parts of a workflow directly. Changing the values of a step (corresponding to data typed into the user input controls or indexes of columns selected) will update the step with the new values (column indexes can be calculated from the order of the columns visible in the data area at the point of the current step, starting with zero). Selecting one or multiple steps in the workflow window and right clicking will allow you to delete the selected steps, create a new step after the last selected (letting you use a tool at the selected position) or to export the selected steps to a new .rfn-file.
To find the settings menu, click the Menu option in the top menu. The File export mode setting lets you decide whether to output files (if you have used the Export to new file-tool) for manual naming and saving, or to automatically save them back to the source folder and name them SourceFileName_processed. The Excel export format setting lets you decide whether to preserve data formats when exporting to Excel, or to export all data in string format. The Visible operations controls lets you select which tools should be visible in tool category menus by dragging and dropping between the visible/non-visible list boxes.
Updating the application
Rafn Analytics Studio is designed to be adaptable to a large range of ETL and data analytics needs. Tools can be custom made and patched into the application with no need for reinstalling it. A patch can be loaded by clicking the Load patch button in the Update the application menu item.