Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Understanding Cube

3 posters

Go down

Understanding Cube  Empty Understanding Cube

Post  hesh 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

Back to top Go down

Understanding Cube  Empty Re: Understanding Cube

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Understanding Cube  Empty Re: Understanding Cube

Post  hang 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

Back to top Go down

Understanding Cube  Empty Re: Understanding Cube

Post  hesh 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

Back to top Go down

Understanding Cube  Empty Re: Understanding Cube

Post  hang 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

Back to top Go down

Understanding Cube  Empty Re: Understanding Cube

Post  hesh 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

Back to top Go down

Understanding Cube  Empty Re: Understanding Cube

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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