Knowage (www.knowage-suite.com) is an open source Business Intelligence suite, that descends from well known SpagoBI.
In this AIKnow.info Blog article, we will see how to create a QBE (Query By Example), very useful when you want to allow end users the possibility to query databases in a visual way, i.e. without using interrogation languages like SQL.
In the following image (Fig.1) you can see QBE interface. Tables regarding facts are available in the left side, and can be analyzed dragging and dropping the attributes of interest in the central panel, where you can order them, group them, filter the resulting informations using various principles and so on.
Fig.1 Knowage QBE
Now we will see the procedure to obtain the above result. Let’s assume that you have Knowage installed, and that you can use a dedicated data base schema. Even if the data of your interest are already in a data base, it’s always a good idea to work in a separate schema to avoid interferences between operational informative systems and business intelligence ones. In this way you can also manage better the so called ETL (Extract, Transform and Load) processes: an essential part of a business intelligence system.
For this example I’ve downloaded some data sets from dati.italiaitalie.it open data portal. I’ve loaded them in a MySQL shema using the Import Wizard utility available in Toad for MySQL tool (Fig.2).
Fig.2 Toad for MySQL Import Wizard
As a result I’ve in MySQL 7 tables regarding some facts of interest: resident population absolute and relative, energy consumption, bank deposits, available income, bank use total, home theft. These tables can be joined by means of two fields: Territory and Year.
Fig.3 data loaded in My SQL
Now we can open Knowage and start the configurations needed to build the QBE.
The first step is to define a new Data Source. You can do it logging to Knowage as administrator and using Data Providers menu section. In Fig. 4 you can find the configuration for MySQL DB.
Fig.4 Data source configuration
The second step is to define the Business Model: this is possible accessing the Menu Catalogs – Business Models catalog, and giving some information (Fig.5). In order to easily profile user access to data, it’s important to define a custom category (ITALIA) for the Business Model. This can be done from Menu Server Settings – Domain management (Fig.6)
Fig. 5 New Business model interface
Fig. 6 New Business Model category definition
Once given the informations requested in Fig.5, clicking on save enables the metamodel creation button. The first step to define the metamodel is to choose the tables to include, from the list coming from the datasource (Fig.7).
Fig.7 Selection of tables from datasource
Clicking on continue it’s possible to edit the Business Model. It’s important to define for every table the key to use (Fig.8) and the relationships between tables (Fig.9)
Fig.8 Selection of keys from tables
Fig.9 Relationship definition
Once all relationships are created, coming back to Catalogs – Business models catalog, and selecting the one just created, it’s available the new button GENERATE (Fig.10 and 11) that allow the creation of the datamart needed to query the metamodel from the Workspace interface.
Fig.10 Business Model creation interface with GENERATE button available
Fig.11 Dialog box to confirm datamart creation
For a user to work with the metamodel just created, it’s necessary to give him the related permissions. This can be done accessing Profile Management – Role Management menu and allowing the business model category ITALIA to the role you want (Fig.12). If the role or user is not already present, you can add it from Roles Management or Users Management menu in Profile Management section.
Fig.12 Adding ITALIA category to the role user
Now, logging in Knowage with the user with the right profile, you can find the metamodel in Data-Models section (Fig.13).
Fig.13 user workspace with metamodel available
Clicking on magnify icon will open QBE interface (Fig.14). In the left column you can find available tables, and for each one available attributes. You can select those of your interest dragging and dropping them in Query Editor window. Here you can define various properties to apply to the attribute (Alias, Ordering etc.) and the filters your need.
Fig.14 QBE interface
When you’re sure about the attribute and the related properties, it’s possible to see a preview of the report (Fig. 15) using the command at the top right of Query editor panel. This can be saved for later use with the floppy disk icon.
Fig.15 Report Preview