How to build Dimension Tables around Fact Table?

I am working on a CUBE built from a SQL Server 2008 environment.

I have a FACT table with these columns:

UserId
ServerName
DateTime
BytesReceived
BytesSend
Region

Open in new window

And few other columns. Fact table is build from join query written in a view.

I am having trouble on building dimensions.

If I have dimensions for Month, ServerName, Region, Year, Time - Cube Process failed.

For dimensions, every view will have UserId as the key column.

For example :

DimServerName:

UserId
ServerName
What could be the reason?

How to have Multidimensions for my Fact table.

As the  FactTable should be in StarSchema Relation. UserId column will build the relation for DImension Tables to the Fact Table.
So i kept the UserId column to all the Dimension Table.

This breaks the Table Distinct Output. Though User Id is unique, Month is not unique ..
chokkaStudentAsked:
Who is Participating?
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.

YZlatCommented:
I am not sure exactly what are you asking. And what do you mean by your cube process failing? Usually you build dimensions and fact tables first and then build cubes.


If you are just building the dimensions, I'd put Month and Year in the same dimension called DimDate

As for ServerName, I would not put UserdID into the DimServerName as it would call for snowflake schema while it is best to stick to star schema not to overcomplicate things. I'd suggest:

DimServer
-----------------
ServerID
ServerName

then I'd have User dimension:

DimUser
-------------
UserID
UserName


and

DimDate
-------------
Date
DateName
Month
Quarter
Year

DimGeography
-----------------------
GeoID
Country
Region
State
City
0
chokkaStudentAuthor Commented:
@YZlat, Yes you are right !! I have to stick with Star Schema.

For DimServer, I don't have ServerId - Can ServerName act as Id Column ??

And also for DimGeography, I don't have GeoId - Can Country act as Id Column ??
0
YZlatCommented:
I just gave you an example, you can build those dimensions with the fields you have. Although I would not make Country a key field for DimGeography. You could create an ID for that dimension as an integer field for ease of use

I suggest you create surrogate keys for DimServer and DimGeography or DIMLocation dimensions

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/dimension-surrogate-key/


Btw, I think DimGeorgraphy should be DimLocation:

DimLocation
________________
LocationID (surrogate integer key)
RegionName
Country
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

YZlatCommented:
Overall I'd suggest something like this:

DimUser
__________
UserID int primary key
UserName VARCHAR(50)

DimDate
____________
[DateKey] INT primary key 
[Date] DATETIME
[DayName] VARCHAR(9) -- Contains name of the day, Sunday, Monday 
[Month] VARCHAR(2) --Number of the Month 1 to 12
[MonthName] VARCHAR(9)--January, February etc
[Quarter] CHAR(1)
[QuarterName] VARCHAR(9)--First,Second..
[Year] CHAR(4)-- Year value of Date stored in Row
[YearName] CHAR(7) --CY 2012,CY 2013
[IsHolidayUSA] BIT-- Flag 1=National Holiday, 0-No National Holiday
[IsWeekday] BIT-- 0=Week End ,1=Week Day


DimServer
_____________
ServerID int identity(1,1) primary Key
ServerName VARCHAR(50)

DimLocation
_____________
LocationID int identity(1,1) primary key
LocationRegion
LocationCountry

and your fact table would be something like that:

UserID
ServerID
DateTime
BytesReceived
BytesSent
LocationID

Open in new window

0
chokkaStudentAuthor Commented:
@YZlat   - Great, Thank you !!
0
chokkaStudentAuthor Commented:
One more clarification

In the Dimension Table - DimServer , as of now I have only ServerName. I can add serverid by including Identity column.

Does the FactTable has to be redesigned in such a way that ServerId has to be picked instead of ServerName ??
0
YZlatCommented:
I am afraid so. Can you redesign it?
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
chokkaStudentAuthor Commented:
Thanks very helpful
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.