BI SQL Editor
This module extends Cyllo's reporting functionality by allowing users, primarily administrators, to create custom reports through SQL queries.
BI SQL Editor elevates Cyllo's reporting capabilities by enabling the creation of custom reports via user-written SQL queries. Admin users can craft their desired SQL query, map the retrieved data fields to corresponding Cyllo fields, and ultimately generate a new menu item, action, and graph view for the report.
Technically, the module creates an SQL View (or a materialized view for faster performance with larger datasets, which requires a cron job for data refreshes). By default, the "SQL Request / User" group can access all views, but you can specify additional groups for granular access control.
This module is designed for individuals with technical expertise within a company and for Cyllo integrators, as it requires a strong understanding of SQL syntax and familiarity with Cyllo models. Due to its technical complexity, it is not recommended for users lacking these skills, especially in a production environment.
Key Features
- Custom report creation via SQL queries
- Support for SQL Views and materialized views
- Access control for user groups
- Automated UI Creation
Custom Report Creation
Generate tailored reports via SQL queries to meet specific analysis needs, including technical requests not supported by the Cyllo framework (e.g., UNION with multiple SELECT statements).
Enhanced Reporting Options
Create new menu options, actions, and graph views for improved data visualization, enabling customization of Cyllo reports by adding or removing fields as necessary.
Materialized View Support
Address performance issues with large datasets by utilizing materialized views, reducing display duration and enhancing overall query performance.
Access Control
Configure permissions for user groups to access and interact with reports, ensuring data security and confidentiality.
Accessing the Module
Navigate to Dashboard / Configuration / SQL Views
Defining your SQL Query
- Add a technical name and set a view order
- Enable check box "is materialized view" to create a materialized view and check "use context field" if you want to use a context column in the field list view
- Enter your SQL request in the provided text field, specifying the data you want to retrieve and manipulate.
Select Access Groups
Choose the group(s) that should have access to the view, ensuring appropriate data security.
Optional: Add a Domain
Optionally, define a domain to filter the data based on specific criteria, such as company IDs in a multi-company context.
Validate SQL Expression
Click on the "Validate SQL Expression" button to verify the correctness of the SQL request.
Field Mapping
- After validating the SQL request, the module analyzes the columns of the view and proposes field mapping.
- For each field, decide whether to create an index and specify its display settings for pivot graphs.
Create SQL Elements
Click on the "Create SQL Elements" button to generate the SQL elements. Note that this step may take some time for materialized views.
Materialized View Settings (if applicable)
If the view is materialized:
- A cron task is created to refresh the view. You can so define the frequency of the refresh.
- Review the size of the view and indexes displayed.
Create UI
Finally, click on "Create UI" to generate a new menu, action, graph view, and search view based on the SQL request.
Access SQL Reports
Navigate to 'Dashboards > SQL Reports' to view the list of available reports. Choose the desired report from the list to access its details.
View Report
Switch between 'Graph' or 'Tree' views as needed to analyze the report data effectively.
