US and Worldwide: +1 (866) 660-7555
Results 1 to 4 of 4

Thread: Operate over range date - Guidance

  1. #1
    Join Date
    Feb 2013
    Posts
    10

    Default Operate over range date - Guidance

    Hi,

    I have customers that receive a given service for a current time. An exceprt of the fact table would be

    Code:
    customer_id INT,
    service_id INT,
    start_date INT,
    end_date INT,
    I have a Time dimension. I'd like to know how should I design my cube, so for a given date I could know whcih service and which customers are concerned.

    If I tight it to either start_date, or end_date it would give me the customer that have a start date being in the current selection, but not during.

    Thank you,

  2. #2

    Default

    YGuenane,

    Lets assume you have time dimension for each day like this (already populated for your needs):
    create table dim_time (day_date INT);

    Also lets assume your fact table is called fct_service.

    Then add view like this:
    create view v_service_fct as
    select dt.*, ser.*
    from dim_time dt
    join fct_service ser on ser.start_date <= dt.day_date and dt.day_date <= ser.end_date;


    Use such a view for cube instead of real fact table.

  3. #3
    Join Date
    Feb 2013
    Posts
    10

    Default

    Hi pied76,

    Thank you for your answer.

    Following the pattern you propose Ill have one line per day. This is something I wanted to avoid to lower the number of rows queried thus the overall performance of my cube. ie. For 200K customer that received a service for the past two year that would results in 73000K rows.

    That is something I wanted to avoid, if possible

  4. #4

    Default

    Quote Originally Posted by YGuenane View Post
    Hi pied76,

    Thank you for your answer.

    Following the pattern you propose Ill have one line per day. This is something I wanted to avoid to lower the number of rows queried thus the overall performance of my cube. ie. For 200K customer that received a service for the past two year that would results in 73000K rows.

    That is something I wanted to avoid, if possible
    I want to make it clear that I have mentioned the VIEW (and not a material view) -- so in DB you will NOT store all these records. Will not Mondrian server ask the underlying RDBMS only for those records needed for particular MDX query? The only other way for limiting the queried records number is to use aggregate tables.

    May be I just do not understand your question? What is the problem you are solving? Is it minimizing the needed RAM (then you are to use aggregates and/or redesign your dimensions)? Or is it minimizing the DB storage (then the "view" method is quite fine, but there are some more solutions)?

    Please, state your problem more directly and clearly.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •