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
LVL 10
itcoupleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.