2

I'm a newbie in datawarehousing, but I am in charge of this task. I need to create a schema (star) to store "Alarms"; so alarms comes from MySQL OLTP database and are a sort of log of access control system. Alarms have to be recorder because it needs to analyze the history of alarms happened in the past (for maintenace purpose).

My idea is

  • DimDate

    • fullDate
    • dayOfMonth
    • dayOfYear
    • dayOfWeek ...

  • DimTime

    • fulltime
    • hour
    • minute
    • second
    • ampm

  • DimAlarm

    • AlarmSourceID
    • AlarmDescriptionENG
    • AlarmDescriptionITA
    • AlarmDescriptionDEU
    • AlarmDescriptionFRA

  • FactAlarm

    • Foreign keys and nothing more?

But, now, I don't know what should be the measures on Fact table. Because an alarm on the source DB is just an ID, a description and a timestamp, but the alarms have to be queryed for all of those farameters.

How is this schema??

Thank you

zero51
  • 801
  • 2
  • 8
  • 20
  • 4
    Looks OK; see http://stackoverflow.com/questions/6788008/warehouse-store-and-count-non-fact-records/6789847#6789847 – Damir Sudarevic May 28 '13 at 16:25
  • 2
    Grab [The Data Warehouse Toolkit](http://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247) and read about **degenerate dimensions** (in your case, `ID` that will be stored in `FactAlarm`) and **factless fact tables** (you don't need any measures at all). – Marek Grzenkowicz May 28 '13 at 18:00
  • @MarekGrzenkowicz a bit expanded, your comment would good answer to the question :-). – N West May 28 '13 at 20:53
  • @DamirSudarevic The link is the answer.I also checked in Kimball (as Marek suggested): "Factless fact tables are the preferred method for recording events in a data warehouse where there is no natural numeric measurement associated with the event". – zero51 May 29 '13 at 08:11

0 Answers0