Looking for the perfect General Ledger cube design...

[fleaster]

In the words of U2: "I still haven't found what I'm looking for..." pretty much sums up my sentiment at this point:)

...given the opportunity to redesign our current general ledger analysis/reporting/forecasting cube (6 dimensions) which has been in operation for the last 6 yrs, i naturally jumped at the chance to put in place all the things we'd been lacking (or had been doing workarounds for).

...hence I came up with the below 16 dimension monster (it was originally 18 dimensions in the first cut):

gldimstruc.JPG (71.97 KiB) Viewed 14621 times

Some questions are:

1/ when putting in place the minimum required dimensions, is it good/bad practice to have spare dimensions?
...theoretically I could cut it down from 16 to 13 dimensions, but then would this really make a significant difference to performance?

2/ in terms of dimension order, I have tried to put Version first and Period/Measures last - some people say putting Period first is better practice...?

3/ ...in terms of performance, what other things should I be looking at in the design?

...am curious to know people's experience with building General Ledger cubes (e.g. smallest vs biggest cubes built?), so any feedback/critique would be appreciated

thanks!

Matt

[Harvey]

1 / In theory, spare dimensions with just 1 element, or even many elements with just 1 utilized, cost you negligent amounts of memory, since TM1 does not store zeros.

However, if you have a lot of rules in your cube, it might make a difference. This would be pretty hard to measure or predict, but logically, parsing rule commands that are longer would have to be (marginally?) slower.

You also need to make sure you either limit your spare dimensions to 1 element, or be very careful that your feeders do not over-feed those dimensions, or it'll have an exponential effect of performance. If you think it through well, you shouldn't have any trouble.

2 / Best practice is generally to use the "double pyramid" technique (divide into sparse and dense, then order from smallest sparse to largest sparse and then from smallest dense to largest dense, with your measure dimension last) suggested in training and the documentation.

However, I usually try consider readability and browsability in my dimension order, which means keeping certain commonly shared dimensions in the same order for all cubes. You could call this personal preference, but I believe it has very positive benefits in the usability of the cubes.

3 / In my experience, the most impactful choice is rules-driven vs static-value cubes. Assuming you are implementing base modules such as revenue, opex, capex, hr, etc, to feed your GL cube, you'll have to choose whether to use rules to copy the values up or write TI processes to do the job.

Many consultants I know use TI exclusively, but it does mean forcing the user to press a button or wait for a scheduled chore to run.

It works pretty well in Contributor if you can trigger the copy process at the right time (this is supposed to be improved with "Applications" in TM1 10), but if you have an Excel front end or your users prefer input via cube views, a more dynamic approach is often desirable.

Personally, I go with the notion of using rules unless performance is an issue.


A colleague and I have developed what we think is the "perfect" GL, but, as with everything TM1, every situation is different. In our case, we've been trying to build one that we can use as a generic template, so our needs are based around flexibility.

PS: I read criticism in another post of your 19-dimension GL, and I agree it seems excessive, but I'm assuming you have good reason for it.

[fleaster]

thanks for the response - if i can ask a dumb question to clarify the terminology:
-small / large dim refers to # of elements in the dim?
-sparse / dense dim refers to how many of those elements are actually populated with values?

...if this is the case, if a dimension has 1 element, does this mean it is small but 100% dense as well?

Re: your GL cube template - anything u'd care to share on this? eg # of dimensions, commonalities with the one I proposed.. etc?

thanks for your time!

[mattgoff]

I think it mainly comes down to architecture preference. It looks like you're including more than account balances in your GL cube, and that (plus spares, never understood the need for those) is really inflating your cube dimensionality. In our case, we have a trim GL cube that parallels Oracle GL string. Subledger/JEs are only accessible via relational drill, and other detailed info (e.g. revenue by order line) are stored in a separate cubes. Our structure is:

• Scenario/Version
• Company (legal entity identifier)
• Segment (COS, Sales, Marketing, G&A, Facilities)
• Product (brand or cost center)
• Account (B/S and I/S in one cube)
• Project (segments account where necessary)
• Period (months)
• Year
• Measure (1 reporting currency @ spot, 1 reporting currency @ budget, local currencies, string comment)

What's the purpose of including spare dimensions? Maybe these are required due to the expanded nature of your cube. In my case, our GL string will NEVER change, so no need to build in room to grow.

Matt

[lotsaram]

2c

- Ditto Matt, I would also say don't build in spare dimensions. There's no point having all data against "NA" or "Other". What do you name such dimensions? It just makes the cube more clumsy and more difficult and less intuitive for users to navigate
- split period into separate year and month dimensions, for a GL it's generally much better for reporting and planning. On the (rare) occasion that the company's tax reporting year changes there will be so much else to straighten out the effort of a minor redevelopment of a TM1 GL cube will be insignificant
- use "sub-ledger" cubes. No point having SKU for example when is it only relevant to a few sales, COGS & possibly M&A accounts -put that stuff in an appropriately dimensioned sales cube and drill to it from the main GL. Similarly things like vendor, customer, asset, etc don't belong in the main GL. Have appropriate sub cubes for AP, AR, Asset register, etc.
- don't be tempted to store transactional data, keep it in the RDBMS where it belongs and use drill-through to access it on the fly
- make EXTENSIVE use of drill-through, both to sub-ledger cubes and to the source GL in the RDBMS (unless its SAP, but great if its Orcale or Dymamics, JDE or SAP B1)
- Ditto Lazarus, ensure consistent ordering of dimensions for users (you can always optimize the cube in the background, but mush easier for users to always see a consistent order of version, year, month, etc)

[Harvey]

• Scenario/Version
• Company (legal entity identifier)
• Segment (COS, Sales, Marketing, G&A, Facilities)
• Product (brand or cost center)
• Account (B/S and I/S in one cube)
• Project (segments account where necessary)
• Period (months)
• Year
• Measure (1 reporting currency @ spot, 1 reporting currency @ budget, local currencies, string comment)

Matt

Our "perfect" GL looks very similar to the above. We also allow for Contributor, so have an approval dimension, and we keep track of which sub-ledger contributed the data with an application dimension (possibly covered by the "Segment" and "Project" dimensions above). This is mainly so we can easily replace sub-ledger cubes without redesigning the core GL.

We also have a separate currency dimension so we can expand the currency functionality without adding measures.

We do not have any "spare" dimensions, as we use sub-ledger cubes for all the contributing data. Each sub-ledger can be customized specifically for the area that works with the data.

I agree with the others about splitting the Year/Period dimensions. If you are going to support projections, it's often a good idea to add a "projected year" dimension, which allows you multiple future years per version. So you'd have your budget year, (2011, 2012, 2013, etc), your month (Jan, Feb, Mar, Qtr1, etc) and your projected year (Y1, Y2, Y3, Y4, Y5, etc). Actual would only even have Y1 populated, and so would budget, but you might have forecast version that project 5 years into the future.

We also store movements and use consolidations in the period dimensions to create balances. This has been by far the easiest approach I've found over time and causes the least headaches.

[fleaster]

Thanks all for the feedback, will just clarify a few things:

-the cube in question here is more a combination of monthend GL + Reporting framework e.g. the user will rolldown the top level Reporting Line (e.g. Expenses) down to the account (e.g. Printing & Stationery) that it consists of, then drill down to analyze DR/CR accounting transactions in an underlying SQL database

-we conduct regular Rolling Forecasts which often cross year boundaries (e.g. Feb 2012 to Jan 2013); hence have found it easier to keep 1 time dimension. additionally we conduct numerous other forecasts during the year (e.g. 4+8, 6+6, 9+3, 3 year strategic plan, full year budget etc) with varying requirements & levels of granularity

-reporting structures and methodologies change on a very frequent basis e.g. in my experience reporting structures will change monthly, and major methodology changes (possibly changes in the relationship between dimensions) could occur 2-3 times a year. Hence it is my goal to keep things as flexible as possible.

...of particular concern is if we need to change the main cube structure to cater for a new requirement, requiring 100s of users to rebuild their Excel slices etc - so to accomodate this, I have included spare dimensions "just in case" (e.g. one of the spares will be used for "Project/Initiative" which have done offline before, but is not always compulsory)

-in addition to the above, we have multiple projects occurring over the next couple of years that will highly likely involve a change in the underlying general ledger system/string

• Scenario/Version
• Company (legal entity identifier)
• Segment (COS, Sales, Marketing, G&A, Facilities)
• Product (brand or cost center)
• Account (B/S and I/S in one cube)
• Project (segments account where necessary)
• Period (months)
• Year
• Measure (1 reporting currency @ spot, 1 reporting currency @ budget, local currencies, string comment)

thanks Matt for sharing your setup - i do like this look as it seems fairly lean

...what I'd like to understand more are the prior comments on "linking to subledger cubes" - what are some examples of this? eg are you referring to drilling back from the accounting GL to more detailed business data?

[mattgoff]

-the cube in question here is more a combination of monthend GL + Reporting framework e.g. the user will rolldown the top level Reporting Line (e.g. Expenses) down to the account (e.g. Printing & Stationery) that it consists of, then drill down to analyze DR/CR accounting transactions in an underlying SQL database

...what I'd like to understand more are the prior comments on "linking to subledger cubes" - what are some examples of this? eg are you referring to drilling back from the accounting GL to more detailed business data?

Yes, that's where I believe most of us would differ from your cube design. It sounds to me like you're approaching your design from a relational/transactional database perspective. OLAP databases are not good for storing transactional data.

We use two methods depending on the source of the of data:

• If we forecast it at a lower level (e.g. Employees), I have separate cubes set up which roll into the GL cube, generally via rules (but sometimes TI). Drill provides convenient access for user browsing in the other direction.
• If we don't forecast it, I generally do a relational drill, meaning that TM1 performs a live SQL query to Oracle when the user drills and just returns the data, nothing stored. In a few exceptional cases (e.g. with my example of revenue order lines), our users REALLY wanted to be able to use TM1 for reporting, so I will periodically suck the data into a standalone cube. But, it's not linked to the GL cube since we don't forecast at such a detailed level.

-we conduct regular Rolling Forecasts which often cross year boundaries (e.g. Feb 2012 to Jan 2013); hence have found it easier to keep 1 time dimension. additionally we conduct numerous other forecasts during the year (e.g. 4+8, 6+6, 9+3, 3 year strategic plan, full year budget etc) with varying requirements & levels of granularity

I'm not sure why one time dimension makes this easier. We do rolling forecasts too, and we handle these various date requirements in our reports, not TM1 (with the exception that we retain "snapshots" of the various forecasts using our scenario/version dimension in TM1). I have no beef with using a single-dimenstion for time (there have been many discussions on this topic and it usually comes down to personal preference), but I'm not sure your justification makes sense.

Do you just have a whole mess of hierarchies in your time dimension, one for each semi-random forecast period? Do you really need a TM1-calculated consolidation for each of these forecasts?

-reporting structures and methodologies change on a very frequent basis e.g. in my experience reporting structures will change monthly, and major methodology changes (possibly changes in the relationship between dimensions) could occur 2-3 times a year. Hence it is my goal to keep things as flexible as possible.

-in addition to the above, we have multiple projects occurring over the next couple of years that will highly likely involve a change in the underlying general ledger system/string

I find this so hard to believe that I must be misunderstanding you. Surely your accounting team isn't scrapping the GL this often. Our GL is a decade old, tacked together with dozens of acquisitions (the dotcom boom days were fun), and we keep putting off a GL redesign because it's so painful that we'd rather live with the kludged up one we have now. Just recently we took a small operating unit passed down from our parent company and matching up the GLs, mapping accounts, and translating and loading history took a few weeks to get right....

If you have all of these extra dimensions, what do you do with history when you add elements? Do you just have one "other" element that you'd populate now? What happens when you add elements-- do you go back and recategorize everything in "other"?

...of particular concern is if we need to change the main cube structure to cater for a new requirement, requiring 100s of users to rebuild their Excel slices etc - so to accomodate this, I have included spare dimensions "just in case" (e.g. one of the spares will be used for "Project/Initiative" which have done offline before, but is not always compulsory)

If I'm understanding you correctly, this is the purpose of our Project dimension. Nearly everything goes in to project 000 (the "default" project), but we use it to randomly segment balances where required. e.g. We might charge all telecom expenses to the Telecom dept (instead of allocating) but we keep track of the detail with a project code for each site. Or, for revenue, we might assign a project code to track a major event (e.g. E3) which spans multiple depts and accounts. This does make this dimension somewhat sparse, but I'd rather have one sparse dimension than three "spares".

Back to the high level, though, after taking all of your comments into consideration I still think you are really approaching your design from a relational database perspective. Relational DBs love to have everything plus the kitchen sink in one massive table (normalization aside) since it avoids joins. In OLAP, you're going to end up with a massively sparse db which is going to lead to performance issues, administrative issues (echoing Lazarus: writing concise rules, and esp feeders, is going to be a nightmare), and usability issues (all these extra dims are going to come into play every time you write a DBRW or navigate in the cube viewer).

Matt

[JDLove]

Interesting .... the "Perfect GL".

As Lazarus mentioned we have spent some time building a generic GL and we feel the result is a very flexible and articulate model.

I think the posts have given a pretty solid grounding on core aspects but its no easy road and performance is very key once you start adding all those dimensions.

In regards to updating the GL we played with both Rules and TI, both have pros and cons but eventually we ran with TI due to what we believed offered us more control, flexibility and of course much better performance.

So we use TI to update the GL from the base application/module and for internal postings related to the cash-flow.

Some additional aspects you may want to consider are;
- the ability to calculate cash-flow from PL accounts that may not have the cash-flow modelled in the base application/module.
- Balance Sheet start balances and cash-flow movements (start balances could be different for each forecast)

We include the cash-flow in the Balance Sheet and we have a Balance Sheet per Base application/module so everything is easily traced back.

In terms of the dimensionality we try and limit the dimensions in the GL and provide Sub cubes for detailed analysis and as mentioned for Actuals you can drill into the relational DB.
We have had rather extreme requests to add many dimensions to the GL but we feel for the reasons already mentioned this is best avoided.
Its nice to have a clean GL that performs well and is easy to report from/update.

Hope it goes well for you !

[fleaster]

Thanks again for all the comments, has been most informative...

Surely your accounting team isn't scrapping the GL this often. Our GL is a decade old, tacked together with dozens of acquisitions (the dotcom boom days were fun), and we keep putting off a GL redesign because it's so painful that we'd rather live with the kludged up one we have now.

Actually it really is that bad ...won't regail you with all the politics behind the Global project team, but what we are looking at is (i) an enhancement to our existing GL in the next year and (ii) an almost certain overhaul of the GL platform in the 1-2 years after that

In terms of the dimensionality we try and limit the dimensions in the GL and provide Sub cubes for detailed analysis and as mentioned for Actuals you can drill into the relational DB.

I think our current setup is similar to this - we have a 6 dimension GL cube w/ relational drill to transactions DB + various other associated cubes for Local Regulatory reporting, Forecast allocations etc

If I'm understanding you correctly, this is the purpose of our Project dimension. Nearly everything goes in to project 000 (the "default" project), but we use it to randomly segment balances where required... This does make this dimension somewhat sparse, but I'd rather have one sparse dimension than three "spares".

To clarify - the three "spares" actually represent formal reporting requirements from global head office:
-Customer Segment / Classification
-Channel (ie how the customer was acquired)
-Initiatives/Projects

...the issue is that head office underutilizes these now - probably only customer segment is the only one formally reported on, but the other two are listed as "future initiatives". My dilemma has been whether to:

(i) create a parallel cube with the extra dimensions

(ii) just add 1 dimension and use 3 x main hierarchies under each - the assumption with this is we will usually be required to report on each dimension individually (e.g. Customer OR Channel OR Initiatives), but very rarely all together (e.g. Customer vs Channel vs Initiatives). If there is a requirement to compare all of them, then we can just concatenate elements or use some other workaround

(iii) add all 3 dimensions - and accept that 2 of them MAY remain underutilized in the near future

Do you just have a whole mess of hierarchies in your time dimension, one for each semi-random forecast period? Do you really need a TM1-calculated consolidation for each of these forecasts?

Not as such - there are the usual rollups like YTD, QTD, Half-year etc... Forecast periods are usually designated by saved subsets. These are used by TI processes for generating forecast data + limiting user input


...A couple of additional questions in my mind:

1/ How would you treat End of Period vs Average Balances? would you:
(i) combine it in the Version dimensions (e.g. Actuals EOP + Actuals AVG)
(ii) embed it in a separate Measures dumension (e.g. just 2 elements EOP, AVG)
(iii) combine it in a Currencies?!? dimension?!? (e.g. LCY EOP, LCY AVG, USD EOP, USD AVG) etc

2/ mattgoff had 9 dimensions in his GL setup - in your experiences what is a "good number" of dimensions to have for your core GL ie how many dimensions is "too many"? do you find coding getting cumbersome / users start getting confused after 12?? dimensions? etc I realize this is a subjective question but am curious all the same

[fleaster]

bump!

[Anuradha]

thanks for the response - if i can ask a dumb question to clarify the terminology:
-small / large dim refers to # of elements in the dim?
-sparse / dense dim refers to how many of those elements are actually populated with values?

...if this is the case, if a dimension has 1 element, does this mean it is small but 100% dense as well?

Re: your GL cube template - anything u'd care to share on this? eg # of dimensions, commonalities with the one I proposed.. etc?

thanks for your time!

Have you got answer for this?

[fleaster]

Hi Anu,
no I hadn't received a response... since then I think I've accepted there probably isn't any overall "perfect" GL design... but the GL design should be customized to suit the company that is using it...

M.