Understanding Cube

View previous topic View next topic Go down

Understanding Cube

Post  hesh on Fri Sep 23, 2011 8:39 pm

Hi,

My OLTP is Loan domain

My DW is generalized, contains two facts one for transaction level granularity and other for accumulative snap shot(loan level)

My Dimensions are CUSTOMER, TIME and EMPLOYEE

FACT (Loan level granularity) contains CUST_KEY,EMP_KEY,TIME_KEY,LOAN_ID,LOAN_STATUS,LOAN_AMT....

CUSTOMER dimension is having attributes as NAME,AGE,INCOME_TYPE(Monthly, Weekly, etc..) and others..

EMPLOYEE dimension(Who created customer) is having attributes like NAME, EMP_TYPE(Permanent, contract..)

I am trying to understand CUBE , where does this fits in and what kind of CUBES to create? do we require them?

Initially I have to create one report to observe Write off trends with different attributes of CUSTOMER dimension like AGE :with multiple buckets 0-18, 19-25, 25-30...

like INCOME_TYPE : Monthly, Weekly,...
like INCOME_LEVEL: 0-1000, 1001-2000,2001-3000,..

do we require CUBES for this? do CUBES require to drill down/roll up ?

Please advice..

Thanks,
Hesh.



hesh

Posts : 12
Join date : 2011-08-16

View user profile

Back to top Go down

Re: Understanding Cube

Post  ngalemmo on Sat Sep 24, 2011 12:56 am

If you are talking about using the MS SQLServer stack, then yes, cubes are used to support the OLAP functionality within SSAS. Reporting does not require cubes.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Understanding Cube

Post  hang on Sat Sep 24, 2011 7:42 am

In dimensional thinking, age and income bands should be de-normalised into a single junk/mini dimension with other low cardinality attributes, if there is any.

Age should not be part of customer dimension as it's growing with time and you can always derive it based on the DOB and date of the fact, or make it a degenerate dimension in the fact table so that you don't have to work it out on the fly.

OLAP cube would be very powerful to analyse the measures from all dimensional perspectives, dice/slice and drill up/down along the hierarchies etc.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Understanding Cube

Post  hesh on Mon Sep 26, 2011 5:34 am

Yes I am using MS-SQL server stack.

Hang, can you please explain me in my scenario, how a cube would be used?(income band) I am struck badly here !


Thanks,
Hesh.

hesh

Posts : 12
Join date : 2011-08-16

View user profile

Back to top Go down

Re: Understanding Cube

Post  hang on Mon Sep 26, 2011 7:10 pm

Say you have a junk dimension called Demographic as follows:

DEMOGRAPHIC_DIM
DEMOGRAPHIC_KEY int (PK)
INCOME_BAND varchar(50)
AGE_GROUP varchar(50)

Your fact table would be like this:
CUST_KEY, EMP_KEY, TIME_KEY, DEMOGRAPHIC_KEY

You may prebuild the junk dimension by cross join income band and age group. In your ETL, you need to work out the DEMOGRAPHIC_KEY in the fact table based on the customer's age and income. You then build the SSAS cube treating the junk dimension like other dimensions with denormalised attributes in it.


Last edited by hang on Tue Oct 18, 2011 5:09 pm; edited 1 time in total (Reason for editing : typo correction)

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Understanding Cube

Post  hesh on Tue Oct 18, 2011 3:06 am

At first glance it was hard to understand junk dimension, at last enjoyed learning it, very useful.

Thanks,
Hesh.

hesh

Posts : 12
Join date : 2011-08-16

View user profile

Back to top Go down

Re: Understanding Cube

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