Dear aBI: Those who report off of history tables are doomed to repeat it.

[Former Member]

I, expert that I am, have been answering questions for our community members as part of our Dear aBI series of business intelligence advisements. This week's question comes from Snickers in Dallas. Yeah, like "Dallas" is a real location.

In a universe, we have a history table and a current table - The structures are in the same formats and contain the same field names. We would like the queries to be directed to the history table if the users choose a date that is > 30 days back, but the current table for dates that are within the 30 day range. We considered using aggregate navigation but the user does not want to have to select a 'history/current' field. The users wish to have the system interpret the value they enter and pick the appropriate navigation path. Can this be accomplished without forcing the user to select a special field for aggregate navigation or without having to choose a context?

Well, Snickers, wherever you are, I have what will probably be the simplest possible answer, one which you've probably received before and which you dismissed for some ridiculous reason: create a derived table and union both tables together in that derived table. Something as simple as "select * from current_table union all select * from history_table" should work.

There are numerous benefits to this approach.

1. It is painfully easy to implement. Seriously. I just wrote the SQL for you.
2. The users will be able to query values both in the last 30 days and before that AT THE SAME TIME! It's like magic, but without the ridiculous hats.
3. This will be totally seamless to the users. They won't even have to pick a context.
4. This will be very easy to maintain. Aggregate aware, while exceedingly powerful, can also be painful to keep up with as your universe grows.

There will be people who say this idea won't work for some of the following reasons.

1. It won't be as performant as querying one table or the other. They may be right. If it is slow, simply put the query (which I have already written for you) into a materialized view or a temporary table of your choosing. Then it should be faster. If it is still slow, go crazy and add an index or something. Hey, be kooky.
2. This will make the application be less performant. If you are writing your universe against a transactional system, first, shame on you for going against best practices. Second, congrats to you for being bold and daring. As far as performance, you've already sort of given in to reporting off of your source system, what's one more little query?
3. I'm your DBA and I refuse to sign off on this change because I'm not the one who came up with it. This is a tough one to work with. Try telling them to suck it up. Then (most likely) try something else, maybe while offering them a donut.

If you really can't do the union query, I'd have to believe that there are some ways to use derived table, universe prompts, contexts, and aggregate aware navigation to create an optimal solution, but more specific requirements would change the details of that answer significantly. Will people pull multiple dates (including ranges from both tables) in the same query, or will every query be based on one snapshot date (also, FYI, if your user says it will be just one snapshot date, they are probably lying - consider yourself warned)? What other tables will these tables be combined with in a result set? What sort of prompts will you use to generate the report (quarterly, monthly, weekly, daily, etc.)? Why exactly can't we use the union query again?

Hopefully this was helpful, not just with the solution but the justification for it. If you have a better answer, please jump in below. Really. I'm loving the comments over on my last Dear aBI: Why are my Live Office Connections So Slow? post (which are probably more useful than the post itself!).