Microsoft Analytical Services / Data Warehouse Cubes - Creating 1 to Many relationship

Question

I have a bit of dilemma creating unique 1 to many type relationships to build a data warehouse table using cubes/Microsoft analytical services.

This is how my data is built

I've got a model with a fact_table, that has an order_no, package_no, and many other columns. For each order + package I want to provide all the performances that are in that package. The model we have is a fixed package can have the same 4 performances. But a flex package can have any 4 performances you want.

The way this is built is

Table1: Package_no | Package_details etc.

Table2: Package_no | Perf_Group_No (this has a unique ID for each package)

Table3: Perf_Group_No | Perf_no

Talbe4: Perf_no | Perf_details etc. ...

Through the above joins, we can display all the performances in a given package. This works really well when it comes to fixed packages because no matter what you buy if you have package ABC you will always have performances A, B and C. When it comes to Flex packages they are built to include all 25 performances and the place where we actually see what you selected is your Order table.

We are trying to translate this data into a dimension in a cube.

Person A - purchased order # 12345. They had Fixed Package ABC with performances A, B and C and they had a Flex Package with performances X, Y and Z

Person B - purchased order # 45678. They had Fixed Package ABC with performances A, B and C and they had a Flex Package with performances J, K and L

Person C - purchased order # 12098. They had Fixed Package ABC with performances A, B and C and they had a Flex Package with performances J, K and Z

---

Having the order_no and package_no helps us identify if its a flex or fixed package but we need the order itself to see what actual performances were purchased in the case of the flex.

Here is what I have Order_No, Package_No I need to build a series of views creating a series of joins between the Fact table with order_no/package_no and the performance_no to provide details for each order. However it seems I need to do it one to one and never one to many. No matter what I do, there is always a one to many relationship.

This is the idea we have -- the package table has a reference, and the performances have a reference and their combo creates the linking view/table. The problem is there is still a 1 to many relationship no matter how you slice it.

Any ideas or suggestions would be appreciated. If more clarification is needed I'd be happy to provide. If there is a way to create a 1 to many relationship in the CUBES itself, that could work as well but I haven't been able to find a good explanation of how to build that.

Thank you,