• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1155
  • Last Modified:

OLAP Cube / Employee Dimension / Hierarchy

Hi

I have 'employee' table which has EmployeeID and SupervisorID (self-join) and I join the table to fact table using Username field as the fact table doesn't have EmployeeID.

I would like to build Employee hierarchy and I have two questions:
1) What is the standard way of doing that? I presume this is rather very common.
2) How can build hierarchy so it appear on multiple levels.... like Tree view?

Many thanks in advance
Emil
0
itcouple
Asked:
itcouple
  • 11
  • 6
  • 2
2 Solutions
 
srnarCommented:
Analysis services have a special type of a dimension called parent-child. I have no personal experience except of study examples but what I noticed is that parent child has many caveats.

But one problem I see in your design is the different key on the fact table and on the dimension table. You may not be able to construct the dimension that will be usable for that fact table.
0
 
itcoupleAuthor Commented:
Hi

Thanks for that. I will try to find something about parent-child dimension.

Regarding the issue with different key that is not a major issue as the database I use is also created by myself so I can add ID from Employee table during ETL process.

Many thanks
Emil
0
 
itcoupleAuthor Commented:
Hi

I've tried to find parent-child dimension in 2008 but cannot find a tutorial which would explain this. It seems it was much easier to do it in 2005.

Help!
Emil
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
srnarCommented:
Here is similar thread - there were some changes in dimension wizard it seems:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_25330595.html

I belive you can find also something in Adventure Works samples.
0
 
itcoupleAuthor Commented:
Thanks.... That was one of my google findings but the problem I have is that I cannot find this property anywhere :) I will keep trying.
0
 
PedroCGDCommented:
I can do an example for you.
Just attach here the CREATE TABLE SQL Statments of your fact and Dim tables and some few rows for each one.
Regards,
Pedro
0
 
itcoupleAuthor Commented:
Hi Pedro

Many thanks for willing to create the example. I appreciate your time.

Below is the SQL Script (there are very few records.... if you need more let me know and I will prepare more a bit later)


CREATE TABLE [dbo].[Job](
	[Job] [int] NOT NULL,
	[User] [nvarchar](32) NOT NULL,
	[PrintedPages] [numeric](20, 0) NOT NULL,
) ON [PRIMARY]

INSERT INTO Job VALUES(1,'emil',100)
INSERT INTO Job VALUES(2,'katie',700)
INSERT INTO Job VALUES(3,'emil',300)
INSERT INTO Job VALUES(4,'TopDog',500)
INSERT INTO Job VALUES(5,'katie',200)
INSERT INTO Job VALUES(6,'emil',600)

CREATE TABLE [dbo].[Employee](
	[EmpID] [varchar](50) NULL,
	[UserID] [varchar](50) NULL,
	[ManagerID] [varchar](50) NULL,
) ON [PRIMARY]

INSERT INTO Job VALUES(1,'TopDog',1)
INSERT INTO Job VALUES(2,'emil',1)
INSERT INTO Job VALUES(3,'katie',1)

Open in new window

0
 
itcoupleAuthor Commented:
BTW the schema can change so changing varchar to int is not a problem and also adding EMPID to Job table is also not a problem.
0
 
PedroCGDCommented:
Job is teh Fact and Employee the Dimension?
0
 
itcoupleAuthor Commented:
yes exactly
0
 
PedroCGDCommented:
I suggest you first to create a SURROGATE KEY for you dimension in spite of using varchar operacional ID
I'll do an example

0
 
itcoupleAuthor Commented:
Sounds like a way to do it.... but could you bring more light on the function of Surrogate key? I've come across them in the past as a subject but never in practise....yet :)
0
 
PedroCGDCommented:
Surrogate key as a key created in data warehouse and independent from operational systems.

Example:
CREATE TABLE [dbo].[Job](
        [Job] [int] NOT NULL,
        [User_dimkey] INT NOT NULL,
        [PrintedPages] [numeric](20, 0) NOT NULL,
) ON [PRIMARY]


CREATE TABLE [dbo].[Employee](
        [User_dimkey] INT NULL,
        [EmpID] [varchar](50) NULL,
        [UserID] [varchar](50) NULL,
        [ManagerID] [varchar](50) NULL,
) ON [PRIMARY]



0
 
itcoupleAuthor Commented:
Hi That makes sense. Does it mean my EmpID can exist several times in the same table? My previous understanding was that surragate keys have something to do with multiple records for the same person but if that is simply identity primary key than it makes it much easier for me :)

Regards
Emi
0
 
PedroCGDCommented:
Surrogate keys are used as best practice and for slowly changing dimensions type 2 (Kimball approach)
0
 
itcoupleAuthor Commented:
I'm not very familiriad with this stuff.... yet. I think I will leave it for another time. Just start with basics ;p
0
 
itcoupleAuthor Commented:
Finally I made it!!! I had to buy SSAS step by step book and link fact table with dimension based on empID not username which probably was the issue as mentioned in the begining and then it was just a matter of removing and adding dimension and use default values. It does it automatically if there is self-join.

Many thanks for all your time and suggestions... I learned a few new basic stuff :p


0
 
PedroCGDCommented:
Fell free to ask me more thinks! Next week I'll have more time to help you into deep detail!
Regards,
Pedro
0
 
itcoupleAuthor Commented:
Thanks Pedro... I have ton of questions so I will definetelly post new questions.... probably how to generate custom Fiscal Calendar which don't follow always the same pattern.... but that will be in a while as I have script to do normal so should keep me going for now :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 11
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now