Post reply

Assist me defining Fact Table

  • May 4, 2007 at 10:30 pm

    #153569

    Hopefully someone can assist me here. I have a small Business Intelligence application.

    OLTP Tables

    Patient, PatientVisit, PatientAddress, PatientDisease, Disease

    Patient has just Patid, FirstName, LastName and DOB

    PatientVisit has PatId, VisitDate, VisitFee

    PatientAddress has PatId, Address, City, State, Zip

    Question of Doctor

    What is my Yearly/Monthly/Quarterly Income from Patient Visit Fees ?

    Which Patient visit me the most?

    I want to know the no. of patients visit each year

     

    Now I have PatientVisit where I can get the PatVisitFee and get the date from PatVisitDate but what I can do with Month, Year, Quarter?

    Should I add more tables to our OLTP database? Should I add tables to Data Set view?

    Please give me any clue how can I accomplish this task.

     

    Thanks,

    Data Sheet

     

  • May 5, 2007 at 12:17 am

    #704498

    Hi

    If you have the date captured in your table, you can find out anything related the day, month or year.

    You have to just fire the query using some date related function e.g datediff and many more to get the result.  DateDiff function gives you the result on day, quarter or yearly basis.

    I think the above will solve your query.

    If not, pl. revert back without any hesitation.

    Sanjay

  • May 5, 2007 at 8:28 am

    #704505

    Hi data,

    as I understand you use an OLTP DB as data source for your cube. You imported the mentioned tables to your Data Source View. Now you want to create a fact table and dimension tables to analyze the data in OLAP.

    The measures you want to analyze are VisitFee and Number of Patients (VisitCount). The dimensions are the time dimension and the patient dimension.

    To analyze the data by the date attribute VisitDate you first have to generate a time dimension. The time dimension usually has a field containing the full date like this "2007-05-06 00:00:00" . You can do this by using a Named view, using time functions e.g. year(visitdate), month(visitdate) and so on.

    For the patient dimension you use the Patient table.

    For joining the time dimension table and the fact table PatientVisit you will have to define a relationship between the tables in the data source view. The same you do for PatientVisit and Patient with PatID.

    Then you define a new cube with a measure group containing VisitFee and VisitCountfrom the table PatientVisit, which contains the data you want to analyze. VisitCount is easily the number of lines in the fact table. If you have an ID for your Visits (1001, 1002...) you can count this. Else you could define a named calculation in your DataSourceView for this table which is simply 1.

    This solution will only work for small numbers of records. If you want to analyze bigger amounts of data you should extract the data to a warehouse, build a real time dimension table and use surrogate keys to join your tables. But this is some more work.

     

    Best regards,

    Stefan

     

     

    Kindest Regards,

    SK

  • May 5, 2007 at 1:23 pm

    #704517

    Hi Stefan,

    Very nicely explained. I will follow the suggestion and will try to see if it works.

    I would probably ask question again.

    Data Sheet

     

     

Viewing 4 posts - 1 through 3 (of 3 total)

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