0

Using Microsoft SSAS and SQLServer 2012 I am currently trying to update my timeseries model by using new values from my original source database table.

The Microsoft tutorials present for DMX suggesting using EXTEND_MODEL_CASES to update the model using PREDICTION JOIN and static values, for the sake of automation my aim is to do this using a SELECT FROM but am unsure how to refer back to my original data source.

EDIT : CLARIFICATION: the below code does not work as SSAS cannot seem to access my database table this way The question therefor is: How can I select a database table from within SSAS

 SELECT [DumpLocation_Id],
 PredictTimeSeries([TotalDumpCount],5, EXTEND_MODEL_CASES) AS PredictDmpCnt
 FROM
 [DumpForecasting_MIXED]
 NATURAL PREDICTION JOIN 
 ( select * from DumpStatistics3
  where TimeIndex >= (getdate() - 2))
 AS t

My expectation is to get a result set from said table inserted into my model.

Say my model contains:

 DumpLocation_id     |  TimeIndex   |   TotalDumpCount  |
 --------------------------------------------------------
  1                  |01-01-2014    |   23
  1                  |02-01-2014    |   13
  1                  |03-01-2014    |   14

I want to be able to select the data from my Database Table:

  DumpLocation_id     |  TimeIndex   |   TotalDumpCount  |
 --------------------------------------------------------
  1                  |01-01-2014    |   23
  1                  |02-01-2014    |   13
  1                  |03-01-2014    |   14    
  1                  |04-01-2014    |   15
  1                  |05-01-2014    |   17

and add it to my model so that the newer entries get entered into my model.

MSB
  • 854
  • 7
  • 24
  • What is the question? (looks more like `tSQL` rather than `MDX`) – whytheq Nov 14 '14 at 19:49
  • @whytheq Actually, it is **DMX**: http://msdn.microsoft.com/en-us/library/ms132031.aspx. But there does not seem to be a tag for DMX on SE, at least not the DMX language of SSAS. – FrankPl Nov 14 '14 at 20:19
  • @FrankPl I've attempted to create a tag - needs a description though. I know nothing about it so will leave that to you. Think once you get into 10000+ territory Frank you get extra tag privileges (wow!). – whytheq Nov 14 '14 at 20:30
  • Even though I find`Microsoft ASS`slightly funny, you should probably change it to`Microsoft SSAS`and`ASS`->`SSAS` :) – jpw Nov 15 '14 at 10:02
  • Analysis Service Server(ASS) seemed just fine to me :p but I see your point. – MSB Nov 15 '14 at 10:06
  • I edited the question to clarify what it is exactly that I'm hoping to achieve. I really hope someone can help me out on this one. – MSB Nov 16 '14 at 06:34

1 Answers1

0

I wanted to place what I came up with on here in case someone else runs into the same problem:

In order to select Tables from a relationalDatabase DMX supports the OPENQUERY statement (this has also been implemented in tsql)

OPENQUERY takes a string as argument that holds an equivalent of a tsql query.

example:

OPENQUERY (relDatabaseServer, 'SELECT name FROM example.titles WHERE name = ''NewTitle''');
MSB
  • 854
  • 7
  • 24