Using the visual no-code Datamap builder you can quickly and easily build a SQL query. The SQL query is one component of a data product that includes:
A dataset
SQL (auto-generated for you)
Metadata
Operations reporting
You will learn how to:
Find the best fit data with Data Explorer (Promethium's intelligent data catalog)
Transform data without writing code
Auto generate a SQL query
Query the dataset in real time
See the options available for publishing the Datamap
How to Create SQL Query with a Datamap
Go to Dashboard tab > select an existing table where you want to connect the new datamap.
2. In the table's view, click on New Datamap button.
3. Select the fields you want to add in your query. Click on Select Fields Next > button.
Note: You can add new fields too.
You can also edit the Alias of each field. Double click on the default alias and modify.
5. Once done editing, click on Done.
To check if you successfully set up this source, you should see it displayed like this.
6. Click on + Add Step button > Group by.
This is where your summary calculation be applied to.
7. After selecting a group field, click on Add Summarization Field button.
You can define your summarization fields by choosing a summarization type and a field to summarize. Example, sum of revenue.
8. Click on Done.
To verify that the groupings and summarizations you created are successfully saved, it should look like this.
9. To join another table, click on + Add Step > Join.
10. Click on Select Table.
It will redirect you to Data Explorer page. Make sure to click on All Items.
11. Click on the table you want to join, then click on Add to Data Map.
12. Click Done.
13. Select a join type and then specify the join keys. Then, click Done.
14. Select data to be included in the query by clicking going to these tabs, check or uncheck fields. And even edit the alias.
If you want to add a customized field, click on Add New Field.
Example:
Click Done.
15. Click Done. Then, click Save and Run.
To verify if fields are successfully saved, you should see the summary in Fields row.
It should show the table results at the bottom part of the screen.
16. Once the results are loaded, click on Publish.
17. The next step is to publish your dataset. Use one of the three options available:
• View - Similar to a database view. The data isn’t copied or written anywhere and the view can be queried
• Table - A new table is created in your chosen data source
• dbt Model - dbt Core™ is used to write the data to your chosen data source (Go to guide)
18. Click on Done. Once the model is successfully published, you should be able to see it at the top part (depending on how you sorted the list) of the list in Data Explorer page.
To get a live preview, click on the model name then go to Samples tab.
