Concepts → Base Table
About a Base Table
A base table is a physical schema entity object, typically a facts or intermediate object, that is a child table in multiple join relationships with other objects in the same physical schema or other physical schemas. You can reference this entity object as a base table to do the following:
- Create dimension-only or attribute-only queries that reference entity objects that do not have a direct join relationship. Typically, these entity objects are dimension tables that do not share common dimensions; however, each of them has either a direct join relationship with the base table (where the base table is the child entity object) or a join path to it.
- Ensure that Incorta applies the join paths you expect when there are multiple join paths between two entity objects in a given query.
- Handle Many-to-Many join relationships.
Example for using a base table
Consider a physical schema with the following entity objects:
- SALES (transactional or facts table)
- PRODUCTS
- CUSTOMERS
- COUNTRIES
The physical schema has the following left-outer join relationships:
| Child Table | Parent Table | Join Condition |
|---|---|---|
| SALES | PRODUCTS | SALES.SALES.PROD_ID = SALES.PRODUCTS.PROD_ID |
| SALES | CUSTOMERS | SALES.SALES.CUSTOMER_ID = SALES.CUSTOMERS.CUST_ID |
| CUSTOMERS | COUNTRIES | SALES.CUSTOMERS.COUNTRY_ID = SALES.COUNTRIES.COUNTRY_ID |
Notice that there is no direct join relationship between the PRODUCTS and COUNTRIES or the PRODUCTS and CUSTOMERS objects.
In the case that you want to create a query, an insight for example, that references both the PRODUCTS and CUSTOMERS or the PRODUCTS and COUNTRIES entity objects, you need to either include the SALES object in the query or explicitly reference it as the driving Base Table for the query.
Referencing the facts or intermediate object in the query may not work when determining the join path in some visualization types, an aggregated table for example. You will need to explicitly define the facts or intermediate object as the base table for the measures from the participating objects.
Base Table context
You can define a base table for one of the following while referencing two or more objects:
- an insight
- an Incorta Analyzer table
- a business schema view
- an Incorta View
You can also define a base table when exploring data from a physical schema or a business schema.
The method you use for defining a base table varies according to the query context or the object.
Define a base table for the Analyzer objects
For objects that you use the Analyzer to create: insights, Incorta Analyzer tables, and Incorta views, or when exploring data, you need to drag a column from the facts table (Base Table) to the Measures tray → pill properties panel → Advanced → Base Field.
Here is an example:
With reference to the physical schema mentioned above, you can use the Analyzer to create an aggregated table insight that shows sold products per country.
In the Cluster Management Console (CMC), you can create a tenant that includes Sample Data for the SALES schema. Load the physical schema data before using it to create the example.
Here are the steps to create the Sold Products Per Country aggregated table insight:
- In the Navigation bar, select the Content tab, and then select + New → Add Dashboard.
- In the Add Dashboard dialog, for Name, enter Sales Dashboard, and then select Add.
- In the Action bar, select + (add icon), or select + Add Insight.
- In the Insight panel, select Listing Table or V.
- In Tables, select Aggregated Table.
- In the Data panel, select Add Data Set (+).
- In the Manage Data Sets panel, in Tables, select SALES. Close the Data Sets panel.
- From the Data panel, drag and drop the
COUNTRIES.CountryNamecolumn to the Grouping Dimension tray. -
Drag and drop the
PRODUCTS.ProductIDcolumn to the Measure tray, and then do the following:- Select > to open the pill properties pane, and then change the Aggregation function to DISTINCT.
- Select Query Plan to check the query plan for the
PRODUCTS.ProductIDcolumn in the Query Plan Viewer. Close the Query Plan Viewer.
- In the Measure tray, for the
PRODUCTS.ProductIDcolumn, select > to open the pill properties panel, and then drag any column from the SALES table to Advanced → Base Field. - Select Query Plan again to check the changes in the query plan for the
PRODUCTS.ProductIDcolumn. Close the Query Plan Viewer.
Notice the following:
- Before defining the Base Field, the insight shows only the number of products while it does not show any country. The Query Plan Viewer shows the PRODUCTS object as the Base Table for the query and no join paths between the participating objects.
- However, after defining the base field, the insight shows the countries in the COUNTRIES table and the number of products per country. The Query Plan Viewer shows the SALES object as the Base Table and the join path to move from the measure object: PRODUCTS to the dimension object: COUNTRIES through the SALES object.
Checking the query plan for your columns is a good practice. It allows you to ensure that The Analytics Service is applying the join paths you expect while running a query.
Defining a base table for a business schema view
For a business schema view, use the Business Schema Designer to set the Base Table. To learn more, review Tools → Business Schema Designer → Set the base table for a business schema view
Define the base table at the business schema view level to avoid defining it multiple times when referencing the same entity objects in different insights, and use the business schema view as the source for the different insights as appropriate.
When integrating with external tools, such as Tableau or PowerBI, make sure that you define a base table for Incorta views that these external tools use.
In the case of using a base table other than a physical schema table, perform a full load of the object data after defining the base table for the business schema view.