Step by step guide on using the BI tools. From Database connection to data preparation, visualization and sharing
Here you establish a connection with your database. DataLynx will try to connect to the database and pull the tables names and structure.
The system uses "PDO" connection drivers as follows:
- PDO_SQLSRV for MSSQL
- PGSQL for PostgreSQL
- MYSQL for MySQL and MariaDB
Here you get to edit your database login information. You can also change the display name of the database an its tables. The display name will be the name used to refer to the database and table throughout the system.
The table structure is pulled the first time the connection is established.
Not all tables are needed here, so in order to avoid over-crowding the system, only the tables set as "data source" will be available in the next steps.
This button will fetch the latest data from source. Data fetches assume that the structure is maintained. If the structure is changed and you want to pull the new structure, then you'll have to create a new DB profile.
Foreign Keys are used to connect two tables. A Primary Key to a Foreign Key. For example in table "sales" you'd find a column called "emplyeeid", which refers to the ID of the employee who made the sale.
This connection gives us the ability to join 2 or more tables into 1 table, which gives us more flexibility in creating meaningful charts. If we use the previous example, this feature allows us to show that employee "John" made the sale instead of employee "5" made the sale.
Here we create our tables. You can choose from data sources you specified earlier in the database tables. Then join the data sources according to your foreign keys settings.
In this page you get to your first visualization of your data, you can join your data into one table. You can also filter the data and create custom columns.
You can edit you table name and column headers names. You can add columns, hide columns, join DB tables, and filter data.
There are four types of filters: Remove row, add prefix, add suffix, and replace row value.
You will have to specify the filter condition. For number and date fields, there are four types of conditions, equal, not equal, greater than, and less than. For other types of fields there are equal and not equal.
Adding columns to the table requires two parameters: name and equation. The equation accepts the four math operators only, ie: +-*/
The saved tables can be found in My Box -> My Chart Tables
Here we view our saved tables. Full data is shown here, so your table is ready to be exported, shared, and embedded into your blog, website, and online presentations.
Chart tables can be exported to Excel, PDF, and more general purpose CSV format.
Sharing is a link that you can distribute in emails, social media, ..etc, for people to view your table. Where embedding is used to make the table part of another page, this page could be your company page or blog post for example, among other things.
Here we create the charts. Charts are extracted from Chart Tables created in the previous step. Each chart can have one x-axis and multiple series (datasets)
Chart Name: The name that will show on the top of the chart box
Chart Table: The table from which the chart will extract the data
X-Axis: The column from Chart Table that will be our chart x-axis
Inervals: If you choose a date column for X-Axis you'll have the option to pick time intervals
Date Format: If you choose "days" interval you can pick your desired format
Minimum Date: The earliest date limit to show on the chart
Maximum Date: The latest date limit to show on the chart
You can create multiple series in your chart, with two types:
Count rows: Simple count of occurrences (rows) for each of the X-Axis elements
Sum Values: This will prompt selection of column values to sum. Only columns with "number" attribute will show
You can create a different type for each series, There are 3 chart types: Column, Area, and Line Charts
These are extra options for customization: Theme (dark, light), palette, line stroke, stacking, enabling data labels, x-axis title, and y-axis title
This button re-draws the chart applying your latest changes