We will have some data will be recorded as a daily balance (e.g., 3/1/08: $1,023, 3/2/08: $1,265, 3/3/08: $1,320, etc..) The idea being that time cannot be rolled up. To look at data by time, you must be at the date level. However, you would want to look at a time period, say March, and see each daily balance (Month, Year, Week, Quarter then become just filters.)
The requirements call for a dimensional model, so that other dimensions with hierarchical data can roll up. There will also be transactional data that would be in the same data mart. I.e., the fact record for March 1 may contain some transactional data and a daily balance. Looking at "March" data, you would need to see daily balances for the snapshot data, but rolled up transaction amounts for the transactional data. Tips, tricks, thoughts, suggestions on the best way to model this in Framework manager?
Thanks!
Can't tell you how to do it in FM, but I'm certain it's possible. The concept you are looking for is a time-state rollup (e.g. beginning-of-period, end-of-period) such as you'd use for inventory and other balance sheet items, and it would be a property of the measure. Unfortunately (I shouldn't be surprised) I can't find it in the FM user guide, and the user guides are all I have of C8... :(
Every search I've done on time-state rollup, points me back to Transformer... I don't see anything on FW Manager. I just wonder if there's a way to do this in FM?
Thanks!
Nathan
Time state rollup is possible in FM 8.3. You can add a new aggregate function in the particular measure for each dimension seperately.
We have tried this also on a snapshot model DWH, but I can tell you this is a performance killer. We have a table with 500.000 records and it will take up to 25 sec to retrieve the data in AS. Instead if we are using SUM, then it will popup the data in less than a second!!!
That is not good. We will likely be in the multi-million range in rows, so I am concerned about performance. While the requirements are not yet set in stone, I am hearing from the team that dimensional modeling will not be a requirement, but rather just simple relational modeling for report use. The projected users will not be analysis users, but rather just report consumers with some light query use. I still have concerns about how this data will work for query or report users with some transactional facts and some snapshot facts in the same package...
Thank you,
Nathan