Reading BQL Query Navigation Results

Use the Navigate Query button at Admin - Define Sources -Query Admin - Queries to see how a BQL query is resolved.

A BQL query can be satisfied by one or more logical tables in the data store (warehouse). Dimensional attributes are retrieved from logical dimension tables, and measures are retrieved from logical fact tables. You can follow the navigation of a BQL query to see how the data is coming from the correct tables.

This is important if you have mapped the same logical column to two different tables, as may be the case for a measure that is mapped to a summary and detail table in the source.

Navigating Measure Columns

For each measure column used in the query, the Navigate Query pane shows a section called Measure Column that lists the tables that contain that measure. The asterisk (*) before the first listed table indicates that Birst used that particular table. Next is the logical table name, followed by the cardinality value the system has generated, and then the physical table, or tables involved in it.

For example, using the following query:

SELECT USING OUTER JOIN [Order_Details.CustomerID], [OrderDate: # OrderID] FROM [ALL]

The OrderID (the count of OrderID) measure is mapped to two measure tables: Orders and Order Details. When you click Navigate Query, you see the navigation report.

For example, the Measure Tables section of the navigation report:

Measure Column: OrderDate: # OrderID
Measure Tables (cardinality):
* OrderDate: Day Employees Orders Fact (6.185907E7) [DW_SF_DAY_EMPLOYEES_ORDERS]
OrderDate: Day Employees Orders Fact_Customers Day Fact (6.8044977E7) [DW_SF_DAY_EMPLOYEES_ORDERS,DW_SF_CUSTOMERS_DAY]
OrderDate: Day Employees Orders Fact_Day Employees Fact (6.8044977E7) [DW_SF_DAY_EMPLOYEES_ORDERS,DW_SF_DAY_EMPLOYEES]
OrderDate: Categories Day Employees Order_Details Products Fact_Day Employees Orders Fact (7.614043642774801E15) [DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS,DW_SF_DAY_EMPLOYEES_ORDERS]
OrderDate: Categories Day Employees Order_Details Products Fact (6.921857857068E15) [DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS]
OrderDate: Categories Day Employees Order_Details Products Fact_Categories Day Fact (7.614043642774801E15) [DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS,DW_SF_CATEGORIES_DAY]
OrderDate: Categories Day Employees Order_Details Products Fact_Categories Day Products Fact (7.614043642774801E15) [DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS,DW_SF_CATEGORIES_DAY_PRODUCTS]
OrderDate: Categories Day Employees Order_Details Products Fact_Customers Day Fact (7.614043642774801E15) [DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS,DW_SF_CUSTOMERS_DAY]
OrderDate: Categories Day Employees Order_Details Products Fact_Day Employees Fact (7.614043642774801E15) [DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS,DW_SF_DAY_EMPLOYEES]
OrderDate: Categories Day Employees Order_Details Products Fact_Day Suppliers Fact (7.614043642774801E15) [DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS,DW_SF_DAY_SUPPLIERS]

Dimension Tables Selected

After the list of measure tables, Birst shows the list of dimension tables selected.

Dimension Tables Selected:
Order_Details Customers [DW_DM_ORDER_DETAILS_CUSTOMERS]
Dimension Column: Order_Details.CustomerID
Dimension Tables (level):
* Order_Details Customers (Customers) [DW_DM_ORDER_DETAILS_CUSTOMERS] [XXXXXXXXXX]
Order_Details Orders (Orders) [DW_DM_ORDER_DETAILS_ORDERS] [XXXXXXXXXX]
Order_Details Order_Details (Order_Details) [DW_DM_ORDER_DETAILS_ORDER_DETAILS] [ XXXXXXX]
Order_Details Orders_Order_Details Customers_ (Orders) [DW_DM_ORDER_DETAILS_ORDERS,DW_DM_ORDER_DETAILS_CUSTOMERS] [XXXXXXXXXX]
Order_Details Order_Details_Order_Details Customers_ (Order_Details) [DW_DM_ORDER_DETAILS_ORDER_DETAILS,DW_DM_ORDER_DETAILS_CUSTOMERS] [ XXXXXXX]
Order_Details Order_Details_Order_Details Orders_ (Order_Details) [DW_DM_ORDER_DETAILS_ORDER_DETAILS,DW_DM_ORDER_DETAILS_ORDERS] [XXXXXXXXXX]
Order_Details Orders_Employees Employees_ (Orders) [DW_DM_ORDER_DETAILS_ORDERS,DW_DM_EMPLOYEES_EMPLOYEES] [XXXXXXXXXX]
Order_Details Orders_Order_Details Customers_Order_Details Order_Details_ (Order_Details) [DW_DM_ORDER_DETAILS_ORDERS,DW_DM_ORDER_DETAILS_CUSTOMERS,DW_DM_ORDER_DETAILS_ORDER_DETAILS] [XXXXXXXXXX]
Order_Details Orders_Order_Details Customers_Employees Employees_ (Orders) [DW_DM_ORDER_DETAILS_ORDERS,DW_DM_ORDER_DETAILS_CUSTOMERS,DW_DM_EMPLOYEES_EMPLOYEES] [XXXXXXXXXX]
Order_Details Order_Details_Order_Details Orders_Employees Employees_ (Order_Details) [DW_DM_ORDER_DETAILS_ORDER_DETAILS,DW_DM_ORDER_DETAILS_ORDERS,DW_DM_EMPLOYEES_EMPLOYEES] [XXXXXXXXXX]

For each dimension column in the query, Birst shows the logical name, the level, the physical name, and whether there is a join between that dimension table and the measure tables listed earlier.

Each X indicates there is a join to the table by the order in which the tables are listed, and a space indicates there is no join. For example, if there were 3 measure tables, you could get [XXX], [ ], [XX ], [X X], [ XX], [X ], [ X ], [ X] indicating the various possible permutations of 0-3 joins. This can get very long for a complex logical query.