1:n relationship fact to dimension

[Pumuckel]

Hi @all,

I'm quite new to OLAP.

My issue now is, that I have some facts in my fact table, which are linked to several members in a dimension.

How can I solve this problem, without blowing up my fact table and without loosing any relations to the dimension as well?

Thx

 

[MDXer]

Man talk about a hard one. without really knowing the specifics it's hard to say what will work. However since I'm brave I'll suggest one avenue to look at. If you can find a way to group the many together and then create a virtual dimension off the column that is the many.

If you have 3 employees all part of the same work unit you could create a workunit and assign a workunit ID in the fact record along with the employeeID. Create a virtual dimension off the workgroup using the employeeid.

This won't require you to explode or compress your data volume. However it could make some types of calculations tricky.

Maybe if you could provide a scenario or a brief data snippet it might make more sense.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Patlin]

Do you not mean that there is a n:n relationship between the fact table and dimensiontable?
In that case you have a select distinct count problem.
And they are hard. Microsoft has function for that.
But it can be slow.

 

[MDXer]

the use of the distinct count function is slow and I rarely use it. If I require calculations based upon distinct counts I will build a cube for the distinct count value and then bring it into a virtual with the rest of the measures. with your average sized cubes it isn't to bad on prformance but on large cubes it can have a noticable impact depending upon the number of distincts and the total number of records.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Pumuckel]

The cube analysis product data.

In the Fact table I'm counting products and in the dimensions the products are specified. But for example one product can have several failures. So what to do, not to loose any of the failures, but not to explode the fact table as well?

It isn't possible to group the failures for one specific product...

Thx