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,