6

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?

mclark1129
  • 7,532
  • 5
  • 48
  • 84
  • Sounds like you have a business case for the Enterprise edition of SQL. You can mock it up in Developer edition and see if the boss will spring for it. – Bill Oct 16 '12 at 15:39
  • @Bill Unfortunately, the cost of Enterprise licensing is prohibitive for this project. – mclark1129 Oct 16 '12 at 16:33

4 Answers4

1

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

Diego
  • 34,802
  • 21
  • 91
  • 134
  • if I create a separate measure group for my OLTP table, with the exact same measures as my OLAP table, is it possible to somehow union those together into a single set? Essentially appending the rows from the OLTP table on to the OLAP table in the query? – mclark1129 Oct 16 '12 at 14:27
  • you would need a key to do that. Dont forget that all your OLAP data comes from an OLTP database, so the principle is the same. If yo uonly need 1 row of data, you may have a fact or even a dimension (called Configuration for example) with one row ID=1 and on your other objects you would need a configID field with the value of 1. – Diego Oct 16 '12 at 14:30
  • Looks like this is ultimately the best solution. I'm going to try to merge the columns using calculated members and CASE statments. Hopefully it doesn't get too messy! :) – mclark1129 Oct 19 '12 at 14:07
0

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.

brian
  • 3,635
  • 15
  • 17
  • The problem that I have is I'm using SSAS Standard, not Enterprise, which limits me to a single partition for a measure group. Would there be some way I could do that using separate measure groups for historical and current data? – mclark1129 Oct 15 '12 at 15:45
  • Geez sorry. Didn't realize there was a partition limitation for standard. One option would be using an OLAP linked server to query the cube portion with OPENQUERY and MDX. Push the results to a temp table or table variable, then do your calculations from T-SQL. – brian Oct 15 '12 at 16:16
0

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 )

Farfarak
  • 1,497
  • 1
  • 8
  • 8
  • Unfortunately, any significant delay would not be acceptable in my case. I need the ability to include (for example) the price of a stock at 2:15 PM today in the moving average calculation. – mclark1129 Oct 16 '12 at 19:30
0

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.

Magnus Smith
  • 5,895
  • 7
  • 43
  • 64