Multi-table support in Direct Discovery

You can use Direct Discovery to load more than one table or view using ANSI SQL join functionality. In a single chart, all measures must be derived from the same logical table in QlikView, but this can be a combination of several tables from source linked via join statements. However, you can use dimensions sourced from other tables in the same chart.

For example, you can link the tables loaded with Direct Discovery using either a Where clause or a Join clause.

  • Direct Discovery can be deployed in a single fact/multi-dimension in memory scenario with large datasets.
  • Direct Discovery can be used with more than one table which match any of the following criteria:
    • The cardinality of the key field in the join is low.

    • The cardinality of the key field in the join is high, DirectEnableSubquery is set to true and all tables have been joined with Direct Discovery.

      Using subqueries with Direct Discovery

  • Direct Discovery is not suitable for deployment in a Third Normal Form scenario with all tables in Direct Discovery form.

Linking Direct Discovery tables with a Where clause

In this example script, we load data from the database AW2012. The tables Product and ProductSubcategory are linked with a Where clause using the common ProductSubCategoryID field.

Product_Join: DIRECT QUERY DIMENSION [ProductID], [AW2012].[Production].[Product].[Name] as [Product Name], [AW2012].[Production].[ProductSubcategory].[Name] as [Sub Category Name], Color, [AW2012].[Production].[Product].ProductSubcategoryID as [SubcategoryID] MEASURE [ListPrice] FROM [AW2012].[Production].[Product], [AW2012].[Production].[ProductSubcategory] WHERE [AW2012].[Production].[Product].ProductSubcategoryID = [AW2012].[Production].[ProductSubcategory].ProductSubcategoryID ;

Linking Direct Discovery tables with Join On clauses

You can also use Join On clauses to link Direct Discovery tables. In this example statement we join the SalesOrderHeader table to the SalesOrderDetail table via the SalesOrderID field, and also join the Customer table to the SalesOrderHeader table via the Customer ID field.

In this example we create measures from the same logical table, which means we can use them in the same chart. For example, you can create a chart with SubTotal and OrderQty as measures.

Sales_Order_Header_Join: DIRECT QUERY DIMENSION AW2012.Sales.Customer.CustomerID as CustomerID, AW2012.Sales.SalesOrderHeader.SalesPersonID as SalesPersonID, AW2012.Sales.SalesOrderHeader.SalesOrderID as SalesOrderID, ProductID, AW2012.Sales.Customer.TerritoryID as TerritoryID, OrderDate, NATIVE('month([OrderDate])') as OrderMonth, NATIVE('year([OrderDate])') as OrderYear MEASURE SubTotal, TaxAmt, TotalDue, OrderQty DETAIL DueDate, ShipDate, CreditCardApprovalCode, PersonID, StoreID, AccountNumber, rowguid, ModifiedDate FROM AW2012.Sales.SalesOrderDetail JOIN AW2012.Sales.SalesOrderHeader ON (AW2012.Sales.SalesOrderDetail.SalesOrderID = AW2012.Sales.SalesOrderHeader.SalesOrderID) JOIN AW2012.Sales.Customer ON(AW2012.Sales.Customer.CustomerID = AW2012.Sales.SalesOrderHeader.CustomerID);
Note: It is not possible to refer to tables by alias in the FROM clause, you need to use the full table name.