Multiple-fact Queries
The GO Data Warehouse (analysis) package has characteristics that affect the results if you use a multiple-fact query with the Inventory and Sales namespaces. Granularity for time differs in that inventory levels are recorded monthly and sales are recorded daily. Also, Sales includes a non-conformed dimension, Order method.
The following examples will help you interpret the results of a multiple-fact query and understand the options for changing a query to obtain the results that you want.
Conformed Dimensions
Individual queries on Inventory and Sales by Quarter and Product yield the following results.
A query on multiple facts and conformed dimensions respects the cardinality between each fact table and its dimensions and returns all the rows from each fact table. The fact tables are matched on their common keys, Product and Time.
Product and Time apply to both Inventory and Sales. However, inventory levels are recorded monthly and sales are recorded daily. In this example, results are automatically aggregated to the lowest common level of granularity. Quantity, which comes from Sales, is rolled up to months.
Nulls are often returned for this type of query because a combination of dimensional elements in one fact table may not exist in the other. For example, if Husky Rope 50 was available in inventory in Q1 2011, but there were no sales of this product in the same time period, Quantity would show a null in the Husky Rope 50 row.
IBM® Cognos® Analytics with Watson does not support conformed dimensions generated by IBM Cognos Framework Manager for SAP BW data sources.
Non-conformed Dimensions
If a non-conformed dimension is added to the query, the nature of the results returned by the query is changed.
Order method type exists only in Sales. Therefore, it is no longer possible to aggregate records to a lowest common level of granularity because one side of the query has dimensionality that is not common to the other side of the query. Opening inventory and Closing inventory results are repeated because it is no longer possible to relate a single value from these columns to one value from Quantity.
Grouping on the Quarter key demonstrates that the result in this example is based on the same data set as the query on conformed dimensions. Summary values are the same. For example, the total quantity for Q1 2011 is 88,320 in both examples.
Filters on Non-conformed Dimensions
By default, to improve performance, filters are applied at the database level. The default behavior can result in unexpected nulls when you add a filter to a multiple-fact query. If you create a filter on the Order method dimension to show only the fax order method and apply the filter at the data source, the report includes nulls.
The filter is applied only to one subject area, Sales. Because Order method type does not exist in Inventory, all products still appear in the report. For example, Course Pro Umbrella was in inventory in Q2 2012. Because there were no sales using the fax order method for this product in Q2 2012, Order method type and Quantity are null.
To remove the nulls, change the filter so that it is applied to the result set instead of the data source. By applying the filter to the result set, you get the same results that you would get if you were working with conformed dimensions. Only the products that were ordered using the fax order method appear in the report.
The overall summary for Quantity is 8,043 using either filter method, which shows that the results are based on the same data set.
In IBM Cognos Analytics - Reporting, there are two types of filters. A detail filter is applied to the data source. A summary filter is applied to the result set.