Tiered Warehouses with Conformed Dimensions

Birst can seamlessly drill down from an aggregated level space to fact tables in a different space. To do this you set up tiered warehouses - a managed data mashup of two spaces with conforming dimensions.

Example of Tiered Warehouses with Conformed Dimensions

In this example there is an aggregated level space that provides a package consumed by a child space. There is a naming conflict (the object names are the same in both) and therefore the objects are merged.

For detailed instructions see Creating a Package and Importing a Package.

The parent space, from which the package is created, has Order Details targeted (aggregated) at the Category level.

In this space the Products hierarchy consists of Categories and Products levels.

In this space you would create a package consisting of the conformed dimension (Product.Categories, Products.Products) and the fact aggregated to the categories level Order_Details fact.

 

The child space, to which the package will be imported, has Order Details aggregated at the Products level.

In the child details space, the Products hierarchy consists of Categories and Products levels, which is a conforming dimension with the parent, aggregated space.

The imported package has the conformed dimension from the parent, aggregated space.

Next, you would set up a drill map.

A report could show Quantity by CategoryName.

The query navigates to the aggregated space containing measures aggregated at the Category level. For example:

SELECT USING OUTER JOIN [OrderDate: Sum: Quantity] 'COL0' , [Products.CategoryName] 'COL1' FROM [ALL]

SELECT SUM(CAST(DW_SF_CATEGORIES_DAY_ORDER_DETAILS0_.Quantity$ AS BIGINT)) AS 'COL0',DW_DM_PRODUCTS_CATEGORIES1_.CategoryName$ AS 'COL1' FROM [AggregatedSpace].DW_SF_CATEGORIES_DAY_ORDER_DETAILS DW_SF_CATEGORIES_DAY_ORDER_DETAILS0_ INNER JOIN [AggregatedSpace].DW_DM_PRODUCTS_CATEGORIES DW_DM_PRODUCTS_CATEGORIES1_ ON DW_SF_CATEGORIES_DAY_ORDER_DETAILS0_.Products$Categories_1697024394$=DW_DM_PRODUCTS_CATEGORIES1_.Categories_1697024394$ GROUP BY DW_DM_PRODUCTS_CATEGORIES1_.CategoryName$

 

The report could drill to the ProductName.

The query navigates to the details space containing measures aggregated at the Products level. For example:

SELECT [OrderDate: Sum: Quantity] 'COL0',[Products.CategoryName] 'COL1',[Products.ProductName] 'COL2' FROM [ALL] WHERE [Products.CategoryName]='Beverages'

SELECT SUM(CAST(DW_SF_DAY_ORDER_DETAILS_PRODUCTS0_.Quantity$ AS BIGINT)) AS 'COL0',DW_DM_PRODUCTS_CATEGORIES1_.CategoryName$ AS 'COL1',DW_DM_PRODUCTS_PRODUCTS2_.ProductName$ AS 'COL2' FROM [Details Space].DW_SF_DAY_ORDER_DETAILS_PRODUCTS DW_SF_DAY_ORDER_DETAILS_PRODUCTS0_ INNER JOIN [Details Space].DW_DM_PRODUCTS_CATEGORIES DW_DM_PRODUCTS_CATEGORIES1_ ON DW_SF_DAY_ORDER_DETAILS_PRODUCTS0_.Products$Categories_1697024394$=DW_DM_PRODUCTS_CATEGORIES1_.Categories_1697024394$ INNER JOIN [Details Space].DW_DM_PRODUCTS_PRODUCTS DW_DM_PRODUCTS_PRODUCTS2_ ON DW_SF_DAY_ORDER_DETAILS_PRODUCTS0_.Products$Products1249892458$=DW_DM_PRODUCTS_PRODUCTS2_.Products1249892458$ WHERE DW_DM_PRODUCTS_CATEGORIES1_.CategoryName$=N'Beverages' GROUP BY DW_DM_PRODUCTS_CATEGORIES1_.CategoryName$,DW_DM_PRODUCTS_PRODUCTS2_.ProductName$