Who is this article for?Coruson administrators or data scientists.
Access keys for Galileo are required.
Galileo is Ideagen's data warehouse for Coruson, which allows you to connect and perform your own analysis of your Coruson data.
When you connect to Galileo you will notice a series of table views available.
This article outlines the steps required to link these tables using Microsoft Power BI. This does not provide a full table map but does provide guidance on how to identify what tables are related.
This article is split into 4 sections:
- Review the Schema
- Add Tables to the Model
- Build a Relationship Model
- Base Report Pack Relationships
- Further Reading
Missing the Galileo query? Visit How to Connect to Galileo using Power BI for steps on how to create this query.
1. Review the Schema
Galileo contains two schemas intended to provide a list of views focussed on different objectives.
- DWReporting: The DWReporting schema contains the base table views needed to create reports on specific objects without pulling in related data unless absolutely necessary. The queries will therefore be simple and easier to manage.
- DWAnalytics: The DWAnalytics schema contains the various pre-created sample and modular fact views, allowing much more complex queries to be created and a greater amount of analysis to be carried out. The queries will therefore be more complex and produce considerably more data.
2. Add Tables to the Model
To add tables to the model:
- Open Power BI.
- Go to the Power Query window by clicking Transform Data.
- Click on the Galileo query you previously created.
The tables in this list have been named according to the Coruson module they hold data on (e.g. dwanalytics_reports contains data on the report module).
If you click on the table you will update your Power Query, therefore for ease of access it is best to create new queries based on the tables you are interested in.
To create a new query:
- Right click on Table.
- Select Create New Query.
- Review the data to confirm this provides all of the data you need.
- Repeat this process for any tables you are interested in including on the report.
Reminder: The more data you query, the longer it'll take to update your reports. It is best practice only to query what you need.
3. Build a Relationship Model
All of the tables in Galileo are inter-linked but depending on the type of analysis you are trying to perform you may need to create or modify the links that exist.
Example: The dwanalytics_audit_table will essentially server as the master table. The supporting tables, such as dwanalytics_audit_checklist and dwanalytics_audit_workflow, will all reference the dwanalytics_audit_table using the audit_id field. Likewise, dwanalytics_audit_table will link to dwanalytics_audit_type. The names of the Ids have been done in a way that the nature of the relationship can be identified from the names.
To build the relationship model:
- Go to the PowerBI window.
- Click on the relationships button.
- Locate the tables you need to link.
- Drag and drop the Id from one table to the other.
4. Base Report Pack Relationships
If you are using the Galileo Base Reporting Pack PBIX (available at How to Set-up the Galileo Base Reporting Pack) then you may need to adjust the relationships.
The below tables have been added to the pack in order to build the relationships and widgets in the pack.
Note: You will need to change the <API> prefix to match the name of your database.
- <API>_lists_table
- <API>_risk_event_table
- <API>_risk_project_table
- <API>_audit_stage_table
- <API>_document_stage_table
- <API>_general_lists_table
- <API>_report_stage_table
- <API>_risk_event_details_table
The below relationships have been configured:
|
From Table |
From Column |
To Table |
To Column |
|---|---|---|---|
|
<API>_risk_event_table |
risk_project_id |
<API>_risk_project_table |
risk_project_id |
|
<API>_document_stage_table |
id |
<API>_general_lists_table |
id |
|
<API>_document_stage_table |
id |
<API>_report_stage_table |
id |
|
<API>_risk_event_details_table |
id |
<API>_risk_event_table |
id |
|
<API>_risk_event_details_table |
id |
<API>_report_Stage_table |
id |
|
<API>_risk_event_details_table |
risk_event_id |
<API>_lists_table |
list_item_id |
Article Comments
1 comment
It makes no sense to link the document_stage_table (id) to the general_lists_table (id), same with risk_event_details_table (id) to report_stage_table (id). And it will return an error when you Close and Apply.
These are not valid relationships. The relationships associated with the Base Reporting Pack are not at all helpful, although the pre-made visuals may be, once the proper data and relationships are there.