How to create many-to-many relationship between two different attributes within a cube?

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question about how to create many-to-many relationship between two different attributes within a cube? (e.g. In insurance case, I want to create a many-to-many relationship between attribute broker and attribute assured company, as one broker may have many different assured companies insured under each of them, and each insurred company may be insured under different policies under different brokers). It seemed we could only create one way relationship between them, but cant make it another way around. But this is the common case in the data though. Could please any experts here shed me any light on it?

Thank you very much in advance and help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi Helen,

take a look at this great paper by Marco Russo:

http://www.sqlbi.eu/Portals/0/Downloads/M2M%20Revolution%201.0.93.pdf

There are many examples in it, and probably also what you are looking for (may be the one about bank accounts).

Francesco

|||

Hi, Francesco,

Thanks a lot for the very helpful and great paper.

With best regards,

Yours sincerely,

How to create many-to-many relationship between two different attributes within a cube?

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question about how to create many-to-many relationship between two different attributes within a cube? (e.g. In insurance case, I want to create a many-to-many relationship between attribute broker and attribute assured company, as one broker may have many different assured companies insured under each of them, and each insurred company may be insured under different policies under different brokers). It seemed we could only create one way relationship between them, but cant make it another way around. But this is the common case in the data though. Could please any experts here shed me any light on it?

Thank you very much in advance and help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi Helen,

take a look at this great paper by Marco Russo:

http://www.sqlbi.eu/Portals/0/Downloads/M2M%20Revolution%201.0.93.pdf

There are many examples in it, and probably also what you are looking for (may be the one about bank accounts).

Francesco

|||

Hi, Francesco,

Thanks a lot for the very helpful and great paper.

With best regards,

Yours sincerely,

How to create dimension with attributes from different tables?

Hi, all here,

I am having a question about how to create dimension with attributes from different tables? For example, I am going to create a dimension called Country-region with attibutes from country table and region table. So how can it be done then?

Thanks a lot in advance for any guidance and help.

Hi,

You will need to create a 'named query' in your DSV and use this named query as a source for your dimension.

HTH,

Eric

|||Hi, Aiwa, thanks a lot. But would you please post some sample code about named query for that then? Thanks a lot.|||

Hi,

I don't know your tables structure but you could create a named query that would look like that:

SELECT DISTINCT a.IdCountry, a.CountryCode, a.CountryName, a.OtherCountryAttributes,b.IdRegion,b.RegionCode, b.RegionName,b.OtherRegionAttributes

FROM Country a

JOIN Region b on b.IdCountry = a.IdCountry

Hopefully, your fact table keeps the region id or region code information. You will then create your Dimension based on the named query and btw, the id of the dimension should be the IdRegion column.

HTH,

Eric

|||Hi, Aiwa, thanks a lot. Got it done.

How to cotrol format of datetime attributes?

There is an attribute that have type DateTime, based on the field type in data source. It hasn't separate name column. The member caption is formatted as yyyy-MM-dd hh:mm:ss. Can I control the format of Member name without giving separate name column.

I assigned different format in the format field of key property window, but it had no effect. What I did wrong?

Hello. I do not think that you will have to pay any penalty from adding a new column in the data source view with a new format of your date column. I recommend to use the TSQL function CONVERT that have arguments for different date formats. Have a look at CONVERT in Books On Line, and you will see the complete list of different codes/arguments for different date formats.

HTH

Thomas Ivarsson

|||

Thank you,

I thought about more sofisticated solution, that can be used in multi culture environment without adding x additional fields with "formatting" of a datetime attribute.

I hoped, that the AS2005 is smarter as AS2005 and offers more possibilties.

Do you know what is the format field in key properties for?