I have to build a Fact table, and I have this situation.
My OLTP database, keeps always the last image of the
record.
Table
Incident(IncidentId, CreationTimme, StatusId, PriorityId,
rankId, Description, ..)
My Log Database, keeps a table per field and tracks all
the changes.
Incident_CreationTime(IncidentId, CreationTime, Timestamp)
Incident_StatusId(IncidentId, StatusId, Timestamp)
Incident_PriorityId(IncidentId, PriorityId, Timestamp)
Incident_RankId(IncidentId, RankId, Timestamp)
You got the Idea.
I have to be based on the Log database to create my Fact
Table of Incident. So what's the best way to create the
fact table in this case ? What about the dimension too ?
ThanksWhat are you trying to measure and how are you measuring it "by" ?
These are the inputs to your design, not the existing schema!
"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
news:0cd301c3bb40$ec0fb7e0$a401280a@.phx.gbl...
quote:|||I'm trying to measure the number of incidents per
> Hi There,
> I have to build a Fact table, and I have this situation.
> My OLTP database, keeps always the last image of the
> record.
> Table
> Incident(IncidentId, CreationTimme, StatusId, PriorityId,
> rankId, Description, ..)
> My Log Database, keeps a table per field and tracks all
> the changes.
> Incident_CreationTime(IncidentId, CreationTime, Timestamp)
> Incident_StatusId(IncidentId, StatusId, Timestamp)
> Incident_PriorityId(IncidentId, PriorityId, Timestamp)
> Incident_RankId(IncidentId, RankId, Timestamp)
> You got the Idea.
> I have to be based on the Log database to create my Fact
> Table of Incident. So what's the best way to create the
> fact table in this case ? What about the dimension too ?
> Thanks
>
Statusid, PriorityId, per RankId.
quote:
>--Original Message--
>What are you trying to measure and how are you measuring
it "by" ?
quote:
>These are the inputs to your design, not the existing
schema!
quote:|||You probably forgot about the date dimension. Then your fact table would
>
>"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
>news:0cd301c3bb40$ec0fb7e0$a401280a@.phx.gbl...
PriorityId,[QUOTE]
Timestamp)[QUOTE]
>
>.
>
look something like:
DateID
StatusID
PriorityID
RankID
IncidentCount
And you would have four dimension tables, Date, Status, Priority, and Rank
each having the descriptive elements of those dimensions, like Date,
StatusDescription, PriorityNumber, RankNumber, or whatever is meaninful for
you for those dimensions.
"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
news:067401c3bb4f$b6e6dee0$a001280a@.phx.gbl...[QUOTE]
> I'm trying to measure the number of incidents per
> Statusid, PriorityId, per RankId.
>
> it "by" ?
> schema!
> PriorityId,
> Timestamp)|||Hi Kevin,
Thanks for answering me back. I still have another
question for you.
It's not my first time that I develop a datawarehouse. My
only worry here, is the fact that these tables are related
together as many-to-many relationship in the OLTP system.
So If I do it like you suggested,
(IncidnetNumber, DateId, StatusId, PriorityId, RankId,
IncidentCount)
I could end up for each Incident something like this in my
FACT table:
1, 20031208, 1, 0, 0, 1
1, 20031208, 0, 1, 1, 1
1, 20031208, 4, 0, 0, 1
1, 20031208, 5, 0, 0, 1
Because the StatusId, PriorityId, RankId there is a
possibility that they change so many times during the life
cycle of an incident.
So what I thought to do, is to create a fact table for
each Fact_IncidentStatus, Fact_IncidentPriority,
Fact_IncidentRank each of these fact tables is related to
its own dimension. and then all those fact tables are
related to FACT_Incident which contains only unique
Incident Numbers.
Do you think this is a good design?
Thanks for your time
quote:
>--Original Message--
>You probably forgot about the date dimension. Then your
fact table would
quote:
>look something like:
>DateID
>StatusID
>PriorityID
>RankID
>IncidentCount
>And you would have four dimension tables, Date, Status,
Priority, and Rank
quote:
>each having the descriptive elements of those dimensions,
like Date,
quote:
>StatusDescription, PriorityNumber, RankNumber, or
whatever is meaninful for
quote:|||Elie,
>you for those dimensions.
>
>"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
>news:067401c3bb4f$b6e6dee0$a001280a@.phx.gbl...
measuring[QUOTE]
message[QUOTE]
situation.[QUOTE]
all[QUOTE]
Timestamp)[QUOTE]
Fact[QUOTE]
the[QUOTE]
too ?[QUOTE]
>
>.
>
It seems to me that you need to have a separate dimension
for the incident numbers.
You need:
DIM_Date
DIM_Status
DIM_Priority
DIM_Rank
DIM_Incident
And:
FACT_IncidentCount
With a surrogate key from each of your dimensions and a
count field that would contain just the number "1". As
the cube aggregated on each dimension, it would add the
count field up.
Hope this helps,
Asa Monsey
quote:
>--Original Message--
>Hi Kevin,
>Thanks for answering me back. I still have another
>question for you.
>It's not my first time that I develop a datawarehouse. My
>only worry here, is the fact that these tables are
related
quote:
>together as many-to-many relationship in the OLTP system.
>So If I do it like you suggested,
>(IncidnetNumber, DateId, StatusId, PriorityId, RankId,
>IncidentCount)
>I could end up for each Incident something like this in
my
quote:
>FACT table:
>1, 20031208, 1, 0, 0, 1
>1, 20031208, 0, 1, 1, 1
>1, 20031208, 4, 0, 0, 1
>1, 20031208, 5, 0, 0, 1
>Because the StatusId, PriorityId, RankId there is a
>possibility that they change so many times during the
life
quote:
>cycle of an incident.
>So what I thought to do, is to create a fact table for
>each Fact_IncidentStatus, Fact_IncidentPriority,
>Fact_IncidentRank each of these fact tables is related to
>its own dimension. and then all those fact tables are
>related to FACT_Incident which contains only unique
>Incident Numbers.
>Do you think this is a good design?
>Thanks for your time
>
>
>fact table would
>Priority, and Rank
dimensions,[QUOTE]
>like Date,
>whatever is meaninful for
>measuring
>message
>situation.
>all
>Timestamp)
>Fact
>the
>too ?
>.
>
No comments:
Post a Comment