"Real-time" ROLAP partitions with MOLAP dimensions

As I've described in another thread, I've doing some work with ROLAP partitions and dimensions in an attempt to have a small set of "real-time" data accessible along with our historical MOLAP cubes. I never found a solution to the problem I described in that thread, so I'm trying a new approach - adding a measure group with a ROLAP partition to an existing cube (in many ways this is preferable anyway, since we could then query across both the historical and real-time stuff).

What I was unclear on is how dimensions would be handled in this case, since they would be the same MOLAP dimensions that the historical cube is using. Since the ROLAP partition will contain facts that are associated with dimension members that haven't been processed yet, how would it handle it? Well, the answer seems to be "not that well". When I browse the ROLAP measures that reference dimension members that haven't been processed yet, I get the error:

"Errors in the OLAP storage engine: The attribute key cannot be found: Table: <fact table name>, Column: <dimension key>"

Is this a totally unreasonable thing to do? Is there some other approach I'm not considering?

This is with SSAS SP2, if it matters.

Do you have some sort of process inserting the new dimension records into the dimesion tables or are you building directly off your source system.

If you have an SSIS package inserting new records you could use a mutlicast transform and send one copy of the rows to the dimension table and another copy to the dimension processing destination. If you then set the dimension processing destination to ProcessAdd it will just append these new rows to the dimension.

|||We have a custom application inserting the dimension records into a traditional star-schema data mart (sort of a domain-specific ETL engine).

Are you talking about using push-mode processing for the new dimension records? Or a standard ProcessAdd processing command?
|||I am talking about push mode processing. The dimension processing destination in SSIS simply generates the appropriate XMLA. If you are not using SSIS it might be easier to generate the XMLA. Greg Galloway has some XMLA samples of doing push mode processing. http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx|||Interesting idea, but I don't think it will work for us. Aside from general concerns about the limited information about push-mode processing out there, and its potential performance impacts, Greg's posts say "Push mode processing is only allowed on partitions or dimensions which use one table from the DSV". Our dimensional structure is heavily snowflakes, so it sounds like it's not really an option for us.

Is our scenario (ROLAP partitions with MOLAP dimensions) an unreasonable one? I don't see anything in the docs that indicate that. Or are most people just not adding unprocessed dimension members before they query their ROLAP partitions?
|||

You could probably mix ROLAP partitions and MOLAP dimensions only if your dimensions are relatively stable. For each fact record it has to do the equivalent of a join to the MOLAP dimension and if you have records that have not been processed into the MOLAP structure yet you will get the error you discribed.

I am begining to feel like we are being squeezed between a rock and a hard place.

You could switch the affected dimensions over to ROLAP storage. I'm honestly not sure what impact this would have on performance - as I think I've mentioned in other threads I have not had to use ROLAP storage with any of my clients in AS2005. I'm guessing the dimension with new members is going to be something like a large customer dimension or something similar.