Topic creation: connect
crystal provides insights through topics. Building your topics is the core of the Self-Service Console. The topic creation flow is made of four main steps:
Connect
The first step indicates that to create a topic you’ll first have needed to connect a data source. To add your data with crystal, access your crystal Self-Service Console, then press the Topics tab in the top menu bar. Press the Add new topic button in the Add new topic card.
When you select a table by pressing the Select button, in the panel that slides up, press the Add new data source button.
You’ll first need to select from where you’ll connect your data: check How to manage your data sources to know more about the types of data sources available and other specific information.
Once you’ve selected your source (note that you can only select one at a time), press Next. You’ll then need to add your host type, database, user, URL, password, port and data source name into the corresponding fields. Note that you’ll need to work alongside someone from your IT department to help you gather this information and ensure that your data is connected properly with crystal. When ready, press Test and connect.
If all the parameters are correct and if there are no connection problems, your data source is now connected. If some information provided was not accurate, you will receive an error message and you will have to revise the fields.
Select a table
Now that you have selected a data source, you will see a list of all tables in the data source and the columns that each table has. You will have to select the first table that will be used to build your topic and then click on Add table. Let’s assume that you want to get all the information from the Clients table.
Great! Now you have a table from which the topic you will create will get the data.
Now that you have selected at least one table, by clicking on the Preview button, you will get an anticipation of your table: data will be shown in the first data table tab, while the other areas are going to be empty for the moment. If you want to know more about the preview function, read Data preview: what it is and how it works.
If you realize that you don’t need this table anymore, you just have to click on the bin icon.
You will get a message to confirm your decision and if you will choose Yes, delete the table will be canceled.
But what happens if you need more data? For example, you may want to connect your clients database with the sales results, to see who are your best buyers.
It’s not hard at all: you just need to join tables!
Join tables
Until here, you have connected a data source and you have selected a table with the information you want to use for your topic. If you need another table to complete your project, no worries: all you have to do is join tables. Click on Create join.
You will see this view, where your table Clients is already present and you have the chance to add a second table by clicking on Select table.
The icon between tables represents the joins, which is the operation you will choose to get together the data of left and right tables.
You will have three possibilities of joining the tables.
The inner join selects all rows from both tables, as long as the conditions on key columns are met. This type of join creates a new table that combines all rows from both of the tables where the values of the key columns match.
The left join shows all the rows of the table on the left side of the join and the matching rows for the table on the right side of the join. If there are left side rows with no matching rows on the right side, a null value will fill the space for the right side.
The right join is similar to the left join. This join returns all of the rows of the table on the right side of the join and the matching rows for the table on the left side of the join. And, for any rows where there are no matching rows on the left side, those values are replaced with null.
The type of join selected by default is the inner join: if you want to change it, click on the icon to open the selection.
Now you will have to choose the second table. In this case, we are choosing the table Sales to relate with Clients. You will find the already previously selected table indicated at the top of the list.
Click on Add table and you will be sent back to the select tables view, but, as you can notice, you will have two tables now.
Select the key column for both tables: key columns will represent the same value, therefore they must be of the same type.
For example, if you have an id product column in the product table, you will need to join it with the product id from the sales table: the two columns will refer to the same value. This means that, if the first table shows numeric values, you will only be shown numeric columns on the right hand selection.
Note that the columns, in order to be joined, must have the same data type (eg. text), otherwise the operation will not go through.
Now click on the join icon found in between the tables: you must select if you are heading for an inner, right or left join.
You can also change the join type after you choose it: just click on the join icon and you will open again the join type selection.
Your tables are now joined and ready to be used to build a topic! If you should need another table, here we go: click again on Create join and you will see this view. All you have to do is click again on Select table and repeat the steps to add another table: the first join will be joined together with this new table.
Remember that the more tables you will add, the longer it will take for the process to be completed due to the large amount of data to consider.
And what if you need to remove a join? Very easy: just click on the bin icon next to the table join.
We hope this article helped you in the first step of the topic creation flow. Check out Topic creation: configure to keep on learning about the topic creation process!
Last updated
