February 20, 2006 at 1:08 pm
I could really use some suggestions on how to model an OLAP cube for rollups of a financial system. I've been reading about dimension tables, fact tables, balanced - unbalanced - ragged hierarchies / dimensions, snowflake and star-schemas, etc. all morning and I'm still not clear as to how to approach this financial system rollup. Suggestions would be much appreciated.
The primary problem is that our financial rollups start off with about 6 scenerios, and each one can have as many as 20 levels and or categories of various accounts that are involved in the rollup. While navigating the hierarchy, it may take 2 levels to get to the actual account, or it might take 20. I think this sounds like a good candidate for an unbalanced dimension. I also think it should be a snowflake schema - although this causes me a problem because the description of each additional linked table may either be an account number, or simply a foreign key relationship with another rollup (or, more correctly, rolldown) level.
Additionally, many accounts will be in multiple rollups. Has anyone tackled something similar to this? If so, how did you approach it?
In our current Hyperion system this structure exists - but it doesn't seem to be represented in a table form. Instead, our structure was manually inputted into the "Outlines" portion of the Hyperion interface and the Outline is simply referenced when building the cube. I'm not sure how to translate these "Outlines" into SQL based dimension tables.
Thanks in advance for any suggestions.
February 20, 2006 at 4:17 pm
Wow that is a lot of stuff
We recently did an accounting cube (Snowflake). This is what we did
GL Entry was the fact
The Account structure was difficult but since it does not change much I made a procedure to walk through the table giving a parent child relationship. For instance
SELECT [GL_Navigation_Id],[GL_Navigation_Parent_Id],GL_Navigation_Account],GL_Navigation_Account_Type (our parent identifier),[GL_Navigation_Account_Description],GL_Navigation_Account_Category]FROM Tbl_GL_Navigation
Our accounts are numbers and are sorted so
Account No 1 is the parent of Account No 2, three could be on the same teir as 2 or underneath it. there are identifiers we found that could give us the parent child relationship(P/C).
After I got the P/C done I made a real nasty looking select query that broke out all the levels till I got to the posting account. That was the key field to join to Entry. Our deepest teir was 6 levels. So there were six fields in the table I export to for the Cube to be processed off of.
This may be kind of simple for your needs but it may get you going in the right direction. If you need further help it is better to ask me more specific questions
February 20, 2006 at 11:57 pm
You could look at using the parent child hierarchy type, this will/should handle your P/C relationships (but may not if you can't find a way to extract them from Hyp with a "ID|ParentID|otherfields.." type format).
You could also start from scratch, use a writeback dimension and build the heirarchies by hand (not an overly quick approach).
The non-uniqueness of members may cause you an issue (ie multiple instances of the same account). You could look at putting the scenarios into their own dim, then use calculated cells (for each slice identifed by a scenario) to dictate the rollup approach for that scenario. Not sure how maintainable that would be though.
Steve.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply