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 key time column and key column for a case table and a nested table for time series

Hi, all experts here,

Thanks for your kind attention.

I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Can you please clarify if you're creating an OLAP mining model or a relational mining model?

Thanks

|||

Hi, Shuvro,

At the moment I am trying a create a relational mining model.

But it will be brilliant to hear from you for both how to create an OLAP mining model as well using case table and nested table with Time Series Algorithm.

Thanks and I am looking forward to hearing from you further for your advices.

With best regards,

Yours sincerely,

|||

Can you please post the schema of your case and nested table for the relational scenario?

For the OLAP mining model, the design would be on the same lines as the relational, but instead using the dimension attribute and the measure. The TIME KEY usually can be selected from your date dimension, depending on the granularity of your data, but you'll have to do some work to make it correctly sortable. (e.g. Month names are not a valid time key).

|||

You can mine just the fact table and ignore the date table. The fact table needs to have the date value anyway. Your model would look something like this

CREATE MINING STRUCTURE TS_Structure

(

[Date] LONG KEY TIME,

[Region] TEXT KEY,

Value1 DOUBLE CONTINUOUS PREDICT,

Value2 DOUBLE CONTINUOUS PREDICT

)

ALTER MINING STRUCTURE ADD MINING MODEL TS_Model

(

[Date],

[Region],

Value1 PREDICT,

Value2 PREDICT

) USING Microsoft_Time_Series

When using the tools, you would simply mark your source table as the "Case" table (not nested) and then mark both the [Date] column and the [Region] column as keys. The tools will figure it out.|||

Hi, Jamie,

Thanks for the advices.

With best regards,

Yours sincerely,