Avoid fact updation

Question

Avoid fact updation

Ritika Thakkar

SSC Journeyman

Points: 99
More actions
June 13, 2008 at 8:48 am

#122809

Hi,
I have 2 dimension tables, one for product and one for manufacturing status of a product. In my fact table, i store a row for each status of the product. A sample fact table row would look like :
Prod ID | Status | Date
1 | 1 | 1-1-2008
1 | 2 | 10-1-2008
I need to be able to calculate time spent in a status. So, in this case, time spent in status 1 should be 9 days and so on. How can i do this without updating the fact table?
Any help would be much appreciated!!
Regards,
Ritika

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Neelakandan V-453229 Ten Centuries Points: 1109 More actions · Answer 1

Hi,

When you load the fact table, you can calculate this 'duration' and store it. You can use this duration as a measure in your cube.

Let me know if I am over-simplifying your problem. 🙂

Regards,

Vijay.

Warm Regards,
Neel aka Vijay.

Neelakandan V-453229 Ten Centuries Points: 1109 More actions · Answer 2

Oops, just noticed that you wanted a solution without updating the fact table! 😛

hmm.. but why? if it's a current production environment and you do not want to disturb it, using a view is an option? You would need to point your cube to the view instead of the fact table. But it's not a best practice. You should consider it only if the fact table is not huge.

Regards,

Vijay.

Warm Regards,
Neel aka Vijay.

Ritika Thakkar SSC Journeyman Points: 99 More actions · Answer 3

Vijay,

Thanks for your response......

However, its not a current production system so the view workaround will not work!!

Is there any other way of doing this?

Regards,

Ritika

Neelakandan V-453229 Ten Centuries Points: 1109 More actions · Answer 4

Well, if it's not in production yet, why don't you consider using a view?

Warm Regards,
Neel aka Vijay.

Ritika Thakkar SSC Journeyman Points: 99 More actions · Answer 5

Ritika Thakkar

SSC Journeyman

Points: 99

June 17, 2008 at 6:50 am

#829720

how exactly do i do that?

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 6

I can't understand why you wouldn't want to update the fact table. You are already updating it with the entry of a new record, so why not have a field that sums the time in status? One of the main purposes of the structure of the data warehouse is to calculate as much useful information in advance as possible so that the cube can performance it's calculations at think time. Either you or someone else are throwing unnecessary obstacles in your way.

Ritika Thakkar SSC Journeyman Points: 99 More actions · Answer 7

the concern here is that if i update the fact table (which is huge in size) then i will need to reprocess the entire cube everyday. I want to be able to avoid that. Is there any other way of achieving this?

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 8

if you were adding information, you could use partitions. Are you already doing this to pick up the new records? If not, don't you have to process anyway. If you are doing this for new records, I agree that the updating of already processed records creates a challenge. I'm not sure how I'd do that, but I'll think about it for a bit.

I can only wonder how many records you have. I have millions and the processing doesn't take too much time, although too much to do in the middle of the day.