Data Marts - Transactional Data - Account Balances

[nchapman]

Hi, I am an Oracle Analyst Programmer who is now looking into the use of Data Marts.

The system I work on is a Banking Data Warehouse, and currently has a large number of derivations which are either computed daily or monthly, and then stored in a number of database tables. These derivations are then analysed by the business.

I am looking at producing a Data Mart(s) which I envisage will hold a transactional fact table, and such dimensions as Customer, Account, Time, Product, Location. I want the business users to be able to generate the derivations themselves whenever they like, by using a data mart.

I would like to know if anyone has had a similar task to do. If anyone can offer me any advice on design etc.

Also, some of the derivations we currently have are average balance month to date, aggregate balance month to date, minimum balance for the cycle to date. How would I design the data mart so that business users, could ask these questions ? Would I have to store the balance for each account, for each day in the fact table ???

 

[blom0344]

Hello nchapman,

I do not work for a bank, but the datamart I recently set up for our bike-sales department does look a bit like your proposed set-up for a datamart. I'm using Informatica's Powermart as an ETL tool, DB2 7.1 as database and Access / Business objects as reporting tool.

The datamart I build is supposed to be used for monthly reporting on such things as revenue, stock , profitability. It is not build to answer questions at specific transactional level. It will exactly tell which customer bought which products in which month for what amount, but it does not offer the exact order-information.

First of all this means that my facttable is a aggregation from the actual sales and outstanding orders details. Since users only want new information each month, every new day of the month a new dataset is added to the facttable. Dimensions of this table are: Year,Month,Sales-rep,Customer,Product. This aggregated facttable is surrounded by dimension tables with details on sales-reps, customers and product.

The more time I spent with datamart design, the more I find that de-normalizing is the key to fast and user-friendly reporting. In fact I use my ETL-tool to generate aggregates from aggregates to give users very fast queries on smaller tables with pre-aggregated measures. With a proper tool like Business Objects monthly reporting is a breeze.

T. Blom
Information analist
Shimano Europe

 

[Sridharan]

nchapman,

instead of storing the balance for each account, for each day in the fact table to do the derivations, one can use OLAP functions like (RunningSum / MovingSum / MovingAvg etc ) at the database side for your needs if you are going to write free hand sql or how is the end user going to ask these questions and view the answers? do they have access to some reporting tools like what blom said....

sridharan