asked on

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 ..

YZlat

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

chokka

ASKER

@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 ??

YZlat

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

YZlat

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

chokka

ASKER

@YZlat - Great, Thank you !!

chokka

ASKER

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 ??

ASKER CERTIFIED SOLUTION

YZlat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chokka

ASKER

Thanks very helpful