Post reply

Design Question

  • September 6, 2006 at 8:09 am

    #113503

    I'm drawing a blank on what the best manner to set something up might be and thus wanted to pose the question here.

    I'm looking to work my way into learning more on Analysis Services and the building of Cubes,etc.

    So what I wanted to do was to build a cube based on some calling patterns that the company I work for does.

    So what I've done is isolated out a month worth of calling records and I wanted to try to design the fact / dimension tables and am drawing a blank.

    What I've got currently:

    Phone (Customer ID basically)

    Calldatetime (Date / Time of Call) - This I'd like to break down into each day of seperate part (year, month,week,day,hour)

    CallResult - Answering Machine,etc

    Agent Spoken to

    Duration of Call

    As I read through the different schools of thought, I'm guessing that for my fact table I'd want to have the Phone,CallResult,Agent,Duration and for the dimensions I'd want to break out the CallDatetime field into the relevant parts and form the cube from this.  Am I correct on this, or is there something better to do from someone who's done this before?

    Thanks in advance.

  • September 6, 2006 at 2:55 pm

    #658895

    I'm guessing here that the Duration would be the "Fact" with all the other attibutes (other than a surrogate key) being keys to your dimensions.

    Each of your dimensions should have a surrogate key, so your Time dimension would look something like:

    CREATE TABLE TIME (TimeID int IDENTITY(1,1), CallTime datetime, Year numeric(4,0), MonthName varchar(15), MonthNo tinyint, WeekNo tinyint, DayName varchar(15), DayOfWeek tinyint, etc...

    I left off the NOT NULL constraints for the sake of brevity, and each column should have the appropriate constraints defined etc...

    HTH

     

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply