too many dimension problem

View previous topic View next topic Go down

too many dimension problem

Post  eunyoung hwang on Tue Jun 21, 2011 8:48 pm

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?

eunyoung hwang

Posts : 5
Join date : 2011-06-21

View user profile

Back to top Go down

Re: too many dimension problem

Post  warrent on Thu Jun 23, 2011 8:04 pm

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
avatar
warrent

Posts : 41
Join date : 2008-08-18

View user profile

Back to top Go down

Re: too many dimension problem

Post  BoxesAndLines on Fri Jun 24, 2011 11:01 am

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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: too many dimension problem

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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