Understanding Cube
3 posters
Page 1 of 1
Understanding Cube
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.
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
Re: Understanding Cube
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.
Re: Understanding Cube
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.
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
Re: Understanding Cube
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.
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
Re: Understanding Cube
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.
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
Re: Understanding Cube
At first glance it was hard to understand junk dimension, at last enjoyed learning it, very useful.
Thanks,
Hesh.
Thanks,
Hesh.
hesh- Posts : 12
Join date : 2011-08-16
Similar topics
» SSAS Cube - zero downtime even during cube processing
» New to BI and need a little help understanding the basic concept
» A complete Understanding on the Data Modeling
» Understanding Fact and Dimension table
» Understanding Materialized Views as aggregate tables
» New to BI and need a little help understanding the basic concept
» A complete Understanding on the Data Modeling
» Understanding Fact and Dimension table
» Understanding Materialized Views as aggregate tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum