Search code examples
iccubeiccube-reporting

use of bridge table with multi column joins in icCube

I have a question on best practice for a bridge table design:

I have a Fact table that has the following 3 columns where the primary keys are in bold:

Generator
FuelType
FuelCost

I then have a Generator dimension with the following columns:

Generator
PrimaryFuelType
SecondaryFuelType

I would like to be able to view my results by the combination of {Generator,PrimaryFuelType} and {Generator,SecondaryFuelType}

I was planning on creating 2 measure groups based on the Fact table and use my Generator dimension as a bridge in both. Basically have {Generator,FuelType} as the Join Columns (fact table) and {Generator,PrimaryFuelType} as Join columns (Bridge table). For the 2nd measure group it would then be {Generator,SecondaryFuelType} as Join columns (Bridge table).

Is that the best way to approach it? Would a potential restructuring of the data format in the dimension table make this more efficient.

thanks, John

Solution

  • Using a bridge in the link definition of the measures between Generator and PrimaryFuelType and SecondaryFuelType (the dimensions) is a possible solution.

    There is also another possibility, is to create a Dimension called Generator with 3 hierarchies ( Generator, PrimaryFuelType and SecondaryFuelType ).

    The solution you choose will depends on the size of the Generator Hierarchy (if it's huge better not) and if you want to have it for your reports. Indeed you could hide the hierarchy using a perspective.

    _hope it helps