Handling many-to-many relationships with a bridge table in transformer

[LDJB81]

Hi all,

Something I have been wondering about for a while is how many-to-many relationships with a bridge table can be handled in Transformer or any other IBM cube technology. Just to give an example, I am working with a pharma client who use SSAS but I will be working with new pharma client in the new year where the logical solution will be similar to the current client but Transformer is the cube technology of choice. Pharma data is known for being quite complex, for example in the SSAS cube for the current client we have a dimension for products which has a direct relationship with the sales fact table. There is also a dimension for market which contains multiple products but a product can also belong in multiple markets. In this star schema the market dimension only joins to the bridge table and not the fact table. I can handle this quite easily in SSAS as you can define the relationships as many-to-many, how would I accomplish this in transformer? I have had a play around with dynamic cubes and it appears that any regular dimension has to have a relationship with the measure dimension which in this scenario wouldn't work.

Can anybody share some similar experiences?

Thanks
Lloyd

[bdbits]

In my opinion, you want to model complex relationships (like bridge tables) in a Framework Manager model and verify they are working correctly there. Create query subjects you need for the dimensions and facts organized in a star schema, even if it only looks like a star schema in the FM context explorer and is not so in the database. Then in Transformer, use the package as your source and pull the items for your levels and facts from the query subjects in the model. This should simplify building the Transformer models considerably.

Bear in mind Dynamic Cubes are different than Transformer and have different applications and parameters around their use. Also, in planning a move to 64-bit we recently realized that Transformer cubes are 32-bit CQM , and there is no plan to offer a 64-bit DQM equivalent. I believe Dynamic Cubes run under a pure 64-bit implementation.

[kiran.Amalakatta]

Hi Lloyd,

I am having same issue in Dynamic cubes where I have supplier and product tables with many to many relationship. How to handle this in Dynamic cube where the supllier table has no relationship with the fact table.

Please let me know if you have any solution for this.

Kiran

[MFGF]

Hi Lloyd,

I am having same issue in Dynamic cubes where I have supplier and product tables with many to many relationship. How to handle this in Dynamic cube where the supllier table has no relationship with the fact table.

Please let me know if you have any solution for this.

Kiran

Hi,

Dynamic Cubes are designed to run over physical star or snowflake schemas in your data warehouse. How does a many-to-many relationship between products and suppliers get resolved in your star schema in the data warehouse? It's a challenge for the data warehousing developers to solve, in reality. The only solution I can see in an OLAP environment is to have alternate hierarchies or separate dimensions - Supplier -> Product as one, and Product -> Supplier as the other?

MF.

[Cognos.Developer]

Hi,

create the model at FM with all required base tables, when come to Impliment Many-Many situatuion

Product, Market are DB tables, create a Bridge Table "Prod_Market" with Primary Key Columns Product, Market Tables.
Define the Relation Market Table to Fact Table(1 to many) , Prod Table to Prod_Market (1 to 1), Prod_Market to Fact (1 to Many). then publish thePackage hence import the Package in to Transformer here Take the Product, Market tables to create Hierarchies. Simply ignore the Prod_Market table.

I hope it may helpful us.

Thanks
Cognos Developer