Detail Dimension for Drillthrough

Question

I have created a number of type 1 dimensions to hold customer/subscription level details. These dimensions are very large compared to any other dimensions I am using with nearly a 1 to 1 relationship to facts. The dimensions are only being used to provide drillthrough details.

It's all working but the size of these dimensions is quite large and I'm running into some memory issues when processing. I'm wondering if there are some porperties I should be setting since these are only used for drillthrough? NonAggregateable?

Would it be better to include details as nonAggregateable Measures since there is nearly a 1 to 1 relationship?

An example would be SubscriptionDetail which has values like email, userUID, activationcode. If users are looking at the subscription fact they can drillthrough to pull these details.

Answer 1

You won't be able to use strings as measures, so Email will be out.

I have had success using hidden datetime measures for drillthrough though, to get the exact datetime when the fact table generally keys off to a date dimension.

If processing is an issue, and there is a 1:1 with the fact, does the dimension change historically? If not, have you tried ProcessAdd to only add the new rows? If you have enterprise SSIS there is a component for this, or you can generate your own XMLA and send it to the server as part of the processing: http://www.artisconsulting.com/Blogs/tabid/94/EntryID/3/Default.aspx