Customer remodeling

View previous topic View next topic Go down

Customer remodeling

Post  umutiscan on Sun Dec 11, 2011 12:52 pm

Hi,

I'm trying to remodel customer and subscriber subject area of a telco company.

We have "subscriber base snapshot" and "subscriber base history" table now. These tables have same columns.
Snapshot contains the latest day's data. History table which is range partitioned contains history of the snapshot.
Unfortunately We don't have customer and subscriber dimensions. Subscriber base history is used when someone needs the history of
customer or subscriber.

They are huge monster tables with 200 columns including :

- Subscriber info like subscription_id, subscription_start_date etc.
- Customer info like customer_name, birth_date etc.
- Some daily usage metrics of subscriber like average_call_duration, latest_call_start_date
that are calculated every day.
- Some usage metrics that never change like first_call_date, first_invoice_date etc.
But they are checked everyday if they are really unchanged or not because of late data.
- Segment values that is calculated once a month.

I planed to create fact and dimension tables below to hold all the infomation :

- DIM_SUBSCRIBER : subscription_id, subscription_start_date etc.
- DIM_CUSTOMER : customer_name, birth date etc.
- FACT_SUBSCRIBER_DAILY : Daily metrics (average_call_duration, latest_call_start_date etc.)
- FACT_SUBSCRIBER_PRIME : Unchanging metrics(first_call_date, first_invoice_date etc.)
- FACT_SUBSCRIBER_MONTHLY : Segments

I will have 3 star schemas and Fact tables will be connected with 3 conformed dimensions (DIM_SUBSCRIBER, DIM_CUSTOMER, DIM_TIME)

We are using Business Objects as reporting tool, and reporting team says it's impossible to join these 3 fact tables in a single report.
I don't have too much info about BO, but isn't it strange?
Business metrics are loaded into fact tables and facts can be used together along conformed dimensions.
This is one of the fundamentals of the dimensional modeling. Do you have any experience like this? Do I have to create only one fact table for one report?

And another problem, reporting team thinks that the values that I modeled in fact tables are dimensions of a subscriber and they think that there is no fact table in this scenario.
They want to see these values in some dimensions like mini-dimension because of their performance and reporting facility.
What do you think about this idea?

Thank you.

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: Customer remodeling

Post  ngalemmo on Mon Dec 12, 2011 11:02 pm

We are using Business Objects as reporting tool, and reporting team says it's impossible to join these 3 fact tables in a single report.
I don't have too much info about BO, but isn't it strange?

Yes it is strange. In BO, you build a universe that includes each star that is defined in its own Context. All share the same physical dimension tables. When a user selects measures from each fact and they select attributes from common dimensions, BO will create three separate aggregate queries and combine the results. However, if a user selects an attribute that is not in common, they will get an error. You could, concievably create a universe that only includes the attributes in common among the three facts to avoid getting errors.
avatar
ngalemmo

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

View user profile http://aginity.com

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