|
Home > Archive > MS SQL Data Warehousing > July 2005 > employee dimension structure question...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
employee dimension structure question...
|
|
|
| Hi,
I'm looking for samples models for an HR model and specially the employee
dimension table.
What is better a slow changing dimension (with full history in the
dimension)?
or separate dimensions? I mean age, "current" organisation (or organization
unit), position etc... in separate tables.
Solution 1: maybe difficult to maintain
Solution 2: a lot of small tables
I'll create OLAP cubes for ad-hoc analysis & reports based on SQL
statements.
I know that I'll have dynamic distinct count formulas to setup in my cubes
(not the dcount aggregation one)
what are your recommendations?
any samples?
thanks.
Jerome.
| |
|
| Basically Employee dimension is a parent child dimension, which is
always a rapidly changing
You checking on the property "Changing" and see also BOL on the same.
They do fall under rapidly changing dimension rather than Slowly
changing dimension
Regards,
Prasanna
| |
|
| I think I'm not clear.
I'm looking for a complete sample with complete employee dimension based on
hundrers of attributes, geographical information, organization information
etc...
Its not a simple changing question, its a complete design question.
For the moment I focus on a hybrid solution, I mean 2 tables 1 for the
historical information (type 2 dimension) and 1 for the "current"
information.
"pras" <prasannat@gmail.com> wrote in message
news:1121405740.245158.172240@g43g2000cwa.googlegroups.com...
> Basically Employee dimension is a parent child dimension, which is
> always a rapidly changing
> You checking on the property "Changing" and see also BOL on the same.
> They do fall under rapidly changing dimension rather than Slowly
> changing dimension
>
> Regards,
> Prasanna
>
| |
| Rodrigo 2005-07-15, 1:23 pm |
| I recommend you Kimball's "DataWarehouse Toolkit".. Chapter 8.. Excelent
Human Resources Management model.
Regards
Rodrigo
<
"Jéjé" wrote:
> I think I'm not clear.
>
> I'm looking for a complete sample with complete employee dimension based on
> hundrers of attributes, geographical information, organization information
> etc...
>
> Its not a simple changing question, its a complete design question.
>
> For the moment I focus on a hybrid solution, I mean 2 tables 1 for the
> historical information (type 2 dimension) and 1 for the "current"
> information.
>
> "pras" <prasannat@gmail.com> wrote in message
> news:1121405740.245158.172240@g43g2000cwa.googlegroups.com...
>
>
>
| |
|
| yes, its a good starting point.
but there is no list of attributes and no sample about how to organize this
dimension for a hierarchical view (I mean OLAP dimension)
I know I have to work with my users, but I want to be prepared with samples
and recommendations (best practices)
"Rodrigo" <Rodrigo@discussions.microsoft.com> wrote in message
news:7261A1BB-E723-45FD-9F47- EF02DE63BE07@microso
ft.com...[color=darkred]
>I recommend you Kimball's "DataWarehouse Toolkit".. Chapter 8.. Excelent
> Human Resources Management model.
>
> Regards
> Rodrigo
>
> <
>
> "Jéjé" wrote:
>
| |
| Mumtaz Zaheer 2005-07-20, 7:23 am |
| Hi,
Beside having a complete example, if you are able to define history
duration, number of attributes for which you require history and ofcourse
number of total attributes in dimension then you can really design your
mart.
If you are able to set a particular condition for history then you can
decide on having copy of each required attributes as many time as history is
required or can have snowflake model and further normalize the employee
dimension. Then there are few techniques to improve the query performance,
like, by adding a flag attribute to show the latest image of record.
Frankly speaking, from your email i am under the impression that you would
be keeping almost all the information in history; this does not look real.
Regards,
..Mumtaz
"Jéjé" < willgart_A_@hotmail_
A_.com> wrote in message
news:O4JP5CXiFHA.1048@tk2msftngp13.phx.gbl...
> yes, its a good starting point.
> but there is no list of attributes and no sample about how to organize
this
> dimension for a hierarchical view (I mean OLAP dimension)
>
> I know I have to work with my users, but I want to be prepared with
samples
> and recommendations (best practices)
>
> "Rodrigo" <Rodrigo@discussions.microsoft.com> wrote in message
> news:7261A1BB-E723-45FD-9F47- EF02DE63BE07@microso
ft.com...
based[color=darkred]
>
>
| |
|
| Hi,
first point ... oohhh no ! I don't want to keep all information in history.
Only required information.
But I have to create a prototype for this HR data warehouse.
I have some idea about some facts, but I want to demonstrate the importance
of the employee dimension. Also I want to have your input about the overall
design of this DW.
So sample list of attributes around the employee is welcome, and sample
hierarchy to render this dimensions in a cube is welcome too.
My actual facts: Activities (who do what when), Contracts (head count,
duration...), Training (achivement, resulting promotions...), Salaries (no
comment), Expenses (no comments), absences
"Mumtaz Zaheer" < mumtazzaheer@hotmail
.com> wrote in message
news:%23Yi6UPRjFHA.1372@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Beside having a complete example, if you are able to define history
> duration, number of attributes for which you require history and ofcourse
> number of total attributes in dimension then you can really design your
> mart.
>
> If you are able to set a particular condition for history then you can
> decide on having copy of each required attributes as many time as history
> is
> required or can have snowflake model and further normalize the employee
> dimension. Then there are few techniques to improve the query performance,
> like, by adding a flag attribute to show the latest image of record.
>
> Frankly speaking, from your email i am under the impression that you would
> be keeping almost all the information in history; this does not look real.
>
> Regards,
> .Mumtaz
>
>
> "Jéjé" < willgart_A_@hotmail_
A_.com> wrote in message
> news:O4JP5CXiFHA.1048@tk2msftngp13.phx.gbl...
> this
> samples
> based
>
>
| |
| Mumtaz Zaheer 2005-07-21, 7:23 am |
| Hi,
http://www.dbmsmag.com/9802d05.html
If you have not gone thru this before, try applying to your scenario.
I guess, you would require more than just one Employee Dimension.
So far, I believe in keeping history in DW not Mart :)
Regards
"Jéjé" < willgart_A_@hotmail_
A_.com> wrote in message
news:u2fbzYSjFHA.2904@tk2msftngp13.phx.gbl...
> Hi,
>
> first point ... oohhh no ! I don't want to keep all information in
history.
> Only required information.
>
> But I have to create a prototype for this HR data warehouse.
> I have some idea about some facts, but I want to demonstrate the
importance
> of the employee dimension. Also I want to have your input about the
overall
> design of this DW.
> So sample list of attributes around the employee is welcome, and sample
> hierarchy to render this dimensions in a cube is welcome too.
>
> My actual facts: Activities (who do what when), Contracts (head count,
> duration...), Training (achivement, resulting promotions...), Salaries (no
> comment), Expenses (no comments), absences
>
>
> "Mumtaz Zaheer" < mumtazzaheer@hotmail
.com> wrote in message
> news:%23Yi6UPRjFHA.1372@TK2MSFTNGP10.phx.gbl...
ofcourse[color=darkr
ed]
history[color=darkre
d]
performance,[color=d
arkred]
would[color=darkred]
real.[color=darkred]
the[color=darkred]
>
>
|
|
|
|
|