too many dimension problem

Hi,

before starting, sorry for my poor english.
i hope you can understand my question.

i have to make a start model for our monitoring service.

monthly we make the customer summary data. i have some log tables with 180 columns like this
-------------------------------------------------
customer_number, monthcode, total number of account, total balance, longest days of deliquency,...
-------------------------------------------------
10593, 201106, 3, 5000,...
10523, 201106, 1, 3000,...
------------------------------------------------

the problem is "180 columns " have to be dimensions. measue is the count of customer.
i need to filter the customer by account repayment type, product type, branch code,...(these are not customer level, account level)
- same report for the A branch (only count the customer managed by A branch)
- same report for the A branch, C product type (the customer managed by A branch and have C product type account)

1. total customer
-------------------------------------------------
total number of account count(distinct customer)
-------------------------------------------------
0~1 1000
5~10 2500
-------------------------------------------------

2. A branch
-------------------------------------------------
total number of account count(distinct customer)
-------------------------------------------------
0~1 100
5~10 250
-------------------------------------------------
3. A branch, C account type
...
i have 40 million customers. and every report needs 12 months data.
I have to finish the model and make 700 report in 3 weeks (T.T)

could you help me?

Hi,

It looks like you are working from what we would call a Monthly Customer Snapshot fact table. If you have a copy of the Data Warehouse Toolkit, 2nd Ed. (Wiley, 2002), you can see the same model at the Account level on page 203. Ralph described the snapshot fact table in an Intelligent Enterprise article in 1998. A few things for you to think about:

- You actually have several measures, including Total Number of Accounts, Total Balance, etc.

- You will have fewer dimensions than you think because many attributes you need to report on are probably related to a set of core dimensions, such as Account, Customer, Product, Branch, Account Status.

- You will have a tough time getting the model designed and properly populated in three weeks. I'm not sure what "700 reports" means, but if you work 12 hour days, you've got about 20 minutes per report if you start now. I think you need to reset expectations.

Good luck,
--Warren

warrent wrote:...
- You will have a tough time getting the model designed and properly populated in three weeks. I'm not sure what "700 reports" means, but if you work 12 hour days, you've got about 20 minutes per report if you start now. I think you need to reset expectations.

Good luck,
--Warren

I think the help needed can be found on dice.com if you know what I mean. The whole idea of building these models is to enable self service.