Dynamic dimensions

View previous topic View next topic Go down

Dynamic dimensions

Post  kskistad on Mon Mar 16, 2009 3:43 pm

What is the best way to handle dimensions that change depending on user parameters? For example, lets say you want "Months Since Inception" to be a dimension, because you want to bucket by it (i.e. 0 to 50 Months, 51 to 100 Months, etc.) but this is calculated by a date parameter given when the user queries the database. For example, the user plugs in a date, say June 2008. Then it uses this date to determine the months between the Inception Date and the date parameter, and that becomes the bucket.

kskistad

Posts : 11
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Dynamic dimensions

Post  BoxesAndLines on Mon Mar 16, 2009 5:09 pm

Update the fact row. I don't know anyway around this. It might not be too bad. I currently have an accumulating snapshot with ~200K rows per partition on Oracle that performs pretty well.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Dynamic Dimensions

Post  kskistad on Tue Mar 17, 2009 10:54 am

After giving this some thought, I came to the conclusion that, although it would be nice to pick any date in history for the "as of" date, it wouldn't be practical. First of all, a 10 million row fact table would grow to over a billion rows if you duplicated it for each day of the year for the past 10 years. Keep in mind that in my example I wasn't tracking history per se, like some fact tables do, but rather I am trying to get at an "as of" date for the report. If the user picks June 2008, it would display fact records up to June 2008. If they picked July 2008, it would show the same fact records, but including the month of July. If they picked December 22, 1999, it would show all facts up to Dec 22, 1999, and so on. This would require duplicating the 10 million rows for each day because, and this is the crux of the problem, the Months Since dimension would be different depending on which date they picked. And as far as I know, dimensions keys don't magically change depending on some filtering criteria

I think my options are to define a set of "as of" periods that users typically use, such as "As Of Today", "As Of Last Month", "As Of Last Year", and then only duplicate the facts for those periods, so 10 million would increase to 40 million (not really, since the further back in time you go, some fact rows will drop off).

The other option is to create a table with only the As of Date key/Months Since Inception key/TransactionID, and that table can be very large, depending on the number of As Of Dates you want to track. But then you can join this table to other fact tables by TransactionID using a view to get all the data you need, while saving storage space.

kskistad

Posts : 11
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Dynamic dimensions

Post  jpayton on Fri May 08, 2009 4:15 pm

Am I missing something? If the requirement is to get a 'balance' as at a specific point in time, why wouldn't you just use your date prompt in your reporting tool as a cut-off, and summarize all activity up to that date. Granted you've got quite a few records in the table, but nothing that couldn't be managed by your DB engine. Plus depending on your toolset, the result could then be used for a drilldown analysis of activity generating the balance. (I'm thinking 'Bank Account' here, but correct me if I'm wrong).

jpayton

Posts : 10
Join date : 2009-05-08
Location : Ontario, Canada

View user profile http://www.dynamicintelligence.ca

Back to top Go down

Re: Dynamic dimensions

Post  ngalemmo on Mon May 18, 2009 12:53 pm

You could use date functions to calculate the other date in the selection range, or you could have a month count in your date dimension table. Month count would be calculated by assigning a sequential count chronologically in the dimension table. Given any date you can calculate the month count range you need to select.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dynamic dimensions

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum