Training a Time Series Data Mining Model From an OLAP Cube

Question

I have a an OLAP cube that stores products and their corresponding sum order quantities

So when I execute the following MDX, I get correct data:

SELECT [Measures].[OrderQTY] ON COLUMNS,
NONEMPTY ([Order Date].[Order YMD].[Order YMD].MEMBERS * [Product].[Product].[Product].ALLMEMBERS ) ON ROWS
FROM [ProductOrdersCUBE]

The output will be similar to the following:

20120928     Product1    100
20120929     Product1    200
20120929     Product2    150

.... etc

Note: The YMD is an long integer that represents a date KEY in the format: yyyymmdd

I created a time series data mining model as follows:

CREATE MINING MODEL OrdersForecastModel
(
  YMD LONG KEY TIME,
  Product TEXT KEY,
  OrderQTY LONG CONTINUOUS PREDICT_ONLY
) USING Microsoft_Time_Series

The model was created successfully.

Afterwards, I tried to fill/train the model from my OLAP cube using the following MDX:

INSERT INTO OrdersForecastModel (Product,YMD,OrderQTY)
SELECT [Measures].[OrderQTY] ON COLUMNS,
NONEMPTY ([Order Date].[Order YMD].[Order YMD].MEMBERS * [Product].[Product].[Product].ALLMEMBERS ) ON ROWS
FROM [ProductOrdersCUBE]

But I get the error:

Server: The operation has been cancelled.
Internal error: The operation terminated unsuccessfully.
Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'OrdersForecastModel_Structure ~MC' cube from the MyOLAP 2008 R2 database.
Errors in the rowset store. Unable to convert a value to the data type requested for column 0.
The following system error occurred:  Type mismatch.

I tried changing INSERT INTO OrdersForecastModel (Product,YMD,OrderQTY) to INSERT INTO OrdersForecastModel (OrderQTY,Product,YMD) and then
INSERT INTO OrdersForecastModel (OrderQTY,YMD,Product)

But I am still getting the same error...

What am I missing here ?!

Please help

---

Luai7

Answer 1

Hi luai7,

We should train mining models by using the Data Mining Extensions (DMX) query language. Data Mining Extensions (DMX) is a query language provided by Analysis Services that you can use to create and work with mining models. For more information about INSERT INTO (DMX), please refer to: http://msdn.microsoft.com/en-us/library/ms132169(v=sql.100).aspx 

Thanks,
Eileen

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Answer 2

Hi Eileen,

Thank you for your help.

In fact, I know that we should use DMX :-)  I have already done this so many times but all the times the training was linked to a relational OLTP database. The idea here is I am trying to train the model from the OLAP cube instead in order to overcome the performance issue related to OLTP. There are not so many examples on how to do that on the Internet. In fact, our proposed solution above was based on an MDX/DMX statement proposed in a well known data mining book (I am not sure if we can mention the name of the book in this forum to avoid legal issues)....

I am pretty certain that it is only a little tweak in the above statement will do the job because the book says that it works... It is something related to the "Type Mismatch" error thing...

If any one has a previous experience working with this, then really appreciate your input and suggestions...

Thank you

Luai7 

---

Luai7

Answer 3

 In fact, our proposed solution above was based on an MDX/DMX statement proposed in a well known data mining book (I am not sure if we can mention the name of the book in this forum to avoid legal issues)....

It would be helpful if you disclose the book. First of all, you did not say anything terrible about the book, second of all you are running under an alias.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 4

Thank you Kalman.

I prefer not to mention the book title as this might be considerd as an Ad. I am not really sure if this is allowed on the forum.

Meanwhile, I really appreciate it if any one has any suggestion or workaround for the above mentioned problem.

BTW, I was able to build the above model successfully when using OLTP as the source data for the model instead of OLAP! However, I still prefer to have the source as OLAP for performance reasons.

Thanks

---

Luai7

Answer 5

I'd also like to know which book/White paper has this documented on? I've seen people talking about excerpts from books, blogs and whitepapers here. So I don't think there's any harm done. And besides, as Kalman said- You're not saying anything bad, just exchanging information whithin the community forum.

Thanks,

Rok

---

please remember to mark as answered if the post helped resolve the issue.