OLAP Cube / Employee Dimension / Hierarchy

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

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

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

https://www.experts-exchange.com/questions/25330595/How-do-I-create-a-parent-child-dimension-in-Analysis-Services-2008.html

I belive you can find also something in Adventure Works samples.

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.

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

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)

Select allOpen in new window

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.

Job is teh Fact and Employee the Dimension?

yes exactly

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 :)

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]

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

Surrogate keys are used as best practice and for slowly changing dimensions type 2 (Kimball approach)

I'm not very familiriad with this stuff.... yet. I think I will leave it for another time. Just start with basics ;p

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

Fell free to ask me more thinks! Next week I'll have more time to help you into deep detail!
Regards,
Pedro

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 :)