MySQL Forums
Forum List  »  Data Warehouse

Problem with granularity choosen
Posted by: Hélio Shimojo
Date: May 09, 2012 02:32PM

Hi.

I have a fact table with a very small granularity, which is a daily snapshot of sales process on my company.

I have told my client I couldn´t handle too much detail because of the poor hardware given. But he insisted on that because he requires detailed data for some decisions.

So we agreed on this deal:

- To persist only the last day snapshot of each month in database;
- But update every day, erasing the previous days of the same month;

Example:

2012-05-03 replaces 2012-05-02
2012-05-04 replaces 2012-05-03
2012-05-05 replaces 2012-05-04
...
2012-05-31 replaces 2012-05-30, maintain 2012-05-31
start another month
2012-06-01
2012-06-02 replaces 2012-06-01
...so on

I realized that deleting large number of records takes too long time.
So I split on 2 tables, one with the last day of the month results, and the other the partially results, so I can perform TRUNCATE TABLE on this partial result table.

1st question: Is that the best solution?

Continuing...
Now I need to perform a UNION ALL with these 2 tables using view. Unfortunately, the performance drops considerably, because I can´t manage the indexes properly (USE INDEX, FORCE INDEX ...). I am not happy with that.

What should I do?

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem with granularity choosen
3367
May 09, 2012 02:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.