Best Practices - Financial Reporting Database

Question

Best Practices - Financial Reporting Database

Joe Contreras-290946

SSCommitted

Points: 1903
More actions
July 9, 2007 at 7:37 am

#117699

Hi,
I'm in the process of design a "reporting" database that will allow the Finance group to be able to retrieve data at the "transactional level" based on parameters. The front-end is Cognos reporting. I would not think that this would be a relational database in the traditional sense nor is it a data warehouse.
My question(s) from a design point of view:
1. I'm looking for best practices in designing this database
2. Designing the database from an optimization (speed in access) best practices
This financial reporting database will accomodate a series of financial products. I want this database to act as a centralized (hub) database
Thanks,
Joe

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

not sure if any of this applies to you, but here goes.

For Reporting, because I don't want some report tying up my production database, I use log shipping to a different server/database. that database is read only, and is the db that the reports can be generated off of in my shop.

The database gets logs every hour, so data updates for reports lags around an hour from reality, which is fine for me; your data may need to be more or less up to date.

I'm not sure if your reports database is to be a mirror image of production, or the compilation of data based on production....you might want to add some more detail on the current data for better suggestions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Joe Contreras-290946 SSCommitted Points: 1903 More actions · Answer 2

I'm looking to design a financial reporting database which can the accomplish some of the following:

1. Be able to distinguish the different product services that are being billed and revenue is being recorded

2. Be able to capture lowest possible data transactions - this will enable a rollup for reporting

3. Data is to be updated on a daily basis

4. Merge of logical financial business segments

5. Ability to provide either YTD or ITD at both transactional and summary level base on finance business requirements

I was thinking on the basis of Meta data to be able to point to the necessary product services that finance would need information.

Thanks,

bnordberg SSCertifiable Points: 5480 More actions · Answer 3

Depending on how big your production system is (# users, transactions ...) I would also recommend you either take a copy of you DB and work "offline" or build an ETL process to glean data from the (I assume) relational structure to more hierarchal. Since you want very granular data, down to transaction, you need to work on identifying properties of each transaction, then build lookup tables (dimensional in data warehouse lingo). For example your lookups would be segment, product type, sales type, salesperson, customer, region, fiscal period (or maybe just calc that off transaction date), promotional event... Then your main transaction (fact table in DW lingo) would be date, product id, price, qty, balance and the keys to your lookup tables. Then index on all your keys to get your performance ... Why not then do Analysis Services cubes and reprocess daily?