Building Fact - Dimention table

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

Hello All ,

Here is my requirement ,

I have below tables in my OLTP database.

Employee
ID BIGINT
Name NVARCHAR

Leaves
EmpId BIGINT
Days NVARCHAR
LeaveType NVARCHAR [Exp SL,CL,PL,ML]

and from the above data we are builing below reports

EmpID	EmpName	LeaveDays
01	ABC	23
02	DEF	25

Empid	EmpName	SL	CL	PL
01	ABC	10	13	0
02	DEF	20	0	5

Please let me know how can I build Fact and Dimenstion table and how can I use SSAS to build the cube.

Posted 12-Mar-12 21:25pm

RDBurmon

Updated 14-Mar-12 0:47am

v5

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mirzakhmet Syzdykov · Accepted Answer · 2012-07-16T20:53:00

If just to create new dimension/fact tables probably archiving the @Employee and @Leaves. It's possible to make it manually:

SQL

DIMENSION_1	=	EMPLOYEE:
	DIMID	BIGINT;
	ID	BIGINT;
	NAME	NVARCHAR

DIMENSION_2	=	LEAVE
	DIMID	BIGINT
	TYPE	NVARCHAR

FACT_TABLE	=	FACT TABLE
	[Key: DIMENSION_1.DIMID] [Key: DIMENSION_2.DIMID] [Days - NUMBER];

Then fill the tables above and recreate foreign key constraints:

SQL

INSERT INTO DIMENSION_1 (DIMID, ID, NAME)
SELECT ROW_NUMBER() OVER (ORDER BY ID), ID, NAME
FROM
(
	SELECT
		ID, MAX(NAME) NAME
	FROM
		Employee
	GROUP BY
		ID
)


INSERT INTO DIMENSION_2 (DIMID, TYPE)
SELECT ROW_NUMBER() OVER (ORDER BY TYPE), TYPE
FROM
(
	SELECT
		DISTINCT LeaveType TYPE
	FROM
		Leaves
)


INSERT INTO FACT_TABLE (KEY_1, KEY_2, DAYS)
SELECT
	DA.DIMID, DB.DIMID, SUM (CONVERT(BIGINT, B.DAYS)) DAYS
FROM
	Employee A,
	Leaves	B,
	DIMENSION_1	DA,
	DIMENSION_2	DB
WHERE
	A.ID = B.EmpId /*(+)*/
	AND A.ID = DA.ID
	AND B.LeaveType = DB.TYPE
GROUP BY
	DA.DIMID, DB.DIMID

SSAS: probably it's possible to create a new cube defining it's attributes as already processed objects.