Combining relational and OLAP data in an MDX query

Question

I have an SSAS 2008 cube that is used to place financial data at the end of the day from the stock market. A cube is processed only once a day after the market closes, so it never has information about current intraday trading data. I also have a relational database that stores current intraday trading information for stocks. I am trying to find a way to combine these two data sources so that I can perform calculations, such as the 30-day moving average for stocks, which is based on the current price, as well as on the previous 29 days of historical data. I use the standard version of SSAS, so I don’t have access to features like Proactive Caching or several partitions to help me process the current data in real time.

Is there any way to dynamically include rows from my SQL database into my fact table for the context of a single query? Essentially, just put a small piece of data into the cube temporarily in order to process a specific calculation?

Answer 1

no, you have to create a measure group that maps to your OLTP table

Answer 2

You should be able to create a partition for current day data and specify ROLAP as the storage mode.

To simplify maintenance, I would probably create a view for the fact table and use date functions in the where clause in the definition. Sort of:

CREATE VIEW CurrentTrades AS SELECT * FROM factTrades WHERE TradingDate BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE())) 

You can then use this view as a data source for the ROLAP section.

Answer 3

You can gradually process the data for the cube at specific time intervals throughout the day, depending on how long it takes to process the new data. (Of course, if the delay is acceptable)

Answer 4

You can write your own DLL and call it from MDX. This is not very graceful, but I have done this in the past.

Not a great idea for 1000 rows of data, but if you need less than 100, your function call can pass the value from MDX to the DLL, which can call the SQL database to return the numbers. Your results are then displayed in the cell phone set next to the OLAP numbers.