CODEWITHSUNDEEP
sqlsql-serverssasmdxolap
mclark1129
mclark1129

Reputation: 7592

Combining Relational and OLAP data in an MDX Query

I have an SSAS 2008 cube that is being used to house end of day financial data from the stock market. The cube is only processed once a day after the market closes, so it never has any information about the current intraday trading data. I also have a relational database that houses the current intraday trading information for stocks. I am trying to find a way to combine those two data sources so that I can perform calculations such as a 30 day moving average for a stock that is based off of its current price, as well as the previous 29 days of historical data. I am using SSAS Standard edition, so I don't have access to features such as Proactive Caching or multiple partitions to help me process the current data in near real time.

Is there any way that can somehow dynamically include rows from my SQL database into my fact table, for the context of an individual query? Essentially just bring in a small subset of data into the cube temporarily in order to process a certain calculation?

Upvotes: 6

Views: 1558

Answers (4)

Magnus Smith
Magnus Smith

Reputation: 5963

It is possible to write your own DLL and call it from within MDX. It's not terribly graceful but I've done it in the past.

Not a great idea for 1000s of rows of data, but if you need less than 100, your function call could pass a value from MDX to the DLL, which can call the SQL database to return the numbers. Then your results get displayed in the cellset alongside the numbers from OLAP.

Upvotes: 0

Farfarak
Farfarak

Reputation: 1517

You can incrementally process data for the cube on specific time intervals during the day depending on how long does it take to process new data. ( Of course if delay are acceptable )

Upvotes: 0

Diego
Diego

Reputation: 36136

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

Upvotes: 1

brian
brian

Reputation: 3695

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

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

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 could then use that view as the data source for the ROLAP partition.

Upvotes: 0

Related Questions