Fact Table Question

Question

Post reply

Fact Table Question

WWL

SSC Enthusiast

Points: 180
More actions
April 10, 2008 at 11:21 am

#184353

This may be an extremely beginner's question but I'm stuck. Basically, I want to know how to design a fact table from a source table.
My source table comes in with the following column heading:
Date
Customer
Location
Product
Officer
then a ton of Financial Columns like Balances, Revenues, Expenses, etc.
My goal is to get these Financial Column headings into Rows so I can then do a lookup and assign KPI ID from my KPI Dimension.
Eventually I would like my Fact table to look like:
Date key
Customer Key
Location Key
Product Key
Officer Key
KPI Key
Amount
How do I go about changing my source table so I can assign KPI keys to Financial Columns? What am I missing?
Thanks for any help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lookup_BI SSCrazy Points: 2964 More actions · Answer 1

Hi WWL,

I would starting by creating several dim table's[Dimension].For example

a Time Dimension Table with columns [ Time id, Month,Year, Quater...],

a Location Dimension Table with columns [Location ID, Address,City,State,Zip],Office Dimension Table [Office ID, Name,Address].These Dimension tables would need to be prepopulated before you can start loading the fact table.You would also need to decide the schema [snow flake or star schema], the relationships between dimension and source tables.You would also need to decide which SSIS transformation to use[to populate fact tables,while grabbing data from Dim tables].Hope this gives you a brief idea.

Here are a few good sites

http://www.sqlservercentral.com/articles/Design/2769/

http://www.sqlservercentral.com/articles/Design/2657/

[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

WWL SSC Enthusiast Points: 180 More actions · Answer 2

First off thanks for the reply!!!

I've got my Dimension Tables setup and in the Database and I understand how to load the keys into the fact table. My issue comes when I start looking at the KPIs(measures) Below is how my source table looks without the other column metrics (customer, product, location, officer, etc)

Avg_Bal End_Bal Interest_Amount Expense_Amount Other_Revenue etc etc etc

100000 80000 6000 300 50 25 10 75

I guess I just create multiple SQL to drop these in a Row format (instead of the current Column format) and assign my KPI Keys in the SQL???

Thanks again

Neelakandan V-453229 Ten Centuries Points: 1109 More actions · Answer 3

Hi,

Not sure what you mean by KPI key. The fact as such can contain only dim keys and facts. You already have the dim keys in your fact table and you can simply bring the facts (like balance, expenses, etc) and create them as measures in the cube.

Am I clear or am I over-simplifying it?

Regards,

Vijay.

Warm Regards,
Neel aka Vijay.

sgmunson SSC Guru Points: 110640 More actions · Answer 4

How about using SQL Server 2005's UNPIVOT operator? See if something like the following might help:

SELECT [Date] AS DateKey, Customer AS CustomerKey,

Location AS LocationKey, Product AS ProductKey,

Officer AS OfficerKey, KPI_Type, KPI_Amount

FROM SOURCE_TABLE

UNPIVOT (KPI_Amount FOR KPI_Type IN (

[KPI Field 1],[KPI Field 2], etc... )) AS UNPVT

The [KPI Field n] field names would come from the actual KPI field names in your source table, and those field names would then become the actual data for the new column KPI_Type. I think that's what you're looking for.

Steve

(aka smunson)

:):):)

WWL (4/10/2008)
This may be an extremely beginner's question but I'm stuck. Basically, I want to know how to design a fact table from a source table.
My source table comes in with the following column heading:
Date
Customer
Location
Product
Officer
then a ton of Financial Columns like Balances, Revenues, Expenses, etc.
My goal is to get these Financial Column headings into Rows so I can then do a lookup and assign KPI ID from my KPI Dimension.
Eventually I would like my Fact table to look like:
Date key
Customer Key
Location Key
Product Key
Officer Key
KPI Key
Amount
How do I go about changing my source table so I can assign KPI keys to Financial Columns? What am I missing?
Thanks for any help

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)