Overall Financials DW

View previous topic View next topic Go down

Overall Financials DW

Post  TheNJDevil on Wed Nov 14, 2012 2:27 pm

For a generation, the financials have been reported from spreadsheets. That data is both manually gathered and semi-automated. The automated data comes from accounting/financial software. The other data comes from Other. I haven't gone thru it all yet, but I've found 24 different "system" (if you can call some of them that).

They want to have all of that go away and have that entire multiple spreadsheet system be automated/recreated/imported into a DW with analytics being the ultimate goal. I am overwhelmed with the complexity of these spreadsheets.

Where do I start? I am basically a one man show and accounting is possibly willing to hand over someone for a while to get this going. I've already said this was a multiyear project and I will tackle it piece by piece. But there are so many pieces. Any valuable suggestions/comments? Thanks.

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Overall Financials DW

Post  Mike Honey on Thu Nov 15, 2012 7:23 pm

Hi NJDevil,

I would just start with one fact and a couple of dimensions e.g. Transactions by Accounts and Date. Design fairly generic Fact and Dimension tables that cater to multiple "systems". Create template input files for loading data (e.g. Text or simple single-sheet no formulas Excel) and the ETL routines to load them. Then get accounting to populate those files for 1 pilot "system", keeping careful track of the time taken.

Generate some nice eye candy, cube etc, then stop and review.

You could then keep going with the generic input file approach - just multiply out the accounting effort x number of systems (maybe with a scale or complexity factor) x refresh frequency. Or you can use that figure to justify the effort required to automate the data load.

You should find out pretty quickly if you either have a gig for life, or they have unrealistic expectations.

Good luck!
Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Overall Financials DW

Post  BoxesAndLines on Thu Nov 15, 2012 10:29 pm

Hire a partner?
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Overall Financials DW

Post  TheNJDevil on Fri Nov 16, 2012 11:43 am

Another FTE was requested in the 2013 budget. Budgets get approved in December. An additional DW/BI person would make a world of difference. Either that, or some actual training.


TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Overall Financials DW

Post  ngalemmo on Fri Nov 16, 2012 5:04 pm

TheNJDevil wrote:For a generation, the financials have been reported from spreadsheets. That data is both manually gathered and semi-automated. The automated data comes from accounting/financial software. The other data comes from Other. I haven't gone thru it all yet, but I've found 24 different "system" (if you can call some of them that).

They want to have all of that go away and have that entire multiple spreadsheet system be automated/recreated/imported into a DW with analytics being the ultimate goal. I am overwhelmed with the complexity of these spreadsheets.

Where do I start? I am basically a one man show and accounting is possibly willing to hand over someone for a while to get this going. I've already said this was a multiyear project and I will tackle it piece by piece. But there are so many pieces. Any valuable suggestions/comments? Thanks.

First step, ignore the noise. Forget about the spreadsheets for now. They are not part of the solution.

Accounting systems pretty much work the same everywhere (unless this is a government agency). You have a general ledger and sub-ledgers (accounts receivable, accounts payable, fixed assets, etc...). Transactions in the sub-ledgers work their way to the general ledger through journals. Journals summarize and reduce the net effect of the transactions to a series of debits and credits to general ledger accounts. Each sub-ledger has its own transaction environment and data requirements. For example, accounts receivable has invoices, cash receipts, and customers. Accounts payable has invoices, payments, and vendors.

The sub-ledgers are fairly straight forward but have the most data and complexity. A general ledger model is dead simple, but reporting is very complex. The key to success here is finding the right reporting tool that understands charts of accounts and financial reporting.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Overall Financials DW

Post  LAndrews on Fri Nov 16, 2012 8:20 pm

I've had the pleasure/pain of doing this a couple times.

As ngalemmo suggested, I'd prioritize the spreadsheet data later in the program.

I've had success starting with the General ledger. Although it might be limited in the analytic power, it will give you a solid foundation to build from. The GL will have the core financial dimensions that will be common with all the other ledgers. What you'll find is that each sub-ledger will be easier, as it will be leveraging the dimensions created for the GL plus a couple new dimensions specific to the sub-ledger.

Your spreadsheets probably contain statistical and budget/plan information. The challenge there will be data quality when trying to conform to your dimensions.

Lastly tackle the summary/aggregate facts. Profit and Loss is a common one here. Although probably the most powerfull one from an analytic perspective, it can be extremely challenging if you don't have the base data captured accurately first.

Hope this helps.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Overall Financials DW

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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